Welcome to this complete tutorial on using SQLite and Sequelize with Models, Migrations, and Seeders. Together, these tools help you design databases, apply changes over time, and populate them with initial data, all while using an intuitive, JavaScript-based approach.
Think of Sequelize as a friendly tour guide that helps you navigate the landscape of your database while SQLite is like a small, portable city you can take anywhere. SQLite is a lightweight and file-based database, often used for smaller projects or quick prototypes. Sequelize is an ORM (Object-Relational Mapping) library in Node.js that translates between your JavaScript code and the underlying database, so you don’t have to speak SQL all day.
In this lecture, we'll build a simple example of a "library system" from start to finish. We'll set up:
Before jumping in, let’s define the key players:
Picture building a house. SQLite is the foundation, and Sequelize is your master builder. Let’s start assembling our project structure. In your chosen directory, create something like this:
project_folder/
├— package.json
├— node_modules/
├— config/
├— migrations/
├— models/
├— seeders/
├— database.sqlite
├— index.js
Notice database.sqlite is just a file that will serve as our actual database. There's no need for a separate database server installation.
First, you need to install the dependencies:
npm install --save sequelize sqlite3
npm install --save-dev sequelize-cli
The sqlite3 library allows Sequelize to interact with SQLite. The sequelize-cli tool helps generate skeletons for models, migrations, and seeders.
Initialize Sequelize in your project_folder:
npx sequelize-cli init
This command creates default folders like config, models, migrations, and seeders if they don’t already exist. You’ll also get a config/config.json (or config.js if you prefer JavaScript) file.
Open config/config.json (or config.js), and customize it to use SQLite:
{
"development": {
"storage": "database.sqlite",
"dialect": "sqlite"
},
"test": {
"storage": "database_test.sqlite",
"dialect": "sqlite"
},
"production": {
"storage": "database_prod.sqlite",
"dialect": "sqlite"
}
}
This means:
A model is like a blueprint for the rooms in your house. If you were building a library system, you might have a Book model. Let’s create a Book model and a matching migration.
npx sequelize-cli model:generate --name Book --attributes title:string,author:string,publishedYear:integer,genre:string
This generates two things:
Open models/book.js to see your new model definition. It should look something like this:
module.exports = (sequelize, DataTypes) => {
const Book = sequelize.define('Book', {
title: {
type: DataTypes.STRING
},
author: {
type: DataTypes.STRING
},
publishedYear: {
type: DataTypes.INTEGER
},
genre: {
type: DataTypes.STRING
}
}, {});
return Book;
};
The Book model corresponds to a Books table in the database, storing information about each book in our library.
Think of migrations as the construction crew that builds the framework of your house. Once you have your blueprint (the model), you use migrations to actually build or modify the database tables.
To run the migration that Sequelize just created for you, use:
npx sequelize-cli db:migrate
This looks at all the migration files and applies any that haven't been run yet. For our Book model, it creates a Books table with the columns you specified.
After our house is built, we want to move in some furniture! Seeders do the job of populating tables with initial or sample data.
Generate a seeder file:
npx sequelize-cli seed:generate --name demo_book_data
You’ll find a new file in the seeders folder called something like XXXXXX-demo_book_data.js.
Open that file and populate it with some sample books:
'use strict';
module.exports = {
up: async (queryInterface, Sequelize) => {
await queryInterface.bulkInsert('Books', [
{
title: 'The Great Gatsby',
author: 'F. Scott Fitzgerald',
publishedYear: 1925,
genre: 'Fiction',
createdAt: new Date(),
updatedAt: new Date()
},
{
title: 'To Kill a Mockingbird',
author: 'Harper Lee',
publishedYear: 1960,
genre: 'Fiction',
createdAt: new Date(),
updatedAt: new Date()
},
{
title: 'A Brief History of Time',
author: 'Stephen Hawking',
publishedYear: 1988,
genre: 'Science',
createdAt: new Date(),
updatedAt: new Date()
}
], {});
},
down: async (queryInterface, Sequelize) => {
await queryInterface.bulkDelete('Books', null, {});
}
};
Notice that seeders typically need createdAt and updatedAt timestamps when using Sequelize with the default settings.
Now, let’s run the seeder:
npx sequelize-cli db:seed:all
Your database will now be populated with three books. In other words, your library is now stocked with sample data – ready for queries and further testing.
You can verify the contents of your database.sqlite using any SQLite viewer or by writing a quick script with Sequelize.
Here’s a simple Node.js script index.js in project_folder/ to check the data:
const { Book } = require('./models');
(async () => {
try {
const books = await Book.findAll();
console.log(books.map(book => book.toJSON()));
} catch (error) {
console.error('Error fetching books:', error);
}
})();
Run node index.js and see if the sample book data prints to your console.
Primary Keys, Timestamps, and More
By default, Sequelize adds an id column as the primary key and also expects
createdAt and updatedAt columns unless you disable timestamps.
In most real-world applications, you might add more columns (like isbn, pages, or publisher)
to your Book model.
Relationships Between Tables
Many projects involve multiple models, for example Authors and Publishers.
With Sequelize, you can set up associations (like belongsTo, hasMany) so that
you can easily fetch data that spans multiple tables.
Migrations in Production
In a production environment, migrations are essential to apply versioned changes to your live database
without losing existing data.
Seeding in Different Environments
Sometimes, you’ll seed only in development or test environments.
You might skip seeding in production or use special production seeders that insert minimal,
essential setup data.
If you want to expand your practice, consider:
Congratulations! You’ve learned how to set up a SQLite database with Sequelize, create models, run migrations, and seed data. This approach works for quick prototypes (thanks to SQLite’s portability) and can even scale up to more robust databases like PostgreSQL, MySQL, or MariaDB by simply adjusting your config settings and installing the proper library.
With these fundamentals in hand, you’re well on your way to building more sophisticated projects that benefit from structured data management. You can extend your library system into a full-fledged, production-ready application or even adapt these steps for any other domain – whether it’s e-commerce, social media, or personal to-do apps.
Happy coding, and enjoy exploring the endless possibilities with SQLite and Sequelize!