Database migrations on deploy

A migration is a versioned file that changes your database schema: add a table, add a column, create an index. The tool records which files already ran, so every environment ends up with the same schema. The hard part is not writing the file. It is running it at the right moment of a deploy, once, with a way back.

This guide covers the three most common setups (Prisma, Drizzle and Alembic), the order of steps, and how to write changes that do not freeze a busy table. If your version of the tool differs, check the commands in its official documentation.

What do you need before you start?#

• A database you can reach from where the deploy runs, with the connection string in an environment variable such as DATABASE_URL. See what environment variables are.
• Migration files in your repository, generated by your tool and committed with the code.
• A copy of the data you can restore. If you have no recent one, read how to back up a database first.

What is the right order of a deploy with migrations?#

Four steps, in this order:

1. Save a restore point. A dump or a platform snapshot of the database.
2. Apply the migrations. One process, one time, with the tool's deploy command.
3. Check the result. The command exit code must be 0, and the tool should report the schema as up to date.
4. Start the new code. Only after step 3 succeeded.

Step 4 depends on step 3. If the migration fails and the app starts anyway, the code queries columns that do not exist. Chaining the commands with && gives you that for free, because the second command does not run when the first one fails.

Be careful with several app instances. If two instances start together and both run the migration, they can collide. Run the migration from a single place (a CI job or one instance), not from every replica.

How do you run migrations with Prisma?#

Production uses prisma migrate deploy, which applies the migration files already in your repository. Prisma's documentation says to run it as part of your CI/CD pipeline and advises against running it locally against a production database. prisma migrate dev, on the other hand, is a development command, and the documentation says it should never be used in production. The prisma CLI is usually a dev dependency, so it must be installed where the command runs.

{
  "scripts": {
    "migrate": "prisma migrate deploy",
    "start": "npm run migrate && node dist/index.js"
  }
}

Locally you create the migration with npx prisma migrate dev --name add_orders_table, commit the generated folder under prisma/migrations, and let the deploy apply it.

How do you run migrations with Drizzle?#

Drizzle splits the work in two. drizzle-kit generate writes SQL files from your schema, and drizzle-kit migrate applies the ones not yet applied. It needs the dialect and the connection credentials, either in drizzle.config.ts or as --dialect and --url flags. Applied migrations are logged in a __drizzle_migrations table, in the drizzle schema by default.

{
  "scripts": {
    "generate": "drizzle-kit generate",
    "migrate": "drizzle-kit migrate",
    "start": "npm run migrate && node dist/index.js"
  }
}

drizzle-kit is usually a dev dependency, so it must be installed where the command runs. To avoid depending on it, the drizzle-orm package ships a migrator you can call from code before the server starts:

import { drizzle } from "drizzle-orm/node-postgres";
import { migrate } from "drizzle-orm/node-postgres/migrator";
import { Pool } from "pg";

const pool = new Pool({ connectionString: process.env.DATABASE_URL });
await migrate(drizzle(pool), { migrationsFolder: "./drizzle" });
await pool.end();

How do you run migrations with Alembic?#

Alembic is the migration tool for SQLAlchemy in Python. You create the environment once with alembic init alembic, create a revision with alembic revision -m "create account table", and apply everything with alembic upgrade head.

alembic upgrade head && python main.py

Two other commands help in a deploy. alembic current prints the revision the database is at, and head appears only when it matches the latest revision in your code. alembic downgrade -1 steps back one revision. Keep the database URL out of alembic.ini: read DATABASE_URL from the environment in env.py so no password lands in Git.

How do you write a migration that does not lock the table?#

Most schema changes take a lock on the table, and queries from your app wait while it is held. In PostgreSQL, ALTER TABLE takes an ACCESS EXCLUSIVE lock, which blocks reads and writes, except for the forms where the documentation notes a different lock. On a small table this lasts milliseconds. On a large one, a scan inside the lock can freeze the app. Some habits help:

• Add a column with a constant default. For a non-volatile DEFAULT, PostgreSQL stores the value in the table metadata instead of rewriting every row, so the statement is fast even on large tables.
• Split NOT NULL. SET NOT NULL scans the whole table. Add a CHECK (col IS NOT NULL) constraint as NOT VALID, validate it separately, then set NOT NULL: PostgreSQL skips the scan when a valid check already proves there are no nulls.
• Add foreign keys as NOT VALID. ADD FOREIGN KEY ... NOT VALID skips the long scan, and VALIDATE CONSTRAINT later takes only a SHARE UPDATE EXCLUSIVE lock.
• Do not rename or drop in the same deploy as the code change. Old code still running (or a rollback) would break. Add the new column, ship code that uses it, and remove the old one in a later deploy.
• Move big data fixes out of the migration. Updating millions of rows in one statement holds locks and fills the log. Backfill in batches from a script.

How do you know the migration worked, and what if it failed?#

Check three things after the command: the exit code, the tool's own status (alembic current, or prisma migrate status), and the schema itself (\d tablename in psql). Then read the app logs after it starts. See how to read app logs.

When a migration fails, stop the deploy and read the error. On PostgreSQL, DDL runs inside a transaction, so the change is usually rolled back and you fix the file and run again. MySQL commits most schema changes implicitly, so a failure can leave a half-applied change that you correct by hand. In both cases, the restore point from step 1 is your way out. More on going back in rollbacks and zero-downtime deploys.

Common mistakes:

• Editing a migration that already ran in production. Create a new one instead.
• Running migrate dev or --autogenerate blindly in production. Read the generated SQL first.
• Letting every replica run the migration at boot.
• Putting the connection string in the repository instead of an environment variable.

How to do this on Square Cloud#

Square Cloud offers hosting for PostgreSQL and MySQL (plus MongoDB and Redis) from the Standard plan, in the same datacenter as your applications. The dashboard hands you the full connection URL and a certificate to download; TLS is mandatory, so the driver must trust the downloaded CA (and, on PostgreSQL, also present the .pem certificate).

Store the connection URL as an environment variable of the app, through the dashboard, API, CLI or VS Code. A change applies on the next restart, with no rebuild. Then pick where the migration runs:

• At boot. The start command runs as a shell command, so npm run migrate && npm start works, as does the Drizzle migrate() call from code. There is no build step, and dependencies install at start with NODE_ENV=production, so devDependencies are not installed: keep prisma or drizzle-kit in dependencies, and build dist/ in CI or inside the start command.
• From your machine or CI. The database accepts connections from outside with its host, port, password and certificate, so a CI job can migrate it before you deploy through GitHub Actions.

If several applications share the database, run the migration from only one place. For the restore point, take a manual snapshot before the deploy. Manual snapshots are available on every plan, and a database snapshot can be restored into the same database, another one of the same engine or a new database, so you can test a risky migration on a copy first. Every plan that includes databases also has a daily automatic snapshot, kept for 7 days.

See the database hosting page for plans and engines.