MySQL Connector/NET 8.0.11 is available as official MySQL NuGet packages

Dear MySQL Connector/NET users,

We are proud to announce that the open source version of Connector/NET 8.0.11 is also available as official MySQL NuGet packages. Connector/NET 8.0.11 is a GA release that can be used in production environments. Additionally, users with an older version can safely upgrade if preferred.

 

What this means for Connector/NET users?

Installing and upgrading packages couldn’t be easier, plus you need only to install the packages that fit your needs. Also, it’s even easier and more straight forward to develop for MySQL from Visual Studio.

NuGet is the package manager for the Microsoft development platform including .NET. The NuGet client tools provide the ability to produce and consume packages. The NuGet Gallery is the central software package repository populated by various package authors and tied directly to developers for ease of installation and development. For more information on NuGet see: https://github.com/nuget/home

Connector/NET NuGet Packages

Currently there are 5 Connector/NET NuGet packages:

  • MySQL Connector/NET Data
    • Provides the core functionality of Connector/NET including using MySQL as a Document Store through the X DevAPI. It implements the required ADO.NET interfaces and integrates into ADO.NET-aware tools. Provides access to multiple versions of MySQL Server and encapsulates database-specific protocols.
    • Supports .NET Framework 4.5.2, .NET Standard 1.6 & 2.0.
    • Refer to the official documentation for additional details.
    • Refer to the X DevAPI User Guide for details on using MySQL as a Document Store with Connector/NET.
  • MySQL Connector/NET Web providers
    • Includes support for the ASP.NET 2.0 provider model. This model enables application developers to focus on the business logic of their application instead of having to recreate boilerplate items such as membership and roles support.
    • Supported providers:
      • Membership
      • Role
      • Profile
      • Session State
    • Supports .NET Framework 4.5.2.
    • Refer to the ASP.NET Provider Model section of the official documentation for additional details.
  • MySQL Connector/NET for Entity Framework 6
    • Provides ORM capabilities enabling developers to work with MySQL databases using domain-specific objects, thus eliminating the need for most of the data access code.
    • Supports .NET Framework 4.5.2.
    • Refer to the Entity Framework 6 Support section of the official documentation for additional details.
  • MySQL Connector/NET for Entity Framework Core
    • Provides multi-platform support for Entity Framework tasks.
    • Supports .NET Framework 4.5.2, .NET Standard 1.6 & 2.0.
    • Refer to the Entity Framework Core Support section of the official documentation for additional details.
  • MySQL Connector/NET for Entity Framework Core Design
    • Provides shared design-time components for Entity Framework Core tools allowing the scaffolding and migration of databases.
    • Supports .NET Framework 4.5.2, .NET Standard 1.6.
    • Refer to the Entity Framework Core Support section of the official documentation for additional details.

In order to install the NuGet Package you can use the Package Manager Console as follows:

PM> Install-Package MySql.Data.EntityFrameworkCore

PM> Install-Package MySql.Data.Web

Or via Visual Studio’s NuGet Package Manager UI: http://docs.nuget.org/docs/start-here/Managing-NuGet-Packages-Using-The-Dialog

Note that packages for Web Providers and Entity Framework have a dependency with the core package MySql.Data, meaning their installation will also result in the installation of the MySql.Data package. Installation through the standalone MSI is recommended whenever you want to make use of all the functionality provided by Connector/NET E.g. availability in the GAC.

For more information or to go directly to our NuGet Packages review the following links:

For those of you interested in how to use Connector/NET with .NET Core you can refer to the MySQL Connector/NET for .NET Core 1.0 blog post.

If you happen to have any questions about this or any other Connector/NET related topic, you can always post them in the product’s main forum. Additionally, bugs can be reported in MySQL Bugs.

Connector/NET Team

MySQL for Visual Studio – Rollback during installation/uninstallation

A number of reports have been raised against versions 1.2.7 and 2.0.5 where the installation/uninstallation of MySQL for Visual Studio would rollback in Windows 7 environments preventing the operation from completing. We are glad to announce this has been addressed in the upcoming 1.2.8 version.

 

What is causing the failure?

It’s been identified that in most cases the rollback occurs when Visual Studio fails to execute the “devenv /updateconfiguration /log” command. Said command signals Visual Studio to merge the Visual Studio packages on the system and to check the MEF cache for any changes allowing it to register/unregister MySQL for Visual Studio. The failure is caused by faulty permissions when trying to execute any “devenv” command and is attributed to a known issue in Windows 7 environments with Visual Studio 2017 as indicated by several online reports such as:

 

Changes applied to MySQL for Visual Studio installer

MySQL for Visual Studio 1.2.8 msi installer has been updated to attempt to execute the command and to display a warning in the event that the execution fails, preventing the installation/uninstallation from rolling back. However, when said warning is raised, users are recommended to manually execute the command via the Developer Command Prompt for VS2017 to ensure the changes are reflected in Visual Studio.

The Developer Command Prompt for VS2017 is a tool included as part of the installation of Visual Studio 2017. It can be easily accessed by going to All Programs -> Visual Studio 2017 -> Visual Studio Tools. Alternatively the user can open any command prompt window and navigate to “<drive>:\Program Files (x86)\Microsoft Visual Studio\2017\<vs_version>\Common7\IDE” from where the “devenv /updateconfiguration /log” command can be executed as well.

 

Installation/uninstallation of MySQL for Visual Studio 1.2.7 / 2.0.5 workaround

As previously stated, the released versions of MySQL for Visual Studio 1.2.7 and 2.0.5 are affected by this issue, however there is a known workaround which is detailed below:

  1. If the user has custom settings set in the Visual Studio environment, proceed to export them via the Tools->Import and Export Settings option as they will be lost when executing step 2.
  2. Go to “%localappdata%\Microsoft\VisualStudio\15.0_<instance_id>” and delete the “privateregistry.bin” file.
  3. Attempt the installation/uninstallation again, it should succeed.
  4. Open Visual Studio. If for any reason Visual Studio fails to start with an “Unknown error” or any other error, again delete the “privateregistry.bin” file and then open Visual Studio.
  5. Import settings if applicable.

MySQL for Visual Studio 1.2.8 has been released

Dear MySQL users,

The MySQL Windows Experience Team is proud to announce the release
of MySQL for Visual Studio 1.2.8.

This is a maintenance release for 1.2.x. It can be used for
production environments.

MySQL for Visual Studio is a product that includes all of the
Visual Studio integration functionality to create and manage
MySQL databases when developing .NET applications.

MySQL for Visual Studio is installed using the MySQL Installer
for Windows which comes in 2 versions:
* Full (386.6 MB) which includes a complete set of MySQL products
with their binaries included in the downloaded bundle.
* Web (1.7 MB – a network install) which will just pull MySQL
for Visual Studio over the web and install it when run.

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

MySQL for Visual Studio can also be downloaded by using the product
standalone installer found at
http://dev.mysql.com/downloads/windows/visualstudio/.

Changes in MySQL for Visual Studio 1.2.8 (2018-05-04, General
Availability)

* Functionality Added or Changed

* Bugs Fixed

Functionality Added or Changed

* When Microsoft Visual Studio receives a request to
register (or unregister) MySQL for Visual Studio within
the IDE during the installation, Visual Studio might not
execute the command properly if the host is running
Windows 7. This fix identifies when Visual Studio does
not register MySQL for Visual Studio as requested and
then provides an alert to perform the registration
manually from the Developer Command Prompt for Visual
Studio using the following command:

devenv /updateconfiguration /log

(Bug #27365261, Bug #87902)

* MySQL for Visual Studio now supports the MySQL 8.0
release series (requires Connector/Net 6.9.12, 6.10.7, or
8.0.11) including:

+ MySQL data dictionary, which uses INFORMATION_SCHEMA
tables rather than tables in the mysql database (see
MySQL Data Dictionary
(http://dev.mysql.com/doc/refman/8.0/en/data-dictionary.html)).

+ The caching_sha2_password authentication plugin
introduced in MySQL 8.0 (see Caching SHA-2 Pluggable
Authentication
(http://dev.mysql.com/doc/refman/8.0/en/caching-sha2-pluggable-authentication.html)).
In addition, MySQL for Visual Studio now requires that
.NET Framework 4.5.2 (or later) be installed for use
within Visual Studio 2012 and Visual Studio 2013.

Bugs Fixed

* The Website Configuration Tool was unable to add an
ADO.NET entity data model to an ADO.NET web application
successfully. In addition to aligning the web.config file
with the installed version of Connector/Net, this fix
also disables the Entity Framework 5 selection when the
installed connector no longer includes a provider for EF5
(Connector/Net 6.10 and higher). (Bug #27593219)

* Queries made with a valid MySQL connection received no
response from the server. (Bug #27584991)

* When the MySQL for Visual Studio parser was unable to
parse an SQL statement, it generated an unhandled
exception that caused Visual Studio to exit unexpectedly.
This fix enables the parser to handle exceptions properly
and updates the parser to include support for CREATE USER
syntax in the MySQL 8.0 release series. (Bug #27580303)

* The version for the MySql.Web assembly was incorrectly
extracted, which prevented the assembly from loading
properly and the MySQL Website Configuration Tool from
launching. (Bug #27450530)

* Attempting to open the MySQL Web Configuration Tool, with
MySQL Connector/Net and MySQL for Visual Studio
prerequisites installed properly, displayed an error
message instead of opening the tool. (Bug #27365141, Bug
#88570)

On Behalf of MySQL Release Engineering Team,
Surabhi Bhat

MySQL Connector/NET 6.9.12 GA has been released

Dear MySQL users,

MySQL Connector/Net 6.9.12 is a maintenance release for the 6.9.x series
of the .NET driver for MySQL. It can be used for production
environments.

It is appropriate for use with MySQL server versions 5.5-5.7.

It is now available in source and binary form from
http://dev.mysql.com/downloads/connector/net/#downloadsandmirrorsites
(note that not all mirror sites may be up to date at this point-if you
can’t find this version on some mirror, please try again later or choose
another download site.)

Note: C/NET 6.9 will not be supported any more. If users are looking to get latest
updates on C/NET, they should upgrade to using C/NET 6.10 and 8.0 series instead.

Changes in MySQL Connector/Net 6.9.12 (2018-05-04, General
Availability)

Functionality Added or Changed

* Connections made to MySQL 8.0 (up to and including
version 8.0.3) and compatibility with the new data
dictionary are now supported. For information about the
data dictionary, see MySQL Data Dictionary
(http://dev.mysql.com/doc/refman/8.0/en/data-dictionary.h
tml).

* Support for the caching_sha2_password authentication
plugin through the classic MySQL protocol was added. In
addition, the sha256_password plugin was extended to
support authentication when RSA keys are available
through non-secure connections. Caching SHA-2 pluggable
authentication offers faster authentication than basic
SHA-256 authentication.

* Support was added for the new caching_sha2_password
padding mechanism introduced in the MySQL 8.0 release
series. The new padding mechanism is enabled when all of
the following conditions apply:

+ The user account is set with the
caching_sha2_password authentication plugin.

+ SSL is disabled explicitly (SslMode=none).

+ The AllowPublicKeyRetrieval connection option is
enabled (AllowPublicKeyRetrieval=true).
When enabled, the new padding mechanism is used to encode
the password during RSA key encryption, which applies the
correct padding to match the server.

Bugs Fixed

* The MySqlConnection.GetSchema(“PROCEDURES”, restrictions)
method call generated an error message, instead of
returning stored procedures, when the server connection
was to the MySQL 8.0 release series. (Bug #25961782)

* Attempting to generate an Entity Framework model from a
MySQL 5.7 database using either EF5 or EF6 produced an
exception that prevented the operation from generating
the expected model. (Bug #22173048, Bug #79163)

The documentation is available at:
http://dev.mysql.com/doc/connector-net/en/

Nuget packages are available at:
https://www.nuget.org/packages/MySql.Data/6.9.12
https://www.nuget.org/packages/MySql.Data.Entity/6.9.12
https://www.nuget.org/packages/MySql.Fabric/6.9.12
https://www.nuget.org/packages/MySql.Web/6.9.12

Enjoy and thanks for the support!

On behalf of the MySQL Release Team,
Sreedhar S

MySQL Connector/C++ 1.1.11 has been released

Dear MySQL Users,

A new GA (general availability) version of MySQL Connector/C++ has been made available: MySQL Connector/C++ 1.1.11 GA. The MySQL Connector/C++ provides a C++ API for connecting client applications to the MySQL Server 5.5 or newer.

You can download the production release at:

http://dev.mysql.com/downloads/connector/cpp/1.1.html

MySQL Connector C++ (Commercial) will be available for download on the My Oracle Support (MOS) website. This release will be available on eDelivery (OSDC) in next month’s upload cycle.

The MySQL driver for C++ offers an easy to use API derived from JDBC 4.0. MySQL Workbench has used it successfully for years.

We have improved the driver since the last GA release. Please see the documentation and the CHANGES file in the source distribution for a detailed description of bugs that have been fixed. Bug descriptions are also listed below.

Enjoy!

Changes in MySQL Connector/C++ 1.1.11 (2018-04-30, General Availability)

Functionality Added or Changed

  • MySQL Connector/C++ 1.1 now works with both MySQL 5.7 GA and MySQL 8.0 GA servers.
      • Applications can connect to MySQL 8.0 servers using accounts that authenticate using the caching_sha2_password authentication plugin.
      • Applications can connect to MySQL 8.0 servers using unencrypted connections by using the OPT_GET_SERVER_PUBLIC_KEY connection option with a value of true.
      • Connector/C++ 1.1 can be built from source against either MySQL 5.7 and MySQL 8.0 server installations.
      • A new BUNDLE_DEPENDENCIES CMake option is available. If enabled, the external libraries on which Connector/C++ depends at runtime (such as OpenSSL), are packaged together with the connector.
  • For connections to the server made using the legacy JDBC API (that is, not made using X DevAPI or XAPI), Connector/C++ now supports an OPT_GET_SERVER_PUBLIC_KEY connection option that enables requesting the RSA public key from the server. For accounts that use the caching_sha2_password or sha256_password authentication plugin, this key can be used during the connection process for RSA key-pair based password exchange with TLS disabled. This capability requires a MySQL 8.0 GA server, and is supported only for Connector/C++ built using OpenSSL.

Bugs Fixed

    • MySQL Connector/C++ packages now include a BUILDINFO.txt file that contains information about the build environment used to produce the distribution. (Bug #23556661)

On Behalf of the MySQL/ORACLE RE Team
Piotr Obrzut

MySQL Connector/NET 6.10.7 GA has been released

Dear MySQL users,

MySQL Connector/NET 6.10.7 is the fourth GA release with .NET Core
now supporting various connection-string options and MySQL 8.0 server
features.

To download MySQL Connector/NET 6.10.7 GA, see the “Generally Available
(GA) Releases” tab at http://dev.mysql.com/downloads/connector/net/

Changes in MySQL Connector/NET 6.10.7 (2018-04-30, General Availability)

Functionality Added or Changed

* Connections made to MySQL 8.0 (up to and including
version 8.0.3) and compatibility with the new data dictionary are
now supported. For information about the data dictionary, see
MySQL Data Dictionary
(http://dev.mysql.com/doc/refman/8.0/en/data-dictionary.html).

* Support for the caching_sha2_password authentication
plugin through the classic MySQL protocol was added. In addition,
the sha256_password plugin was extended to support authentication
when RSA keys are available through non-secure connections.
Caching SHA-2 pluggable authentication offers faster
authentication than basic SHA-256 authentication.

* Support was added for the new caching_sha2_password
padding mechanism introduced in the MySQL 8.0 release series. The
new padding mechanism is enabled when all of the following
conditions apply:

+ The user account is set with the
caching_sha2_password authentication plugin.

+ SSL is disabled explicitly (SslMode=none).

+ The AllowPublicKeyRetrieval connection option is enabled
(AllowPublicKeyRetrieval=true).  When enabled, the new padding
mechanism is used to encode the password during RSA key
encryption, which applies the correct padding to match the
server.

Bugs Fixed

* Attempting to open the MySQL Web Configuration Tool, with
Connector/NET and MySQL for Visual Studio prerequisites installed
properly, displayed an error message instead of opening the tool.
(Bug #27457398, Bug #88544)

* The ADO.NET Entity Data Model wizard within Visual Studio
closed unexpectedly without producing the data model.  Thanks to
Laurents Meyer for the patch. (Bug #27420311, Bug #89338)

* An exception prevented MySQL.Data.Entity for Entity
Framework 6 from operating as expected. Thanks to Cédric Luthi
for the patch. (Bug #27360520, Bug #89134)

* MySQL Installer could not be installed with NuGet
packages from Microsoft Visual Studio 2015. (Bug #27251839,
Bug #88838)

* With valid references to the DLLs provided, using
DbConfiguration.SetConfiguration(new
MySql.Data.Entity.MySqlEFConfiguration()) to set up the DbContext
threw an exception. (Bug #25185319)

* Attempting to generate an Entity Framework model from a
MySQL 5.7 database using either EF5 or EF6 produced an exception
that prevented the operation from generating the expected model.
(Bug #22173048, Bug #79163)

Nuget packages are available at:

https://www.nuget.org/packages/MySql.Data/6.10.7
https://www.nuget.org/packages/MySql.Web/6.10.7
https://www.nuget.org/packages/MySql.Data.Entity/6.10.7
https://www.nuget.org/packages/MySql.Data.EntityFrameworkCore/6.10.7
https://www.nuget.org/packages/MySql.Data.EntityFrameworkCore.Design/6.10.7

Enjoy and thanks for the support!

On behalf of the MySQL Release Team,
Nawaz Nazeer Ahamed

MySQL Document Store CRUD Quick Start

This post serves as a quick start guide on how to use the MySQL Document Store with the official Node.js connector. It mainly highlights how easy it is to leverage CRUD-style operations and getting up and running with the X DevAPI.

Before you jump in

Make sure you have Node.js 7.6.0 (or higher) and MySQL 8.0.11 (or higher) installed on your machine.

Setting up your Node.js project

First, using the command line, let’s start by creating a directory for our sample application.

Create a package.json manifest file using the following command:

Next, install the MySQL Node.js Connector from npm:

Starting the MySQL Server

For additional tips on how to install and secure your MySQL server, check the documentation.

  1. Download (if you have not yet) MySQL 8.0
  2. Install and set up a basic (and insecure) server process.

We are using  --initialize-insecure here for the sake of simplicity, however, make sure you use --initialize  (and set up users and privileges accordingly) on production environments and/or “real-world” scenarios.

Connecting to MySQL

Create a new app.js file and write the following code for establishing a connection to the MySQL server using the myproject database/schema (which will be created by default).

The getSession method should return a JavaScript Promise which resolves with a Session object containing the connection details or fails with a Node.js Error if the connection could not be established.

Run the app using the following command:

You should be able to see a Successful server connection  message printed in the console.

Inserting documents

Use the following code to create (or re-use if it exists) documents  collection and add three new documents to it.

The add method returns a Promise which resolves with a Result  object containing details such as:

  • the number of items affected by the operation
  • a list of document _ids auto-generated by the server
  • any server-side warnings resulting from the operation

Add an insertDocuments call in the existing main function like the following:

Running the app should yield the following output:

Retrieving all documents

You can use the following code to retrieve all the existing documents in the collection.

To collect and process records from the result set, you should provide a callback in the execute method.

Use the findDocuments method in the main function.

Finding documents with a given criteria

You can narrow down the results by specifying a query criteria like the following:

The result set will now include just the documents containing 'a': 3.

Updating documents

To modify an existing document i.e. updating specific properties or adding new ones, you can also provide a criteria (or true to modify all) for the operation.

Update the main function to use the updateDocument method like the following:

Removing documents from a collection can also be done based on a given criteria (or true  to remove all). The following code removes the document where the field a is greater than 2.

To test this behavior, call the removeDocument method in the main function.

Creating secondary indexes

You can create a secondary index for any property of a document in a collection using the following code:

Just like before, update the main function to use the createIndex method.

Wrapping up

This is just an overview of some of the features and API methods for taping into the MySQL document store using the Node.js connector and the X Dev API. For additional details, check the following links:

MySQL Connector/Python 8.0.11 has been released

Dear MySQL users,

MySQL Connector/Python 8.0.11 is the first GA release version of the MySQL Connector Python 8.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 Connector/Python, and its usage, see http://dev.mysql.com/doc/dev/connector-python.

Please note that the X DevAPI requires 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.

To download MySQL Connector/Python 8.0.11, see the “General Available
(GA) releases” tab at http://dev.mysql.com/downloads/connector/python/

Enjoy!


Changes in MySQL Connector/Python 8.0.11 (2018-04-19)

     * Functionality Added or Changed

     * Bugs Fixed

   Functionality Added or Changed

     * X DevAPI: Previously, when documents without an _id
       attribute were added to a collection, Connector/Python
       automatically generated _id for them. Now a MySQL 8
       server generates the _id attribute unless a document
       already contains one. The generated IDs resulting from a
       document-add operation can be obtained using the new
       get_generated_ids() method.
       This capability requires a MySQL 8.0 server. Because
       MySQL 5.7 does not support document ID generation, the
       document-add operation returns an error if you do not
       define the _id's.
       Incompatibility: The get_generated_ids() method replaces
       the now removed get_document_ids(). (Bug #27627366)

     * Added NOWAIT and SKIP_LOCKED support to the
       ReadStatement.lock_shared() and
       ReadStatement.lock_exclusive() methods. Example usage:
       lock_exclusive(mysqlx.LockContention.SKIP_LOCKED).

     * The C extension (cext) is now enabled by default, as the
       use_pure option changed from True to False by default.
       If the C extension is not available on the system then
       the Python implementation is used instead, and use_pure
       is set to True.

     * Added the X DevAPI SHA256_MEMORY authentication
       mechanism.
       Example mysqlx.get_session() usages: ?auth=SHA256_MEMORY
       via a connection string, "auth":
       mysqlx.Auth.SHA256_MEMORY via a dictionary, or
       auth=mysqlx.Auth.SHA256_MEMORY via method parameters.

   Bugs Fixed

     * Warnings are now stored as a list of dictionaries instead
       of a list of tuples. In other words, get_warnings()
       returns the likes of [{"level": _level_, "code": _code_,
       "msg": _msg_}] instead of [(_level_, _code_, _msg_))].
       (Bug #27639119)

     * The mapped MySQL Server error codes were synced with
       MySQL Server 8.0.11. (Bug #27634885)

     * Removed upsert functionality from InsertStatement as it
       can only be used by collections, so upsert remains
       available to AddStatement. (Bug #27589450)

     * MySQLConverter.escape() functionality was added to
       create_schema()'s count mechanism. (Bug #27528842)

     * When using prepared statements, string columns were
       returned as bytearrays instead of strings. The returned
       value is now a string decoded using the connection's
       charset (defaults to 'utf8'), or as a bytearray if this
       conversion fails. (Bug #27364914)

     * The result from JSON_TYPE() was returned as a bytearray
       instead of a string. The returned value is now a string
       decoded using the connection's charset (defaults to
       'utf8'), or as a bytearray if this conversion fails. (Bug
       #24948205, Bug #83516)

     * JSON integer values were cast to bytes in Python instead
       of integers. (Bug #24948186, Bug #83513)

On Behalf of Oracle/MySQL Release Engineering Team
Prashant Tekriwal

MySQL 8.0 – Welcome to the DevAPI!

 

By now you’ve read Mike Frank’s excellent introduction to the MySQL 8.0 release and it’s Document Store.  In that post Mike laid out the benefits of the new Document Store model and briefly outlined the different components involved.  While it’s fair to consider the Document Store as the first pillar of the new MySQL 8 Document story, we must identify the X DevAPI to be the second.  The post covers our motivation, goals, and overall design principles of this new API.

Motivation

Most of our connectors have not implemented their own API.  Rather, most of them implement externally defined interfaces.   Examples include our Connector/J that implements the JDBC standard, Connector/Net that implements the ADO.Net standard, and our Connector/ODBC.  None of these standards work well for a document-oriented database.  In fact, there really are no defined standard APIs for document databases.  Therefore we knew we needed to develop a new API for our users.

Another reason why we needed a new API is because we are doing something that has not been done before.  Document databases exist.  Relational databases exist.  We even see databases that support relational and document querying over the same data set.  However we have yet to see a relational database include a document model so that a user can use document objects alongside their existing relational data.

We are only beginning to bring forth the unifying power of MySQL 8.   In the coming releases you’ll see exciting developments like being able to link your existing relational data with your document data in a single query using simple API patterns.

Goals

We have some very clear goals we follow as we develop the X DevAPI.  They are:

  • Simplicity — No one likes a complicated API.  We wanted the API to be easy to understand no matter what language you are using.   It’s important to us that if you write X DevAPI code using Node.JS then a Python developer, for example, can read it and know what is going on.
  • Bridge the gap between relational and document data — MySQL runs some of the world’s largest web properties and they have petabytes of relational data.  Asking them to add tables or columns to their data stores can be very challenging.  The new document store allows connecting these different types of data in the same queries.  We want to provide a powerful API for this.
  • Expressive — We wanted our API to be very expressive and follow a fluent interface style of development.  We wanted this to enable writing and executing the same X DevAPI code in the shell as in your application.
  • Seamless support for InnoDB Cluster farms — We want the API to seamlessly and transparently allow simple development of applications that span from one machine to hundreds.

Please note that these are goals. Some of them may not be fully realized in the initial release of the X DevAPI but these goals outline what we are thinking about during this development.

Design

No matter what connector you use the design of the X DevAPI core components remains the same.  The API involves some central objects outlined below. You’ll hear more about these objects and more in the individual product announcements and in our documentation found at https://dev.mysql.com/doc/.

Session Represents a logical connection to a server or farm
Schema Represents a MySQL database/schema
Collection Represents a collection of JSON documents
Result There’s a series of Result objects that handle different scenarios

Our goal is that each connector will include a core API (set of objects, properties and methods) that are the same across all connectors.  Outside of the core API, each connector can and will provide additional syntax or support additional methods.

Of course we also realize that each language and framework has it’s own style and it’s important to us to be respectful of that.  An example might be a count property that might appear as .Count  in one language but should be represented as .getCount()  in another.  Again, please recognize they are the same concept just different syntax.

Looking at the New X DevAPI Syntax

You’ll learn much more about the X DevAPI and our different languages when you read our announcement blogs and our documentation but I wanted to take just a minute to give quick examples of how you can use the X DevAPI in the shell to do quick ad-hoc prototyping.  First let’s look at opening a session to server.  In this example we open a session  to localhost and get a reference to the test schema so we can work with it.

Next we’ll create a collection and add a document to it.

We can modify the document very easily.  This example finds all documents that have a length of 250 and changes that length to 125.

We can find those documents if we want to work with them

And finally we can remove the documents and drop our collection

These have just been some quick examples but we hope that it has shown some of the basic ideas we have in the X DevAPI and you are interested in learning more and trying it out.

Availability

With the release of MySQL 8.0 we are also making available 8.0 GA versions of our connectors.  Each of them, save ODBC, provide an initial implementation of X DevAPI.  These connectors should be installed from their respective package stores.  You can also find them at our website at https://www.mysql.com/downloads/.

You can read much more about each of the products on their announcement blogs:

We know that we have not covered every language available.  If you are using MySQL 8 with a connector in a different language please let us know.  We want to work with the community to bring X DevAPI to more and more languages.

Thank you!

I want to thank you for your time to read this and learn about our new products.  I hope that you will take the time to get to know what we’ve created and let us know where we fell down.  We have much more planned and we want to hear from you!

Introducing Connector/Node.js for MySQL 8.0

As you may have heard, MySQL 8.0 is now officially GA, and it comes with a bunch of additional goodies. Among those is the brand new Connector/Node.js, which is the official MySQL driver for Node.js and, currently, the only one with support for the latest server versions and features (such as the MySQL document store).

Here’s a rundown of what’s available:

  • Out-of-the box support for MySQL 8.0
  • Document-store API as a first-class citizen
  • TLS/SSL and SHA256 authentication
  • Fluent API with support for flexible parameters
  • Semantic methods to encode common CRUD operations
  • Modern Node.js asynchronous interface based on Promises
  • Abstractions for common database development tasks
  • Transactions, savepoints and row locking

MySQL 8.0

Connector/Node.js is currently the only driver in the Node.js ecosystem that works out-of-the-box with the latest MySQL 8.0 series and implements the brand new X Protocol and X DevAPI, which unlocks exclusive server features such as the MySQL document store.

In a nutshell, the X Protocol is based on the Google Protocol Buffers serialization format, and provides a common interface for a different set of official connectors to bridge into the MySQL server via the X plugin, which contains the server-side implementation of the document store and a surrounding scaffolding ecosystem including things like common CRUD expression trees, bound parameters, or expectations and conditions for statement pipelining.

The X DevAPI is the common client-side API used by all connectors to abstract the details of the X Protocol. It specifies the common set of CRUD-style functions/methods used by all the official connectors to work with both document store collections and relational tables, a common expression language to establish query properties such as criteria, projections, aliases, and a standard set of additional database management features for handling things like transactions, indexes, etc.

The fact that most of these features share the same format and API between connectors, makes the X DevAPI a perfect fit for mordern polyglot development environments such as microservices, and the fact that they are based on a well-documented format allows advanced users to extend client-side implementations and build new middleware components or extensions tailor-made for their use case.

Although, there are (most of the times) matching client APIs to work with relational tables, this overview will focus mostly on document-store related features. Check the official Connector/Node.js documentation or the X DevAPI user guide to get the full picture.

Secure by default

With Connector/Node.js, SSL/TLS is enabled by default for server TCP connections and, additionally, the server identity can be validated against a given certificate authority (CA).

Of course you can explicitely override this behavior (at your own peril).

Local Unix sockets don’t use SSL/TLS since they don’t really benefit much from that level of security. At the same time, that removes the possibility of any additional performance overhead caused by the SSL/TLS handshake.

In the authentication realm, besides the traditional SHA1-based server authentication plugin, Connector/Node.js also supports the latest secure authentication plugins based on SHA-256. Of course you can always use your own custom server plugins, as long as the authentication data can be sent using one of the existing client-side authentication mechanisms (in the simplest form, via plain text).

Additional details about Connector/Node.js security can be found here.

Fluent API

The public API flows nicely from a single getSession()  method. Whereas, when it comes the point of creating and issuing database operations, you get a nice fluent query builder where those operations are encapsulated in specialized and specific methods, which, compared to using raw SQL statements, brings benefits such as:

  • more readable, maintainable (and even testable) code
  • better tooling integration
    • scaffolding for code refactoring
    • text-editor (or IDE) hints and auto-completion
  • smaller SQL injection surface area
  • common standard between different programming languages and environments

Most public API methods provide alternative input handling flavors:

  • multiple individual arguments
  • a single array of arguments
  • an object with named properties (where it applies)

Promise-based asynchronous tasks

Being a good Node.js citizen, Connector/Node.js encapsulates all blocking I/O operations with asynchronous methods. Each method that sends a message to the MySQL server is expected to return a JavaScript Promise , which resolves to the specific result or fails with an error. This pattern also unlocks other platform abstractions such as the async/await  syntax, making it even more flexible for the user than just using traditional error-first callbacks.

So, after building a query, it can be sent to the server via the execute()  method. In turn, the method receives an optional callback as argument, which runs for each element in the result set. When using relational tables, an additional callback function can be used to tap into the given column metadata.

Other methods, such as the ones that operate on a single instance of a connection, database object (be it a schema, table/collection, row, document, etc.) will return a Promise  by themselves (dropping the extra call to execute() ). Some examples:

  • mysqlx.getSession()
  • session.getSchemas()
  • session.createSchema()
  • schema.getCollections()
  • schema.createCollection()
  • collection.getOne()
  • collection.addOrReplaceOne()
  • collection.replaceOne()
  • collection.createIndex()

Data consistency

With MySQL 8.0, you get session-level consistency and document-level isolation via multiple database constructs, such as transactions, savepoints and row locking. This allows to encapsulate a set of operations (particularly DML) encompassing multiple documents or collections in a single atomic procedure within a given session.

Connector/Node.js provides APIs to create, commit or rollback a transaction as well as to create, release or rollback to an intermediate savepoint within that transaction.

In the presence of concurrent transactions, the isolation level of each operation within the transaction, operating on a given document, can be determined using row locks.

Raw SQL interface

If you are looking for a feature that is still not available on the X DevAPI, or simply something that does not fit the CRUD model, you can always resort to plain old SQL.

Getting Started

If you want to use Connector/Node.js in your own project or just want to play around with, download the latest version from npm:

New releases DO NOT follow semantic versioning, so, to avoid being affected by breaking changes, make sure you use --save-exact  when pulling the package, particularly if you don’t have npm-shrinkwrap.json  or package-lock.json  files locking down your project’s dependencies.

If you are an advanced user, being an open source project, the code is hosted on the official GitHub repository and contributions are welcome, either in the form of bugs ( Connector for Node.js  category) or pull requests.

Up and Running

Assuming you are running MySQL 8.0 with the default configuration and Node.js v8.0.0  or later (for async/await  support), using Connector/Node.js and the MySQL document store in your project is as simple as follows:

These are just some of the highlights. Make sure you check out the official Connector/Node.js documentation or the X DevAPI user guide for usage examples and even more details on how to get started.

Please give it a try! Your feedback is more than welcome.