Streamlining Your Development Workflow with SQLite

What is SQLite?

SQLite is a self-contained SQL database engine that’s fast, reliable, and full-featured. Its compact size and independence from external databases make it an ideal choice for many applications, whether mobile or web-based.

The Benefits of Using SQLite

By integrating SQLite into our development environment, we can eliminate the need for external databases and enjoy a more streamlined workflow. With SQLite, our database setup is done just once, and then we can easily initialize our project across different development environments without worrying about setting up or configuring a database.

• This eliminates the issue of data inconsistency and the difficulty of connecting to a new database, migrating, and seeding data every time we switch environments.
• As a result, we can significantly speed up our workflow and focus on building amazing applications.

When to Use SQLite (and When Not To)

While SQLite is an excellent choice for many cases, it’s essential to consider the size and type of your application.

• SQLite doesn’t support concurrency, which means only one user can write to the database at a time. If your application requires multiple users to write to the database concurrently, PostgreSQL might be a better option for your production environment.
• Additionally, SQLite is suitable for applications that don’t receive more than 100,000 hits per day. If your application is expected to scale past this limit, SQLite might not be the best choice for production.

Building a Node.js Application with SQLite

Let’s demonstrate how to 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

First, we’ll ensure that we have Node installed in our local environment. Then, we’ll install the Express application generator and scaffold our Node app.

npx express-generator myapp
cd myapp
npm install

We’ll also install the required dependencies, including Sequelize, a promise-based Node.js ORM, and dotenv, which makes our environment variables accessible to our application.

npm install sequelize dotenv

Configuring Our Database

We’ll create a file named.env in our root directory and add variables for our production database URL and environment.

touch.env

Then, we’ll navigate to the./config/config.json file and convert it to a JavaScript module. We’ll fill in the details for our development, test, and production environments, using SQLite for development and test environments and PostgreSQL for production.

module.exports = {
  development: {
    username: 'root',
    password: null,
    database: 'database_test',
    host: '127.0.0.1',
    dialect: 'qlite',
  },
  test: {
    username: 'root',
    password: null,
    database: 'database_test',
    host: '127.0.0.1',
    dialect: 'qlite',
  },
  production: {
    username: 'root',
    password: null,
    database: 'database_production',
    host: '127.0.0.1',
    dialect: 'postgres',
  },
};

Generating Our Database Model

Next, we’ll use Sequelize to create a table in our database. We’ll create a table called User with columns username and password.

const { Sequelize, DataTypes } = require('sequelize');

const sequelize = new Sequelize('sqlite::memory:');

const User = sequelize.define('User', {
  username: {
    type: DataTypes.STRING,
  },
  password: {
    type: DataTypes.STRING,
  },
});

sequelize.sync().then(() => {
  console.log('Database & tables created!');
});

When we navigate to our root directory, we’ll see a file named sqlite-dev.db, which is our newly created SQLite database.

Generating a Seed File for Our Database

We’ll generate a seed file for our user table, which adds initial data to our database for testing purposes.

module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.bulkInsert('Users', [
      { username: 'user1', password: 'password1' },
      { username: 'user2', password: 'password2' },
      { username: 'user3', password: 'password3' },
    ]);
  },
  down: async (queryInterface, Sequelize) => {
    await queryInterface.bulkDelete('Users', null, {});
  },
};

Finally, we’ll seed our database by running a command on our terminal.

npx sequelize db:seed:all

The Power of SQLite

With SQLite, we can access our database quickly and efficiently, without the need for external calls from our application. We can focus on building amazing applications, knowing that our database setup is streamlined and hassle-free. By integrating SQLite into our development environment, we can take our productivity to the next level and build faster, more reliable applications.