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.

Existing Database

If you're working with an existing project, you might already have data in your database. Migrations should be set up in a manner that updates your schema without data loss or downtime. Understanding the downstream effects of each migration is important.

Brand New Database

For new projects, starting with a sound and adaptable schema is a gift to your future self—and to the developers who come after you. Planning your initial migration with foresight will save time and potential headaches down the line.

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:

Amend your `schema.prisma` to reflect changes like adding or removing columns or tables.

Create the new migration:

Rerun the migration command, specifying a new name for your changes:

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.

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

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. However, you'd deploy changes to your application and then run the migrations on your production database. The order is crucial—removing columns or tables should be executed after deployment, while adding columns or tables should be executed before deployment. This ensures that your application is always in sync with the back end.

But I have a brand new database?

You would repeat the same migration steps for a fresh database in a new project. However, at the initial stage, you'd apply the changes in the init migration.sql file to your turso database.