Sequelize.js: How to Connect to SQLite Database

Introduction

Sequelize is a promise-based Node.js ORM (Object-Relational Mapping) for PostgreSQL, MySQL, MariaDB, SQLite, and MSSQL. It is robust and easy to use, offering a comprehensive set of features for handling relational data. This article provides a step-by-step guide on how to establish a connection between Sequelize and an SQLite database, which is a popular choice for embedded storage solutions.

Prerequisites

• Basic knowledge of JavaScript and Node.js.
• Node.js and npm installed.
• A text editor or an IDE (Integrated Development Environment).

Step by Step Guide

Step 1: Setting Up the Project

Create a new directory for your project and initialize it with npm:

$ mkdir sequelize-sqlite-project
$ cd sequelize-sqlite-project
$ npm init -y

Now, install the Sequelize package and the SQLite3 Node.js module:

$ npm install sequelize
$ npm install sqlite3

Step 2: Basic Connection

Start by creating a new file index.js in your project directory. Then, set up a simple connection to an SQLite database:

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

// Setting up the database connection
const sequelize = new Sequelize({
  dialect: 'sqlite',
  storage: 'path/to/your/database.sqlite'
});

// Testing the connection
async function testConnection() {
  try {
    await sequelize.authenticate();
    console.log('Connection has been established successfully.');
  } catch (error) {
    console.error('Unable to connect to the database:', error);
  }
}

testConnection();

Step 3: Defining Models

With the connection in place, define a model to structure your data:

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

// Defining a model
const User = sequelize.define('User', {
  name: {
    type: DataTypes.STRING,
    allowNull: false
  },
  email: {
    type: DataTypes.STRING,
    allowNull: false,
    unique: true
  }
});

// Synchronizing the model with the database
sequelize.sync({ force: true }).then(() => {
  console.log('Models synchronized successfully.');
});

Step 4: CRUD Operations

With the model set up, implement CRUD operations:

// Create a new user
User.create({ name: 'John Doe', email: '[email protected]' });

// Read data
User.findAll().then(users => {
  console.log(users);
});

// Update data
User.update({ name: 'Jane Doe' }, {
  where: { email: '[email protected]' }
});

// Delete data
User.destroy({
  where: { name: 'John Doe' }
});

Step 5: Advanced Configuration

Explore the advanced options for configuring your Sequelize connection, such as setting up event hooks and logging queries:

const sequelize = new Sequelize({
  dialect: 'sqlite',
  storage: 'path/to/your/database.sqlite',
  logging: console.log,
  hooks: {
    beforeConnect: (config) => {
      console.log('Before connecting to the database.');
    },
    afterConnect: () => {
      console.log('After connecting to the database.');
    }
  }
});

Step 6: Handling Transactions

Transactions ensure data integrity. Sequelize makes handling transactions easy:

await sequelize.transaction(async (t) => {
  const transactionHost = { transaction: t };

  // Operations within a transaction
  const user = await User.create({ name: 'John Transaction', email: '[email protected]' }, transactionHost);

  await User.update({ name: 'John Commit' }, {
    where: { email: '[email protected]' }
  }, transactionHost);
});

Conclusion

In this article, we’ve covered the basics of connecting Sequelize to an SQLite database and delved into more advanced concepts like model definitions, CRUD operations, advanced configurations, and handling transactions. Sequelize offers a range of features that make it a powerful tool for working with relational databases. With practice, you can leverage its full potential for efficient and effective database management.