Tutorial: Getting Started

Getting Started

MySQL Connector/Node.js is the official Node.js driver for MySQL. It is written in JavaScript, does not require compiling, and is, currently, the only driver with out-of-the-box support for the MySQL document-store, through the X Protocol (it does not support the classic MySQL protocol).

Requirements

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

Altough some of the latest MySQL 5.7 versions are partially supported, you will only take advantage of the entire feature set using, at least, MySQL 8.0.11.

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 root directory of your project:

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

Overview

The MySQL Connector/Node.js allows you, among other things, to 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 connection = {};

mysqlx
    .getSession('root@localhost:33060')
    .then(session => {
        connection.session = session;

        return session.createSchema('mySchema');
    })
    .then(schema => {
        connection.schema = schema;

        return connection.schema
            .createCollection('myCollection');
    })
    .then(() => {
        return connection.schema
            .getCollection('myCollection')
            .add([{ name: 'foo', age: 42 }])
            .execute();
    })
    .then(() => {
        return connection.schema
            .getCollection('myCollection')
            .find()
            .fields(['name', 'age'])
            .execute(row => {
                console.log(row); // { name: 'foo', age: 42 }
            });
    })
    .then(() => {
        return connection.schema
            .getCollection('myCollection')
            .modify()
            .where('age = 42')
            .set('name', 'bar')
            .execute();
    })
    .then(() => {
        return connection.schema
            .getCollection('myCollection')
            .find()
            .fields(['name', 'age'])
            .execute(row => {
                console.log(row); // { name: 'bar', age: 42 }
            });
    })
    .then(() => {
        return connection.schema
            .getCollection('myCollection')
            .remove('true')
            .execute();
    })
    .then(() => {
        return connection.schema
            .dropCollection('myCollection');
    })
    .then(() => {
        return connection.session
            .dropSchema('mySchema');
    })
    .then(() => {
        return connection.session
            .close();
    })
    .catch(err => {
        console.log(err);
    });

You can always fallback to plain-old raw SQL statements if you feel the need to. In fact, there's currently no support for relational DDL operations through the X DevAPI, which means you 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 connection = {};

mysqlx
    .getSession('root@localhost:33060')
    .then(session => {
        connection.session = session;

        return session.createSchema('mySchema');
    })
    .then(schema => {
        connection.schema = schema;

        return connection.session
            .sql('CREATE TABLE mySchema.myTable (_id SERIAL, name VARCHAR(3), age TINYINT)')
            .execute();
    })
    .then(() => {
        return connection.schema
            .getTable('myTable')
            .insert(['name', 'age'])
            .values(['foo', 42])
            .execute();
    })
    .then(() => {
        return connection.schema
            .getTable('myTable')
            .select(['name', 'age'])
            .execute(row => {
                console.log(row); // ['foo', 42]
            });
    })
    .then(() => {
        return connection.schema
            .getTable('myTable')
            .update()
            .where('age = 42')
            .set('name', 'bar')
            .execute();
    })
    .then(() => {
        return connection.schema
            .getTable('myTable')
            .select(['name', 'age'])
            .where('name = "bar"')
            .execute(row => {
                console.log(row); // ['bar', 42]
            });
    })
    .then(() => {
        return connection.schema
            .getTable('myTable')
            .delete('true')
            .execute();
    })
    .then(() => {
        return connection.schema
            .dropTable('myTable');
    })
    .then(() => {
        return connection.session
            .dropSchema('mySchema');
    })
    .then(() => {
        return connection.session
            .close();
    })
    .catch(err => {
        console.log(err);
    });

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.