2.1 Database Connection Example

The code that is needed to connect to a MySQL document store looks a lot like the traditional MySQL connection code, but now applications can establish logical sessions to MySQL server instances running the X Plugin. Sessions are produced by the mysqlx factory, and the returned sessions can encapsulate access to one or more MySQL server instances running X Plugin. Applications that use Session objects by default can be deployed on both single server setups and database clusters with no code changes.

Create an X DevAPI session using the mysqlx.getSession(connection) method. You pass in the connection parameters to connect to the MySQL server, such as the hostname and user, very much like the code in one of the classic APIs. The connection parameters can be specified as either a URI type string, for example user:@localhost:33060, or as a data dictionary, for example {user: myuser, password: mypassword, host: example.com, port: 33060}. See Connecting to the Server Using URI-Like Strings or Key-Value Pairs for more information.

The MySQL user account used for the connection should use either the mysql_native_password or caching_sha2_password authentication plugin, see Pluggable Authentication. The server you are connecting to should have encrypted connections enabled, the default in MySQL 8.0 and later. This ensures that the client uses the X Protocol PLAIN password mechanism which works with user accounts that use either of the authentication plugins. If you try to connect to a server instance which does not have encrypted connections enabled, for user accounts that use the mysql_native_password plugin authentication is attempted using MYSQL41 first, and for user accounts that use caching_sha2_password authentication falls back to SHA256_MEMORY.

The following example code shows how to connect to a MySQL server and get a document from the my_collection collection that has the field name starting with L. The example assumes that a schema called test exists, and the my_collection collection exists. To make the example work, replace user with your username, and password with your password. If you are connecting to a different host or through a different port, change the host from localhost and the port from 33060.

MySQL Shell JavaScript Code

var mysqlx = require('mysqlx');

// Connect to server on localhost
var mySession = mysqlx.getSession( {
                host: 'localhost', port: 33060,
                user: 'user', password: 'password' } );

var myDb = mySession.getSchema('test');

// Use the collection 'my_collection'
var myColl = myDb.getCollection('my_collection');

// Specify which document to find with Collection.find() and
// fetch it from the database with .execute()
var myDocs = myColl.find('name like :param').limit(1).
        bind('param', 'L%').execute();

// Print document
print(myDocs.fetchOne());

mySession.close();

MySQL Shell Python Code

from mysqlsh import mysqlx

# Connect to server on localhost
mySession = mysqlx.get_session( {
        'host': 'localhost', 'port': 33060,
        'user': 'user', 'password': 'password' } )

myDb = mySession.get_schema('test')

# Use the collection 'my_collection'
myColl = myDb.get_collection('my_collection')

# Specify which document to find with Collection.find() and
# fetch it from the database with .execute()
myDocs = myColl.find('name like :param').limit(1).bind('param', 'L%').execute()

# Print document
document = myDocs.fetch_one()
print(document)

mySession.close()

Node.js JavaScript Code

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

// Connect to server on localhost
mysqlx
  .getSession({
    user: 'user',
    password: 'password',
    host: 'localhost',
    port: '33060'
  })
  .then(function (session) {
    var db = session.getSchema('test');
    // Use the collection 'my_collection'
    var myColl = db.getCollection('my_collection');
    // Specify which document to find with Collection.find() and
    // fetch it from the database with .execute()
    return myColl
      .find('name like :param')
      .limit(1)
      .bind('param', 'L%')
      .execute(function (doc) {
        console.log(doc);
      });
  })
  .catch(function (err) {
    // Handle error
  });

C# Code

// Connect to server on localhost
var mySession = MySQLX.GetSession("server=localhost;port=33060;user=user;password=password;");

var myDb = mySession.GetSchema("test");

// Use the collection "my_collection"
var myColl = myDb.GetCollection("my_collection");

// Specify which document to find with Collection.Find() and
// fetch it from the database with .Execute()
var myDocs = myColl.Find("name like :param").Limit(1)
	.Bind("param", "L%").Execute();

// Print document
Console.WriteLine(myDocs.FetchOne());

mySession.Close();

Python Code

import mysqlx

# Connect to server on localhost
my_session = mysqlx.get_session({
     'host': 'localhost', 'port': 33060,
     'user': 'user', 'password': 'password'
 })

my_schema = my_session.get_schema('test')

# Use the collection 'my_collection'
my_coll = my_schema.get_collection('my_collection')

# Specify which document to find with Collection.find() and
# fetch it from the database with .execute()
docs = my_coll.find('name like :param').limit(1).bind('param', 'L%').execute()

# Print document
doc = docs.fetch_one()
print(doc)

my_session.close()

Java Code

import com.mysql.cj.xdevapi.*;

// Connect to server on localhost
Session mySession = new SessionFactory().getSession("mysqlx://localhost:33060/test?user=user&password=password");

Schema myDb = mySession.getSchema("test");

// Use the collection 'my_collection'
Collection myColl = myDb.getCollection("my_collection");

// Specify which document to find with Collection.find() and
// fetch it from the database with .execute()
DocResult myDocs = myColl.find("name like :param").limit(1).bind("param", "L%").execute();

// Print document
System.out.println(myDocs.fetchOne());

mySession.close();

C++ Code

#include <mysqlx/xdevapi.h>

// Scope controls life-time of objects such as session or schema

{
  Session sess("localhost", 33060, "user", "password");
  Schema db= sess.getSchema("test");
  // or Schema db(sess, "test");

  Collection myColl = db.getCollection("my_collection");
  // or Collection myColl(db, "my_collection");

  DocResult myDocs = myColl.find("name like :param")
                           .limit(1)
                           .bind("param","L%").execute();

  cout << myDocs.fetchOne();
}