Running Database Migrations with Turso DB and Prisma ORM

Context

Before we get started, it's crucial to understand the context in which we're working. We're assuming you have a running application with an existing database. We'll refer to this as an 'existing database' and, in contrast, 'a brand new database' will be a scenario where we're setting up a fresh database for a new project.

Initial Migration with Prisma ORM

Your starting point with Turso DB and Prisma ORM is to create an initial migration. Prisma ORM operates against the defined schema in `prisma/schema.prisma`. Here's how you can perform your first migration:

Run the initial migration command:

npx prisma migrate dev --name init

This will generate a SQL migration file in '/prisma/migrations' indicating the evolution of your database schema. However, at this point, you'll ignore this file, as your existing database should already reflect these changes.

Apply the changes to your schema:

Now you can amend your `schema.prisma` with the changes you want to make, like adding / removing a column and adding / removing a table.

Create the new migration:

Rerun the migration command, specifying a new name for the changes you just made:

npx prisma migrate dev --name adding_column

This step generates a new migration SQL file that captures only the incremental changes you made in the schema.

You'll see this file under `/prisma/migrations` and it'll be in its own folder.

Apply the changes to your dev database using Turso's CLI:

Connect to your turso account via the CLI. Here's a link to the docs on how to set that up.

Once connected, run this command replacing `{your-dev-database}` with the name of your development database. You don't need to include `{ }` in the command.

turso db shell {your-dev-database} < ./prisma/migrations/20230922132717_add_column/migration.sql

Make sure to run 'npx prisma generate' locally to use the updated Prisma client, reflecting the latest schema definitions.

Deploying to production

In a production environment, you would follow the same steps as in the development stage. But, if you want to avoid errors the order in which you do things matters.

Removing something?

Do all of your code changes (after running the migration on your dev db).

Then deploy your code.

Then, run this command on your production database using the same migration file as you did on dev:

turso db shell {your-production-database} < ./prisma/migrations/20230922132717_remove_column/migration.sql

Adding something?