Using sqlite for dev nodeJS environments


29 November 2016, by

This post will explain how to get one click DBs working in nodeJS.

Problems with your zero to hero

I was recently working on a NodeJS project, and it was set up to use a mySQL database. This was fine for production, but meant that people joining the project had several manual steps to get the project working.

  • Install SQL server
  • Set up a SQL database
  • Update the NodeJS config to point to the database.

This isn’t too difficult to do. But I wanted to reduce the ramp up to getting the project up and running on a dev machine.

This meant moving to a database that could configure itself from whatever was checked into the codebase.

One option would have been to translate the steps above into code so that running a command would create a SQL database that was configured to be used by the code.

This would have been overkill, as there are much better options.

I chose to instead configure dev and test environments to use a local sqlite database.

Local sqlite database

A sqlite database can be added as an npm dependency, so that it is installed as part of dependency management when a new developer checks out the code.

"dependencies": {
  "sqlite3": "3.1.4"
}

Now that sqlite has been added as a dependency, it can be called from the ORM that we’re using. In this example, I’ve used Sequelize as the ORM, as once set up, it allows easy mapping of Javascript objects to database objects, as well as having built in support for database migrations.

The next thing to do is to have a configuration file which details how to connect to the database in different environments:

module.exports = {
  development: {
    dialect: 'sqlite',
    storage: 'data/dev-db.sqlite3'
  },
  test: {
    dialect: 'sqlite',
    storage: 'data/test-db.sqlite3'
  },
  production: {
    username: process.env.RDS_USERNAME,
    password: process.env.RDS_PASSWORD,
    database: 'ebdb',
    port: process.env.RDS_PORT,
    host: process.env.RDS_HOSTNAME,
    dialect: 'mysql'
  }
};

This config.js file shows that in dev and test mode, we’ll connect to the sqlite database which is stored in the file “data/<env>-db.sqlite3”.

In production, it instead connects to an RDS instance (the production machine in this project was running on AWS), with the connection details stored in the environment on the cloud machine (rather than being checked into the code).

Now we need to setup sequelize to use the correct database when it is initialised. This is done in the index.js file under the models folder:

var path = require('path');
var Sequelize = require('sequelize');
const basename = path.basename(module.filename);
const env = process.env.NODE_ENV || 'development';
const config = require(path.join(__dirname, '..', 'config', 'config.js'))[env];
const db = {};
var sequelize = new Sequelize(config.database, config.username, config.password, config);

This code fetches the relevant section of the config.js file, and uses it to initialise the ORM. What’s not shown above is the code that loads all the models into Sequelize. We’ll leave that as an excercise for the reader.

As you can see. This file is using an environment variable to determine what mode we are running the code in. All that’s required to run in production is set NODE_ENV in the environment variables to be equal to “production”.

The app can now be started using “npm start”, which will start, using the dev database.

Migrations

Just a quick note here on using Sequelize for database migrations. You can hook your migrations into the “npm start” command above by adding the following to your package.json file:

"scripts": {
    "prestart": "sequelize db:migrate -c config/config.js"
}

Summary

So what have we learnt?

  • You should try and ensure setting up a project is as easy as possible for new developers
  • You can use different environments variables to separate out dev/test/production environments (and default to test).
  • This method can work, even if you are deploying to the cloud.
  • You can easily add database migrations into the start command of your application.

This post originally appeared on Chris Arnott’s personal blog.

Tags: ,

Categories: Technical

«

Leave a Reply

* Mandatory fields


five − 1 =

Submit Comment