Migrate from Raw SQL Queries to Sequelize Models

From Raw SQL to Sequelize ORM

Migrating from raw SQL queries to an ORM like Sequelize involves two steps: converting your schema to model definitions and then replacing query strings with Sequelize method calls. This tool handles the first step automatically.

Step 1: Schema Conversion (Automated)

Paste your CREATE TABLE statements into the converter. For example:

CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  company VARCHAR(255),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE invoices (
  id SERIAL PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  amount DECIMAL(10,2) NOT NULL,
  status VARCHAR(20) DEFAULT 'pending',
  due_date DATE NOT NULL
);

The converter generates complete Model.init() calls with correct DataTypes, constraints, and association hints.

Step 2: Query Migration (Manual)

Replace raw SQL queries with Sequelize methods:

SELECT:

// Before: db.query('SELECT * FROM customers WHERE email = $1', [email])
// After:
const customer = await Customer.findOne({ where: { email } });

INSERT:

// Before: db.query('INSERT INTO invoices (customer_id, amount, due_date) VALUES ($1, $2, $3)', [...])
// After:
const invoice = await Invoice.create({ customer_id: customerId, amount, due_date: dueDate });

JOIN:

// Before: db.query('SELECT i.*, c.email FROM invoices i JOIN customers c ON i.customer_id = c.id')
// After:
const invoices = await Invoice.findAll({ include: [{ model: Customer, attributes: ['email'] }] });

UPDATE:

// Before: db.query('UPDATE invoices SET status = $1 WHERE id = $2', ['paid', id])
// After:
await Invoice.update({ status: 'paid' }, { where: { id } });

Benefits of Migration

• Type safety (with TypeScript): Catch column name typos at compile time
• Query building: Complex WHERE clauses, pagination, and sorting via JS objects
• Associations: Automatic JOIN generation via include
• Validation: Built-in validators on model fields
• Hooks: Lifecycle callbacks (beforeCreate, afterUpdate, etc.)

Gradual Migration Strategy

You do not need to migrate all queries at once. Sequelize supports sequelize.query() for raw SQL, so you can convert models first and migrate queries incrementally.