-
Notifications
You must be signed in to change notification settings - Fork 4
Home
A mostly simple, yet powerful C++ data integration toolset for nodakwaeri (nk) or other software(s) which would make use of it.
Licensed under Apache v2.0, nodamysql features the MySQL C++ Connector from Oracle - which is licensed according to the terms provided under the FOSS License Exception when used with nodakwaeri (nk) or other Free and Open Source Software sporting acceptable license types.
nodamysql (nk-mysql) is designed to use Prepared Statements - and to allow implicit asynchronous usage - in order to help keep your application secure and efficient.
| OS | Documentation |
|---|---|
| Linux | Installing nodamysql on Linux |
| Windows | Installing nodamysql on Windows |
| Mac & SunOS | Sorry, but I do not own a Mac or run SunOS; Please get in touch with me or submit a pull request if you'd like to help support Mac or SunOS. |
Including nodamysql into your project is relatively simple:
// Require our type
var nkmysql = require( 'nk-mysql' ),
driver = nkmysql.driver;
...nodamysql supports the use of models when using prepared statements. Models help to automate select and insert clause statement creation, and are most commonly used with - and provided by - nk-mvc's model system. Models cannot be used with regular statements (unprepared), or with UPDATE, DELETE, CREATE, and advanced SELECT queries when using prepared statements.
/*
If you are opting to use a model, you would define one like the one below.
Models are not required at all. The purpose of specifying the type and whether
a column is a key or required, is to provide necessary information to some
advanced tools coming soon, such as the migration and code-first support tools
for nodamysql and nk-mvc.
*/
var fakeModel =
{
id: [ 'key', 'type', 'Record Id' ],
title: [ false, 'type', 'Record Title' ],
description: [ false,'type', 'Record Description' ]
};
// Create a config, which contains our connection details and/or model if needed
// (you can leave the model property out altogether).
var config =
{
host: 'localhost',
port: '3306',
database: 'nktest',
username: 'nktestadm',
password: '^NKTestPass777$',
//model: fakeModel
};
// And get us a dbo
var db = new driver( config );
...Unprepared Statements are Directly Executed Queries. This is not recommended for situations where queries are reused with the same parameters containing only differing values. There are several good reasons for this, all can be read up on here: http://dev.mysql.com/doc/refman/5.7/en/c-api-prepared-statements.html
...
// Let's run a simple query then
var records = db.query( 'SELECT * FROM tableName' ).execute();
// And view our results.
for( var recs in records )
{
console.log( 'Query results: ' + recs['id'] + ', ' + recs['title'] + ', ' + recs['description'] + '.' );
}// If we do not want to instantiate another db object after running a query, we can reset the one we have once
// we're done so that we can reuse it:
db.reset();
...
// Let's run another simple query then
var affected = db.query( "UPDATE tableName SET columnName='value' WHERE id='1'" ).execute();
// And view our results.
console.log( 'Affected Rows: ' + affected );
}Keep in mind that Create and Drop queries will always return 0, unless - and only in the case of a Drop query - there are records in the table at the time it is dropped, you may see the number of rows instead as a return result.
More examples of regular statements can be seen in the test file here.
Prepared statements are the recommended way to execute queries in any application when queries are reused over and over as they may provide a performance benefit as well as a security benefit. The link given above under Regular Statements to MySQL explains why this is.
/*
If you are running a select query, you might have passed a model to the driver's constructor, so that we know
ahead of time what to select. You can set a new model at any time by invoking .reset( newModel );
Alternatively, you can invoke .reset( false ) to clear the currently set model, or .reset() to leave the model
situation as is.
*/
db.reset( modelForExample );
...
// Let's run a simple select query
var records = db.select( 'tableName' ).execute();Otherwise, if no model we would specify the selection
db.reset( false );
...
var records = dbo.select( 'col1, col2, col3 from tableName' ).execute();
// And view our results
for( var recs in records )
{
console.log( 'Query results: ' + recs['id'] + ', ' + recs['title'] + ', ' + recs['description'] + '.' );
}// Let's run a more involved select query, we're going to use a model so we do not need to write out the full
// selection, and add a join and where clause as well.
db.reset( modelForExample );
...
var whereVals =
{
title: [ '=', '<titleValue>' ] // You can replace the operator and <titleValue>....
};
var records = db.select( 'tableName' )
.join( 'table2Name' )
.on( 'table2Name.id = tableName.id' )
.where( whereVals )
.execute();
...// Let's run an advanced select query, we're not going to use a model so we do need to write out the full
// selection
db.reset( false );
...
var whereVals =
{
title: [ '=', '<titleValue>' ]
};
var records = db.select( 'col1, col2, col3, table2Name.col1 as fakeCol from tableName' )
.join( 'table2Name' )
.on( 'table2Name.id = tableName.id' )
.where( whereVals )
.execute();
...// Let's run an update query, no model is used here
db.reset( false );
...
var updateVals =
{
accessLevel: 3,
description: 'Updating the description.'
},whereVals =
{
title: [ '=', '<titleValue>' ] // You should replace the operator and <titleValue>....
};
var records = db.update( 'tableName' ) // Records contains the number of rows affected.
.values( updateVals )
.where( whereVals )
.execute();
...// Let's run an insert query, no model is used here
...
var insertVals =
{
title: 'titleVal',
description: 'Description.'
};
var records = dbo.insert( 'tableName' ) // Records contains the number of rows affected.
.values( insertVals )
.execute();
...// Let's run an insert query, no model is used here and we're inserting multiple values at once
db.reset( false );
...
var records = db.insert( 'tableName' ) // Records contains the number of rows affected.
.values
([
{ accessLevel: 3, description: 'Inserting value 1' },
{ accessLevel: 3, description: 'Inserting value 2' },
{ accessLevel: 3, description: 'Inserting value 3' },
])
.execute();
// The query was just sent to the server once, and we sent all the sets of parameters separately
// in a loop via binary transfer to be executed. This is part of the beauty of prepared statements.
...// Let's run an insert query, no model is used here and we're inserting multiple values at once, but doing so
// in a way that if not for fun; would otherwise seem pointless and counter-productive
db.reset( false );
...
var records = db.insert( 'tableName' ) // Records contains the number of rows affected.
.values( { accessLevel: 3, description: 'Inserting value 1' } )
.values( { accessLevel: 3, description: 'Inserting value 2' } )
.values( { accessLevel: 3, description: 'Inserting value 3' } )
.execute();
// Although not as efficient with the entire process as the last example, like above we only sent the query to
// the server once - and then sent all the sets of parameters separately one at a time in a loop via
// binary transfer to be executed. This is part of the beauty of prepared statements.
...// Let's run an delete query, no model is used here.
db.reset( false );
...
var whereVals =
{
title: [ '=', '<titleValue>' ] // You should replace the operator and <titleValue>....
};
var records = db.delete( 'tableName' ) // Records contains the number of rows affected.
.where( whereVals )
.execute();
...As you've seen thus far, models are used only to automate select and insert clause creation as far as nk-mysql is concerned - and are never required. They are used a bit more extensively by nodakwaeri, and are planned to be used for more advanced data tools as well.
Aside from keeping in mind that Limit clauses should always be at the end of a query statement, and that you should invoke members of the database object in the order you would typically write out a query manually; there are some tools in place to help out:
For example, you cannot invoke.join() before setting a clause (such as .select, .insert, or .delete)
- .on(), .where(), .values(), .order(), .limit(), .execute(), .executeQuery() all follow similar common-sense rules as .join() to help avoid SQL errors.
...
console.log( db.getConnection() );
......
console.log( db.getQuery() );
...