Working with NoSQL in a SQL Database (XDevApi)

Working with NoSQL
in a SQL Database (XDevApi)
Lior Altarescu
Lioral@wix.com linkedin/lioraltarescu github.com/lioraltarescu

▪ 7 years a DBA…
▪ Oracle, MySQL, MongoDB,
Elasticsearch, Cassandra, and
PostgreSQL
▪ Proud data nerd

TODAY
What X DevAPI is
Nosql and SQL in a single
database
CRUD operations
Our experience with it

X 130M
One big JSON
Team
(microservice)
#1
Team
(microservice)
#2

How developers see data How DBAs see data

X DevAPI MySQL
C++
Java
.Net
Node.js
Python
PHP
X Plugin
Async CRUD
operations

X DevAPI MySQL
C++
Java
.Net
Node.js
Python
PHP
X Plugin
Async CRUD
operations
session
Transparently
scale to a
multiple server
environment

MySQLX DevAPI
It’s a
document
datastore.
session

MySQL
It’s a
document
datastore.
Schemaless
NoSQL, fully ACID
Keeps data integrity
Allows SQL & CRUD
Max Document size 1G
Very secure
GTID Replication
transaction_isolation

MySQL
ID #1
JSON
Collection A
A collection
of…
documents.

NoSQL and SQL
in a single database
02

Storage
Optimizer
MySQL
connection
handling
Application
MySQL Connector / ODBC
Classic
MySQL protocol
MySQL

Application
MySQL Connector / ODBCX DevAPI
X DevAPI
X DevAPI
SQL
result
MySQL
Storage
Optimizer
MySQL
connection
handling
X Plugin
Classic
MySQL protocolX protocol

Application
MySQL Connector / ODBC
MySQL Shell
Node.js Application
Windows Application
JavaScript / Python
MySQL C / Node.js
MySQL connector / Net
X DevAPI
X DevAPI
X DevAPI
SQL
result
MySQL
Storage
Optimizer
MySQL
connection
handlingX PluginX Plugin
Classic
MySQL protocolX protocol

CRUD operations
with X DevAPI
03

A New
Collection
db.createCollection('meetup_collection’)
<Collection:meetup_collection>

List / Drop a
Collection
db.getCollections()
[
<Collection:items_table>,
<Collection:meetup_collection>
]
-- drop collection
db.dropCollection('meetup_collection')
db.getCollections()
[
<Collection:items_table>
]

A New
Document
with SQL:
insert into items_table values(‘phone’,21443,’blue’);
insert into items_table values(‘phone’,567734,’pink’);
with X DevAPI:
db.items_table.add({"name":"phone","price":21443,"color":"blue"}).
add({"name":"tablet","price":567734,"color":"pink"})

A New
Document
with SQL:
insert into items_table values(‘phone’,21443,’blue’);
insert into items_table values(‘phone’,567734,’pink’);
with X DevAPI:
db.items_table.add({"name":"phone","price":21443,"color":"blue"}).
add({"name":"tablet","price":567734,"color":"pink"})
//OR
db.items_table.add({"name":"phone","price":21443,"color":"blue"})
db.items_table.add({"name":"tablet","price":567734,"color":"pink"})

Find a
Document
with SQL:
select * from items_table where price > 25000;
with X DevAPI:
db.items_table.find("price>25000")
db.items_table.find("price>:param").bind(‘param’,’25000’)

Modify a
Document
with SQL:
update items_table set price=11111 where name = 'phone’;
with X DevAPI:
db.items_table.modify("name=phone").set("price","11111")
db.items_table.find("name='phone'")
{
"_id": "00005dc476b00000000000000009",
"name": "phone",
"color": "blue",
"price": 11111
}

Remove a
Document
with SQL:
delete from items_table where name = 'phone'
with X DevAPI:
db.items_table.remove("name='phone'")

A Full Data
Transaction
var myColl = db.createCollection('dummy_collection');
session.startTransaction();
myColl.add({name: 'Rohit', age: 18, height: 1.76}).execute();
session.setSavepoint('sp1')
myColl.add({name: 'Misaki', age: 24, height: 1.65}).execute();
myColl.add({name: 'Leon', age: 39, height: 1.9}).execute();
session.rollbackTo('sp1')
session.commit();

Does NOT support
the X protocol

Mysql connectors as a replacement to proxySQL ?
Connector XdevAPI Failover Load Balancing
Java ✓ ✓ ✓
Nodejs ✓ ✓ ✘
Python ✓ ✓ ✘
Net ✓ ✓ ✓
PHP ✓ ✓ ✓
C++ ✓ ✘ ✘

Play the Waiting Game
Solution #1

Mysql InnoDB
cluster
Mysql replication
Primary election when the current
primary failure VS
Need to have a script or additional
infrastructure for automatic failover
Advances AdminAPI for administrative
purpose
No AdminAPI, regular commands
Easy to scale with innodb cluster requires some effort to scale
Not mature Solution Mature Solution
VS

X DevAPI allows you
to work with MySQL
as a document store
(both relational and
schema-less data).
Takeaways

X DevAPI allows you
to work with MySQL
as a document store
(both relational and
schema-less data).
Takeaways
ProxySQL doesn’t
support it, but we got
around it with InnoDB
cluster.

Q&A

Thank You

Working with NoSQL in a SQL Database (XDevApi)

More Related Content

What's hot

Similar to Working with NoSQL in a SQL Database (XDevApi)

Recently uploaded

Working with NoSQL in a SQL Database (XDevApi)