Documentation Home
X DevAPI User Guide
Download this Manual

X DevAPI User Guide  /  Working with Collections  /  Basic CRUD Operations on Collections

4.1 Basic CRUD Operations on Collections

Working with collections of documents is straight forward when using the X DevAPI. The following examples show the basic usage of CRUD operations when working with documents.

After establishing a connection to a MySQL Server, 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({_id: '1', name: 'Sakila', age: 15}).execute();
myColl.add({_id: '2', name: 'Susanne', age: 24}).execute();
myColl.add({_id: '3', name: 'User', age: 39}).execute();

// Find a document
var docs = myColl.find('name like :param1 AND age < :param2').limit(1).
        bind('param1','S%').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({'_id': '1', 'name': 'Sakila', 'age': 15}).execute()
myColl.add({'_id': '2', 'name': 'Susanne', 'age': 24}).execute()
myColl.add({'_id': '3', 'name': 'User', 'age': 39}).execute()

# Find a document
docs = myColl.find('name like :param1 AND age < :param2') \
          .limit(1) \
          .bind('param1','S%') \
          .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: 'Sakila', age: 15 }).execute(),
        myColl.add({ name: 'Susanne', age: 24 }).execute(),
        myColl.add({ name: 'User', age: 39 }).execute()
      ])
      .then(function () {
        // Find a document
        return myColl
            .find('name like :name && age < :age')
            .bind({ name: 'S%', 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 = "Sakila", age = 15}).Execute();
myColl.Add(new { name = "Susanne", age = 24}).Execute();
myColl.Add(new { name = "User", age = 39}).Execute();

// Find a document
var docs = myColl.Find("name like :param1 AND age < :param2").Limit(1)
.Bind("param1", "S%").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': 'Sakila', 'age': 15}).execute()
my_coll.add({'name': 'Susanne', 'age': 24}).execute()
my_coll.add({'name': 'User', 'age': 39}).execute()

# Find a document
docs = my_coll.find('name like :param1 AND age < :param2') \
    .limit(1) \
    .bind('param1', 'S%') \
    .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\":\"Sakila\", \"age\":15}").execute();
myColl.add("{\"name\":\"Susanne\", \"age\":24}").execute();
myColl.add("{\"name\":\"User\", \"age\":39}").execute();

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

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

// Drop the collection
myDB.dropCollection("test", "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": "Sakila", "age": 15})").execute();
myColl.add(R"({"name": "Susanne", "age": 24})").execute();
myColl.add(R"({"name": "User", "age": 39})").execute();

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

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

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

User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
Sign Up Login You must be logged in to post a comment.