Speeding up your development environment with SQLite

As we seek different tools and techniques to speed up our development workflow, database management remains overlooked. It’s still rigorous and time-consuming to set up and maintain when working with relational databases like PostgreSQL.

Fortunately, SQLite, a self-contained SQL database engine, reduces a lot of the labor of working with databases in our development environment.

How SQLite works

From the official docs:

SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured SQL database engine.

Apart from being fast, SQLite is self-contained, and this makes it a great choice for a lot of applications, whether for mobile or web. It eliminates the need for applications to rely on external databases in order to function. With this in mind, SQL can become a very good option for working with databases in our development environment.

Since our SQLite database will exist inside our project, its content will be consistent across all our development environments. The database setup is done just once, and with every other initialization of our project across different development environments, we won’t need to do any additional work to set up a database.

This will significantly speed up our workflow because it eliminates the issue of data inconsistency as well as the difficulty of connecting to a new database, migrating, and then seeding data every time we decide to work with a new development environment.

When not to use SQLite

SQLite is a good option in many cases, but considering the size and type of your application, it might not be the best choice for your production environment.

SQLite does not support concurrency. This means that only one user can write to the database at a time, so when working with applications that require multiple users to write to the database concurrently, PostgreSQL might be a better option for your production environment.

Also, SQLite works well with applications that don’t receive traffic of more than 100,000 hits per day. If your application is expected to scale past this limit, then SQLite is probably not the best option for production. Since our focus is on our development environment, however, this likely won’t be an issue.

Let’s demonstrate how we can use SQLite to speed up our development environment by building a Node.js application. We’ll show how SQLite can be used alongside other relational databases like PostgreSQL, with SQLite used for development and PostgreSQL used for production.

Scaffolding our Node.js application

Let’s start by scaffolding a new Node.js app with Express.js. First, we’ll ensure that we have Node installed in our local environment. Let’s do that by running the following command on our terminal:

node --version

This should return the version of Node we have installed. Click here to see Node installation instructions if the command returns an error message.

Next, we’ll install the Express application generator, which we’ll use to scaffold our Node app:

npm install -g express-generator

Now let’s run the following command on our terminal:

express node_sqlite

Then npm install to install the default npm packages.

If you’ve followed all the steps correctly, you should be able to view your Node app when you cd into your new app directory and run npm start:

cd node_sqlite
npm start

Our new app directory should look like this:

├── bin
├── public
├── routes
├── views
├── package.json
├── app.js

Installing the required dependencies

• To handle our database queries, we’ll be using Sequelize, a promise-based Node.js ORM (object-relational mapper)
• For making our environment variables accessible to our application, we’ll need the dotenv package

Let’s install our packages with the following command:

npm i -s sequelize sequelize-cli dotenv

Before we initialize Sequelize, we’ll add the following script to our package.json file, which can be found in the root directory:

"sequelize": "node_modules/.bin/sequelize"

This makes it easier for us to access the Sequelize commands from our terminal. With our newly added script, our package.json file should like this:

// ./package.json

{
  "name": "sqlite-test",
  "version": "0.0.0",
  "private": true,
  "scripts": {
    "start": "node ./bin/www",
    "sequelize": "node_modules/.bin/sequelize"
  },
  "dependencies": {
    ...
  }
}

Now let’s go ahead and initialize Sequelize by running the following command on our terminal:

npm run sequelize init

This generates a models folder for housing model files, which we’ll be using to describe the logical structure of our database, and a config folder for our database configuration file.

We want to store our production database URL as an environment variable. This eases the process of updating it in the future. To do this, we’ll create a file named .env in our root directory and add a variable DATABASE_URL to it:

// .env

DATABASE_URL= your database url here
NODE_ENV=development

Notice that we also added a variable NODE_ENV to our .env file. This indicates the environment in which we will be running our application. Here, we’ve used development.

Now that we have that set up, let’s navigate to the ./config/config.json file. This is the file that Sequelize generates for our app. By default, it should look like this:

{
  "development": {
    "username": "root",
    "password": null,
    "database": "database_development",
    "host": "127.0.0.1",
    "dialect": "mysql",
    "operatorsAliases": false
  },
  "test": {
    "username": "root",
    "password": null,
    "database": "database_test",
    "host": "127.0.0.1",
    "dialect": "mysql",
    "operatorsAliases": false
  },
  "production": {
    "username": "root",
    "password": null,
    "database": "database_production",
    "host": "127.0.0.1",
    "dialect": "mysql",
    "operatorsAliases": false
  }
}

Since we’ll be accessing our production database URL (which we stored in our .env file) from here, let’s convert this to a JavaScript module instead of a JSON file. To do this, we’ll first rename the file config.json to config.js and then replace its content with the following code block:

module.exports = {
  development: {
  },
  test: {
  },
  production: {
  },
};

Next, we’ll fill in the details for our development, test, and production environments. Let’s edit our config.js file to look like this:

require('dotenv').config();

module.exports = {
  development: {
    dialect: "sqlite",
    storage: "./sqlite-dev.db"
  },
  test: {
    dialect: "sqlite",
    storage: "./sqlite-test.db"
  },
  production: {
    url: process.env.DATABASE_URL,
    dialect: 'postgres',
  },
};

Notice how we used sqlite for our development and test environments and then postgres for our production environment. Depending on the type/size of our application, we could go ahead and also use sqlite for our production environment. We also initialized our dotenv module on line 1.

Generate our database model

For our next step, we’ll be using Sequelize to create a table in our database. If our Node environment is set to either development or test in our dotenv file (as we’ve done), Sequelize will generate a new SQLite database in our root directory with the name we used in our config file before creating the table.

Let’s create a table called User by running the following command on our terminal:

npm run sequelize -- model:generate --name User --attributes username:string,password:string

This creates a table User with columns username and password in our database. When we navigate to our root directory, we’ll see a file named sqlite-dev.db. This is our newly created SQLite database.

To view our SQLite database in a database management system, we can use the DB Browser for SQLite tool. Here’s the download link.

Generating a seed file for our database

Seed files are used to add initial data to our database. This data is usually used for testing. In our case, we’ll be adding three default users to our SQLite database. To generate a seed file for our user table, let’s run the following command on our terminal:

npm run sequelize -- seed:generate --name user

This creates a new file in the directory ./seeders. Depending on the date, its name will look similar to 20200428202218-user.js.

By default, the generated file should look like this:

'use strict';
module.exports = {
  up: (queryInterface, Sequelize) => {
    /*
      Add altering commands here.
      Return a promise to correctly handle asynchronicity.
      Example:
      return queryInterface.bulkInsert('People', [{
        name: 'John Doe',
        isBetaMember: false
      }], {});
    */
  },
  down: (queryInterface, Sequelize) => {
    /*
      Add reverting commands here.
      Return a promise to correctly handle asynchronicity.
      Example:
      return queryInterface.bulkDelete('People', null, {});
    */
  }
};

Let’s edit it to this:

'use strict';
module.exports = {
  up: queryInterface =>
    queryInterface.bulkInsert('Users', [
      {
        username: 'johndoe',
        password: 'dontstorepasswordsthisway',
        createdAt: new Date().toDateString(),
        updatedAt: new Date().toDateString()
      },
      {
        username: 'janedoe',
        password: 'youreallyshouldhashpasswords',
        createdAt: new Date().toDateString(),
        updatedAt: new Date().toDateString()
      },
      {
        username: 'ritadoe',
        password: 'outofpasswordideas',
        createdAt: new Date().toDateString(),
        updatedAt: new Date().toDateString()
      }
    ], {}),
  down: (queryInterface, Sequelize) => {
    /*
      Add reverting commands here.
      Return a promise to correctly handle asynchronicity.
      Example:
      return queryInterface.bulkDelete('People', null, {});
    */
  }
};

Now that we have generated our seed file, we can seed our database by running the following command on our terminal:

npm run sequelize db:seed:all

We should see a success message similar to this:

Loaded configuration file "config\config.js".
Using environment "development".
== 20200428202218-user: migrating =======
== 20200428202218-user: migrated (0.020s)

Conclusion

With this done, we can go ahead and create controllers for querying and mutating our SQLite database. Using SQLite makes accessing our database a lot faster, as there is no need for an external call from our application.

As we discussed in the introduction, our database setup is done just once, and then with every other initialization of our project across different development environments, there’s no need for any work to be done concerning setting up or configuring a database for our application.

Here’s the link to the GitHub repository for our API setup: node_sqlite_setup