Database Migrations and Seeding

Migrations

In the article about Git, we discussed why it is a good idea to version your code. The same principles also apply to databases. Instead of uploading the entire database, files containing SQL code are created. When executed, they create the required database.

This brings benefits such as eliminating human errors (typos, incorrect relationships), quickly getting new team members started, and being able to restore the database to its previous state.

Key Terms

UP - new changes (adding tables and columns)
DOWN / ROLLBACK - reverting the database to its previous state (deleting tables and columns)
Migration table - a table where the tool records which migrations have already been executed

How Migrations Work and How to Create Them

Migration tools are often part of frameworks (Prisma, Laravel, TypeORM). They work with chronologically named files so that the correct version is executed.

When migrate is run, the tool compares the files in the project with the records in the migration table. It executes the files that are not recorded in the table and then creates a new record.

First migration

-- File: 20260909120000_create_students_table.sql

-- UP: Step forward (applying changes)
CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

-- DOWN: Step back (returning to the previous state)
DROP TABLE students;

Second migration

-- File: 20260909130000_add_email_to_students.sql

-- UP: Adding a new column
ALTER TABLE students ADD COLUMN email VARCHAR(100);

-- DOWN: Removing the added column during rollback
ALTER TABLE students DROP COLUMN email;

Seeding

To avoid having to enter data manually during development, we use a process called seeding. It is the automated process of populating a database with data, which can be divided into:

static data - fixed values used in production (admin account)
fake data - a generator of random values for development (these do not need to be written manually; for example, there is Faker JS Documentation)

-- 1. Static system data (required for the system to run)
INSERT INTO subjects (id, name) VALUES
(101, 'Computer Science'),
(102, 'Mathematics'),
(103, 'English');

-- 2. Test data for development
INSERT INTO students (id, name) VALUES
(1, 'John Smith'),
(2, 'Michael Brown'),
(3, 'Emily Johnson');

INSERT INTO grades (id, student_id, subject_id, value, note) VALUES
(1, 1, 101, 1, 'Mid-year project'),
(2, 2, 101, 3, 'Retake test'),
(3, 3, 102, 1, 'Class participation');

Workflow

Using migrations and seeding follows a simple and intuitive sequence:

schema migration
seeding static and test data
the database is ready for further work