SQL Databases with Sequelize

Reviewed & published by Brayan K

Sequelize is an ORM for Node that maps JavaScript objects to rows in a SQL database, so you can define models and run create, read, update, and delete operations without writing raw SQL.

Part of the free Node.js course at LearnCodingFast — hands-on lessons with worked examples and the output they print, plus practice exercises and a quick quiz.

In this lesson you'll define a model, perform CRUD with methods like create, findAll, update, and destroy, link tables with associations, learn why migrations beat sync() in production, and see how Sequelize compares with Mongoose.

What You'll Learn in This Lesson

• What an ORM is and why Sequelize saves you from raw SQL
• Defining a model with fields and data types
• CRUD: create, findAll, findByPk, update, destroy
• Filtering rows with where clauses and operators
• Linking tables with associations (hasMany / belongsTo)
• Why migrations beat sync(), and Sequelize vs Mongoose

1️⃣ Defining a Model

A model is a JavaScript description of a table: its name and its columns (with types and rules). You connect to the database once, then sequelize.define(...) creates the model. Each field gets a DataTypes type (STRING, INTEGER, …) and optional constraints like allowNull or unique.

// Sequelize is an ORM — it maps JS objects to SQL table rows.
// Install once:  npm install sequelize sqlite3
const { Sequelize, DataTypes } = require('sequelize');

// Connect. (SQLite here for zero setup; swap the URL for Postgres/MySQL.)
const sequelize = new Sequelize('sqlite::memory:');

// Define a MODEL — this maps to a "Users" table.
const User = sequelize.define('User', {
  name:  { type: DataTypes.STRING, allowNull: false },
  email: { type: DataTypes.STRING, unique: true },
  age:   { type: DataTypes.INTEGER },
});

// sync() creates the table from the model (use migrations in production).
await sequelize.sync();

2️⃣ CRUD Operations

Once you have a model, the four basic operations each map to a method. create inserts a row, findAll and findByPk read, save (or update) writes changes, and destroy deletes. All return Promises, so you await them. Filters go in a where clause, with operators like Op.gt for "greater than".

To see CRUD with real output and no database to install, here's a Map-backed model exposing the same method names:

// A tiny Map-backed "model" with the SAME method names as Sequelize, so you
// can run CRUD with real output without a database installed.

function defineModel() {
  const rows = new Map();
  let nextId = 1;
  return {
    create(data) {
      const row = { id: nextId++, ...data };
      rows.set(row.id, row);
      return row;                       // mirrors await Model.create()
    },
    findAll() {
      return [...rows.values()];        // mirrors await Model.findAll()
    },
    findByPk(id) {
      return rows.get(id) || null;      // mirrors await Model.findByPk(id)
    },
    update(id, changes) {
      const row = rows.get(id);
      if (row) Object.assign(row, changes);
      return row;
    },
    destroy(id) {
      return rows.delete(id);           // mirrors await row.destroy()
    },
  };
}

const User = defineModel();

console.log('Created:', User.create({ name: 'Ada', age: 36 }));
console.log('Created:', User.create({ name: 'Linus', age: 54 }));
User.update(1, { age: 37 });            // Ada has a birthday
User.destroy(2);                        // remove Linus
console.log('findAll:', User.findAll());
console.log('findByPk(1):', User.findByPk(1));
console.log('findByPk(2):', User.findByPk(2));

3️⃣ Associations, Migrations & vs Mongoose

Associations connect tables: User.hasMany(Post) and Post.belongsTo(User) create the foreign key and let you load related rows together. For schema changes, prefer migrations — versioned, reversible scripts run with the Sequelize CLI — over sync(), which can silently alter or drop columns. And remember the big picture: Sequelize is for SQL databases with JOINs and foreign keys, while Mongoose (the previous database lesson) is for MongoDB documents.

// Associations: a User has many Posts; each Post belongs to a User.
User.hasMany(Post);
Post.belongsTo(User);

// Now you can load a user WITH their posts in one call:
const withPosts = await User.findByPk(1, { include: Post });

// --- A migration file (run with: npx sequelize-cli db:migrate) ---
module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.addColumn('Users', 'verified', {
      type: Sequelize.BOOLEAN,
      defaultValue: false,
    });
  },
  async down(queryInterface) {
    await queryInterface.removeColumn('Users', 'verified');
  },
};

Your turn. The model below works once you fill in the two method names marked ___. Follow the 👉 hints, then run it.

// 🎯 YOUR TURN — replace each ___ then run it.
function defineModel() {
  const rows = new Map();
  let nextId = 1;
  return {
    create(data) {
      const row = { id: nextId++, ...data };
      rows.set(row.id, row);
      return row;
    },
    findAll() { return [...rows.values()]; },
  };
}
const Book = defineModel();

Book.___({ title: 'Node Mastery' });   // 👉 which method inserts a row? (create)
Book.create({ title: 'SQL Basics' });

console.log(Book.___());               // 👉 which method lists all rows? (findAll)

// ✅ Expected output:
//    [ { id: 1, title: 'Node Mastery' }, { id: 2, title: 'SQL Basics' } ]

Mini-Challenge: A WHERE Filter

No blanks — just a brief and an outline. Add a where-style filter to findAll so you can fetch only the rows that match. Build it, run it, and compare with the example output.

// 🎯 MINI-CHALLENGE: a model with a WHERE-style filter
// 1. defineModel() with create(data) and findAll(where = {}).
// 2. findAll(where) returns only rows where EVERY key in "where" matches.
// 3. Create three tasks (two with done:false, one with done:true).
// 4. Print findAll() (all of them) and findAll({ done: false }) (the open ones).
//
// ✅ Example output:
//    All: [ ...three tasks... ]
//    Open: [ task 1, task 3 ]

// your code here

Common Errors (and the fix)

• ❌ "SequelizeConnectionError" — wrong host, port, credentials, or the database isn't running. ✅ Double-check the connection string and that the database server is up.
• ❌ Forgetting await — every model method returns a Promise, so without await you log a pending Promise, not data. ✅ Await each call inside an async function.
• ❌ "no such table" — you queried before creating the schema. ✅ Run sync() (dev) or your migrations (prod) first.
• ❌ Including an association that isn't defined — include: Post fails if you never called hasMany/belongsTo. ✅ Declare the association before querying with include.
• ❌ Using sync({ alter: true }) in production — it can drop or rewrite columns and lose data. ✅ Use migrations for any database you care about.

Pro Tips

• 💡 Develop on SQLite, deploy on Postgres: Sequelize's dialects let your model code stay the same across databases.
• 💡 Always migrate, never sync in prod: versioned migrations give you a reversible, source-controlled schema history.
• 💡 Watch for N+1: load related rows with include instead of looping and querying per row.

📋 Quick Reference — Sequelize CRUD

🎉 Lesson Complete!

• ✅ Sequelize is an ORM that maps JS objects to SQL rows so you avoid raw SQL
• ✅ sequelize.define describes a table with typed fields and constraints
• ✅ CRUD: create, findAll, findByPk, save/update, destroy — all awaited
• ✅ where clauses and operators filter results
• ✅ Associations (hasMany/belongsTo) link tables via foreign keys
• ✅ Use migrations, not sync(), for production schema changes; Sequelize is SQL, Mongoose is MongoDB

Practice quiz

Continue this course

• Previous: GraphQL APIs
• Next: Caching with Redis (ioredis) — Speed up apps with the cache-aside pattern and TTLs
• Quick reference: Node.js cheat sheet

Frequently asked questions

What is an ORM and why use Sequelize?

An ORM (Object-Relational Mapper) is a library that lets you work with database rows as plain JavaScript objects instead of writing raw SQL strings. With Sequelize you define a model (User.create({...}), User.findAll()) and it generates the INSERT, SELECT, UPDATE, and DELETE statements for you, handling parameter escaping (which prevents SQL injection), type conversion, and differences between databases. The benefits are productivity and safety; the cost is a layer of abstraction that can hide what SQL is actually running and occasionally produce inefficient queries — so for hot paths you sometimes drop down to raw queries, which Sequelize also supports.

How are Sequelize associations different from Mongoose references?

Both express relationships between records, but they map to different database models. Sequelize is for SQL databases, so associations like User.hasMany(Post) and Post.belongsTo(User) correspond to foreign keys and let you JOIN tables — the relational engine enforces referential integrity. Mongoose is for MongoDB, a document database, where you either embed related data inside a document or store an ObjectId reference and use .populate() to fetch it in a second query; there are no real JOINs or foreign-key constraints. Choose by your database: Sequelize when your data is naturally relational and you want SQL's guarantees, Mongoose when you want flexible, document-shaped data.

Should I use sync() or migrations?

sequelize.sync() inspects your models and creates or alters tables to match — convenient for quick prototypes and local development. But in production it's risky: sync({ alter: true }) can make destructive schema changes, and there's no record of how the schema evolved. Migrations are versioned scripts (run with the sequelize-cli) that describe each schema change explicitly, can be applied and rolled back in order, and are committed to source control so every environment converges to the same schema. The rule of thumb: sync() for throwaway experiments, migrations for any database whose data you care about.

Does Sequelize work with PostgreSQL, MySQL, and SQLite?

Yes. Sequelize supports PostgreSQL, MySQL, MariaDB, SQLite, and Microsoft SQL Server through pluggable dialects. You install the matching driver (for example pg for Postgres, mysql2 for MySQL, sqlite3 for SQLite) and point the connection string at your database; most of your model and query code stays identical across them. This is part of an ORM's appeal — you can develop against an in-memory SQLite database for fast tests and deploy against Postgres in production with minimal changes. Be aware that a few features and data types are dialect-specific, so test against the database you'll actually run.