MySQL :: The JavaScript Connector for MySQL you never heard of

It’s easier now than ever before to stand up a web server and connect it to a database using node.js and Express. Using node.js to get data out of a relational database used to require that users become well versed in SQL technology.

So the MySQL dev team started on a project to make it trivial (well, easy) to store and retrieve documents from MySQL and deliver them to node.js applications. Without using SQL. The result is called mysql-js and I’d like to introduce it to you. You can download it today at github.

The project started by looking at storing JSON documents that were relatively simple: each property of the document would be stored in a column in the database. This model allows storing simple documents in a way that could take full advantage of the relational data model. But storing complex properties was non-trivial.

So we developed a technique by which complex properties (e.g. arrays, nested JavaScript objects) would be serialized into JSON before being stored. When retrieving these properties from the database, the serialization process would be reversed and the original property would be restored.

With this change, we could store homogeneous documents that had a well-defined structure. But tables that stored documents with extra properties were still problematic. So we introduced a column that would store all properties that didn’t have their own column in the database.

With this data model in place, we concentrated on the data access APIs. We started with the basics: insert and delete. Since tables have a primary key column, which has a corresponding property in the document, storing a document is as simple as identifying which table it belongs to and writing it out.

Deleting documents is simple enough. Just identify the document you want to delete by its primary key or a unique key.

Finding documents once you store them requires that you identify the document by its primary or unique key. Queries of arbitrary properties that might return multiple documents is obviously more complex so we treated that as a separate issue.

Updating documents requires two things: identifying which document to update by specifying its primary key or a unique key, and specifying which properties to change.

The main user interface to the connector is the Session. In the MySQL architecture, a session is a server artifact that is responsible for accessing data on behalf of a user, receiving requests and delivering results. In the JavaScript connector, we use a local session to which you make requests, which the connector then forwards to the server session.

Code examples

Getting a session is straightforward. If you know the connection properties for your MySQL server, you can construct a regular JavaScript object and then ask the session service for a session to use. If your server uses the standard host and port, you can use the defaults that the connector already knows about. If you want to add credentials, add them to the connection properties:

var mySQLClient = require('mysql-js');
var properties = new mySQLClient.ConnectionProperties('mysql');
properties.user = 'user1';
properties.password = 'sekrit';
mySQLClient.openSession(properties, onOpenSession);

var mySQLClient = require('mysql-js');

var properties = new mySQLClient.ConnectionProperties('mysql');

properties.user = 'user1';

properties.password = 'sekrit';

mySQLClient.openSession(properties, onOpenSession);

The session is delivered to you in a callback. Callbacks follow the node.js convention, which have at least two parameters: err and data. In this case, data is the session object that you use to interact with the database. The connector also supports Promises/A+ (more about that later).

CRUD Operations

Session supports CRUD operations, queries, and some utility functions. CRUD operations deal with one row in the database and one object in JavaScript. You can create objects via either JSON literal notation (created directly via the ‘{name: value}’ syntax) or constructor, allowing you to take advantage of object-oriented programming.

For the following examples, assume a table exists in the default database.

CREATE TABLE author (
  user_name varchar(20) NOT NULL PRIMARY KEY,
  full_name varchar(250),
  posts int unsigned not null default 0
) ENGINE=ndbcluster;

CREATE TABLE author (

user_name varchar(20) NOT NULL PRIMARY KEY,

full_name varchar(250),

posts int unsigned not null default 0

) ENGINE=ndbcluster;

Insert

To insert a row in the author table, use the insert function.

var craig = {'user_name': 'clr', 'full_name': 'Craig Russell'};
session.insert('author', craig, onInsert);

1 2	var craig = {'user_name': 'clr', 'full_name': 'Craig Russell'}; session.insert('author', craig, onInsert);

Since the posts column has a default, it does not need to be included in the insert data.

Insert or Update

To insert a row in the author table, or update the row if it already exists, use the save function. This corresponds to the SQL clause ON DUPLICATE KEY UPDATE [Some APIs use the term “write” but this doesn’t have much semantic content. Other APIs call this “upsert” but we found this term disturbing. We considered “indate” but that didn’t seem to help.]

var craig = {'user_name': 'clr', 'full_name': 'Craig Russell', posts:100};
session.save('author', craig, onSave);

1 2	var craig = {'user_name': 'clr', 'full_name': 'Craig Russell', posts:100}; session.save('author', craig, onSave);

Find

To find a single row in the database, use the find function. The key is a primitive that is the full primary key, an object whose properties include the primary key, or an object whose properties include a unique key.

function onFound(err, row) {
 // error handling omitted for clarity
 // prints {'user_name': 'clr', 'full_name': 'Craig Russell' 'posts': 100}
  console.log(row); 
}
// find(tableName, key, callback)
session.find('author', 'clr', onFound);

function onFound(err, row) {

// error handling omitted for clarity

// prints {'user_name': 'clr', 'full_name': 'Craig Russell' 'posts': 100}

console.log(row);

}

// find(tableName, key, callback)

session.find('author', 'clr', onFound);

Update

To update a single row in the database, use the update function. The key is used only to uniquely identify the row; the value is used only to update the properties specified.

// update(tableName, key, value, callback)
session.update('author', 'clr', {'full_name': 'Craig L Russell'}, onUpdate);

1 2	// update(tableName, key, value, callback) session.update('author', 'clr', {'full_name': 'Craig L Russell'}, onUpdate);

Delete

To delete a single row in the database, use the delete function. [We aliased the t function with the remove function in case you don’t like your IDE telling you about your use of the delete keyword in an unexpected context.]

// delete(tableName, key, callback)
session.delete('author', 'clr', onDelete);

1 2	// delete(tableName, key, callback) session.delete('author', 'clr', onDelete);

Using Constructors

You might want to use constructors to better organize your application. We support the Domain Model pattern elaborated in Martin Fowler’s excellent reference, Patterns of Enterprise Application Architecture. In this case, define the constructor as you wish and use it (or instances of it) in the session operations.

function Author(name, full_name) {
  if (name) this.user_name = name;
  if (full_name) this.full_name = full_name;
}
Author.prototype.getNumberOfPosts = function() {
  return this.posts;
}
Author.prototype.toString = function() {
  return ((this.posts > 100)?'Esteemed ':'') + 
      'Author: ' + this.name + 
      ' Full Name: ' + this.full_name + 
      ' posts: ' + this.posts;
}

function Author(name, full_name) {

if (name) this.user_name = name;

if (full_name) this.full_name = full_name;

}

Author.prototype.getNumberOfPosts = function() {

return this.posts;

}

Author.prototype.toString = function() {

return ((this.posts > 100)?'Esteemed ':'') +

'Author: ' + this.name +

' Full Name: ' + this.full_name +

' posts: ' + this.posts;

}

There’s just one thing extra to do when using constructors. Currently, the constructor is annotated with the name of the table to use to store the data. We’re working on allowing the table name to default to the constructor function name.

new mySQLClient.TableMapping('author').applyToClass(Author);

1	new mySQLClient.TableMapping('author').applyToClass(Author);

Insert

To insert a row in the author table, use the insert function. Since you are using a constructor, there is no need to name the table. The connector will use the table in the TableMapping.

var craig = new Author('clr', 'Craig Russell';
session.insert(craig, onInsert);

1 2	var craig = new Author('clr', 'Craig Russell'; session.insert(craig, onInsert);

Since the posts column has a default, it does not need to be included in the insert data.

Insert or Update

To insert a row in the author table, or update the row if it already exists, use the save function.

var craig = new Author('clr', 'Craig Russell');
craig.posts = 100;
session.save('author', craig, onSave);

var craig = new Author('clr', 'Craig Russell');

craig.posts = 100;

session.save('author', craig, onSave);

Find

To find a single row in the database, use the find function.

function onFound(err, row) {
  // prints Author: clr Full Name: Craig Russell posts: 0
  console.log(row);
}
session.find(Author, 'clr', onFound);

function onFound(err, row) {

// prints Author: clr Full Name: Craig Russell posts: 0

console.log(row);

}

session.find(Author, 'clr', onFound);

Update

To update a single row in the database, use the update function.

var key = new Author('clr');
var changes = new Author('', 'Craig L. Russell');
session.update(Author, key, changes, onUpdate);

var key = new Author('clr');

var changes = new Author('', 'Craig L. Russell');

session.update(Author, key, changes, onUpdate);

Delete

To delete a single row in the database, use the delete (or remove) function.

session.delete(Author, 'clr', onDelete);

1	session.delete(Author, 'clr', onDelete);

Promises

When using callbacks and JavaScript (in other words, when using node.js in the normal way) error handling code can obscure your application code. Promises are a way of writing much cleaner code because error handling is abstracted.

For example, code with error handling might look like this:

// find an object
function onSession(err, s) {
  session = s;
  if (err) {
    console.log('Error onSession:', err);
    process.exit(0);
  } else {
    session.find('Author', 'clr', onFindByTableName);
  }
};

// find an object

function onSession(err, s) {

session = s;

if (err) {

console.log('Error onSession:', err);

process.exit(0);

} else {

session.find('Author', 'clr', onFindByTableName);

}

};

There is only one line of actual code but it’s lost in the error handling.

Instead, using promises, you can write code like this. Each function called out in the then function will be executed in sequence.

var session;

function exit(code) {
  process.exit(code);
}
function setSession(s) {
  session = s;
}
function insertByConstructor() {
  return session.insert(Author, new Author('sam', 'Sammy Snead'));
}
function insertByTableName() {
  return session.insert('author', 
    {user_name: 'char', full_name: 'Charlene LeMain'});
}
function saveByConstructor() {
  return session.save(Author, new Author('sam', 'Sammy Snead'));
}
function saveByTableName() {
  return session.save('author', 
    {user_name: 'char', full_name: 'Charlene LeMain'});
}
function findByConstructor() {
  return session.find(Author, 'sam');
}
function findByTableName() {
  return session.find('author', 'char');
}
function updateByConstructor() {
  return session.update(Author, 'sam', 
    new Author(null, 'Samuel K Snead'));
}
function updateByTableName() {
  return session.update('author', 'char', 
    {full_name: 'Charlene K LeMain'});
}
function deleteByConstructor(sam) {
  return session.delete(Author, 'sam');
}
function deleteByTableName(char) {
  return session.delete('author', 'char');
}
function closeSession() {
  return session.close();
}
function reportSuccess() {
  session.sessionFactory.close();
  console.log('All done.');
  exit(0);
}
function reportError(e) {
  console.log('error:', e);
  exit(1);
}

mySQLClient.openSession(dbProperties, null)
.then(setSession)
.then(insertByTableName)
.then(insertByConstructor)
.then(saveByTableName)
.then(saveByConstructor)
.then(updateByTableName)
.then(updateByConstructor)
.then(findByTableName)
.then(deleteByTableName)
.then(findByConstructor)
.then(deleteByConstructor)
.then(closeSession)
.then(reportSuccess, reportError);

function reportSuccess() {

session.sessionFactory.close();

console.log('All done.');

exit(0);

}

function reportError(e) {

console.log('error:', e);

exit(1);

}

mySQLClient.openSession(dbProperties, null)

.then(setSession)

.then(insertByTableName)

.then(insertByConstructor)

.then(saveByTableName)

.then(saveByConstructor)

.then(updateByTableName)

Promises requires that the implementation (the mysql-js connector) define a then method that takes two arguments: a function called after the asynchronous operation succeeds and returns a single value; and a function called only if the asynchronous operation fails and throws an exception. The application can use the then function to organize asynchronous functions in many ways, including error handling.

There are many more topics to discuss, including the use of transactions for guaranteed ACID properties, multiple back-end storage, complex queries, complex document storage and retrieval, and joining document-oriented tables with normalized relational tables. Stay tuned.

Fork it at Github

https://github.com/mysql/mysql-js