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

nk-mysql v0.4.x (Stable)

Node.js <= v0.12.x:

nk-mysql v0.2.x (Stable; Deprecated)

Node.js <= v0.10.x:

nk-mysql development (Testing/Unstable)

Node.js <= v0.12.x:

Regular builds and testing provided by travis-ci.org.

• Binaries are not supplied to Windows users for Node.js v0.12.x , you must build this package at this time (Binaries will be provided at a later date). [1]
• If for any reason you have an issue when utilizing Node.js v0.10.x, beyond submitting a bug report; please try installing nk-mysql using the following: npm install nk-mysql@node-v10. [2]
• MySQL Connector C++ is now at v1.1.5. Changes since v1.1.3 included a forced static binding to libmysql for the connector.
• nk-mysql may also be used with mysql-cluster.
• MySQL Cluster now supports both traditional interfacing, as well as noSQL. Oracle has been working on a Memcached entry point for MySQL Cluster, which boasts the speed and efficiency found with noSQL based database systems; yet for both traditional and noSQL based transactions.
• In other MMOD project; namely nk-mvc and nk-xrm - we are expanding upon the data tools to enable support for both developer and designer. Our aim is to introduce a truly innovative approach, not only to agile development via our original MESSSE Code Methodology - but also to product development in general.

Notes:

[1] Binaries are supplied to Windows users utilizing Node.js v0.10.x, and are enabled by default for Windows users who do not have build tools installed (i.e. node-gyp).

[2] Support for Node.js v0.12.x is now included for all platforms, and this singular package is backwards-compatible with Node.js v0.10.x as well.

Full and complete installation documentation can be found at the nodakwaeri wiki @ Github

There are a few prerequisites that need to be in place in order to properly build nodamysql. On Windows, a precompiled binary is usually available but not always at this point. If you are familiar with nodakwaeri, and are sure you meet the requirements to build then:

To install (installing is how we build too!); open a terminal/shell/command tools/prompt and browse to the root of your application. Run the following command:

path_to_application/> npm install nk-mysql

If you're not building (meaning node-gyp and python are not installed), but meet the requirements for a precompiled add-on; a fall-back solution should be loaded for you.

At this time, examples may be found in the nodakwaeri wiki @ Github and nodamysql repository @ Github, though I'll leave this documentation with a copy of the wiki content:

Including nodamysql into your project is relatively simple:

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

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

<br />
#### Insert, Update, Delete (No resultsets)
```node
// 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 );
}

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.

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

// And view our results for( var recs in records ) { console.log( 'Query results: ' + recs['id'] + ', ' + recs['title'] + ', ' + recs['description'] + '.' ); }

<br />
##### A more involved Select
```node
// 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();     
...

var whereVals = { title: [ '=', '' ] };

var records = db.select( 'col1, col2, col3, table2Name.col1 as fakeCol from tableName' ) .join( 'table2Name' ) .on( 'table2Name.id = tableName.id' ) .where( whereVals ) .execute();
...

<br />
#### Update

##### The basic example
```node
// 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();                     
...

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

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

<br />
##### The alternate advanced example
```node
// 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.
...

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

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.

Feel free to fork the repository and submit pull requests. Browse any of our other repositories as well MMOD @ Github.

You may also contribute by making a donation

Eclipse Luna

Nodeclipse (Eclipse Marketplace, site)

Node.js