Prasad Saya

Posted on Sep 16, 2023

Using MySQL as a NoSQL Document Store - Introduction

#mysql #json #nosql #database

MySQL is mostly known to be SQL database server. To store JSON data it has table columns of JSON data type. In addition, MySQL can be used as a document store and perform "NoSQL" operations. This post is about getting an introduction to this feature.

Specialized clients can perform SQL as well as CRUD operations on the document database. These clients are MySQL Shell and the MySQL Connectors. MySQL Shell is an interactive interface using JavaScript, Python or SQL modes. Connectors are used for developing applications using programming languages like Java, NodeJS, Python, C++, etc.

The main components for using MySQL as document store are X Plugin, X Protocol and X DevAPI.

X Plugin enables MySQL Server to communicate with these clients. These clients communicate with a MySQL Server using X Protocol. X DevAPI is used to develop client applications.

In this post we will use MySQL Shell to perform some operations on document store.

To try the code samples you will require an installation of MySQL Server and MySQL Shell on your computer. I had used MySQL Server and Shell with the version 8.0.34 on a Windows 11 computer.

MySQL Server

In case you need to install MySQL newly, there are instructions from the GitHub Gist: Basic steps to install MySQL 8 Server and create database data

Verify X Plugin is installed on the server using the following command from the mysql client:

mysql> SHOW plugins;
+-------------------+----------+
| Name              | Status   | ...
+-------------------+----------+
...
| mysqlx            | ACTIVE   | ...
...

MySQL Shell

The next step is to install MySQL Shell software.

Requirement

Make sure you have the Visual C++ Redistributable for Visual Studio 2015. You can verify if already installed from Windows Control Panel -> Programs and Features.

Download and install

Visit the MySQL website and navigate to MySQL Community Downloads -> MySQL Shell

Starting and connecting to MySQL Server

Make sure your MySQL Server is started and running. We are using the Windows command prompt.

From the MySQL Shell install directory:

command prompt>> bin\mysqlsh
MySQL Shell 8.0.34
...
mysql-js>

By default the shell starts in JS mode.

Note the MySQL Shell is not connected to the server. The connection to MySQL Server instance is handled by a session object.

To see information about the current global session:

mysql-js> shell.status()
MySQL Shell version 8.0.34
Not Connected.

Connect to the server

mysql-js> \connect mysqlx://user_name@localhost:33060
mysql-js> shell.status()
MySQL Shell version 8.0.34
Connection Id: 28
Server version: 8.0.34 MySQL Community Server - GPL
Current user: user@localhost
Protocol version: X protocol
...

NOTE: You can also start mysqlsh with a session from command prompt:
command prompt>> mysqlsh mysqlx://user_name@localhost:33060

Exit the MySQL Shell.

mysql-js> \quit

MySQL Shell Global Objects

MySQL Shell includes a number of built-in global objects that exist in both JavaScript and Python modes. Some useful ones are the session, db and shell. Their usage is shown in this post's code samples.

db is available when the global session was established using an X Protocol connection with a default database specified, and represents that schema.
session is available when a global session is established, and represents the global session.
shell provides access to various MySQL Shell functions.

Document Store

The main concepts are the JSON Document, Collection and CRUD (Create, Read, Update and Delete) operations.

A JSON document is a data structure composed of key-value pairs. This is the fundamental structure for using MySQL as document store. The document key-value values can be simple data types, such as integers or strings, but can also contain other documents, arrays, and lists of documents.

A JSON document is represented internally using the MySQL binary JSON object, through the MySQL JSON datatype.

Note that a document does not need to have a predefined structure. A collection can contain multiple documents with different structures.

A collection is a container that is used to store JSON documents in a MySQL database. You can perform CRUD operations against a collection of documents.

Each collection has a unique name and exists within a schema. Schema is equivalent to a database.

Collection - create, list and delete

Start MySQL Shell and connect to the server.

mysql-js> db                    /* this returns empty, as no existing schema is specified with the connection */
mysql-js> \use <existing_db>    /* specify a default schema, and it must be an existing schema */

mysql-js> \sql                  /* change the mode to sql */
                                /* check what databases exist, and can create one if needed */
                                /* in SQL mode you can use mysql SQL commands */
SQL> SHOW DATABASES;        -- list databases
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
...

SQL> CREATE DATABASE x_db;  -- create a new database
SQL> \js                    -- return to JavaScript mode

mysql-js> \use x_db;                    // make the newly created database as default database
mysql-js> db
Default schema `x_db` accessible through db.

mysql-js> db.getCollections()           // list collections in the default schema
[]
mysql-js> db.createCollection("users")  // create a new collection in the database
<Collection:users>
mysql-js> db.getCollections()
[
    <Collection:users>
]
mysql-js> db.dropCollection("users")    // delete the collection

CRUD Operations on the Collection

CREATE

mysql-js> db.users.add({ name: "Jerry", age: 10 })  // insert a new document into the collection

mysql-js> db.users.find()                           // list all documents in the collection
{
    "_id": "000065017f9d0000000000000001",
    "age": 10,
    "name": "Jerry"
}

Note the _id field. The _id field is a mandatory field for each document in the collection. This acts as a primary key. It is (and must be) a unique value within the collection. Its value is set by the server (when not provided) or can be provided. It is immutable (cannot be changed or deleted).

mysql-js> db.users.add({ name: "Jill", age: 12 }, { name: "Jim", age: 9 })  // insert two documents
mysql-js> db.users.add({ name: "Jack", age: 8, city: "New York" })          // insert document with different structure
mysql-js> db.users.add({ _id: "myid_99", name: "Jamie", age: 11 })          // insert a document with a provided _id

mysql-js> db.users.count()
5

READ

db.users.find("name = 'Jack'")              // search for specific document(s) using a filter
db.users.find("name LIKE 'Ji%' AND age < 10")

The following operators can be used to specify search conditions: OR (||), AND (&&), XOR, IS, NOT, BETWEEN, IN, LIKE, !=, <>, >, >=, <, <=, &, |, <<, >>, +, -, *, /, ~, and %.

The syntax for expressions that form a search condition is the same as that of traditional MySQL. Note that you must enclose all expressions in quotes.

db.users.find().fields([ "_id", "name" ])   // project specific field(s) in output

db.users.find().fields([ "_id" ]).limit(1)  // read only one document
db.users.find().sort("age desc")            // read documents sorted by age descending

UPDATE

db.users.modify("name = 'Jack'").set("city", "Chicago")             // change Jack's city as Chicago
db.users.modify("name = 'Jack'").unset("city")                      // remove the city field for Jack
db.users.modify("true").set("info", { state: "NY", favs: [] })      // update all documents with a new object field

db.users.modify("name = 'Jack'").arrayAppend("$.info.favs", "Red")  // add a value to the array field for Jack
db.users.modify("name = 'Jim'").set("info.city", "LA")              // update an object field

DELETE

db.users.remove("name = 'Jill'");   // remove documents with a condition
db.users.remove("true").limit(1);   // remove one document
db.users.remove("true");            // remove all documents in collection

Other Notable Features

You can create indexes on the document field(s).
You can define document validation i.e., verify documents against a JSON schema. This enables that all documents in the collection have a certain predefined structure.
There are operations on the collections which work with one document only. These are getOne, replaceOne, addOrReplaceOne and removeOne. All these methods take the string _id value as a parameter. For example, db.users.getOne("myid_99")

Working with Result Sets

All CRUD operations discussed above return a result set object with various attributes.

The add, modify and remove operations return a Result class object. This has details about number of rows affected by the operation, auto generated document IDs, warnings, etc., depending on the operation.

The find operation returns a DocResult class object. This is the The fetched data set.

Using the MySQL Shell in JS mode:

// Add a new document, and verify the result object

var r = db.users.add({ name: "John" }).execute()
r.getAffectedItemsCount()   // 1
r.getGeneratedIds()         // [ "000064f987110000000000000004" ]

Note the execute method. This is to be specified when using the JavaScript code in MySQL Shell. In interactive mode this is automatic.

// Get all documents and print one document at a time:

var r = db.users.find().execute()
var doc
while (doc = r.fetchOne()) {
    print(doc)
}

// Get all documents as a list object:

var r = db.users.find().execute()
var list = r.fetchAll()
print(list)
print(list.length)
for (index in list) { 
    print(list[index].name) 
}

// And, use the list as JS array:
[...list].map(e => e.name).forEach(e => print(e, " "))

Working with Relational Tables

Perform CRUD operations on MySQL relational tables in the MySQL Shell.

First, the table(s) to work with need to be created in MySQL Shell's SQL mode or in mysql client. For example, we work with cities table in the current schema.

SQL> CREATE TABLE cities (city VARCHAR(20) NOT NULL PRIMARY KEY, state VARCHAR(2) NOT NULL);

MySQL Shell JS mode:

db.getTables()  // list all tables in the current database

db.cities.insert([ "city", "state" ]).values("New York", "NY")
db.cities.insert().values("Los Angeles", "CA")

db.cities.select()
db.cities.select(["city"]).where("state = 'NY'").orderBy("city asc")

db.cities.update().set("city", "Buffalo").where("city = 'New York'")

db.cities.delete().where("city = 'Buffalo'")

The above CRUD operation functions have the same results as when used with SQL.

Like the result set for the collections's find, the table's select also returns a Result object. You can use the fetchOne and fetchAll methods on it.

In addition, you can also run SQL operations using the session object. For example, session.sql("SELECT * FROM cities"). This returns a SqlResult class object.

Conclusion

The X DevAPI allows working with JSON data and relational table data within the same application program using the "NoSQL" syntax. This has advantages from the flexibility of the JSON document and the strict rules used with relational data.

You can also use the db.getCollectionAsTable("collection_name") to use the collection as table and use the table CRUD syntax on it. For example, db.getCollectionAsTable("users").select().

DEV Community