4.1 Basic CRUD Operations on Collections

Working with collections of documents is straightforward when using X DevAPI. The following example shows the basic usage of CRUD operations (see Section 4.3, “Collection CRUD Function Overview” for more details) when working with documents: After establishing a connection to a MySQL Server instance, a new collection that can hold JSON documents is created and several documents are inserted. Then, a find operation is executed to search for a specific document from the collection. Finally, the collection is dropped again from the database. The example assumes that the test schema exists and that the collection my_collection does not exist.

MySQL Shell JavaScript Code

// Connecting to MySQL Server and working with a Collection

var mysqlx = require('mysqlx');

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

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

// Create a new collection 'my_collection'
var myColl = myDb.createCollection('my_collection');

// Insert documents
myColl.add({ name: 'Laurie', age: 19 }).execute();
myColl.add({ name: 'Nadya', age: 54 }).execute();
myColl.add({ name: 'Lukas', age: 32 }).execute();

// Find a document
var docs = myColl.find('name like :param1 AND age < :param2').limit(1).
        bind('param1','L%').bind('param2',20).execute();

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

// Drop the collection
myDb.dropCollection('my_collection');

MySQL Shell Python Code

# Connecting to MySQL Server and working with a Collection
from mysqlsh import mysqlx

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

myDb = mySession.get_schema('test')

# Create a new collection 'my_collection'
myColl = myDb.create_collection('my_collection')

# Insert documents
myColl.add({ 'name': 'Laurie', 'age': 19 }).execute()
myColl.add({ 'name': 'Nadya', 'age': 54 }).execute()
myColl.add({ 'name': 'Lukas', 'age': 32 }).execute()

# Find a document
docs = myColl.find('name like :param1 AND age < :param2') \
          .limit(1) \
          .bind('param1','L%') \
          .bind('param2',20) \
          .execute()

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

# Drop the collection
myDb.drop_collection('my_collection')

Node.js JavaScript Code

// --------------------------------------------------
// Connecting to MySQL Server and working with a Collection
var mysqlx = require('@mysql/xdevapi');
var db;

// Connect to server
mysqlx
  .getSession({
    user: 'user',
    password: 'password',
    host: 'localhost',
    port: '33060',
  })
  .then(function (session) {
    db = session.getSchema('test');
    // Create a new collection 'my_collection'
    return db.createCollection('my_collection');
  })
  .then(function (myColl) {
    // Insert documents
    return Promise
      .all([
        myColl.add({ name: 'Laurie', age: 19 }).execute(),
        myColl.add({ name: 'Nadya', age: 54 }).execute(),
        myColl.add({ name: 'Lukas', age: 32 }).execute()
      ])
      .then(function () {
        // Find a document
        return myColl
            .find('name like :name && age < :age')
            .bind({ name: 'L%', age: 20 })
            .limit(1)
            .execute(function (doc) {
              // Print document
              console.log(doc);
            });
      });
  })
  .then(function(docs) {
    // Drop the collection
    return db.dropCollection('my_collection');
  })
  .catch(function(err) {
    // Handle error
  });

C# Code

// Connecting to MySQL Server and working with a Collection

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

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

// Create a new collection "my_collection"
var myColl = myDb.CreateCollection("my_collection");

// Insert documents
myColl.Add(new { name = "Laurie", age = 19 }).Execute();
myColl.Add(new { name = "Nadya", age = 54 }).Execute();
myColl.Add(new { name = "Lukas", age = 32 }).Execute();

// Find a document
var docs = myColl.Find("name like :param1 AND age < :param2").Limit(1)
.Bind("param1", "L%").Bind("param2", 20).Execute();

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

// Drop the collection
myDb.DropCollection("my_collection");

Python Code

# Connecting to MySQL Server and working with a Collection

import mysqlx

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

my_schema = my_session.get_schema('test')

# Create a new collection 'my_collection'
my_coll = my_schema.create_collection('my_collection')

# Insert documents
my_coll.add({ 'name': 'Laurie', 'age': 19 }).execute()
my_coll.add({ 'name': 'Nadya', 'age': 54 }).execute()
my_coll.add({ 'name': 'Lukas', 'age': 32 }).execute()

# Find a document
docs = my_coll.find('name like :param1 AND age < :param2') \
    .limit(1) \
    .bind('param1', 'L%') \
    .bind('param2', 20) \
    .execute()

# Print document
doc = docs.fetch_one()
print("Name: {0}".format(doc['name']))
print("Age: {0}".format(doc['age]))

# Drop the collection
my_session.drop_collection('test', 'my_collection')

Java Code

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

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

// Create a new collection 'my_collection'
Collection myColl = myDb.createCollection("my_collection");

// Insert documents
myColl.add("{ \"name\":\"Laurie\", \"age\":19 }").execute();
myColl.add("{ \"name\":\"Nadya\", \"age\":54 }").execute();
myColl.add("{ \"name\":\"Lukas\", \"age\":32 }").execute();

// Find a document
DocResult docs = myColl.find("name like :name AND age < :age")
        .bind("name", "L%").bind("age", 20).execute();

// Print document
DbDoc doc = docs.fetchOne();
System.out.println(doc);

// Drop the collection
myDb.dropCollection("my_collection");

C++ Code

// Connecting to MySQL Server and working with a Collection

#include <mysqlx/xdevapi.h>

// Connect to server
Session session(33060, "user", "password");
Schema db = session.getSchema("test");

// Create a new collection 'my_collection'
Collection myColl = db.createCollection("my_collection");

// Insert documents
myColl.add(R"({ "name": "Laurie", "age": 19 })").execute();
myColl.add(R"({ "name": "Nadya", "age": 54 })").execute();
myColl.add(R"({ "name": "Lukas", "age": 32 })").execute();

// Find a document
DocResult docs = myColl.find("name like :param1 AND age < :param2").limit(1)
                        .bind("param1","L%").bind("param2",20).execute();

// Print document
cout << docs.fetchOne();

// Drop the collection
db.dropCollection("my_collection");