Introduction to nanoSQL

nanoSQL is a universal JavaScript client library that is used for connecting to several different databases, both in memory and permanent.

As your web application becomes more complex, you may find yourself using more than just a single database for saving and retrieving data. For example, you might use both MySQL and Redis in an application.

While the backend database is saving and updating permanent data, you can use Redis as a database in the frontend layer to store session tokens and cache or to create real-time statistics for an admin dashboard or competitive gaming. The read/write speed of traditional databases such as MySQL is too slow to create the “instant” experience modern users expect.

To connect to your database, you’ll need a database client library. If you’re using Node.js, you can use mysqljs to connect to MySQL and Redis to connect to the Redis database. That means you need to learn two different sets of database client libraries and know how to write the correct query to achieve what you want to do.

nanoSQL solves this problem. It enables you to connect and use different database systems without needing to install a dedicated client library for every single database you’re going to use. It does this by creating a standardized query language that can be used in all types of supported databases.

By using nanoSQL, you can run several databases in parallel, with each database using its own adapter. You can create one nanoSQL instance that connects to MySQL and another one that connects to Redis.

Another benefit is that you can use nanoSQL in the client, server, or mobile device, as long as you’re using JavaScript.

nanoSQL in action

Let’s see how nanoSQL can help us operate the local storage and MySQL database using the same query language and API.

We’ll create a small form component using React that saves temporary user inputs in local storage. That way, when the app is reloaded, we can fetch data that was previously entered. When the user clicks submit, the app will save the data in a table in MySQL through a REST API built with Express.

Before we start, make sure you have MySQL installed on your machine. One of the easiest way to do so is by installing XAMPP, which includes phpMyAdmin for MySQL’s administration.

Once you have MySQL running, start by writing the REST API using Express. The code for this app is available on GitHub.

nanoSQL on the backend

First, create an npm package directory for your application using npm init. You can fill the package details with your own information.

Next, install the necessary packages in the directory.

npm install express @nano-sql/core @nano-sql/adapter-mysql

The express package is used to create the REST API, while @nano-sql/core and @nano-sql/adapter-mysql are for connecting and operating the MySQL database.

Once installed, create a new file named server.js and start importing the required modules.

const express = require('express');
const bodyParser = require("body-parser");
const { nSQL } = require("@nano-sql/core");
const { MySQL } = require("@nano-sql/adapter-mysql");

Create an instance of Express server to be used in your app. We’ll use the express.json() function so that Express can recognize a JSON object from the incoming POST request.

Next, create a middleware to allow CORS requests from localhost:3000, which we’ll use for the React app later. We’ll also set Express to listen for requests on localhost port 5000.

const app = express();
app.use(express.json());
app.use(function (req, res, next) {
  res.setHeader('Access-Control-Allow-Origin', 'http://localhost:3000');
  res.setHeader('Access-Control-Allow-Methods', 'GET,POST,PUT,DELETE,OPTIONS');
  res.setHeader('Access-Control-Allow-Headers', 'Content-Type, Access-Control-Allow-Headers');
  next();
});
const PORT = process.env.PORT || 5000;
app.listen(PORT, () => {
  console.log(`Listening on port ${PORT}`);
});

It’s time to connect Express with MySQL using nanoSQL’s createDatabase function, which takes three arguments:

1. The id argument is the unique identifier for the database
2. The mode argument is for defining the database; you can specify built-in mode or any one of the supported adapters
3. The tables argument takes an array of objects that define your database. You can define the name of the table and its columns by using a model object, as shown below

Since we’re using MySQL, we’ll use the MySQL function we imported from the adapter earlier. We need to specify our MySQL configuration inside that function.

nSQL().createDatabase({
  id: "mysql-db",
  mode: new MySQL({ 
    // specify your MySQL credentials here
    host: "localhost",
    database: "test",
    user: "root",
    password: ""
  }),
  tables: [
    {
      name: "tb_users",
      model: {
        "id:uuid": {pk: true},
        "name:string": {},
        "age:int": {},
        "role:string": {}
      }
    }
  ],
})

While putting credentials such as user, host, database, password, and port is not recommended in a production environment, we’ll write it in this file to simplify the tutorial. You may need to use environment variables for production, but that is outside the scope of this guide.

Now that nanoSQL is connected to our MySQL, we can create Express routes to do specific operations to the database. We’ll create three simple routes for operating the database:

1. /get for retrieving all data from the table
2. /save for saving new entry into the table
3. /delete/:id for deleting an entry with the ID specified

First, create the /get route to select all rows from tb_users table. The syntax for a query in nanoSQL looks like this:

nSQL("table name")
.query("query type", ...query args)
...optional filtering, etc..
.exec()

Instead of writing the whole query in a string, you use functions and chain them together. This looks very similar to a query builder such as Rails’ Active Record.

Started by creating the /get route and querying all rows in the table.

app.get('/get', (req, res) => {
  nSQL("tb_users").query("select").exec()
  .then( rows => {
    res.json(rows)
  })
});

Here is the route for /save and /delete/:id routes. Instead of using insert like you would in most SQL queries, we’ll use upsert, which can perform an insert or an update, depending on the number of query arguments we define in the call.

app.post('/save', (req, res) => {
  const { name, age, role } = req.body
  nSQL("tb_users").query("upsert", {name, age, role}).exec()
  .then(
    res.json({message: 'Data is saved!'})
  )
});

app.delete('/delete/:id', (req, res) => {
  const { id } = req.params
  nSQL("tb_users").query("delete").where(['id', '=', id]).exec()
  .then(
    res.json({message: 'User is deleted!'})
  )
});

Finally, the delete route will take an id parameter and perform a delete operation when the row with a matching ID is found.

Compare the code you’ve written with the source on GitHub.

Creating a React form on the frontend

With the REST API complete, we can start writing a React application that will consume this API.

Open the terminal in your project directory and use create-react-app to bootstrap your React app. We’ll name this app “client.”

npx create-react-app client

Once the React app is installed, go into the directory and install both axios and @nano-sql/core.

npm install axios @nano-sql/core

It’s time to write the frontend of our application. Let’s begin by writing the new index.js component.

Render your App component inside this file:

import React from 'react';
import ReactDOM from 'react-dom';
import App from './App';
ReactDOM.render(<App />, document.getElementById('root'));

Next, create a file named App.js, import the required modules, and write your state.

We’re going to create a simple component with four state properties:

import React from 'react';
import Axios from 'axios';
import {nSQL} from '@nano-sql/core';

const API_URL = 'http://localhost:5000/' // for connecting to API later

class App extends React.Component {
  state = {
    name: '',
    age: '',
    role: '',
    users: []
  }

The name, age, and role properties will be used to store the form input value, while users will store data retrieved from Express later.

Just below the state, write a function to retrieve user data and consume the /get route we created earlier.

getMerchant = () => {
  Axios.get(`${API_URL}get`)
  .then(res => {
    this.setState({users: res.data});
  });
}

Call that function from the componentDidMount method so that the user data is filled as soon as the component is rendered.

componentDidMount() {
  this.getMerchant();
}

Next, write down the render method. Simply use the state properties to store input values (except for users). We just need to render the users value using the map function.

Just below the form, add a button to delete data from MySQL database.

render() {
  const {name, age, role, users} = this.state
  return(
    <div className='app'>
      <h1>Auto-save form with NanoSQL for local storage and MySQL</h1>
      <h2>Current data: </h2>
      <ul>
      {
        users.map( user => {
          return (
           <li key={user.id}> {user.id} / {user.name} / {user.age} / {user.role} </li>
          )
        })
      }
      </ul>
      <form onSubmit={this.handleSubmit}>
      <div>
        <label>Name: </label>
        <input
          name="name"
          type="text"
          placeholder="Enter name"
          value={name}
          onChange={event => this.setState({name: event.target.value})}
          />
      </div>
      <div>
        <label>Age: </label>
        <input
          name="age"
          type="number"
          placeholder="Enter age"
          value={age}
          onChange={event => this.setState({age: event.target.value})}
          />
      </div>
      <div>
        <label>Role: </label>
        <input
          name="age"
          type="text"
          placeholder="Enter role"
          value={role}
          onChange={event => this.setState({role: event.target.value})}
          />
      </div>
      <button type='submit'>Submit</button>
      </form>
      <button onClick={this.handleDelete}>Delete</button>
    </div>
  )
}

The last things to tackle are form submission and user deletion.

handleSubmit = (e) => {
  e.preventDefault()
  const {name, age, role} = this.state
  Axios.post(`${API_URL}/save`, {
    name,
    age,
    role
  })
  .then(response => {
    alert(response.data.message)
    this.setState({
      name: '',
      age: '',
      role: ''
    })
    this.getMerchant()
  })
}

handleDelete = () => {
  let id = prompt('Enter user id');
  if (id){
    Axios.delete(`${API_URL}delete/${id}`)
    .then(response => {
      alert(response.data.message)
      this.getMerchant()
    })
  }
}

Now your application is complete. Try it out by running npm start and fill in the form. You should be able to add and delete user data without any problem.

Saving temporary user input in local storage

Since we’re using nanoSQL, let’s try to use it on the frontend. We’ll save the state properties for our form — name, age, and role — in the browser’s local storage database. When we close the app and open it again, our app will retrieve the state value from local storage.

Head back into the componentDidMount method we wrote earlier and add the following code.

componentDidMount() {
  this.getMerchant()

  nSQL().createDatabase({
    id: "ls-db",
    mode: "LS",
    tables: [
      {
        name: "tb_temp",
        model: {
            "id:uuid": {pk: true},
            "data:string": {},
            "form:string": {}
        }
      }
    ]
  }).then(() => {
    nSQL("tb_temp").query("select").where(['form', '=', 'user']).exec().then((row) => {
      console.log(row)
      if(row.length){
        console.log('update state')
        const data = JSON.parse(row[0].data)
        this.setState({
          name: data.name,
          age: data.age,
          role: data.role
        })
      } else {
        const {name, age, role} = this.state
        nSQL("tb_temp")
        .query("upsert",{data: JSON.stringify({name, age, role}), form: 'user'})
        .exec()      
      }
    })
  })
}

If the above code looks familiar, it’s because you’ve written it before. We’re performing the same createDatabase function we performed with MySQL. The table has three columns:

1. id as the primary key of the table
2. data to store the form values formatted as a JSON string
3. form as a unique identifier of your data, which is useful when you have multiple forms in your app

Once connected to the database, query into the table for any row with the specified form value. When a row is found, put it into the state. If there is no data, simply insert a new row with the user form, but with the initial state values for the data column. You need this row so you can update it later as the user types into the form.

The last step is to update the local storage row as the state is changed. You can do that using the componentDidUpdate method.

componentDidUpdate() {
  const {name, age, role} = this.state
  nSQL("tb_temp")
  .query("upsert",{data: JSON.stringify({name, age, role})})
  .where(['form', '=', 'user'])
  .exec().then(
    console.log('Data updated')
  )
}

When the component updates because of a state change, we’ll retrieve the state properties and update the data column with it. That’s all we need to do!

Now open your React app and type something into the form. Close the app and open it again. You’ll see that the values you entered into the form are still there, waiting for you to submit it.

Conclusion

As your web application grows more complex, there will come a time when you need to make use of more than just one database system. To create a seamless experience for your users, you may need to use both frontend and backend storage systems — which means you’ll need to learn how to operate them efficiently.

nanoSQL helps you to reduce development time by enabling a standardized query language so that you can use the same API on different database systems.

Even though it has “SQL” in its name, nanoSQL also supports noSQL databases, such as MongoDB and ScyllaDB. That means you don’t need to learn a new query language when you add any of these databases to your architecture. Jumping between different databases is a trivial matter.

You can even add your own query functions into nanoSQL. If you’re developing software with multidatabase architecture, be sure to check out nanoSQL documentation to see if it’s the right choice for you.