Id generation
When adding documents to a collection, if a document does not contain an _id, it will be automatically assigned a sequential UUID-like value. One can, however, override this behavior by providing a static _id for each document.
Using automatically assigned values
const mysqlx = require('@mysql/xdevapi');
const docs = [];
mysqlx.getSession('mysqlx://localhost:33060/mySchema')
.then(session => {
return session.getSchema('mySchema').createCollection('myCollection');
})
.then(collection => {
return collection.add({ name: 'foo' })
.execute()
.then(() => {
return collection.find()
.execute(doc => docs.push(doc));
})
})
.then(() => {
// the `_id` value is just an example in this case
console.log(docs); // [{ _id: '00005a640138000000000000002c', name: 'foo' }]
});
Using static values
const mysqlx = require('@mysql/xdevapi');
const docs = [];
mysqlx.getSession('mysqlx://localhost:33060/mySchema')
.then(session => {
return session.getSchema('mySchema').createCollection('myCollection');
})
.then(collection => {
return collection.add({ _id: 1, name: 'foo' })
.execute()
.then(() => {
return collection.find()
.execute(doc => docs.push(doc));
});
})
.then(() => {
console.log(docs); // [{ _id: 1, name: 'foo' }]
});
Single document CRUD
The connector 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 mySchema.myCollection 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');
mysqlx.getSession('mysqlx://localhost:33060/mySchema')
.then(session => {
const collection = session.getSchema('mySchema').getCollection('myCollection');
return collection.replaceOne('1', { name: 'baz', age: 23 })
.then(result => {
console.log(result.getAffectedItemsCount()); // 1
return collection.find()
.execute();
});
})
.then(result => {
console.log(result.fetchAll()); // [ { _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');
mysqlx.getSession('mysqlx://localhost:33060/mySchema')
.then(session => {
const collection = session.getSchema('mySchema').getCollection('myCollection')
return collection.replaceOne('3', { name: 'baz', age: 23 })
.then(result => {
console.log(result.getAffectedItemsCount()); // 0
return collection.find()
.execute();
});
})
.then(result => {
console.log(result.fetchAll()); // [ { _id: '1', name: 'foo' }, { _id: '2', name: 'bar' } ]
});
If the replacement document in the second argument contains an _id property and its value is different from the id value provided as the first argument, an error will be reported, regardless of whether documents with either of those ids already exist in the collection or not.
const mysqlx = require('@mysql/xdevapi');
mysqlx.getSession('mysqlx://localhost:33060/mySchema')
.then(session => {
const collection = session.getSchema('mySchema').getCollection('myCollection')
return collection.replaceOne('1', { _id: '2', name: 'baz', age: 23 })
})
.catch(err => {
console.log(err.message); // Replacement document has an _id that is different than the matched document.
});
Creating or updating a single document
The connector 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');
mysqlx.getSession('mysqlx://localhost:33060/mySchema')
.then(session => {
const collection = session.getSchema('mySchema').getCollection('myCollection');
return 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/8.0/en/insert-on-duplicate.html
console.log(result.getAffectedItemsCount()); // 2
return collection.find()
.execute();
})
.then(result => {
console.log(result.fetchAll()); // [ { _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');
mysqlx.getSession('mysqlx://localhost:33060/mySchema')
.then(session => {
const collection = session.getSchema('mySchema').getCollection('myCollection');
return collection.addOrReplaceOne('3', { name: 'baz', age: 23 })
.then(result => {
console.log(result.getAffectedItemsCount()); // 1
return collection.find()
.execute();
})
.then(result => {
console.log(result.fetchAll()); // [ { _id: '1', name: 'foo' }, { _id: '2', name: 'bar' }, { _id: '3', name: 'baz', age: 23 } ]
});
});
Just like replaceOne(), when calling addOrReplaceOne(), if the replacement document in the second argument contains an _id property and its value is different from the id value provided as the first argument, an error will be reported, regardless of whether documents with either of those ids already exist in the collection or not.
const mysqlx = require('@mysql/xdevapi');
mysqlx.getSession('mysqlx://localhost:33060/mySchema')
.then(session => {
const collection = session.getSchema('mySchema').getCollection('myCollection');
return collection.addOrReplaceOne('3', { _id: '4', name: 'baz', age: 23 })
})
.catch(err => {
console.log(err.message); // Replacement document has an _id that is different than the matched document.
});
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 mySchema.myCollection 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');
mysqlx.getSession('mysqlx://localhost:33060/mySchema')
.then(session => {
const collection = session.getSchema('mySchema').getCollection('myCollection');
return 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/8.0/en/insert-on-duplicate.html
console.log(result.getAffectedItemsCount()); // 2
return collection.find()
.execute();
})
.then(result => {
console.log(result.fetchAll()); // [ { _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');
const docs = [];
mysqlx.getSession('mysqlx://localhost:33060/mySchema')
.then(session => {
const collection = session.getSchema('mySchema').getCollection('myCollection');
return 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/8.0/en/insert-on-duplicate.html
console.log(result.getAffectedItemsCount()); // 2
return collection.find()
.execute();
})
.then(result => {
console.log(result.fetchAll()); // [ { _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/mySchema')
.then(session => {
return session.getSchema('mySchema').getCollection('myCollection').addOrReplaceOne('1', { name: 'bar' });
})
.catch(err => {
console.log(err.message);
})
Retrieving a single document
There is 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/mySchema')
.then(session => {
return session.getSchema('mySchema').getCollection('myCollection').getOne('1');
})
.then(doc => {
console.log(doc); // { _id: '1', name: 'foo' }
});
const mysqlx = require('@mysql/xdevapi')
mysqlx.getSession('mysqlx://localhost:33060/mySchema')
.then(session => {
return session.getSchema('mySchema').getCollection('myCollection').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');
mysqlx.getSession('mysqlx://localhost:33060/mySchema')
.then(session => {
const collection = session.getSchema('mySchema').getCollection('myCollection');
return collection.removeOne('1')
.then(result => {
console.log(result.getAffectedItemsCount()); // 1
return collection.find()
.execute();
})
.then(result => {
console.log(result.fetchAll()); // [ { _id: '2', name: 'bar' } ]
});
});
const mysqlx = require('@mysql/xdevapi');
mysqlx.getSession('mysqlx://localhost:33060/mySchema')
.then(session => {
const collection = session.getSchema('mySchema').getCollection('myCollection');
return collection.removeOne('3')
.then(result => {
console.log(result.getAffectedItemsCount()); // 0
return collection.find()
.execute();
})
})
.then(result => {
console.log(result.fetchAll()); // [ { _id: '1', name: 'foo' }, { _id: '2', name: 'bar' } ]
});