Database Migration Script
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
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
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
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
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
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
- Migrations
Learn about schema migrations.
- Indexes
Learn about indexes.
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.