Tutorial: Working with Documents

Working with Documents

UUID generation

When adding documents to a collection, if a document does not contain an _id, it will be automatically assigned a UUID-like value. This value does not strictly follow the format described by RFC 4222, but instead adheres to the following convention.

RFC 4122 UUID: 5C99CDfE-48CB-11E6-94F3-4A383B7fCC8B

MySQL Document ID: 4A383B7FCC8B-94F3-11E6-48CB-5C99CDFE

One can however provide a static _id for each document, or even provide a custom function to generate them in using some sort of special convention.

Using automatically assigned values

const mysqlx = require('@mysqlx/xdevapi');

mysqlx
    .getSession('mysqlx://localhost:33060/testSchema')
    .then(session => {
        return session
            .getSchema('testSchema')
            .createCollection('testCollection');
    })
    .then(collection => {
        let docs = [];

        return Promise
            .all([
                collection.add({ name: 'foo' }).execute(),
                collection.find().execute(doc => docs.push(doc))
            ])
            .then(() => docs);
    })
    .then(docs => {
        // the `_id` value is just an example in this case
        console.log(docs); // [{ _id: '4A383B7FCC8B-94F3-11E6-48CB-5C99CDFE', name: 'foo' }]
    })

Using static values

const mysqlx = require('@mysqlx/xdevapi');

mysqlx
    .getSession('mysqlx://localhost:33060/testSchema')
    .then(session => {
        return session
            .getSchema('testSchema')
            .createCollection('testCollection');
    })
    .then(collection => {
        let docs = [];

        return Promise
            .all([
                collection.add({ _id: 1, name: 'foo' }).execute(),
                collection.find().execute(doc => docs.push(doc))
            ])
            .then(() => docs);
    })
    .then(docs => {
        console.log(docs); // [{ _id: 1, name: 'foo' }]
    })

Using a generator function

const crypto = require('crypto');
const mysqlx = require('@mysqlx/xdevapi');

const options = {
    dbHost: 'localhost',
    dbPort: 33060,
    idGenerator: () => crypto.randomBytes(4).toString('hex'),
    schema: 'testSchema'
};

mysqlx
    .getSession(options)
    .then(session => {
        return session
            .getSchema('testSchema')
            .createCollection('testCollection');
    })
    .then(collection => {
        let docs = [];

        return Promise
            .all([
                collection.add({ name: 'foo' }).execute(),
                collection.find().execute(doc => docs.push(doc))
            ])
            .then(() => docs);
    })
    .then(docs => {
        // the `_id` value is just an example in this case
        console.log(docs); // [{ _id: 'ccdef991', name: 'foo' }]
    })

Node: the id generator function can only be provided through a session configuration object.

Single document CRUD

C/Node.js provides a set of utility methods that can be used to add, remove, replace or retrieve a single specific document via its _id property.

Consider a collection test_schema.test_collection containing the following documents:

[{
    "_id": "1",
    "name": "foo"
}, {
    "_id": "2",
    "name": "bar"
}]

The following scenarios are possible.

Replacing a single document

If a document with a given _id already exists in the database, it can be replaced via the Collection.replaceOne() method.

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

let data = [];

mysqlx
    .getSession('mysqlx://localhost:33060/test_schema')
    .then(session => {
        return session
            .getSchema('test_schema')
            .getCollection('test_collection')
            .replaceOne('1', { name: 'baz', age: 23 });
    })
    .then(result => {
        console.log(result.getAffectedItemsCount()); // 1

        return collection
            .find()
            .execute(doc => data.push(doc));
    })
    .then(() => {
        console.log(data); // [ { _id: '1', age: 23 }, { _id: '2', name: 'bar' } ]
    });

If no such document exists, the method will neither fail nor have any effect.

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

let data = [];

mysqlx
    .getSession('mysqlx://localhost:33060/test_schema')
    .then(session => {
        return session
            .getSchema('test_schema')
            .getCollection('test_collection')
            .replaceOne('3', { name: 'baz', age: 23 });
    })
    .then(result => {
        console.log(result.getAffectedItemsCount()); // 0

        return collection
            .find()
            .execute(doc => data.push(doc));
    })
    .then(() => {
        console.log(data); // [ { _id: '1', name: 'foo' }, { _id: '2', name: 'bar' } ]
    });

Creating or updating a single document

C/Node.js also provides an additional utility method - Collection.addOrReplaceOne() - that allows to seamlessly either create a document with a given _id and properties or automatically replace an existing matching document.

So, if a document with the given _id already exists in a collection, the behavior is the same as with Collection.replaceOne().

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

let data = [];

mysqlx
    .getSession('mysqlx://localhost:33060/test_schema')
    .then(session => {
        return session
            .getSchema('test_schema')
            .getCollection('test_collection')
            .addOrReplaceOne('1', { name: 'baz', age: 23 });
    })
    .then(result => {
        // the existing row is re-created (leading to two different operations)
        // see https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html
        console.log(result.getAffectedItemsCount()); // 2

        return collection
            .find()
            .execute(doc => data.push(doc));
    })
    .then(() => {
        console.log(data); // [ { _id: '1', name: 'baz', age: 23 }, { _id: '2', name: 'bar' } ]
    });

If no such document exists, a new one will be created.

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

let data = [];

mysqlx
    .getSession('mysqlx://localhost:33060/test_schema')
    .then(session => {
        return session
            .getSchema('test_schema')
            .getCollection('test_collection')
            .addOrReplaceOne('3', { name: 'baz', age: 23 });
    })
    .then(result => {
        console.log(result.getAffectedItemsCount()); // 1

        return collection
            .find()
            .execute(doc => data.push(doc));
    })
    .then(() => {
        console.log(data); // [ { _id: '1', name: 'foo' }, { _id: '2', name: 'bar' }, { _id: '3', name: 'baz', age: 23 } ]
    });

When additional unique key constraints exist for a collection, a few additional scenarios are brought up. Assuming, the name property has a unique key constraint established by a auto-generated column.

ALTER TABLE test_schema.test_collection ADD COLUMN name VARCHAR(3) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(doc, '$.name'))) VIRTUAL UNIQUE KEY NOT NULL

Existing documents will be updated with the given properties, provided that there are no unique key constraint violations with other documents.

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

let data = [];

mysqlx
    .getSession('mysqlx://localhost:33060/test_schema')
    .then(session => {
         return session
            .getSchema('test_schema')
            .getCollection('test_collection')
            .addOrReplaceOne('1', { name: 'baz' });
    })
    .then(result => {
        // the existing row is re-created (leading to two different operations)
        // see https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html
        console.log(result.getAffectedItemsCount()); // 2

        return collection
            .find()
            .execute(doc => data.push(doc));
    })
    .then(() => {
        console.log(data); // [ { _id: '1', name: 'baz' }, { _id: '2', name: 'bar' } ]
    });

Unique key values themselves can also be updated with the same restrictions.

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

let data = [];

mysqlx
    .getSession('mysqlx://localhost:33060/test_schema')
    .then(session => {
         return session
            .getSchema('test_schema')
            .getCollection('test_collection')
            .addOrReplaceOne('1', { name: 'foo', age: 23 });
    })
    .then(result => {
        // the existing row is re-created (leading to two different operations)
        // see https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html
        console.log(result.getAffectedItemsCount()); // 2

        return collection
            .find()
            .execute(doc => data.push(doc));
    })
    .then(() => {
        console.log(data); // [ { _id: '1', name: 'foo', age: 23 }, { _id: '2', name: 'bar' } ]
    });

Unique key constraint violations will, of course, result in an error.

const mysqlx = require('@mysql/xdevapi')

mysqlx
    .getSession('mysqlx://localhost:33060/test_schema')
    .then(session => {
         return session
            .getSchema('test_schema')
            .getCollection('test_collection')
            .addOrReplaceOne('1', { name: 'bar' });
    })
    .catch(err => {
        console.log(err.message);
    })

Retrieving a single document

There's also an utility method to retrieve a single document from a collection, given its id - Collection.getOne(). The method returns a Promise which resolves to the document instance (in the form of a literal object), if it exists or null, if it does not.

const mysqlx = require('@mysql/xdevapi')

mysqlx
    .getSession('mysqlx://localhost:33060/test_schema')
    .then(session => {
        return session
            .getSchema('test_schema')
            .getCollection('test_collection')
            .getOne('1');
    })
    .then(doc => {
        console.log(doc); // { _id: '1', name: 'foo' }
    });
const mysqlx = require('@mysql/xdevapi')

mysqlx
    .getSession('mysqlx://localhost:33060/test_schema')
    .then(session => {
        return session
            .getSchema('test_schema')
            .getCollection('test_collection')
            .getOne('3');
    })
    .then(doc => {
        console.log(doc); // null
    });

Removing a single document

One can also remove a specific document from a collection given its id - Collection.removeOne(). If no such document exists, the operation succeeds, but nothing really happens.

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

let data = [];

mysqlx
    .getSession('mysqlx://localhost:33060/test_schema')
    .then(session => {
        return session
            .getSchema('test_schema')
            .getCollection('test_collection')
            .removeOne('1');
    })
    .then(result => {
        console.log(result.getAffectedItemsCount()); // 1

        return collection
            .find()
            .execute(doc => data.push(doc));
    })
    .then(() => {
        console.log(data); // [ { _id: '2', name: 'bar' } ]
    });
const mysqlx = require('@mysql/xdevapi');

let data = [];

mysqlx
    .getSession('mysqlx://localhost:33060/test_schema')
    .then(session => {
        return session
            .getSchema('test_schema')
            .getCollection('test_collection')
            .removeOne('3');
    })
    .then(result => {
        console.log(result.getAffectedItemsCount()); // 0

        return collection
            .find(doc => data.push(doc))
            .execute();
    })
    .then(() => {
        console.log(data); // [ { _id: '1', name: 'foo' }, { _id: '2', name: 'bar' } ]
    });