developing modern applications with the mysql document ... · the examples in this workbook. if you...

Developing Modern Applications with the MySQL Document Store and NodeJS [HOL1706]

Jesper Wisborg Krogh

Senior Principal Technical Support Engineer

Lig Isler-turmelle Principle Technical Support Engineer

Prerequisites

Note: This is not required when using the laptops at HOL1706 at Oracle OpenWorld

2018.

It is assumed the following software is already installed on the machine where you try

the examples in this workbook. If you are attending the hands-on labs session

Developing Modern Applications with the MySQL Document Store and NodeJS

[HOL1706] at Oracle OpenWorld 2018, you do not need to do anything, and you can skip

to the next section.

Required Software List

MySQL Server 8.0.12 or later

MySQL Shell 8.0.12 or later

MySQL Connector/Node.js 8.0.12 or later

Node.js 8.12 (other 8.X versions should work as well; other versions may require

changes to the examples)

Data List

The world database: http://downloads.mysql.com/docs/world.sql.gz

The world_x database: http://downloads.mysql.com/docs/world_x-db.tar.gz

An empty hol1706 schema

Installing the Software

The following steps can be used to install the required software on Oracle Linux 7. For

other platforms the steps will be different.

# Install the latest public yum repo

# and enable the required ol7_developer repos:

cd /etc/yum.repos.d/

mv public-yum-ol7.repo public-yum-ol7.repo.bak

wget http://yum.oracle.com/public-yum-ol7.repo

yum-config-manager --enable ol7_developer_nodejs8

yum-config-manager --enable ol7_developer_EPEL

# Install MySQL Repo

wget https://dev.mysql.com/get/mysql80-community-release-el7-

1.noarch.rpm

yum install mysql80-community-release-el7-1.noarch.rpm

# Install MySQL Server, Shell, and connectors

http://downloads.mysql.com/docs/world.sql.gz

http://downloads.mysql.com/docs/world_x-db.tar.gz

yum install mysql-community-client \

mysql-community-common \

mysql-community-devel \

mysql-community-libs \

mysql-community-libs-compat \

mysql-community-server \

mysql-shell \

nodejs-8*

# Start MySQL Server for the first time

# (initializes the data directory)

# and update the root password.

systemctl start mysqld

passwd=$(grep 'A temporary password is generated for root@localhost'

/var/log/mysqld.log | sed -re 's/^.* (.+)$/\1/')

mysql --user=root --password="${passwd}" --connect-expired-password -e

"SET PASSWORD = '<some secure password>'"

unset passwd

Note: It is recommended not to put the password on the command line. The above is

done for simplicity.

Installing Data

The following instructions assume the previously mentioned software has been

installed, that MySQL Server has been started, and that the root password has been

updated (it is set to an expired password by default that can be found in

/var/log/mysqld.log after MySQL has been started the first time).

The data needed for this hands-on lab can be installed following these steps:

wget http://downloads.mysql.com/docs/world.sql.zip

wget http://downloads.mysql.com/docs/world_x-db.zip

unzip world.sql.zip

unzip world_x-db.zip

mysql --user=root --password \

--execute "SOURCE world.sql; SOURCE world_x-db/world_x.sql;"

HOL Information

The following information is useful for the hands-on lab session at Oracle OpenWorld

2018:

Linux Username lab

Linux Password oracle

MySQL Username hol1706

MySQL Password hol@OOW18

MySQL Schemas hol1706 world world_x

Connectors and APIs Installed MySQL Connector/Node.js 8.0.12 @mysql/xdevapi

MySQL Connector/Python 8.0.12 mysql.connector (PEP 249 Python DB API) mysqlx (X DevAPI – for the Document Store) MySQL Connector/J 8.0.12 java.sql.*

Documentation This workbook as well as documentation for the X DevAPI, MySQL Connector/Node.js, MySQL Connector/Python, and MySQL Shell can be found in the /home/lab/docs directory. Firefox is set to open with an overview of the available documentation as the home page.

Tip: A login path has been configured for the hol1706 user, so it is not necessary to

specify the username and password when using the mysql command-line client or

MySQL Shell.

The MySQL Document Store

The MySQL Document Store was developed throughout the MySQL Server 5.7 lifetime.

The server-side is implemented through the X plugin (called mysqlx in the

information_schema.PLUGINS view), and was first introduced as a beta release with

MySQL Server 5.7.12. The X plugin reached general availability (GA) status with MySQL

Server 8.0.11 and is now a built-in plugin and enabled by default. That is, on the server-

side you do not need to do anything to start using the MySQL Document Store.

mysql> SELECT *

FROM information_schema.PLUGINS

WHERE PLUGIN_NAME = 'mysqlx'\G

*************************** 1. row ***************************

PLUGIN_NAME: mysqlx

PLUGIN_VERSION: 1.0

PLUGIN_STATUS: ACTIVE

PLUGIN_TYPE: DAEMON

PLUGIN_TYPE_VERSION: 80012.0

PLUGIN_LIBRARY: NULL

PLUGIN_LIBRARY_VERSION: NULL

PLUGIN_AUTHOR: Oracle Corp

PLUGIN_DESCRIPTION: X Plugin for MySQL

PLUGIN_LICENSE: GPL

LOAD_OPTION: ON

1 row in set (0.00 sec)

There are a few more components to the MySQL Document Store:

X Plugin: This is the server-side plugin that provides support for the X DevAPI.

X Protocol: The protocol used for an application to communicate with the X

Plugin.

The X DevAPI: The API used with the X Protocol.

Collectively these components are known as the MySQL Document Store.

X Plugin Port and Other Variables

Because the X Plugin uses a different protocol to the traditional MySQL protocol, it

needs to listen to a different port than the usual. The X Plugin by default uses port

33060. This can be configured using the mysqlx_port option. Similarly, if you want to

connect using a UNIX socket file, you need a separate socket file.

All of the variables for the X Plugin are prefixed mysqlx_. The complete list of variables

with their default values (using the MySQL Server RPM for Oracle Linux/RHEL 7) is:

mysql> SELECT *

FROM performance_schema.global_variables

WHERE VARIABLE_NAME LIKE 'mysqlx%';

+-----------------------------------+-----------------------------+

| VARIABLE_NAME | VARIABLE_VALUE |

+-----------------------------------+-----------------------------+

| mysqlx_bind_address | * |

| mysqlx_connect_timeout | 30 |

| mysqlx_document_id_unique_prefix | 0 |

| mysqlx_idle_worker_thread_timeout | 60 |

| mysqlx_interactive_timeout | 28800 |

| mysqlx_max_allowed_packet | 67108864 |

| mysqlx_max_connections | 100 |

| mysqlx_min_worker_threads | 2 |

| mysqlx_port | 33060 |

| mysqlx_port_open_timeout | 0 |

| mysqlx_read_timeout | 30 |

| mysqlx_socket | /var/run/mysqld/mysqlx.sock |

| mysqlx_ssl_ca | |

| mysqlx_ssl_capath | |

| mysqlx_ssl_cert | |

| mysqlx_ssl_cipher | |

| mysqlx_ssl_crl | |

| mysqlx_ssl_crlpath | |

| mysqlx_ssl_key | |

| mysqlx_wait_timeout | 28800 |

| mysqlx_write_timeout | 60 |

+-----------------------------------+-----------------------------+

21 rows in set (0.00 sec)

Several of the options have counterparts for the old MySQL protocol; for these the

variable names are the same just with mysqlx_ prefixed. For the purpose of this hands-

on lab, the default values can be used.

The X DevAPI

From an end user perspective, the most interesting part of the MySQL Document Store

is the X DevAPI. This is the API used to interact with the MySQL Document Store from

your programs and from MySQL Shell.

The X DevAPI is designed from the ground up with modern day usage in mind. It is

available for a range of languages, for example: Python (MySQL Connector/Python),

JavaScript (MySQL Connector/Node.js), PHP (mysql_xdevapi PECL extension), Java

(MySQL Connector/J), C++ (MySQL Connector/C++), DotNet (MySQL Connector/NET).

The X DevAPI is uniform across the supported programming languages while still

maintaining the characteristics of the language. An example if the method to get a

session is get_session() in Python but getSession() in Node.js.

The X DevAPI has three different parts. Which part you should use depends on how you

want to interact with MySQL:

Collections: The create-read-update-delete (CRUD) methods to work with JSON

documents, i.e. using MySQL as a document store. This is a NoSQL API.

SQL Tables: The CRUD methods to work with SQL (relational) tables. This is a

NoSQL API.

SQL: This can be used to execute arbitrary SQL statements against both

collections and SQL tables.

The easiest way to try the X DevAPI is to use MySQL Shell. MySQL Shell is a relatively

new command-line tool that not only support SQL statements but also Python and

JavaScript. This makes it possible to test code before implementing it in an actual

program, or use MySQL Shell to execute scripts that include use of Python or JavaScript

routines.

Note: Python and JavaScript in MySQL Shell do not use the connectors, so while the API

is the same, there are some differences in their use.

Start MySQL Shell

As MySQL Shell support JavaScript and the X DevAPI, it is a good tool for testing code

interactively. MySQL Shell is started using the mysqlsh command in the terminal.

Optionally specify the language mode you want to use. The language mode can also be

set after starting MySQL Shell. The following table shows how to specify the language

mode on the command-line or the MySQL shell prompt.

Language Mode Command-Line MySQL Shell Prompt

JavaScript --js \js

Python --py \py

SQL --sql \sql

The default language mode is JavaScript. The exercises in this lab will include examples

of changing the language mode.

Tip: When you switch language mode, you can keep using your existing connection if

you are already connected to a MySQL instance.

For now, start MySQL Shell without any arguments:

[lab@localhost ~]$ mysqlsh

MySQL Shell 8.0.12

Copyright (c) 2016, 2018, Oracle and/or its affiliates. All rights

reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type '\help' or '\?' for help; '\quit' to exit.

mysql-js>

You can connect to MySQL using the \connect command:

mysql-js> \connect hol1706@localhost

Creating a session to 'hol1706@localhost'

Fetching schema names for autocompletion... Press ^C to stop.

Your MySQL connection id is 12 (X protocol)

Server version: 8.0.12 MySQL Community Server - GPL

No default schema selected; type \use <schema> to set one.

mysql-js> session

<Session:hol1706@localhost>

No password is required as MySQL Shell has been set up to fetch the password from a

login path. If this has not been done, MySQL Shell will interactively ask for the password

and offer to store it for you.

The session can be accessed through the session object. This can be useful for example

to fetch a schema, controlling transactions, etc. You will see examples of this later in the

lab.

You can now set the default schema (this can also be done when creating the

connection) using the \use command:

mysql-py> \use hol1706

Default schema `hol1706` accessible through db.

Notice how MySQL Shell assigned the hol1706 schema to the db object. You can now

use the db object to access the schema specific methods.

Before continuing, let’s pause for a moment to consider the prompt.

The Prompt

The default prompt includes information about the connection, whether it uses SSL, the

default schema, and the language mode. An example of the default prompt can be seen

in the following figure:

MySQL Shell in the hands-on lab virtual machine has been set up to use the

Powerline+Awesome fonts. This gives a prompt with the same information as the

default prompt but using some additional custom characters:

It is beyond the scope of this lab session to go through the installation of the Powerline

and Awesome fonts. If you are interested, you can see an example of installing the

required fonts in https://mysql.wisborg.dk/2018/09/04/awesome-mysql-shell-prompt/.

Before continuing learning how to work with the MySQL Document Store from MySQL

Shell, let’s look at what you can do, if you need help.

Built-In Help

A great feature in MySQL Shell is also the ability to obtain help directly within MySQL

Shell. This is not limited to the standard --help command-line argument; it extends to

within MySQL Shell including for each object type.

You can get general help, for example about the commands available (you have already

used the \connect and \use commands):

mysql-js> \?

The Shell Help is organized in categories and topics. To get help for a

specific category or topic use: \? <pattern>

The <pattern> argument should be the name of a category or a topic.

The pattern is a filter to identify topics for which help is required,

it can

use the following wildcards:

- ? matches any single charecter.

- * matches any character sequence.

The following are the main help categories:

https://mysql.wisborg.dk/2018/09/04/awesome-mysql-shell-prompt/

- AdminAPI Introduces to the dba global object and the InnoDB

cluster

administration API.

- Shell Commands Provides details about the available built-in shell

commands.

- ShellAPI Contains information about the shell and util global

objects

as well as the mysql module that enables executing

SQL on

MySQL Servers.

- SQL Syntax Entry point to retrieve syntax help on SQL

statements.

- X DevAPI Details the mysqlx module as well as the capabilities

of the

X DevAPI which enable working with MySQL as a

Document Store

The available topics include:

- The dba global object and the classes available at the AdminAPI.

- The mysqlx module and the classes available at the X DevAPI.

- The mysql module and the global objects and classes available at the

ShellAPI.

- The functions and properties of the classes exposed by the APIs.

- The available shell commands.

- Any word that is part of an SQL statement.

SHELL COMMANDS

The shell commands allow executing specific operations including

updating the

shell configuration.

The following shell commands are available:

- \ Start multi-line input when in SQL mode.

- \connect (\c) Connects the shell to a MySQL server and assigns

the

global session.

- \exit Exits the MySQL Shell, same as \quit.

- \help (\?,\h) Prints help information about a specific topic.

- \history View and edit command line history.

- \js Switches to JavaScript processing mode.

- \nowarnings (\w) Don't show warnings after every statement.

- \option Allows working with the available shell options.

- \py Switches to Python processing mode.

- \quit (\q) Exits the MySQL Shell.

- \reconnect Reconnects the global session.

- \rehash Refresh the autocompletion cache.

- \source (\.) Loads and executes a script from a file.

- \sql Switches to SQL processing mode.

- \status (\s) Print information about the current global

session.

- \use (\u) Sets the active schema.

- \warnings (\W) Show warnings after every statement.

GLOBAL OBJEECTS

The following modules and objects are ready for use when the shell

starts:

- db Used to work with database schema objects.

- dba Used for InnoDB cluster administration.

- mysql Support for connecting to MySQL servers using the classic

MySQL

protocol.

- mysqlx Used to work with X Protocol sessions using the MySQL X

DevAPI.

- session Represents the currently open MySQL session.

- shell Gives access to general purpose functions and properties.

- sys Gives access to system specific parameters.

- util Global object that groups miscellaneous tools like upgrade

checker.

For additional information on these global objects use: <object>.help()

EXAMPLES

\? AdminAPI

Displays information about the AdminAPI.

\? \connect

Displays usage details for the \connect command.

\? checkInstanceConfiguration

Displays usage details for the dba.checkInstanceConfiguration

function.

\? sql syntax

Displays the main SQL help categories.

The examples at the end shows how you can get additional help. For example, to learn

how to use the \connect command:

mysql-js> \? connect

NAME

connect - Establishes the shell global session.

SYNTAX

shell.connect(connectionData[, password])

WHERE

connectionData: the connection data to be used to establish the

session.

password: The password to be used when establishing the session.

DESCRIPTION

This function will establish the global session with the received

connection data.

…

As the help is very extensive, the whole output will not all be included here.

You can also get help directly about an object you are working with. For example, you

have the db object with the hol1706 schema. To get help about the db object, use the

help() method of the object:

mysql-js> db.help()

NAME

Schema - Represents a Schema as retrived from a session created

using the

X Protocol.

DESCRIPTION

View Support

MySQL Views are stored queries that when executed produce a

result set.

…

FUNCTIONS

createCollection(name)

Creates in the current schema a new collection with the

specified

name and retrieves an object representing the new

collection

created.

…

As the help output shows, a schema object for example has a method called

createCollection(). You can get additional help about the createCollection()

method by passing that as an argument to the help() method:

mysql-py> db.help("createCollection")

NAME

createCollection - Creates in the current schema a new collection

with

the specified name and retrieves an object

representing the new collection created.

SYNTAX

<Schema>.createCollection(name)

WHERE

name: the name of the collection.

RETURNS

the new created collection.

DESCRIPTION

To specify a name for a collection, follow the naming conventions

in

MySQL.

Note that to get help for SQL statement, you must be connected to a MySQL instance,

and you either need to be in the SQL language mode or explicitly tell MySQL Shell that

you want help for an SQL statement. For example: \? SQL Syntax/SELECT.

Feel free to execute the help commands as you go through the exercises.

Built-in Objects

You have already encountered some of the built-in objects of MySQL Shell: The session

and db objects. There are more however built-in objects. From the output of \? you

executed a little earlier:

db: Used to work with database schema objects.

dba: Used for InnoDB cluster administration.

mysql: Support for connecting to MySQL servers using the classic MySQL

protocol.

mysqlx: Used to work with X Protocol sessions using the MySQL X DevAPI.

session: Represents the currently open MySQL session.

shell: Gives access to general purpose functions and properties.

sys: Gives access to system specific parameters.

util: Global object that groups miscellaneous tools like upgrade checker.

The two most useful for this lab are the db and session objects.

You are now ready to create a collection.

Lab Exercises

This lab will primarily use Node.js directly, however the collection is initially created

using MySQL Shell. You can also execute the exercises in MySQL Shell, but there will be

some differences due to MySQL Shell using a synchronous execution model whereas

Node.js is asynchronous. The general use of the X DevAPI will however be similar.

Tip: Most of the Node.js examples are also available in the /home/lab/bin directory.

When this is the case, the file name will be visible from the caption to the example.

If you wish to reset the city collection, you can execute the reset_city.sh script.

Create Collection

Collections are the containers for the documents. In the SQL world a collection would be

called a table. A collection is always located in a schema (database). You can create a

collection using the createCollection() method which is part of the db object. The

method takes the name of the new collection as a string. For example to create the city

collection using MySQL Shell and keep a reference to the collection in the city object:

mysql-js> city = db.createCollection("city")

<Collection:city>

What does a collection look like? You can check the definition of the underlying table

using a SHOW CREATE TABLE SQL query:

mysql-py> \sql

Switching to SQL mode... Commands end with ;

Fetching table and column names from `hol1706` for auto-completion...

Press ^C to stop.

mysql-sql> SHOW CREATE TABLE city\G

*************************** 1. row ***************************

Table: city

Create Table: CREATE TABLE `city` (

`doc` json DEFAULT NULL,

`_id` varbinary(32) GENERATED ALWAYS AS

(json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,

PRIMARY KEY (`_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

1 row in set (0.0007 sec)

mysql-sql> \js

Switching to JavaScript mode...

mysql-js>

This shows how you can change mode to use SQL if that is more convenient. At the end

the mode is changed back to JavaScript. The collection consists of an InnoDB table with

a normal column called doc and a generated column called _id. The doc column is

where the JSON document is stored. The _id column is generated by extracting the

value of the _id object at the base of the JSON document; this is the primary key of the

table and must always be present. If you create a document without the _id field,

MySQL will auto-generate one.

Tip: If you later need to get an object for the city collection again, you can get it using: city = db.getCollection("city")

Now that there is a collection to work with, let’s discuss how you can use MySQL

Connector/Node.js with that collection.

Using MySQL Connector/Node.js

To create a Node.js program using MySQL Connector/Node.js you need to import the

@mysql/xdevapi module:

const mysqlx = require('@mysql/xdevapi');

It is also recommended to enable strict mode.

A connection is created using the getSession() method (this is also available in MySQL

Shell). You need to pass the connections options, either directly as options or as an URI.

An example of creating the sessions is:

#!/usr/bin/env node

'use strict';


const mysqlArgs = {

host: 'localhost',

port: 33060,

password: 'hol@OOW18',

user: 'hol1706',

};

const session = mysqlx.getSession(mysqlArgs);

console.log(session);

process.exit(0);

Script file: getSession.js

This just creates the connection, then logs what the sessions is:

Promise { <pending> }

mysqlx.getSession() returns Promise, i.e. it is for asynchronous use .

In real-world examples, you should not include all the connection options in the

program itself; that is neither secure nor practical.

Warning: Do not include the connection options – particularly the password – in the

source code of real program. In the example it is done to keep the initial connection

example self-contained.

One option is to have the connections in a custom module:

const config = {};

config.mysqlArgs = {

host: 'localhost',

port: 33060,

password: 'hol@OOW18',

user: 'hol1706',

};

config.schema = 'hol1706';

module.exports = config;

Script: config.js

If you save this as config.js in the same directory as your Node.js programs, you can

now connect like:

#!/usr/bin/env node

'use strict';


const config = require('./config')

mysqlx.getSession(config.mysqlArgs)

.then(function(session) {

console.log('The session is ready for use.');

process.exit(0);

});

Script: getSession2.js

This also adds the use of the then() method to tell what should happen once the

promise is fulfilled.

To actually work with a collection, you first need retrieve the schema, then the

collection. The schema object is obtained using the getSchema() method of the session,

likewise the collection object is obtained using the getCollection() method of the

schema:

#!/usr/bin/env node

'use strict';


const config = require('./config')

mysqlx.getSession(config.mysqlArgs)

.then(function (session) {

const schema = session.getSchema(config.schema);

return schema.getCollection('city');

})

process.exit(0);

Script: getCollection.js

The code is nested using the then() method to work with the asynchronous nature of

Node.js. An alternative way to get the collections is:

#!/usr/bin/env node

'use strict';


const config = require('./config');

(async function() {

let session;

try {

session = await mysqlx.getSession(config.mysqlArgs);


const city = schema.getCollection('city');

console.log('The city collection is ready to use');

} catch (err) {

console.error(err.message);

} finally {

session && await session.close();

}

})();

Script: getCollection2.js

This uses async and await with asynchronous functions to avoid the nesting. This

requires Node.js 8.0 or later. However, enough of creating connections without using

them. It is time to add some data and query it.

Executing Document Store Queries

Executing queries is a topic too large to cover in full in this lab, however you are

encouraged to experiment yourself using the documentation as help. Here three cities

will be added to the collection, two queries will be executed to find cities matching

given criteria, documents will be updated, and finally deleted.

Adding Documents

Since JSON documents are native to JavaScript (JSON stands for JavaScript Object

Notation), they work out of the box without any special consideration. The X DevAPI

supports both inserting a single document at a time or multiple documents. Let’s look at

an example of two add statements inside the same transaction adding a total of three

documents.

#!/usr/bin/env node

'use strict';



// Define the documents to add

// Populations are from the world sample database, so quite outdated.

const sanFrancisco = {

'Name': 'San Francisco',

'Demographics': {

'Population': 776733,

},

'Geography': {

'Country': 'United States of America',

'State': 'California',

'Location': {'Type': 'Point', 'Coordinates': [-122.419,

37.779]},

},

'Capital': false,

};

const london = {

'Name': 'London',

'Demographics': {


},

'Geography': {

'Country': 'United Kingdom',

'State': 'England',

'Location': {'Type': 'Point', 'Coordinates': [-0.143, 51.501]},

},

'Capital': true,

};

const washington = {

'Name': 'Washington',

'Demographics': {


},

'Geography': {

'Country': 'United States of America',

'State': 'District of Columbia',

'Location': {'Type': 'Point', 'Coordinates': [-77.035, 38.889]}

},

'Capital': true,

};

(async function() {

let session;

try {




await session.startTransaction();

// Add a single city (San Francisco) in one statement

const result1 = city.add(sanFrancisco).execute();

if (result1.getWarningsCount() > 0) {

console.error(result1.getWarningsCount()

+ 'warnings occurred when adding San Francisco:\n'

+ result1.getWarnings());

await session.rollback();

await session.close();

process.exit(1);

} else {

console.log('Adding San Francisco with ID: '

+ result1.getGeneratedIds());

}

// Add two cities (London + Washington) in one statement

const stmt = city.add(london);

stmt.add(washington);

const result2 = await stmt.execute();

if (result2.getWarningsCount() > 0) {

console.error(result2.getWarningsCount()

+ 'warnings occurred when adding London and

Washington\n'

+ result2.getWarnings());

await session.rollback();

await session.close();

process.exit(1);

} else {

console.log('Adding London and Washington with IDs: '

+ result2.getGeneratedIds());

}

await session.commit();

} catch (err) {


} finally {


}

})();

Script: add.js

The example starts out defining JSON documents for three cities. The location of a city is

specified using the GeoJSON format (http://geojson.org/ and

https://en.wikipedia.org/wiki/GeoJSON). Since the JSON documents are schemaless,

http://geojson.org/

https://en.wikipedia.org/wiki/GeoJSON

you are of course free to choose whatever format you wish, but if you want to add an

index for the location (see later), then MySQL expects the format to be GeoJSON.

The actual work is all done inside the async function. The collection is obtained as

before, then a transaction is started. The MySQL Document Store has full transactional

support including for savepoints. If any warnings occur while adding documents, the

transaction will be rolled back and the program will exit with the error code 1.

The city of San Francisco is added by itself. This is done using a chained statement where

the statement is created and executed in one. In contrast London and Washington is

added using three separate lines of code. You could also have written that as:

const result2 = await city.add(london).add(washington).execute()

Or you can choose a mix. The workflow will often dictate what works the best, for

example if the documents are added in a loop, then separate lines of code will work the

best. It is also possible to add several documents in one add() call by passing the

documents as an array.

In MySQL Connector/Node.js it is always required to call execute() to execute the

statement. This is in contrast with MySQL Shell for interactive queries without assigning

the result to a variable.

After each execute(), the number of warnings is checked and if any occurred they are

logged and the program exits. Warnings are often a sign of something not working as

expected, so it is important always to check the warnings and handle them as necessary.

The earlier you add support for handling warnings to your programs, the easier it.

Now there is some data, let’s query it.

Finding Documents

The main method to query documents is the find() method. It takes an argument with

the filter to apply to the documents. Additional modifications of the query can be made

with methods applied to the statement returned by find(). Each of the statement

methods return the statement itself (except execute() which returns the result), so

you can keep modifying the statement either in separate lines of code or by chaining.

You can then use the following methods to modify the query:

fields(): Specify which fields to return from the matching documents.

groupBy(): Which fields to group the result by.

having(): A filter that is applied after the data has been grouped.

sort(): Which fields to sort the result by.

limit(): The maximum number of documents to return.

offset(): The offset to use together with the limit() method.

lockExclusive(): Take an exclusive lock on the matching documents.

lockShared(): Take a shared lock on the matching documents.

bind(): Used to assign values when parameters are used to set the filter

condition.

execute(): Execute the query – a document result object is returned.

Most of these methods are also available when modifying or deleting documents. Let’s

try to see it in use in a program that will log the total number of documents and the

cities in the United States:

#!/usr/bin/env node

'use strict';



(async function() {

let session;

let docs = [];

function storeResult(doc) {

docs.push(doc);

}

try {




// Find the cities in the United States

const stmt = city.find('Geography.Country = :country')

.fields('Name', 'Geography.State AS State').sort('Name');

stmt.bind('country', 'United States of America');

const result = await stmt.execute(storeResult);

for (let i = 0; i < docs.length; i++) {

console.log(i + ': ' + docs[i]['Name'] + ', ' +

docs[i]['State'])

}

} catch (err) {


} finally {


}

})();

Script: find.js

There are a couple of things worth nothing in the example:

The fields in the filter, fields, and for sorting are given as JSON paths (except that

$. to denote the root of the document is optional).

Filters are written directly as a statement – MySQL will figure the rest out. This

makes it simple to write conditions.

The name of the country to filter by is given as a parameter with the actual value

specified in the call to bind(). This has the advantages that MySQL will ensure

the value gets escaped, and that it is possible to reuse the statement, if you need

to execute the same statement multiple times with different values.

The resulting documents are sent one by one to the callback function given as an

argument to execute().

The output of running the program is:

0: San Francisco, California

1: Washington, District of Columbia

It is worth considering how the fields are specified in a bit more detail.

Fields and JSON Paths

The field can either be a JSON path or an expression (possibly referencing one or more

fields in the JSON document). A JSON path is created as:

The document root is represented by $. Except for specifying fields for an index

(more later) it is optional whether $ is included in the path. This makes it simple

to specify top level objects as you just need to use the name as in the previous

example.

A dot (.) is used to separate elements. For example, if you have: {

"Red": {

"Pink": "FFC0CB",

"Pure_red": "FF0000",

"Maroon": "800000"

}

} Then to get the hex value for Maroon you can use $.Red.Maroon .

* is a wildcard that means “all members”. The wildcard can also be used as

[prefix]**{suffix} where the prefix is optional and the suffix is mandatory.

For arrays square brackets can be used to return an element. [N] returns the Nth

element (0-based) and [*] returns all elements (same as not specifying the

brackets at all).

The first find() example was a fairly simple query, so let’s look at an example using the

spatial information.

Spatial Query

In MySQL 8.0 there is support for spatial reference systems (SRIDs), so spatial

calculations can be made accurately. Since the locations of the cities in the sample

collection are in the GeoJSON format, they can be used to find spatial properties such as

the distance between the city and some other point. The following example sets a

reference point at the location of San Francisco and calculates the distance to the cities

and includes those further away than 1000 kilometers.

#!/usr/bin/env node

'use strict';



(async function() {

let session;

let docs = [];

let distance_km;

try {




// Get the location of San Francisco

// This could have been obtained from a query

const sf_loc = '{"Type": "Point", "Coordinates": [-122.419,

37.779]}';

// Define how the distance is calculated:

// ST_Distance(ref_location, city_location)

// Each location is obtained using ST_GeomFromGeoJSON() which

// converts a location in GeoJSON format to the representation

// used internally in MySQL.

const distance = 'ST_Distance(ST_GeomFromGeoJSON('

+ sf_loc + ', 1, 4326), '

+ 'ST_GeomFromGeoJSON(Geography.Location, 1, 4326))';

// Find the distance for each city to San Francisco

const stmt = city.find(`${distance} > :min_distance`)

.fields('Name', `${distance} AS Distance`)

.sort(distance);

const result = await stmt.bind('min_distance', 1000*1000)

.execute(doc => docs.push(doc));

for (let i = 0; i < docs.length; i++) {

distance_km = (docs[i]['Distance']/1000).toFixed(0);

console.log(`${i}: ${docs[i]['Name']}: ${distance_km} km`);

}

} catch (err) {


} finally {


}

})();

Script: find2.js

The formula for getting the distance is to convert the GeoJSON location to the internal

representation using the ST_GeomFromGeoJSON() function

(https://dev.mysql.com/doc/refman/8.0/en/spatial-geojson-

functions.html#function_st-geomfromgeojson). Then use the ST_Distance() function

(https://dev.mysql.com/doc/refman/8.0/en/spatial-relation-functions-object-

shapes.html#function_st-distance) to calculate the distance between the two points.

The ST_GeomFromGeoJSON() function takes three arguments:

The location to convert. This argument is mandatory.

An integer 1-4 defining what to do in case the GeoJSON value has more than two

dimensions. 1 means reject, 2-4 means accept and ignore the higher order

dimensions. The default is 1.

The spatial reference system ID (SRID) as an unsigned integer. The default is

4326 which is the Earth. Supported values can be found in the SRS_ID column of

the information_schema.ST_SPATIAL_REFERENCE_SYSTEMS table. Support for

spatial reference systems is a MySQL 8.0 feature.

In the example the default values are used for the second and third argument, so they

could have been left off but are included to make it clear how the locations are handled.

The ST_Distance() function takes the spatial reference system of the points into

consideration, so the distance is calculated correctly (this is a MySQL 8.0 feature). The

calculated distance is for SRID = 4326 returned in meters.

Other than this, the example is straight forward. Compared to the previous example,

there are three changes:

The resulting documents are sorted by the distance in ascending order using the

sort() method.

The callback function for execute() has been to push the new document into

the docs array directly.

The logging is used doing template literals.

The output is:

0: Washington: 3928 km

1: London: 8638 km

https://dev.mysql.com/doc/refman/8.0/en/spatial-geojson-functions.html#function_st-geomfromgeojson

https://dev.mysql.com/doc/refman/8.0/en/spatial-geojson-functions.html#function_st-geomfromgeojson

https://dev.mysql.com/doc/refman/8.0/en/spatial-relation-functions-object-shapes.html#function_st-distance

https://dev.mysql.com/doc/refman/8.0/en/spatial-relation-functions-object-shapes.html#function_st-distance

The city collection is small with just three documents and each document does not

contain much data. What if you have a large collection? In that case – as with SQL tables

– indexes can be useful.

Adding an Index

The MySQL Document Store supports indexes on the collections. This can greatly

improve the query performance for queries that otherwise would need to check a large

number of documents to return a few of them or sort/group based on a given field.

You can add an index through the collection object. You can create an index with the

createIndex() method which takes two arguments: The index name and a dictionary

defining the index. The definition of the index includes three elements:

type. The index type. Currently normal ordered (B-TREE) indexes and spatial

indexes are supported. Specify INDEX for a normal ordered index and SPATIAL

for a spatial index.

unique. Whether it is a unique index. Currently this must be set to false (which

is the default).

fields. A list of the fields to include in the index.

The fields list include a dictionary for each field to include in the index. Do note that the

order does matter. Each dictionary element for the field includes some or all of the

following elements:

field. The JSON path to the value to index. The $ to specify the root of the

document must be included.

type. Equivalent to the data type for columns in a relational table. For example

to say the value is an unsigned integer use INT UNSIGNED.

required. Whether the value must be present. Specify as a Boolean. Equivalent

to whether NOT NULL is specified.

options. For spatial columns this is an integer (1-4) specifying what to do if the

value is of dimension higher than two. Use 1 (the default) to reject such values

and 2, 3, or 4 to accept them. See

https://dev.mysql.com/doc/refman/en/spatial-geojson-

functions.html#function_st-geomfromgeojson for details.

srid. The spatial reference system for spatial values. The default is 4326 (the

usual representation of the Earth). Supported values can be found in the SRS_ID

column of the information_schema.ST_SPATIAL_REFERENCE_SYSTEMS table.

This all feels quite complex, so it is worth looking at an example. In the example you will

add an index for the location of the city:

https://dev.mysql.com/doc/refman/en/spatial-geojson-functions.html#function_st-geomfromgeojson

https://dev.mysql.com/doc/refman/en/spatial-geojson-functions.html#function_st-geomfromgeojson

#!/usr/bin/env node

'use strict';



(async function() {

let session;

try {




const field = {

'field': '$.Geography.Location',

'type': 'GEOJSON',

'required': true,

'options': 1,

'srid': 4326,

};

const index_def = {

'fields': [field],

'type': 'SPATIAL',

};

await city.createIndex('Location', index_def);

console.log('Index created on $.Geography.Location.');

} catch (err) {


} finally {


}

})();

Script: createIndex.js

As usual, adding indexes can improve the performance of finding documents (whether it

is to return them, update them, or delete them), but adds overhead to keep the indexes

up to date and makes the optimizer evaluate more options for the query plan. So, it is

recommended to always test the effect of indexes for your queries and remove indexes

you no longer use.

With indexes in place, let’s move on to modifying documents.

Modifying Documents

The action of modifying documents can basically be separated into two steps: finding

the document to change and applying the change. Finding the documents is essentially

the same as for a find statement. The more interesting part here is to apply the changes.

There are three methods to change documents:

addOrReplaceOne(): For upserting a document by specifying the document ID. If

there is already a document with the ID, then it is replaced, otherwise a new

document is added with the ID.

modify(): The most advanced method where you can modify one or more

documents. It can take most of the arguments known from find queries to

specify which documents to change, and additional there are methods to specify

which change to make.

replaceOne(): Similar to addOrReplaceOne() but if the document ID does not

already exist, then no change occurs.

The method that will be demonstrated here is the modify() method. As mentioned,

you need to use a sub method to specify how to make the modification of the matching

documents. The available modify methods are:

arrayAppend(): Takes an existing array element and changes it to include the

original value plus the new value(s). For example if the original array is ["One",

"Two", "Three"] and you want to append [1, "Uno"] to the element with the

value "one", the resulting array is [["One", [1, "Uno"]], "Two", "Three"].

arrayInsert(): Takes an existing array and inserts into the element position

specified. For example, if the existing document is {"fruits" = ["apple",

"orange", "watermelon"]} and the array is specified as fruits[1] with the

new element being kiwi, then the resulting document is {"fruits" =

["apple", "kiwi", "orange", "watermelon"]}.

set: If the specified element exists, update it, otherwise add the element.

patch: Takes a part of a document and replaces it with a new document part.

This supports adding, replacing, or removing parts of a document. This is a very

powerful yet simple method.

unset: Removes an element from the document.

Note: It is mandatory to set a filter condition when calling modify(). If you fail to do

this, MySQL rejects the call with the error: “A valid condition needs to be provided with

`modify()`”.

If you really want to modify all documents, specify a valid truth condition like 'true' or

similar; this also makes it clear when you look at the code later on that you meant to

match all documents.

Let’s look at two examples: using set() to add/replace a given field and patch() to

add, change, and remove fields.

Using the modify().set() Method

The set() method is simple as it takes a path to a field and sets or replaces the value.

You can also use set() to update the existing value based on the current value. An

example of this is to change increase the population of the cities in the United States

with 10%:

#!/usr/bin/env node

'use strict';



(async function() {

let session;

let docs_before = [];

let docs_after = [];

try {




// Define the parameter to use for filtering, so it can

// be reused.

const param = {'country': 'United States of America'};

// Get the before populations

const pop_stmt = city.find('Geography.Country = :country')

.fields('Name', 'Geography.State AS State',

'Demographics.Population AS Population')

.sort('Name', 'State', '_id')

.bind(param);

await pop_stmt.execute(doc => docs_before.push(doc));

// Update the populations


const stmt = city.modify('Geography.Country = :country')

.set('Demographics.Population',

mysqlx.expr('FLOOR(Demographics.Population * 1.10)'));

stmt.bind(param);

const result = await stmt.execute();

const affectedDocs = result.getAffectedItemsCount();

console.log(`Number of documents updated: ${affectedDocs}`);


// Get the populations after the update and log

await pop_stmt.execute(doc => docs_after.push(doc));

for (let i = 0; i < docs_before.length; i++) {

let name = docs_before[i]['Name'];

let state = docs_before[i]['State'];

let pop_before = docs_before[i]['Population'];

let pop_after = docs_after[i]['Population'];

console.log(`${name}, ${state}: ${pop_before} =>

${pop_after}`);

}

} catch (err) {


} finally {


}

})();

Script: modify_set.js

There are a few new things in this example. The parameter used in bind() is stored as a

JSON object in a variable. The bind() method can take the parameter(s) in other ways

than the two arguments you have seen previously including as a JSON object. Since the

same parameter is reused for several queries, it is easier to have it defined in one place

and reuse it.

Secondly, the sort clause for the find statement includes the city name, state, and

document ID. This is to ensure the before and after queries return the documents in the

same order. It is assumed here that all affected documents already have the

$.Demographics.Population field; if that was not the case, that would have to be

taken into account. The _id field is included to ensure the ordering is deterministic in

case there are two cities with the same name and state (which is not the case here, but

it is good practice to add the primary key for these kind of cases).

The third thing is the modify().set() methods that is the topic of the example. The

filter condition is passed to the call to modify() whereas set() takes two arguments:

the JSON path to the field to change/set and the new value. Since the new population is

calculated from the old population, an expression is used to avoid first read the

population, then update one document at a time. To use an expression, the

mysqlx.expr() function is used.

Fourth, the find statement that was used to find the populations before the update is

reused to find the population afterwards as well. This shows an example of how you can

reuse statements. In this case, the bind parameter is the same, so we can just use the

statements as is, but it would also be possible to execute the statement with a different

parameter.

Finally, the updated cities are logged with the before and after populations:

Number of documents updated: 2

San Francisco, California: 776733 => 854406

Washington, District of Columbia: 572059 => 629264

What if you need to make more advanced modifications to the documents, such as

removing fields, adding multiple fields, etc.? The patch() method is great for that.

Using the modify().patch() Method

The patch() method is the most powerful of the modify methods, however it is also

simple. The method takes a JSON document (which may be just part of the document

you want to modify). For each element in the provided document, the element will be

added, modified, or removed depending on whether the element exists and what the

new value is.

As an example consider the following changes to London:

Update the population to be 8 million (8000000).

Set the life expectancy to 77.7 years.

Remove the $.Geography.State field and instead add a field called

NonSovereignCountry set to England.

This can be achieved with the following example program:

#!/usr/bin/env node

'use strict';



(async function() {

let session;

try {




// Define the parameter to use for filtering,

// so it can be reused.

const param = {'city_name': 'London'};

// Get the before document

console.log('London before patching:');

const find_stmt = city.find('Name = :city_name').bind(param);

await find_stmt.execute(

doc => console.log(JSON.stringify(doc, null, 4)));

// Update the populations

const patch = {

'Demographics': {


'LifeExpectancy': 77.7,

},

'Geography': {

'State': null,

'NonSovereignCountry': 'England',

},

};


const stmt = city.modify('Name = :city_name')

.patch(patch);

const result = await stmt.bind(param).execute();


console.log(`\nNumber of documents updated: ${affectedDocs}`)


// Get the document after the change

console.log('\nLondon after patching:')

await find_stmt.execute(

doc => console.log(JSON.stringify(doc, null, 4)));

} catch (err) {


} finally {


}

})();

Script: modify_patch.js

The parameter used in the bind() calls is here stored in a JSON document itself to make

it easier to reuse the parameter for the statements. After logging the document for

London before the change, the patch is defined. The $.Geography.State field is set to

null which causes it to be removed. For existing fields that should be updated, the field

is included with the new value, and for new fields the field is included with the value.

So, in reality it is easy to make changes using the patch() method. The limitation is that

it is mostly suitable for updating a single document. After the document has been

patched, the new document is logged. The output of the program is (document IDs will

be different):

London before patching:

{

"_id": "00005b99c948000000000000004c",

"Name": "London",

"Capital": true,

"Geography": {

"State": "England",

"Country": "United Kingdom",

"Location": {

"Type": "Point",

"Coordinates": [

-0.143,

51.501

]

}

},

"Demographics": {

"Population": 7285000

}

}

Number of documents updated: 1

London after patching:

{

"_id": "00005b99c948000000000000004c",

"Name": "London",

"Capital": true,

"Geography": {

"Country": "United Kingdom",

"Location": {

"Type": "Point",

"Coordinates": [

-0.143,

51.501

]

},

"NonSovereignCountry": "England"

},

"Demographics": {

"Population": 8000000,

"LifeExpectancy": 77.7

}

}

This concludes the examples of updating documents. The final CRUD method is to delete

documents.

Removing Documents

There are two methods to remove documents: remove()and removeOne(). The

remove() method finds documents similar to how the find() method does whereas

removeOne() finds a single document by the ID.

As an example, delete the first document sorted alphabetically by Name where the

country is the United States of America:

#!/usr/bin/env node

'use strict';



(async function() {

let session;

try {





const stmt = city.remove('Geography.Country = :country')

.sort('Name').limit(1);

stmt.bind('country', 'United States of America')

const result = await stmt.execute();


console.log(`Number of documents deleted: ${affectedDocs}`)


} catch (err) {


} finally {


}

})();

Script: remove.js

By now this should start to look familiar. The remove statement support mostly the

same methods as find and modify statements to determine which documents to

remove.

This concludes the tour of the CRUD methods. You are encouraged to keep playing with

the MySQL Document Store for the remainder of this hands-on lab as well as at home.

Please ask if you have any questions.

References

If you want to learn more about the MySQL Document Store, the following references

are useful:

X DevAPI User Guide: https://dev.mysql.com/doc/x-devapi-userguide/en/

MySQL Connector/Node.js with X DevAPI:

https://dev.mysql.com/doc/dev/connector-nodejs/8.0/

https://dev.mysql.com/doc/x-devapi-userguide/en/

https://dev.mysql.com/doc/dev/connector-nodejs/8.0/

MySQL Shell Documentation – JavaScript API:

https://dev.mysql.com/doc/dev/mysqlsh-api-javascript/8.0/

Using promises: https://developer.mozilla.org/en-

US/docs/Web/JavaScript/Guide/Using_promises

MySQL Shell 8.0 Manual: https://dev.mysql.com/doc/mysql-shell/8.0/en/

https://dev.mysql.com/doc/dev/mysqlsh-api-javascript/8.0/

https://developer.mozilla.org/en-US/docs/Web/JavaScript/Guide/Using_promises

https://developer.mozilla.org/en-US/docs/Web/JavaScript/Guide/Using_promises

https://dev.mysql.com/doc/mysql-shell/8.0/en/