MySQL Connector/Node.js

MySQL Connector/Node.js is a MySQL 8 driver for Node.js, officially supported and maintained by Oracle. It contains a pure JavaScript implementation of the X DevAPI, an Application Programming Interface for working with the MySQL Document Store through CRUD-based, NoSQL operations on top of the X Protocol (it does not support the classic MySQL protocol).

Requirements

  • MySQL 8.0.11 or higher
  • Node.js 12.0.0 or higher

Altough some of the latest MySQL 5.7 versions are partially supported, the entire feature set is only available in the latest 8.x version.

Installation

Download and install directly from the npm registry:

$ npm install @mysql/xdevapi

Alternatively, download the tarball from the official website and install the package by running the following command in the project's root directory:

$ npm install /path/to/mysql-connector-nodejs-<version>.tar.gz

Overview

The MySQL Connector/Node.js allows to, among other things, tap into the MySQL document-store and write schemaless data apps or plain old traditional relational-flavored apps using a fluent API and an integrated small but expressive query language (for complex queries). Besides traditional document-store functionality, the MySQL Connector/Node.js provides additional support for features such as transactions, savepoints and row-locking.

The API is entirely asynchronous and uses the JavaScript Promise interface for flow control, which means it also enables the use of async-await on Node.js 8.0.0 or above. Check the following links to learn how to use Promise and async-await in JavaScript and Node.js:

Database operations encompassing the entire DML surface and some DDL surface as well, are constructed using a contextual query-builder implemented through a fluent API. This API follows the same design rules on every official MySQL connector and matches the underlying X Protocol constructs implemented using the Google Protocol Buffers open standard.

The API provides support for managing database sessions and schemas, working with document-store collections and using raw SQL statements.

The following is an example encompassing the different sort of CRUD operations using the document-store:

const mysqlx = require('@mysql/xdevapi');
const config = { collection: 'myCollection', schema: 'mySchema', user: 'root' };

mysqlx.getSession({ user: config.user })
    .then(session => {
        const schema = session.getSchema(config.schema);

        return schema.existsInDatabase()
            .then(exists => {
                if (exists) {
                    return schema;
                }

                return session.createSchema(config.schema);
            })
            .then(schema => {
                return schema.createCollection(config.collection, { reuseExisting: true });
            })
            .then(collection => {
                return collection.add([{ name: 'foo', age: 42 }])
                    .execute()
                    .then(() => {
                        return collection.find()
                            .fields('name', 'age')
                            .execute();
                    })
                    .then(res => {
                        console.log(res.fetchOne()); // { name: 'foo', age: 42 }
                    })
                    .then(() => {
                        return collection.modify('age = :value')
                            .bind('value', 42)
                            .set('name', 'bar')
                            .execute();
                    })
                    .then(() => {
                        return collection.find()
                            .fields('name', 'age')
                            .execute();
                    })
                    .then(res => {
                        console.log(res.fetchOne()); // { name: 'bar', age: 42 }
                    })
                    .then(() => {
                        return collection.remove('true')
                            .execute();
                    })
                    .then(() => {
                        return collection.find()
                            .fields('name', 'age')
                            .execute();
                    })
                    .then(res => {
                        console.log(res.fetchAll()); // []
                    });
            })
            .then(() => {
                return schema.dropCollection(config.collection);
            })
            .then(() => {
                return session.dropSchema(config.schema);
            })
            .then(() => {
                return session.close();
            });
    });

Executing plain-old raw SQL statements is also possible. In fact, there are currently no methods for relational DDL operations in the X DevAPI, which means one will most likely need some SQL to work with traditional tables and views.

The following is an example encompassing the different sort of CRUD operations using relational tables:

const mysqlx = require('@mysql/xdevapi');
const config = { schema: 'mySchema', table: 'myTable', user: 'root' }

mysqlx.getSession({ user: config.user })
    .then(session => {
        return session.sql(`create database if not exists ${config.schema}`)
            .execute()
            .then(() => {
                return session.sql(`create table if not exists ${config.schema}.${config.table} (_id SERIAL, name VARCHAR(3), age TINYINT)`)
                    .execute()
            })
            .then(() => {
                const table = session.getSchema(config.schema).getTable(config.table);

                return table.insert('name', 'age')
                    .values('foo', 42)
                    .execute()
                    .then(() => {
                        return table.select('name', 'age')
                            .execute()
                    })
                    .then(res => {
                        console.log(res.fetchOne()); // ['foo', 42]
                    })
                    .then(() => {
                        return table.update()
                            .where('age = :v')
                            .bind('v', 42)
                            .set('name', 'bar')
                            .execute()
                    })
                    .then(() => {
                        return table.select('name', 'age')
                            .where('name = :v')
                            .bind('v', 'bar')
                            .execute()
                    })
                    .then(res => {
                        console.log(res.fetchOne()); // ['bar', 42]
                    })
                    .then(() => {
                        return table.delete()
                            .where('true')
                            .execute();
                    })
                    .then(() => {
                        return table.select()
                            .execute()
                    })
                    .then(res => {
                        console.log(res.fetchAll()); // []
                    });
            })
            .then(() => {
                return session.sql(`drop table if exists ${config.schema}.${config.table}`)
                    .execute();
            })
            .then(() => {
                return session.sql(`drop database if exists ${config.schema}`)
                    .execute();
            })
            .then(() => {
                return session.close();
            });
    });

Note: raw SQL statements are also sent to the server using a specialized protobuf message, again, since there is no support for the classic MySQL protocol.

Licensing information

This product may include third-party software, used under license. If you are using a Commercial release of MySQL Connector/Node.js 8.0, see the MySQL Connector/Node.js 8.0 Commercial License Information User Manual for licensing information, including licensing information relating to third-party software that may be included in this Commercial release. If you are using a Community release of MySQL Connector/Node.js 8.0, see the MySQL Connector/Node.js 8.0 Community License Information User Manual for licensing information, including licensing information relating to third-party software that may be included in this Community release.