Skip to content

Database Migration Script

Medium ~20 min

What you'll build

A series of migration scripts that start with a basic users table and evolve it over three versions: adding columns, creating indexes, and adding a new table.

Goals

  • Write up and down migration scripts
  • Use ALTER TABLE to add and remove columns
  • Create indexes in a migration
  • Understand how schemas evolve over time

Prerequisites

  • CREATE TABLE syntax
  • ALTER TABLE basics
  • Understanding of indexes

Steps

  1. 1

    Migration 001: Create users table

    Write the initial migration that creates a basic users table with id, name, and email.

    Code
    -- Migration 001: Initial users table
    -- Up
    CREATE TABLE users (
      id     SERIAL PRIMARY KEY,
      name   TEXT NOT NULL,
      email  TEXT NOT NULL
    );
    
    -- Down
    DROP TABLE users;

    Expected result

    The users table is created with three basic columns.

  2. 2

    Migration 002: Add timestamps and unique email

    In the second migration, add created_at and updated_at columns, and add a unique constraint on email.

    Code
    -- Migration 002: Add timestamps and unique email
    -- Up
    ALTER TABLE users ADD COLUMN created_at TIMESTAMP DEFAULT NOW();
    ALTER TABLE users ADD COLUMN updated_at TIMESTAMP DEFAULT NOW();
    ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email);
    
    -- Down
    ALTER TABLE users DROP CONSTRAINT users_email_unique;
    ALTER TABLE users DROP COLUMN updated_at;
    ALTER TABLE users DROP COLUMN created_at;

    Expected result

    Two timestamp columns and a unique constraint are added.

  3. 3

    Migration 003: Add index and profiles table

    In the third migration, create an index on email for fast lookups, and add a profiles table linked to users.

    Code
    -- Migration 003: Add email index and profiles
    -- Up
    CREATE INDEX idx_users_email ON users(email);
    
    CREATE TABLE profiles (
      id           SERIAL PRIMARY KEY,
      user_id      INTEGER REFERENCES users(id),
      bio          TEXT,
      avatar_url   TEXT
    );
    
    -- Down
    DROP TABLE profiles;
    DROP INDEX idx_users_email;

    Expected result

    An index and a related profiles table are created.

  4. 4

    Verify the final schema

    After all migrations, verify the schema by describing the users table and inserting a user with a profile.

    Code
    -- Check the schema
    \d users
    
    -- Insert a user and profile
    INSERT INTO users (name, email) VALUES ('Ada', '[email protected]');
    INSERT INTO profiles (user_id, bio) VALUES (1, 'Mathematician and programmer');
    
    -- Query with join
    SELECT u.name, u.email, p.bio
    FROM users u
    LEFT JOIN profiles p ON p.user_id = u.id;

    Expected result

    The final schema has all columns, constraints, and indexes from the migrations.

  5. 5

    Add indexes to optimize queries

    Create indexes on frequently queried columns to improve query performance. Indexes are the primary means of database performance optimization, but be mindful of write overhead.

    Code
    -- Create indexes on frequently filtered columns
    CREATE INDEX idx_users_email ON users(email);
    CREATE INDEX idx_orders_user_id ON orders(user_id);
    CREATE INDEX idx_orders_created_at ON orders(created_at DESC);
    
    -- Verify the index is used
    EXPLAIN SELECT * FROM orders WHERE user_id = 42;

    Expected result

    EXPLAIN output shows an index scan using idx_orders_user_id instead of a full table scan.

Try it yourself

What you learned

You wrote versioned migration scripts with up and down directions, evolving a schema over time — the same approach used by Flyway, Alembic, Prisma Migrate, and other migration tools.

Related Resources

Related Lessons

Related Glossary

Related Glossary

A beginner-friendly glossary explains jargon in plain English — variables, functions, DOM, Promise, and more.

Decode error messages

Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.

View errors

← Back to topic