As you may have heard, MySQL 8.0 is now officially GA, and it comes with a bunch of additional goodies. Among those is the brand new Connector/Node.js, which is the official MySQL driver for Node.js and, currently, the only one with support for the latest server versions and features (such as the MySQL document store).
Here’s a rundown of what’s available:
- Out-of-the box support for MySQL 8.0
- Document-store API as a first-class citizen
- TLS/SSL and SHA256 authentication
- Fluent API with support for flexible parameters
- Semantic methods to encode common CRUD operations
- Modern Node.js asynchronous interface based on Promises
- Abstractions for common database development tasks
- Transactions, savepoints and row locking
MySQL 8.0
Connector/Node.js is currently the only driver in the Node.js ecosystem that works out-of-the-box with the latest MySQL 8.0 series and implements the brand new X Protocol and X DevAPI, which unlocks exclusive server features such as the MySQL document store.
In a nutshell, the X Protocol is based on the Google Protocol Buffers serialization format, and provides a common interface for a different set of official connectors to bridge into the MySQL server via the X plugin, which contains the server-side implementation of the document store and a surrounding scaffolding ecosystem including things like common CRUD expression trees, bound parameters, or expectations and conditions for statement pipelining.
The X DevAPI is the common client-side API used by all connectors to abstract the details of the X Protocol. It specifies the common set of CRUD-style functions/methods used by all the official connectors to work with both document store collections and relational tables, a common expression language to establish query properties such as criteria, projections, aliases, and a standard set of additional database management features for handling things like transactions, indexes, etc.
The fact that most of these features share the same format and API between connectors, makes the X DevAPI a perfect fit for mordern polyglot development environments such as microservices, and the fact that they are based on a well-documented format allows advanced users to extend client-side implementations and build new middleware components or extensions tailor-made for their use case.
Although, there are (most of the times) matching client APIs to work with relational tables, this overview will focus mostly on document-store related features. Check the official Connector/Node.js documentation or the X DevAPI user guide to get the full picture.
Secure by default
With Connector/Node.js, SSL/TLS is enabled by default for server TCP connections and, additionally, the server identity can be validated against a given certificate authority (CA).
mysqlx.getSession({ user: 'user', sslOptions: { ca: '/path/to/ca.pem' } })
mysqlx.getSession('mysqlx://user@localhost?ssl-ca=(/path/to/ca.pem)')
mysqlx.getSession({ sslOptions: { ca: '/path/to/ca.pem', crl: '/path/to/crl.pem' } })
mysqlx.getSession('mysqlx://user@localhost?ssl-ca=(/path/to/ca.pem)&ssl-crl=(/path/to/crl.pem)')
Of course you can explicitely override this behavior (at your own peril).
mysqlx.getSession({ user: 'user', ssl: false })
mysqlx.getSession('mysqlx://user@localhost?ssl-mode=DISABLED')
Local Unix sockets don’t use SSL/TLS since they don’t really benefit much from that level of security. At the same time, that removes the possibility of any additional performance overhead caused by the SSL/TLS handshake.
In the authentication realm, besides the traditional SHA1-based server authentication plugin, Connector/Node.js also supports the latest secure authentication plugins based on SHA-256. Of course you can always use your own custom server plugins, as long as the authentication data can be sent using one of the existing client-side authentication mechanisms (in the simplest form, via plain text).
// works with the caching_sha2_password plugin (as long as the password is cached)
mysqlx.getSession({ user: 'user', auth: 'SHA256_MEMORY' })
mysqlx.getSession('mysqlx://user@localhost?auth=SHA256_MEMORY')
// works with the mysql_native_password plugin
mysqlx.getSession({ user: 'user', auth: 'MYSQL41' })
mysqlx.getSession('mysqlx://user@localhost?auth=MYSQL41')
// works with any server-side plugin
mysqlx.getSession({ user: 'user', auth: 'PLAIN' })
mysqlx.getSession('mysqlx://user@localhost?auth=PLAIN')
Additional details about Connector/Node.js security can be found here.
Fluent API
The public API flows nicely from a single getSession() method. Whereas, when it comes the point of creating and issuing database operations, you get a nice fluent query builder where those operations are encapsulated in specialized and specific methods, which, compared to using raw SQL statements, brings benefits such as:
- more readable, maintainable (and even testable) code
- better tooling integration
- scaffolding for code refactoring
- text-editor (or IDE) hints and auto-completion
- smaller SQL injection surface area
- common standard between different programming languages and environments
Most public API methods provide alternative input handling flavors:
- multiple individual arguments
- a single array of arguments
- an object with named properties (where it applies)
mysqlx.getSession('root@localhost')
mysqlx.getSession({ user: 'root' })
collection.add({ name: 'foo' }).add({ name: 'bar' }).execute()
collection.add([{ name: 'foo' }, { name: 'bar' }]).execute()
collection.find('name = :name').bind('name', 'foo').execute()
collection.find('name = :name').bind({ name: 'foo' }).execute()
collection.find().fields('foo', 'bar').execute()
collection.find().fields(['foo', 'bar']).execute()
Promise-based asynchronous tasks
Being a good Node.js citizen, Connector/Node.js encapsulates all blocking I/O operations with asynchronous methods. Each method that sends a message to the MySQL server is expected to return a JavaScript Promise , which resolves to the specific result or fails with an error. This pattern also unlocks other platform abstractions such as the async/await syntax, making it even more flexible for the user than just using traditional error-first callbacks.
So, after building a query, it can be sent to the server via the execute() method. In turn, the method receives an optional callback as argument, which runs for each element in the result set. When using relational tables, an additional callback function can be used to tap into the given column metadata.
collection.add({ name: 'foo' }).execute()
collection.find().execute(doc => {
// do something with the result set document currently being processed
})
table.insert(['name']).values(['foo']).execute()
table.select().execute(row => {
// do something with the result set row currently being processed
}, metadata => {
// do something with the metadata for each column in the row
})
Other methods, such as the ones that operate on a single instance of a connection, database object (be it a schema, table/collection, row, document, etc.) will return a Promise by themselves (dropping the extra call to execute() ). Some examples:
- mysqlx.getSession()
- session.getSchemas()
- session.createSchema()
- schema.getCollections()
- schema.createCollection()
- collection.getOne()
- collection.addOrReplaceOne()
- collection.replaceOne()
- collection.createIndex()
Data consistency
With MySQL 8.0, you get session-level consistency and document-level isolation via multiple database constructs, such as transactions, savepoints and row locking. This allows to encapsulate a set of operations (particularly DML) encompassing multiple documents or collections in a single atomic procedure within a given session.
Connector/Node.js provides APIs to create, commit or rollback a transaction as well as to create, release or rollback to an intermediate savepoint within that transaction.
(async function () {
try {
await session.startTransaction()
// run some operations (1)
await session.createSavepoint('foo')
// run more operations (2)
await session.releaseSavepoint('foo')
await session.commit()
} catch (err) {
try {
await session.rollbackTo('foo') // go to (2)
} catch (err) {
await session.rollback(); // revert the entire thing
}
}
})();
In the presence of concurrent transactions, the isolation level of each operation within the transaction, operating on a given document, can be determined using row locks.
collection.find('name = "foo"').lockExclusive()
collection.find('name = "foo"').lockExclusive(mysqlx.LockContention.DEFAULT) // same as above
collection.find('name = "foo"').lockExclusive(mysqlx.LockContention.NOWAIT)
collection.find('name = "foo"').lockExclusive(mysqlx.LockContention.SKIP_LOCKED)
collection.find('name = "foo"').lockShared()
collection.find('name = "foo"').lockShared(mysqlx.LockContention.DEFAULT) // same as above
collection.find('name = "foo"').lockShared(mysqlx.LockContention.NOWAIT)
collection.find('name = "foo"').lockShared(mysqlx.LockContention.SKIP_LOCKED)
Raw SQL interface
If you are looking for a feature that is still not available on the X DevAPI, or simply something that does not fit the CRUD model, you can always resort to plain old SQL.
// create a table
session.sql('CREATE TABLE foo (bar VARCHAR(3))').execute()
// add an unique constraint
session.sql('ALTER TABLE foo.bar ADD COLUMN baz VARCHAR(3) GENERATED ALWAYS AS (doc->>"$.baz") VIRTUAL UNIQUE KEY NOT NULL').execute()
// execute a JOIN query
session.sql('SELECT DISTINCT t1.bar FROM foo t1 JOIN baz t2 ON t1.bar = t2.qux WHERE t1.qux = t2.quux').execute()
Getting Started
If you want to use Connector/Node.js in your own project or just want to play around with, download the latest version from npm:
$ npm install --save --save-exact @mysql/xdevapi
New releases DO NOT follow semantic versioning, so, to avoid being affected by breaking changes, make sure you use –save-exact when pulling the package, particularly if you don’t have npm-shrinkwrap.json or package-lock.json files locking down your project’s dependencies.
If you are an advanced user, being an open source project, the code is hosted on the official GitHub repository and contributions are welcome, either in the form of bugs (Connector for Node.js category) or pull requests.
Up and Running
Assuming you are running MySQL 8.0 with the default configuration and Node.js v8.0.0 or later (for async/await support), using Connector/Node.js and the MySQL document store in your project is as simple as follows:
'use strict';
const mysqlx = require('@mysql/xedvapi');
const options = { user: 'user', password: 'password', schema: 'mySchema' };
(async function () {
let session;
try {
session = await mysqlx.getSession(options);
const collection = await session.getSchema(options.schema).createCollection('myCollection');
await collection.add({ name: 'foo' }).execute();
await collection.find().fields('name').execute(console.log); // { name: 'foo' }
} catch (err) {
console.error(err.message);
} finally {
session && session.close();
}
})();
These are just some of the highlights. Make sure you check out the official Connector/Node.js documentation or the X DevAPI user guide for usage examples and even more details on how to get started.
Please give it a try! Your feedback is more than welcome.