What is nanoSQL?

Web development is getting easier and highly performant. Libraries are released every day that solve common issues in software development. In this shot, we discuss nanoSQL.

This shot covers the following information:

• What nanoSQL has to offer
• How to install and use nanoSQL
• How to utilize nanoSQL to optimize app performance and make our developing experience less stressful

nanoSQL

nanoSQL is a universal database layer for the client, server, and mobile devices.

nanoSQL is a tool that provides a SQLstandard querying language data modeling system that can be used across databases for data storage, retrieval, updates, and deletion.

We can use MySQL and NoSQL databases in our project with only one central query system provided by nanoSQL. We don’t have to write SQL queries and NoSQL queries or install database client libraries for either of the databases.

According to the creator of nanoSQL, one of the reasons he created nanoSQL was because of the tradeoffs among databases.

NoSQL databases are highly performant and very popular among developers. On the other hand, MySQL databases are not as performant as NoSQL, but they have a stable data modeling system and a very advanced query system.

nanoSQL embodies the high performance of NoSQL and the advanced query and modeling system of MySQL.

To use the NoDQL and MySQL databases in our app, we need to install database adapters. nanoSQL has database adapters for the databases it supports:

1. Included In The Box Memory (Browser/NodeJS/Electron) Snap DB (NodeJS/Electron) Indexed DB (Browser) WebSQL (Browser) Local Storage (Browser)

2. RocksDB (NodeJS/Electron)

3. LevelDB (NodeJS/Electron)

4. SQLite (NodeJS/Electron)

5. SQLite (Cordova)

6. SQLite (NativeScript)

7. React Native

8. Redis

9. MySQL

10. Amazon Dynamo DB

11. MongoDB

12. ScyllaDB

nanoSQL supports popular databases. For example, we can use MongoDB and MySQL in our app with the single query language provided by nanoSQL for both databases.

Now, we won’t have to write NoSQL commands and SQL queries and will have a common folder for them.

We can even use browser-based databases in nanoSQL, such as LocalStorage, Memory database, and IndexedDB. With the same query language, we can use all the databases for CRUD actions.

nanoSQL also supports major query languages:

• GraphQL

• MongoDB QL

Features of nanoSQL

Identical API everywhere

One query language is used in any database. For example, we can develop an app with a database of our choice, e.g., MongoDB. Then, on deployment, we want the app database to be hosted on a preferred database, such as MySQL or AWS. The APIs and queries in the development will be identical to whatever database we previously choose. We see that all data is portable and all features are isomorphic.

Custom query building

With nanoSQL, we can write query functions, which can call into nanoSQL to retrieve data. Just like classical RDBMS, we can use queries like aggregate functions, joins, and group bys in nanoSQL.

Data models

We can create data models and use the RDBMS style to make them highly performant. The data models are flexible, so they can be changed as often as we want, and we can typecast them if need be.

Graph queries

nanoSQL supports graph queries and can use indexing to build nested graph queries on our data.

Autocompletion

nanoSQL has an autocompletion feature built into it, so it can autocomplete queries and data models for us, which leads to faster development.

CSV/JSON import & export

We can import and export data models and databases in the popular CSV or JSON format.

Size

nanoSQl is only 30kb.

Installation

You can use nanoSQL from the browser and Node.js.

Browser

<!-- ES6 Only (Faster & Smaller) -->
<script src="https://cdn.jsdelivr.net/npm/@nano-sql/core@2.3.7/dist/nano-sql.min.js"></script>

<!-- ES5 (Internet Explorer/Old Browser Support) -->
<!-- Promise must be polyfilled as well -->
<script src="https://cdn.jsdelivr.net/npm/@nano-sql/core@2.3.7/dist/nano-sql.min.es5.min.js"></script>

Whichever you choose, you must add it to the head section of your page.

Node.js

In Node.js, we install the @nano-sql/core library as shown below:

npm i @nano-sql/core --save

Then, we import nSQL from "@nano-sql/core":

import { nSQL } from "@nano-sql/core";

nSQL exports core function/methods and properties of the nanoSQL query language.

Usage

To use nanoSQL, nSQL exports a createDatabase() function that is used to tell nanoSL which database you will be using. createDatabase() takes an object that contains the type of database to use, among other things.

For example, if we want to use MySQL, we have to install and import its nanoSQL adapter.

Install:

npm i @nano-sql/adapter-mysql

Import:

const { nSQL } = require("@nano-sql/core");
const { MySQL } = require("@nano-sql/adapter-mysql");

Next, we call the createDatabase() function:

nSQL().createDatabase({
    id: "mysql-db",
    mode: new MySQL({
        host: "localhost",
        database: "test",
        user: "root",
        password: ""
    }),
    tables: [
        {
            name: "users",
            model: {
                "id:uuid": {pk: true},
                "username:string": {},
                "email:string": {},
                "password:string": {}
            }
        }
    ]
})

The id is the name/ID of the database. The mode specifies the type of database to use, e.g., MySQL, MongoDB, Dynamo, Local Storage, etc. tables is an array, and its elements are objects that specify the tables your database will have. The element objects will have a name prop that is the name of the table. model is an object that holds the columns of the table in a key-value pair.

In our code above, we create a MySQl database with id mysql-db. This database has one table called users, and the users table has columns. The first column is the id field, and the accompanying primary key is pk: true. Other columns are username, which will hold the user’s name, email, which will hold the user’s email in a string format, and password, which will hold the user’s hashed password in a string.

We can now perform queries to our MySQL server with nSQL:

SELECT

nSQL("users")
    .query("select")
    .exec()
    .then( rows => {
        console.log(rows)
    })

nSQL will take the table name we want to query on as the parameter. The query() method takes the type action to perform on the table as its parameter. exec() executes the query and returns the result in a Promise. The resolved value of the Promise becomes the result of the query.

Here, we query to return the data in the users table. The results are then logged on the console. Nothing will show because our table has no data. Let’s try to insert some data into it.

UPSERT

nSQL("users")
    .query("upsert", {
        username: "nnamdi",
        email: "nnamdi@gamil.com",
        password: "vFdfRTgfGfGF"
    })
    .exec()

The upsert query does two things: update and insert, depending on the number of arguments in it.

Our code above inserts the data in our users table. When we perform the select query, our result will be as follows:

nSQL("users")
    .query("select")
    .exec()
    .then( rows => {
        console.log(rows)
        /*
            [
                {
                    username: "nnamdi",
                    email: "nnamdi@gamil.com",
                    password: "vFdfRTgfGfGF"
                }
            ]
        */
    })

Like all query languages, we can delete resource(s) from a table.

nSQL("users")
    .query("delete")
    .where(['username', '=', "nnamdi"])
    .exec()

Here, we use the “delete” parameter in the query function, which deletes resources in the table. We use a condition to determine what to delete from the users table.

where() is a condition function that checks the columns based on the condition given. The conditions are in an array. Here, where() is given the condition to delete a resource from the users table whose username field value is nnamdi. We have a field in the users table with the username field value nnamdi, so it will be deleted.

So, if we query for data in the users table, we will have an empty table.

nSQL("users")
    .query("select")
    .exec()
    .then( rows => {
        console.log(rows)
        /*
            []
        */
    })

nanoSQL and arrays

We can use an in-memory database in nanoSQL. Here, we see how we can use an array as a database/datastore in nanoSQL.

nSQL([
    { a: 1, b: 10 },
    { a: 2, b: 20 },
    { a: 1, b: 40 }
])
.query("select")
.where(["a", "<", 2])
.exec()
.then(function(result) {
    console.log(result)
})

Since we use an array as the database, we pass the array as a parameter to the nSQL() function. nanoSQL then uses the array as the datastore for all our queries.

We call a select query with a condition to select items from the array datastore if the “a” field value is less than 2. The result is as follows:

[
    { a: 1, b: 10 },
    { a: 1, b: 40 }
]

Note: The elements in the array must be an object, and these objects are the tables of the database. The props in the objects are the fields in the table.

nanoSQL and local storage

Let’s see how we can use local storage as a database in nanoSQL.

nSQL().createDatabase({
    id: "ls-db",
    mode: "LS",
    tables: [
        {
            name: "tb_temp",
            model: {
            "id:uuid": {pk: true},
            "data:string": {},
            "form:string": {}
            }
        }
    ]
})

The only difference from the previous example is that we set mode to LS, which tells nanoSQL that we are using the local storage as a database.

All other queries in the MySQL and array examples will work here due to the identical API/ Run everywhere attribute.