Memory Storage for Test Environment with Fastify, Sequelize, and SQLite

I am pretty sure that you have been in a situation where you need to use data from the database in unit tests, that would be run on the CI/CD or locally during development.

Cities

There were possible two approaches:

1. Using test database — where the most amount of time will be spent on setting connection, creating the database, and clearing it after every test.
2. Stubbing methods for reading and writing data from the database — the main drawback of this approach is that we lose the importance of the real data in testing.

There is a third approach — Using Memory Storage. It will have real data as we store it in persistent storage but we don’t need to connect databases because our data will be placed in memory.

In this example, we will show usage of Memory storage in test execution together with using persistent storage for development.

Tools we will use:

• Fastify — Node.js web framework (We will use it for making RESTful API call and injecting call in tests)
• Sequelize — Promise-based Node.js ORM for SQL databases (Postgres, Mysql, MariaDB, SQLite, and MsSQL)
• SQLite — Small, fast, self-contained, high-reliability, and full-featured SQL engine.

For the Development environment, we will use a file-based SQLite database (containing *.sqlite3file, where data is persisted), but it is possible to use any of the SQL databases.

For the Test environment, we will use SQLite database with a memory store.

Source code of this example with instructions are on the next link: https://github.com/NerminImamovic/fastify-sequelize-sqlite

To start with development:

$ mkdir fastify-sequelize-sqlite && cd fastify-squelize-sqlite
$ npm init

Now we should install libraries:

$ npm install fastify sequelize sqlite3
$ npm install -D pino-pretty sequelize tap

It is good to update scripts in package.json because that will make development easier.

"scripts": {
  "sequelize": "node_modules/.bin/sequelize",
  "migrate": "npm run sequelize db:migrate",    
  "seed": "npm run sequelize db:seed:all",    
  "start": "node server.js",    
  "test": "NODE_ENV=test node app.test.js"
},

First, we should initialize sequelize configuration:

$ npm run sequelize init

As the result we will have 4 different folders: config , seeders, migrations , and models .

In config folder we can find the file config.json , that contains data about the environments we use, that we should update to look like this:

{  
  "development": {
    "dialect": "sqlite",
    "storage": "database.sqlite3"
  },
  "test": {
    "dialect": "sqlite",
    "storage": ":memory:"
  }
}

As we can see we will use sqlite databases with different types of storage.

We will create a new model with migration together called City :

$ npm run sequelize -- model:generate --name City --attributes name:string

This command will generate a migration file YYYYMMDDHHMMSS-create-city.js in migrationsfolder and a model file City.jsin models folder. We Generated migration file what we have looks like:

Now we should run migrations. If we don’t have a file database.sqlite3 , it will be created.

$ npm run migrate

After we created migration it is good that we insert some data in the database, so we need to create a seeder for our model.

$ npm run sequelize -- seed:generate --name seed-cities

This command will generate a file called YYYYMMDDHHMMSS-seed-cities.js what we should update to look like:

Now we can seed our database:

$ npm run seed

We will create three different files in the source directory related to the running server and test execution.

First is app.js , where we declared get route to show our all cities:

The most important line of code here is on line 13: await City.sync() , it is not needed for the development environment because data will be read from persistent storage, but for tests, we need to sync data with other files.

In server.js we can find the running of the server on port 3000:

We can run:

$ npm run start

and we can see in our browser or Postman tool:

The last file that we create is app.test.js where we can examine the behavior of our REST methods using fastify.inject .

We can run the test and see results:

$ npm run test

Conclusion

Final conclusion of using memory store for tests:

Benefits:

1. We don’t need to have a test database and worry about reading and writing data from it.
2. CI/CD pipeline job for tests is faster than using a real test database.
3. We don’t need to stub methods for reading and writing data in the database, so we can work with real data.

Drawbacks:

1. SQLite with memory store can cover a lot of possibilities of other SQL databases but not all.
2. It is hard to reproduce when we want to test the behavior of the database if there is some constraint that can play important role in our application.

References:

1. Using Sequelize.js and SQLite in an Express.js App — https://stackabuse.com/using-sequelize-js-and-sqlite-in-an-express-js-app/

If you’ve found any of my articles helpful or useful then please consider throwing a coffee my way to help support my work or give me patronage😊, by using

Ko-fi.com

Thanks,
Nermin