Documentation Home
MySQL Shell for VS Code

Pre-General Availability: 2023-01-18

5.7 X DevAPI

X DevAPI details the mysqlx module as well as the capabilities of the X DevAPI, which enable you to work with MySQL as a Document Store. X DevAPI enables you to work with both relational and document data when MySQL Shell is connected to a MySQL server using the X Protocol.

Relational databases such as MySQL usually require a schema to be defined before documents can be stored. However, you can use MySQL as a document store, which is a schema-less, and therefore schema-flexible, storage system for documents. For example, when you create documents describing products, you do not need to know and define all possible attributes of any products before storing and operating with the documents. This differs from working with a relational database and storing products in a table, when all columns of the table must be known and defined before adding any products to the database.

Clients that communicate with a MySQL server using X Protocol can use X DevAPI to develop applications.

X DevAPI specifies a common set of CRUD-style methods. The API is implemented by MySQL Shell in JavaScript and Python. The table that follows provides links to example-rich usage information for the MySQL Shell implementation using Python syntax.

More information by topic Description
Connections and Sessions An X DevAPI session is a high-level database session concept that is different from working with traditional low-level MySQL connections. Sessions can encapsulate one or more actual MySQL connections when using the X Protocol. Use of this higher abstraction level decouples the physical MySQL setup from the application code. Sessions provide full support of X DevAPI and limited support of SQL.
CRUD Operations Use the X DevAPI for Create Read, Update, and Delete (CRUD) operations. The core domain of MySQL has always been working with relational tables. X DevAPI extends this domain by adding support for CRUD operations that can be run against collections of documents.
Working with Collections Use CRUD operations on Collections and return Documents. The most common operations to be performed on a Collection are the Create, Read, Update, and Delete (CRUD) operations.
Working with Documents Once a collection has been created, it can store JSON documents. Every document has a unique identifier called the document ID, which can be thought of as the equivalent of a table's primary key. The document ID value is usually automatically generated by the server when the document is added, but can also be manually assigned.
Working with Relational Tables The X DevAPI SQL CRUD functions allow you to work with relational tables in manners similar to using traditional SQL statements. Use the add() and select() methods of the X DevAPI SQL CRUD functions, which are similar to issuing INSERT and SELECT statements on a table with a SQL client.
Working with Relational Tables and Documents Use documents for very specific tasks inside an application and rely on relational tables for other tasks. Or a very simple document only application can outgrow the document model and incrementally integrate or move to a more powerful relational database. This way the advantages of both documents and relational tables can be combined. SQL tables contribute strictly typed value semantics, predictable and optimized storage. Documents contribute type flexibility, schema flexibility and non-scalar types.
Statement Issuing Use transactions to group operations into an atomic unit. X DevAPI supports savepoints, which enable you to set a named point within a transaction that you can revert to. X DevAPI supports MySQL locking through the lockShared() and lockExclusive() methods for the Collection.find() and methods. This enables you to control row locking to ensure safe, transactional document updates on collections and to avoid concurrency problems
Working with Result Sets All database operations return a result. The type of result returned depends on the operation which was executed.
Building Expressions When working with MySQL expressions used in CRUD, statements can be specified in two ways. The first is to use strings to formulate the expressions which should be familiar if you have developed code with SQL before. The other method is to use Expression Builder functionality.
CRUD EBNF Definitions This section provides a visual reference guide to the objects and functions available in the X DevAPI.
Expressions EBNF Definitions This section provides a visual reference guide to the grammar for the expression language used in X DevAPI.

For more information about the MySQL Shell implementation of X DevAPI using JavaScript syntax, see: