Why migrations matter

You just learned how to create tables. You wrote CREATE TABLE, picked column types, added constraints like NOT NULL and UNIQUE, and set up a primary key. You did all of that by typing commands straight into the SQLite shell. That is a great way to learn, but it is not how real projects work.

Here is the problem. The shell only changes the database on your laptop. Nobody else on your team gets those changes. Your staging server does not get them either. Production has no idea any of this happened. The moment more than one computer is involved, typing SQL into a shell stops being enough.

This lesson is where we change habits. From now on in this course, every schema change is a migration. No more typing CREATE TABLE into the shell and hoping for the best. We will write schema changes as small, numbered files, check them into git, and run them with a script. That is how teams and production systems stay in sync.

Why the shell approach breaks down

Imagine this. You add a bio column to the users table on your laptop. You update your application code to use that column. Everything works. You push your code to the team’s git repo and move on.

A teammate pulls your code, runs the app, and it crashes. Why? Because their local database does not have the bio column. Your code expects it, but their database was never updated. They only got the code, not the schema change.

Now picture this happening across a team, with a staging environment and a production server mixed in. Everyone’s database is slightly different, and nobody knows which version is which. Features break in confusing, hard-to-debug ways. “It works on my machine” becomes a daily phrase.

A migration fixes this. A migration is a versioned SQL script that describes a single change to the database schema. Instead of making changes by hand in the shell, you write a file. That file gets checked into version control right next to your code. To bring any database up to date, you run all the migration files that have not been applied yet.

What a migration looks like

Each migration gets a sequential number and a descriptive name:

migrations/
  001_create_users.sql
  002_add_bio_to_users.sql
  003_create_authors_and_books.sql

Migration 001 always runs before 002, which always runs before 003. The numbering controls the order. If a database has already applied 001 and 002, only 003 runs the next time.

What goes inside a migration

A migration contains the SQL needed to make one change. That might be:

• Creating a new table
• Adding a column to an existing table
• Creating an index
• Renaming a column
• Dropping a table you no longer need

Each file is small, focused, and does one thing. Think of it like a git commit for your database schema. Small, self-contained, easy to reason about.

What if I am working alone?

Even solo projects benefit from migrations. Without them, you will eventually forget what changes you made to the database. You will accidentally break something and have no way to recover. You will struggle to set up the project on a new machine because you cannot remember the exact sequence of ALTER TABLE commands you ran three months ago.

With migrations, setting up a fresh database is just running every migration file in order. It is repeatable. It is reliable. It is boring, and boring is exactly what you want from your schema management.

[!TIP] Teach yourself to never modify a table by hand again. Always write a migration file, even when prototyping. The habit pays off almost immediately.

You now understand why migrations exist and why we are about to rely on them for everything that follows. In the next lesson, we will build a migration system from scratch.