Thursday, April 9, 2020

MySQL without the SQL

MySQL can be used as a NoSQL JSON Document Store as well as a traditional relational database.  Before the MySQL Document Store you needed a DBA, or someone acting as one, to set up the schema to hold the tables and the tables according to how the data was arranged (and the indexes, constraints, and all the other stuff RDMS are known for). This was great for cases when you know exactly what the data looks likes, there are no changes to the structure of that data, and once things are set they go one that way forever.

But what about projects where change is constant, or where making changes to accommodate but keeping the old stuff as already formatted is needed? That is a perfect case for the MySQL Document Store.


So you can connect to the server, create a schema, create a document collection, and store data without once using SQL. 

This video shows you a simple example of doing just that!  I connected to a MySQL server running 8.0.19 using the new MySQL Shell (mysqlsh) using an account I set up earlier and for security reasons you still need someone with a semblance of DBA skills for setting up an account with CREATE USER <user>@<host> IDENTIFIED BY <password> and GRANT ALL ON <shcma>,* to <user>@<host> or the MySQL Workbench equivalent. 

After the login, I typed session to see the detail on the session.  Next I created a schema with the session.createSchema() operator.  So the schema was created without Structured Query Language,

Next I needed to point the db object to that new schema and you can use either the \use command or session.setCurrentSchema()to do just that.

Now we need a document collection and to create one use db.createCollection() and from there it is easy to use add() to store data.

Commands Used

myssqlsh demo@localhost
\use demo  ( or session.setCurrentSchema('demo')  )
db.dave.add( { name : "Dave", "demo" : "Working!" })