Develop By Example – Document Store: working with collections using Node.js

In the previous blog post we explained how to create schemas and collections. In this one we are going to explain how to work with collections: adding, updating and deleting documents.

The following code demonstrates how to add a single document to an existing collection:

In the previous code, first we get the objects that represent the schema (schema) and the collection (coll) that we want to work with. An object using JSON (newDoc) is created and is passed as a parameter to the coll object’s add method. Calling the execute method the document is added to the collection. Once the execute method has finished, we receive an object (added) that contains information about the document added. To verify if the document was added, we can call the added object’s getAffectedItemsCount method, which will return how many documents were added.

But, what if you want to add multiple documents?

You can do it with almost no changes in your code. The following code adds two documents at the same time:

As you can see, the previous code is almost identical to the first example. We just add an extra line to declare the new document (newDoc2), and we add the new document as a parameter in the coll object’s add method. At the end we call added object’s getAffectedItemsCount method to verify we added the two documents.

Now we know how to add multiples documents to a collection using multiple variables and passing them as parameters, but we can also do the same using an Array object. In the following code example we create an array object to use it to add new documents to a collection.

The previous code is almost identical to the first example; the difference is that we pass an array object as a parameter instead of a JSON object. The rest of the code is the same. This could be useful if you receive an array of objects from the client or if you load the data from a JSON file, you just pass the whole array to upload it to the collection.

Updating a field in a document is also very easy to do. The following code is an example of how to do it:

In the previous code, first we get the objects that represent the schema (schema) and the collection (coll) we want to work. Then we declare the query variable which contains the where clause for our update. Next, we call the coll object’s modify method that receives the query variable as a parameter. Chained to the modify method is the set method, which receives a pair of objects; the first one is the field to update and the second one the new value to be set. As we did in our previous examples we call the execute method to perform the action requested. When the execute method finishes we receive an object (updated) with information about the update. To know how many documents were updated we call the updated object’s getAffectedItemsCount method.

Now that we know how to add and update documents in a collection, we are going to explain how to remove them. The following code demonstrates it.

The previous code defines the objects that represent the schema (schema) and the collection (coll) where we want to work. Then, we define the query variable again to contain the where clause for our operation, the remove in this case. To remove a document we call the coll object’s remove method followed by the execute method. Once the execute method is completed, we receive an object (deleted) with the information about the operation that has finished. By calling the deleted object’s getAffectedItemsCount method, we know how many documents were removed from the collection.

Now we are going to see how to get documents from a collection. In the following example, we are retrieving the document that match the _id that we want:

The previous code defines the objects that represent the schema (schema) and the collection (coll) we want to work with. Then the query variable is defined and the where clause is set to it. Then we call the coll object’s execute method to perform the query. When the execute method completes, we receive the document that match our search criteria and is send to the console to view it.

But, what if we want all the records from a collection? Well that is simple; we just need to remove the search criteria from the find method. The updated code would look like the following:

Now we know how to search a specific document and get all the documents from a collection. What if we want to get just some number of documents that match query criteria? The next example shows the code to do it:

The previous code looks very similar to the example that returns one document with a specific _id, the difference here is that our query is performing a like and we are adding a call to the coll object’s limit method. Note that the query statement is case sensitive; this means that if we have documents that have  ‘test’ in the field ‘name’, those documents will not be returned because we are searching for ‘Test’ names.

See you in the next blog post.

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:

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.

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.

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.

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:

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:

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.

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.

MySQL for Visual Studio 2.0.3 has been released

The MySQL Windows Experience Team is proud to announce the release of MySQL for Visual Studio 2.0.3 m2. Note that this is a development preview release and not intended for production usage.

MySQL for Visual Studio 2.0.3 M2 is the second development preview release of the MySQL for Visual Studio 2.0 series.  This series adds support for the new X DevAPI. The X DevAPI enables application developers to write code that combines the strengths of the relational and document models using a modern, NoSQL-like syntax that does not assume previous experience writing traditional SQL.

To learn more about how to write applications using the X DevAPI, see http://dev.mysql.com/doc/x-devapi-userguide/en/. For more information about how the X DevAPI is implemented in MySQL for Visual Studio, and its usage, see http://dev.mysql.com/doc/refman/5.7/en/mysql-shell-visual-studio.html.

Please note that the X DevAPI requires at least MySQL Server version 5.7.12 or higher with the X Plugin enabled. For general documentation about how to get started using MySQL as a document store, see http://dev.mysql.com/doc/refman/5.7/en/document-store.html.

You can download MySQL Installer from our official Downloads page at http://dev.mysql.com/downloads/installer/.

MySQL for Visual Studio 2.0.3 m2 can also be downloaded by using the product standalone installer found at http://dev.mysql.com/downloads/windows/visualstudio/, under the tab “Development Releases”.

Changes in MySQL for Visual Studio 2.0.3 m2

Bugs Fixed

  • The “mysqlx” module was not imported properly to execute JavaScript queries.  (Bug #23091964, Bug #81052)
  • After opening a valid MySQL connection and creating a new JavaScript MySQL script, disconnecting then reconnecting to the MySQL Server while changing the port to 33060 would fail.
  • MySQL for Visual Studio now shows a message stating that a SSL connection is required by the MySQL server if the require_secure_transport variable is set.
  • All script editors now display detailed information about the connection used. Before, the information was displayed in the toolbar as labels, but now all information is consolidated in a menu opened where the connection name is displayed. Additional information includes the connection method, host identifier, server version, user, and schema.
  • Output from executing JavaScript and Python commands were not visible unless the Output window was already opened.  The Output window now automatically opens when executing commands.

What’s new in 2.0.3 m2

  • Improved the handling of errors, warnings and execution stats output of X DevAPI statements. All messages are properly handled and displayed after batch or console execution.
  • Added SSL support for MySQL connections that use the X Protocol. SSL support works with PEM files, so SSL connections need to be created through the “MySQL Connections Manager” in MySQL for Visual Studio, or from MySQL Workbench.
  • Added support for the following X DevAPI functions:
    parseUri() and isOpen().
  • A new “MySQL Output” pane was added that contains a results grid view similar to the view found in MySQL Workbench. It contains the following data for executed statements: Success, Execution index, Execution Time, Query Text, Message (output from the server), and Duration / Fetch. This functionality is available for JavaScript and Python queries.
  • Added “Console Mode” support for JavaScript and Python script editors, where query execution mimics the way the MySQL Shell works, meaning X DevAPI statements are executed after hitting “ENTER” and results are displayed inline.
  • Added the ability to switch between “Batch” (execute multiple statements) and “Console” (execute each statement after pressing Enter) modes, from the Query Editor toolbar as a dropdown list.
  • A MySQL connection manager dialog was added to help fully manage MySQL connections. It supports connection sharing with MySQL Workbench, and supports create, edit, configure, and delete actions.  MySQL connections created with the connection manager where the password is securely stored in the system’s password vault functions with the Server Explorer in Visual Studio. The password is extracted from the password vault, and persists in the Server Explorer connections.

Known limitations

  • Some features such as Entity Framework and some Server Explorer functionality like drag & drop elements into a Dataset Designer or Design Tables do not work in this version.

Quick links

Enjoy and thanks for the support!

MySQL for Visual Studio Team.

MySQL Connector/NET 7.0.3 m2 development has been released

MySQL Connector/Net 7.0.3 is the second development release of MySQL Connector/Net  7.0 series.

MySQL Connector/Net 7.0 adds support for the new X DevAPI which enables developers to write code that combines the strengths of the relational and document models using a modern, NoSQL-like syntax that does not assume previous experience writing traditional SQL.

To learn more about how to write applications using the X DevAPI, see this User’s Guide. For more information about how the X DevAPI is implemented in Connector/Net, please check the official product documentation.

Please note that the X DevAPI requires at least MySQL Server version 5.7.12 or higher with the X Plugin enabled. For general documentation about how to get started using MySQL as a document store, see this chapter at the reference manual.

Changes in MySQL Connector/Net 7.0.3 (2016-06-20, Milestone 2)

Functionality Added or Changed:

  • Fixed binary collations as strings instead of bytes.
  • Added TLS support for TLSv1.1 and TLSv1.2 when connecting to MySQL Server 5.7.

Bugs Fixed:

  • Added results to the Commit() and Rollback() Session X DevAPI methods, in order to read Warnings. This feature has limitations that will be addressed in a future release.
  • Replaced the use of “@” for “$” in JSON path expressions for X DevAPI usage. This feature has limitations that will be addressed in a future release.
  • Added X DevAPI support for TLSv1.0. This feature has limitations that will be addressed in a future release.

Nuget packages are available at:

https://www.nuget.org/packages/MySql.Data/7.0.3-DMR

https://www.nuget.org/packages/MySql.Data.Entity/7.0.3-DMR

https://www.nuget.org/packages/MySql.Fabric/7.0.3-DMR

https://www.nuget.org/packages/MySql.Web/7.0.3-DMR

We love to hear your thoughts or any comments you have about our product. Please send us your feedback at our forums, fill a bug at our community site, or leave us any comment at the social media channels.

Enjoy and thanks for the support!

On behalf of the MySQL Release Team

MySQL Connector/J 5.1.39 has been released

I’m pleased to announce that MySQL Connector/J 5.1.39 Maintenance Release is now generally available.

MySQL Connector/J can be downloaded from the official distribution channels MySQL Downloads and The Central Repository. The commercially licensed version is available for download at My Oracle Support.

As always, we recommend that you check the CHANGES file in the download archive and/or the release notes page to know what is new and if there are any changes that might affect your applications.

With MySQL Connector/J 5.1.39 you get the continuously improved JDBC driver for MySQL databases, now including several fixes and upgrades. Even if you didn’t face any of the fixed issues, we do recommend that you upgrade to the latest version.

I’d like to highlight the most relevant fixes and improvements in this release:

MySQL Fabric support related issues

As you well know by now, MySQL Fabric support in Connector/J is built on its generic multi-host connections feature, more specifically on replication connections. This feature, along with load-balanced connections support, have received several improvements all over the latest releases. This time, we fixed and tuned up a few incoherences that were spotted on a few corner cases, namely when, due to the Fabric management process, the list of known hosts in an active connection could actually become empty for a short period of time while performing the fail-over, and so causing a whole set of new problems. As a consequence of these developments, we introduced a new connection property:

loadBalanceHostRemovalGracePeriod. This property sets the time, in milliseconds, that the driver should wait for a load-balanced connection to be allowed to switch to a different host when the currently active one is being removed, either by an internal process such as in a Fabric or a replication connection, or by dynamic hosts management, through the JMX interfaces the driver provides. Default value is 15000 milliseconds.

Additional improvements and fixes

  • Unnecessary exception throwing and capturing when establishing connections.
  • Temporal data corruption in prepared statements under special circumstances.
  • Incorrect JDBC 4.2 Java 8 Time support when using cached prepared statements.
  • Application server using multiple class loaders could face a NullPointerException when setting up the driver’s time zone configurations.
  • Exception caused by missing metadata information in updatable result sets.
  • Concurrent modification issue on closing statements and connections concurrently from different threads.
  • Maintenance fixes in the test suite.
  • Source code formatting, Copyright notice fixes.
  • Build script adjustments, new code coverage reports, compiler warnings cleanup.
  • Manifest fixed to expose Fabric connections.
  • Support for latest changes in MySQL protocol, introduced in MySQL 5.7, namely the deprecation of EOF packets.

Thanks!

Enjoy this new release of Connector/J and stay tuned for the next releases as there is still much to deliver.

Get the most out of this Connector/J release by reading its official documentation or by getting the developer’s support directly from the forum channel.

Thank you all for your support and keep in touch!

On behalf of the MySQL Connector/J Team

MySQL for Visual Studio 2.0.2 has been released

The MySQL Windows Experience Team is proud to announce the release of MySQL for Visual Studio 2.0.2 m1. Note that this is a milestone release and not intended for production usage.

MySQL for Visual Studio 2.0.2 m1 is the first development release of MySQL for Visual Studio to add support for the new X DevAPI. The X DevAPI enables application developers to write code that combines the strengths of the relational and document models using a modern, NoSQL-like syntax that does not assume previous experience writing traditional SQL.

For more information about how the X DevAPI is implemented in MySQL for Visual Studio, and its usage, please refer to the MySQL for Visual Studio Quick-Start Guide.

Please also note that the X DevAPI requires at least MySQL Server version 5.7.12 or higher with the X Plugin enabled. For general documentation about how to get started using MySQL as a document database, see Using MySQL as a Document Store.

You can download MySQL for Visual Studio 2.0.2 m1 at this link http://dev.mysql.com/downloads/windows/visualstudio/, under the tab “Development Releases”.

What’s new in 2.0.2 m1

  • Added support for the new X DevAPI.
  • Updated the MySQL parser’s grammar to include keywords introduced in MySQL 5.7.
  • Minor optimizations to script editor window.

Known limitations

  • SSL connections are not yet supported.

Quick links

Enjoy and thanks for the support!

MySQL for Visual Studio Team.

Announcing MySQL Connector/J 6.0

We are pleased to announce the first release of MySQL Connector/J 6.0! This is a new branch of development, which breaks from some of the traditions of the very stable and very mature Connector/J 5.1 branch. We have combed through lots of code and refactored it in order to support development of future features including our X DevAPI implementation and support for X Protocol. This work manifests in a number of visible changes, including revising the growing set of connection and build properties.

Beginning with Connector/J 6.0, we are moving away from having one jar that supports all versions of Java. Instead we are building one jar/package for every supported version of Java. This simplifies the build process as well as lots of code that was required to support many versions of Java. The current package is built for Java 8.

Note that Connector/J 6.0.2 is a milestone release and not intended for production usage.

All changes are documented in the MySQL Connector/J 6.0 Developer Guide.

Updates to connection properties, including removal of several options that are no longer needed with the latest versions of MySQL and Java. Additionally, we removed some legacy behavioral options. You can see the changes in the documentation:
Connector/J 6.0 Changes in Connection Properties

If you are building from source or running the test suite, check the following:
Connector/J 6.0 Changes for Build Properties
Connector/J 6.0 Changes for Test Properties

Finally, API changes and package reorganizations are documented at:
Changes in the Connector/J API

Release notes are available at Changes in MySQL Connector/J 6.0.2 (2016-04-11, Milestone 1).

MySQL Connector/Net 7.0.2 has been released

Dear MySQL users,

MySQL Connector/Net 7.0.2 M1 is the first development release that adds support for the new X DevAPI.  The X DevAPI enables application developers to write code that combines the strengths of the relational and document models using a modern, NoSQL-like syntax that does not assume previous experience writing traditional SQL.

To learn more about how to write applications using the X DevAPI, see this User Guide. For more information about how the X DevAPI is implemented in Connector/Net, please check the official product documentation.

Also note that the X DevAPI requires at least MySQL Server version 5.7.12 or higher with the X Plugin enabled. For general documentation about how to get started using MySQL as a document store, see this chapter at the reference manual.

Changes in MySQL Connector/Net 7.0.2 (2016-04-11, Milestone 1)

Functionality Added

  • Added support for the new X DevAPI

Nuget packages are available at:

https://www.nuget.org/packages/MySql.Data/7.0.2-DMR

https://www.nuget.org/packages/MySql.Data.Entity/7.0.2-DMR

https://www.nuget.org/packages/MySql.Fabric/7.0.2-DMR

https://www.nuget.org/packages/MySql.Web/7.0.2-DMR

We love to hear your thoughts or any comments you have about our product. Please send us your feedback at our forums, fill a bug at our community site, or leave us any comment at the social media channels.

Enjoy and thanks for the support!

On behalf of the MySQL Connector/Net Team