nk mysql

nodamysql (nk-mysql)

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, nk-mysql 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.

nk-mysql is designed to use Prepared Statements - and to allow implicit asynchronous usage - in order to help keep your application secure and efficient.

Installation

OS	Documentation
Linux	Installing nk-mysql on Linux
Windows	Installing nk-mysql 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.

Usage

Preparing our DBO

Including nk-mysql into your project is relatively simple:

// Require our type
var nkmysql = require( 'nk-mysql' ),
	driver = nkmysql.driver;
...

nk-mysql 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 nk-mysql 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 );
...

Regular Statements (Unprepared)

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

Select

...

// 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'] + '.' );
}

Insert, Update, Delete (No resultsets)

// 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

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.

Select

Simple Select

/* 
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'] + '.' );
}

A more involved Select

// 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();		
...

An advanced example

// 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();		
...

Update

The basic example

// 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();						
...

Insert:

The simple example

// 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();			
...

The advanced example

// 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.
...

The alternate advanced example

// 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.
...

Delete:

The simple example

// 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();
				
...

Models

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 (nk), and are planned to be used for more advanced data tools as well.

Additional Notes & Troubleshooting

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:

Logical Implementation

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.

Getting Connection Info

...
console.log( db.getConnection() );
...

Reviewing the Query

...
console.log( db.getQuery() );
...