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:

1

2

3

4

5

varmySQLClient=require('mysql-js');

varproperties=newmySQLClient.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.

MySQL

1

2

3

4

5

CREATETABLEauthor(

user_namevarchar(20)NOT NULLPRIMARY KEY,

full_namevarchar(250),

postsintunsignednot nulldefault0

)ENGINE=ndbcluster;

Insert

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

JavaScript

1

2

varcraig={'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.]

1

2

varcraig={'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.

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

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.

JavaScript

1

2

3

4

5

6

7

8

9

10

11

12

13

functionAuthor(name,full_name){

if(name)this.user_name=name;

if(full_name)this.full_name=full_name;

}

Author.prototype.getNumberOfPosts=function(){

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

1

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

JavaScript

1

2

varcraig=newAuthor('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.

1

2

3

varcraig=newAuthor('clr','Craig Russell');

craig.posts=100;

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

Find

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

1

2

3

4

5

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

1

2

3

varkey=newAuthor('clr');

varchanges=newAuthor('','Craig L. Russell');

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

Delete

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

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:

1

2

3

4

5

6

7

8

9

10

// find an object

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

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

functionreportSuccess(){

session.sessionFactory.close();

console.log('All done.');

exit(0);

}

functionreportError(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

Post navigation

2 thoughts on “The JavaScript Connector for MySQL you never heard of”

Hi, question: if i DO know SQL, would there still be some other benefits to using this connector? Is there a connector based on SQL? PS, Oracle’s keyword for ‘upsert’ is MERGE, why don’t you use that? It seems good to me. i feel sorta the same as you about the term “upsert”– i find it upserting.

The benefits of this connector are not just to avoid SQL, but to allow very flexible domain models in the JavaScript environment. You can map multiple tables using joins to a single domain object. You can define multiple mappings based on different parts of your application. Take a look at Projection for examples. Another benefit is possible higher performance. You can avoid using the mysql server entirely and interact directly with the cluster as an application node.

There are open source connectors that are low level and require SQL. In fact, we use one of these for the low level access to the database. It’s known as the node-mysql connector and is on github.

I like your idea of merge as the function to insert/update. Thanks for the feedback.