Develop By Example – Creating schemas and collections using Node.js

In a previous post we explained how to connect to a MySQL server configured as a document store using the new MySQL Connector/Node.js. In this post we are going to explain how to create a schema, create a collection and add documents to the collection.

Creating a new schema is very easy; the following code demonstrates how to do it:

var mysqlx = require('mysqlx');
mysqlx.getSession({
  host: 'host',
  port: '33060',
  dbUser: 'root',
  dbPassword: 'my pass'
}).then(function (session) {
  session.createSchema('mySchema').then(function(schema){
    console.log('Schema created');
    session.close();
  });
}).catch(function (err) {
  console.log(err.message);
  console.log(err.stack);
});

In the previous code example we created a connection and then used the XSession object to create a schema, finally we closed the connection.

The first line of code loads the Connector/Node.js client module, mysqlx. We then call its getSession() method to get the object that we are going to use to create the schema. We then call the createSchema method to create our schema; once the schema is created we call the session object’s close method to close our connection.

But, what will happen if we try to create a schema that already exists? An error will be thrown.

To avoid that situation we can verify if the schema exists before trying to create it.

var mysqlx = require('mysqlx');
mysqlx.getSession({
  host: 'host',
  port: '33060',
  dbUser: 'root',
  dbPassword: 'my pass'
}).then(function (session) {
  var schema = session.getSchema('mySchema');
  schema.existsInDatabase().then(function (exists) {
    if (!exists) {
      session.createSchema('mySchema').then(function(schema){
      console.log('Schema created');
      session.close();
      });
    }
    else {
      console.log('Schema already exists');
      session.close();
    }
  });
}).catch(function (err) {
  console.log(err.message);
  console.log(err.stack);
});

In the previous code, before trying to create the schema we call the session object’s getSchema method. The object (schema) returned represents the schema we want to create, we then call its existsInDatabase method which will return false if the schema does not exist in the server or true otherwise. The following lines of code attempt to create the schema. The program notifies the user if the schema is created successfully, or if the schema already exists. Then it closes the session.

Now that we know how to create a schema and verify if it already exists in the database, it is time to work with collections. When creating a collection, we need to know which schema object it will belong to. This can be a new schema, or an existing one. If we try to create the collection in a schema where that collection is already present, the program returns an error. The following example demonstrates how to check if a collection already exists before creating it.

var mysqlx = require('mysqlx');
mysqlx.getSession({
  host: 'host',
  port: '33060',
  dbUser: 'root',
  dbPassword: 'my pass'
}).then(function (session) {
  var schema = session.getSchema('mySchema');
  schema.existsInDatabase().then(function (exists) {
    if (!exists) {
      session.createSchema('mySchema').then(function(schema){
        console.log('Schema created');
     });
    }
    else {
      console.log('Schema already exists');
    }
  });

  var coll = schema.getCollection('myColl');
  coll.existsInDatabase().then(function (exists) {
    if(!exists){
      schema.createCollection('myColl').then(function (coll) {
        console.log('Collection created');
        session.close();
      });
    }
    else{
      console.log('Collection already exists');
      session.close();
    }
  });
}).catch(function (err) {
  console.log(err.message);
  console.log(err.stack);
});

In the previous code, the first lines are the same for the code to verify if a schema exists if not it is created. Then we call the schema object’s getCollection method which returns an object that represents the collection (coll) we want to create. As we did for the schema object, we call the coll object’s existsInDatabase method. If the collection does not exist, it is created by calling the schema object’s createCollection method and we receive a confirmation message. If the collection already exists we are notified of the fact. Finally, the session is closed.

To add new documents to a collection we need to use a JSON notation for the document to create. In the following code, we are going to demonstrate how to add a document to a collection, bear in mind that in the code we assume that a schema and a collection already exist in the server.

var mysqlx = require('mysqlx');
mysqlx.getSession({
     host: 'host',
     port: '33060',
     dbUser: 'root',
     dbPassword: 'my pass'
}).then(function (session) {
  var schema = session.getSchema('mySchema');
  var coll = schema.getCollection('myColl');
  var newDoc = { name: 'Test Name', description: 'Test Description' };

  coll.add(newDoc).execute().then(function (added) {
    console.log('Document(s) added: ' +
                 added.getAffectedItemsCount());
    session.close();
  })
  .catch(function (err) {
    console.log(err.message);
    console.log(err.stack);
   });
}).catch(function (err) {
  console.log(err.message);
  console.log(err.stack);
});

In the previous code to add a new document, first, we get the objects that represent the schema (schema) and the collection (coll) that we want to work with. Then we created an object using JSON (newDoc). To add the document to the collection we call the coll object’s add method followed by the execute method, once the execute method has finished we receive an object (added) that contains information about the document added. By calling the added object’s getAffectedItemsCount method we know if the document was added.

In the next blog post, we are going to explain in more detail how to work with collections.
See you then.

Develop By Example – Document Store Connections using Node.js

In this post we are going to explain how to connect a Node.js application to a MySQL server using the new MySQL Connector/Node.js; needless to say that we will be using the MySQL server as a document store.

There are two types of session that a connection can provide: XSession and NodeSession.
An XSession encapsulates access to a single MySQL server running the X Plugin or
multiple MySQL Cluster nodes; and the NodeSession serves as an abstraction for a physical connection to exactly one MySQL server running the X Plugin. To enable the XPlugin in the MySQL server using the MySQL Client command line you need to use the root account or an account with INSERT privilege to mysql.plugin table:

  • Invoke the MySQL command-line client: mysql -u user –p
  • Run the following command: INSTALL PLUGIN mysqlx SONAME ‘mysqlx.so’;

Click here for more information about how to setting up MySQL as document store.

Creating a connection to a MySQL server as a document store is quite similar to create a connection to a traditional MySQL server; we require the following connection parameters: host, database user, user password, and port.

The following example demonstrates how to connect to a single MySQL Server using XSession:

var mysqlx = require('mysqlx');
mysqlx.getSession({
        host: 'localhost',
        port: '33060',
        dbUser: 'testUser',
        dbPassword: 'myPass'
}).then(function (session) {
        console.log('We are connected!');
        session.close();
}) .catch(function (err) {
        console.log(err.message);
        console.log(err.stack);
});

In the previous code example, we created and closed a connection to a server using an XSession; as you can see the code is very simple and easy to read.

The first line of code loads the Connector/Node.js client module, mysqlx. We then call its getSession method. This method implements a promise. If the connection to the MySQL server is successful the promise is fulfilled by returning an XSession (session) object. We then call the session object’s close method to close the connection.

In the previous code there are two important things to note. The first one is that we do not specify a schema because the XSession works similar to a traditional session: You do not need to specify a schema because, at the time you connect, your working schema might not exist yet. The second one is the port. By default the X DevAPI uses the port 33060; we are assuming that the running server is using the default port for TCP/IP connections. The port can be configured when the server starts and is stored in a server variable.

The following example demonstrates how to connect to a single MySQL Server using NodeSession:

var mysqlx = require('mysqlx');
mysqlx.getNodeSession({
        host: 'localhost',
        port: '33060',
        dbUser: 'testUser',
        dbPassword: 'myPass'
}).then(function (nodeSession) {
        console.log('We are connected!');
        nodeSession.close();
}) .catch(function (err) {
        console.log(err.message);
        console.log(err.stack);
});

The NodeSession example code is almost the same code used to get an XSession object, the difference is the method that is called to get the session object and the port; the code does exactly the same.

You might need to use a NodeSession in certain scenarios where you require access to SQL features that are not supported by an XSession. In a subsequent post we are going to cover some examples about how to use the NodeSession.

To work with schemas and collections we need to add some extra lines of code. The following code demonstrates how to do it.

var mysqlx = require('mysqlx');
mysqlx.getSession({
        host: 'localhost',
        port: '33060',
        dbUser: 'testUser',
        dbPassword: 'myPass'
}).then(function (session) {
     var schema = session.getSchema('test');
     var coll = schema.getCollection('myColl');

     coll.find("$._id == '1'").execute(function (myDoc) {
           return myDoc;
     }).catch(function (err) {
           console.log(err.message);
           console.log(err.stack);
     });
     session.close();
}) .catch(function (err) {
        console.log(err.message);
        console.log(err.stack);
});

In the last code example; from the session object, we call the getSchema method to get an object (schema) that represents the schema in which we want to work. Once we have the schema object we execute the method getCollection to get an object (coll) that represents the collection we want to work with. In this example, we want to retrieve the document with an id value of ‘1’ from the collection. First we call the find method passing the JSON path and value we are searching for. Then we call the execute method to perform the query. The execute method returns a promise which supplies the requested document when the method completes.

See you in the next blog post where we are going to explain more about the operations that can be performed using collections.

Develop By Example – New MySQL Document Store Series

Examples are a great way to learn new things. As many of you may or may not know we’ve added some new things to MySQL Server 5.7.12  and the ecosystem around it, extending it  to allow you to use the MySQL as a Document Store. Meeting the challenge meant expanding Developer Interfaces and Database tools.

  • Addressing information with a both classic and modern data architectures
  • For all types of data – structured, semi, and unstructured
  • Empowering developers – Simpler, Faster, Flexible
  • Leveraging latest NoSQL oriented tools/methods – JavaScript, Node.js, JSON, CRUD, Methods chaining, and more

From the developer side the MySQL Document Store new APIs by introducing a JSON/Document Store oriented called the MySQL X DevAPI. This programming API provides the option for accessing MySQL; and its design unifies JSON document and table access; and it includes SQL support as well. Since the API features a popular fluent interface style, you will be able to use a NoSQL-like syntax to execute Create, Read, Update, Delete (CRUD) operations against these documents.

This new API is provided to developers in our latest MySQL Connectors. As again as learning by example is often the best way to get started with new things we’re also providing an example application we’re calling Movie Review to show you how it’s used in real applications with use cases that likely map to how you’d want to develop.

As there are a range of languages supported by the MySQL Connectors and Drivers – New Connector/Node.js as well as connectors for Java, Python, .NET, C, C++, and PHP – we plan to do Movie Review examples applications across the various languages.
Were calling our new example application – Movie Review – and as you might have guessed it revolves around developing web applications that allows users to review movies via a simple application that demonstrates the usage of the new features available in the X Dev API and connectors.

We will have 2 user types within the application – users and administrators.

The users can:

  • Search for a movie to view its description and any existing reviews
  • Review a movie
  • Update or delete a review they have written

The administrators can:

  • See the movies to view its description and any existing reviews
  • Upload new information to the database manually or from a JSON file
  • View the current data in the collections to edit it or delete it
  • View some simple reports.

With these use cases we hope to quickly and simply take you through the key development concepts quickly using CRUD type programming.

The Document Store Data Model

Since the application will be kept simple, we’re also including simple document store database you can easy load. It includes four collections: Actors, Movies, Reviews, and Users. It comes with the entire example data loaded that you’ll need as well. This will help to teach you some of the basics of document store style modeling.

We’ll provide the steps to install, etc in the example blogs and have you up and running and developing with MySQL Document Store in no time.

See you in the next blog post – where we get into the Movie Review application written with Node.js.