Migrations with Drizzle just got better: push to SQLite is here

drizzle-kit push is finally here for SQLite. We’ll explore how it works: locally and at the edge.

Overview

When it comes to databases, it is always hard to keep your schema up-to-date. There is the ‘old good’ approach with a set of migration files that can keep track of schema changes and help you upgrade your schema gradually. However, when it comes to local prototyping and rapid schema changes, this approach can quickly become a real mess.

So, are migration files the only way to manage your schema?
In this article, we’ll explore a different approach featured by drizzle-kit, based on a push/pull workflow.

We’ll showcase drizzle-kit push:sqlite, to manage migrations with local SQLite files, which is a great productivity boost for local development and rapid prototyping.

But if you think SQLite is still only good for local development, you haven't been paying attention! To spice it up, we'll showcase applying those same migrations to Turso, an Edge database compatible with SQLite.

What is Turso?

Turso is an edge-hosted, distributed database based on libSQL, an open-source and open-contribution fork of SQLite. It was designed to minimize query latency for applications where queries come from anywhere in the world. In particular, it works well with edge functions provided by cloud platforms such as CloudFlare, Netlify, and Vercel, by putting your data geographically close to the code that accesses it

For this example, we will set up a new project that will use Turso as the database

Installation

1. Install Turso on your machine:

curl -sSfL https://get.tur.so/install.sh | bash

2. Verify the installation:

turso --version

Managing you databases

1. Use the auth command to start the login process. The command launches your default browser and prompts you to log in with GitHub:

turso auth login

2. Create a new Turso database:

turso db create drizzle-demo

3. Create an auth token to get access to your database using libSQL

turso db tokens create drizzle-demo

Congratulations! 🎉
You have created your first edge database with Turso!

Now that we have a full set of keys, we can explore the best practices for managing your schema with Drizzle and Turso databases

Schema management flows

To ensure a smooth and easy development flow, let’s stick to a simple pattern:

1. Change your drizzle schema.
2. Run drizzle-kit push:sqliteon a local database.
3. Check if the schema is ready to be deployed. If not, repeat steps 1–3
4. If the schema is ready, run drizzle-kit generate:sqlite
5. Execute migrations on a production database during a backend code deployment.

In case you want to push your schema directly to a production database, follow these steps:

1. Change your drizzle schema.
2. Run drizzle-kit push:sqlite on a production database.

** However, we will stick with the first approach because the second one is risky and not recommended for usage on production databases

Using local file for prototyping (First approach)

Let’s install all the necessary dependencies for this example

We will use a libSQL driver, which is an open-source and open-contribution fork of SQLite. It will help us communicate with both local SQLite files and the Turso database you just created

pnpm add drizzle-orm@latest
pnpm add drizzle-kit@latest -D

pnpm add @libsql/client

Firstly, we should create a basic schema for the drizzle tables in SQLite.

import { index, sqliteTable, integer, text } from "drizzle-orm/sqlite-core";

export const users = sqliteTable( "users", {
    id: integer("id"),
    name: text("name").notNull(),
  },
  (table) => ({
    idx1: index("name_index").on(table.name),
  })
);

And a drizzle.config.ts file for migration variables with package.json scripts for running migrations:

import { Config } from "drizzle-kit";

export default {
  schema: "./schema.ts",
  out: "./migrations",
  driver: "turso",
  dbCredentials: {
    url: 'file:./local.db',
  }
} satisfies Config;

{
  "scripts": {
    "generate": "drizzle-kit generate:sqlite",
    "push": "drizzle-kit push:sqlite",
  }
}

All you need to do is to run drizzle-kit push:sqlite anytime you change your schema file while implementing any new feature in your project

pnpm run push

If there is no possibility of data loss while changing your schema,
drizzle-kit will output

No config path provided, using default 'drizzle.config.ts'
Reading config file './drizzle.config.ts'

[✓] Changes applied

You can use the --verbose option to print all statements that Drizzle is going to execute in order to sync your schema.

Additionally, you can use the --strict option to make Drizzle always ask for your confirmation about actions to be taken, even if they do not result in data loss.

import { Config } from "drizzle-kit";

export default {
  schema: "./schema.ts",
  out: "./migrations",
  driver: "turso",
  dbCredentials: {
    url: 'file:./local.db',
  },
  // Print all statements
  verbose: true,
  // Always ask for my confirmation
  strict: true,
} satisfies Config;

In this case, the same table creation process will yield the following output:

You can continue pushing any changes you need while developing your application. Let’s say you no longer need the name column and instead need to create a new non-null column called position

import { integer, sqliteTable } from "drizzle-orm/sqlite-core";

export const users = sqliteTable(
  "users",
  {
    id: integer("id"),
    position: integer("position").notNull(),
  }
);

Finally, when you are ready with the table structure, you can generate the migration file and then run it on a Turso production database.

pnpm run generate

No config path provided, using default 'drizzle.config.ts'
Reading config file './drizzle.config.ts'

1 tables
users 2 columns 0 indexes 0 fks

[✓] Your SQL migration file ➜ drizzle/0000_amusing_ego.sql 🚀

Important! The migration files flow was designed for migrating production databases with data. Since in SQLite, many changes require the use of a mirror table and copying data between tables, the generate command won’t automatically handle those changes. Drizzle will provide suggestions, but you are the one who needs to decide on a better migration strategy for your application. Here is an example of the generated output for changes like dropping NOT NULL.

/*
 SQLite does not support "Drop not null from column" out of the box, 
 we do not generate automatic migration for that, so it has to be done manually
 Please refer to: https://www.techonthenet.com/sqlite/tables/alter_table.php
                  https://www.sqlite.org/lang_altertable.html
                  https://stackoverflow.com/questions/2083543/modify-a-columns-type-in-sqlite3

 Due to that we don't generate migration automatically 
 and it has to be done manually
*/

Now you can create migrate.ts file to either run migrations on server start or in your CI.

import { migrate } from "drizzle-orm/libsql/migrator";
import { drizzle } from "drizzle-orm/libsql";
import { createClient } from "@libsql/client";

async function main() {
  const db = drizzle(createClient({ url: "", authToken: "" }));

  console.log('Running migrations')

  await migrate(db, { migrationsFolder: "drizzle" });

  console.log('Migrated successfully')

  process.exit(0)
}

main().catch((e) => {
    console.error('Migration failed')
    console.error(e)
    process.exit(1)
});

Push directly to Turso (Second approach)

If you still don’t need those migration files and you are fine with applying schema changes directly to a remote database, you can still do it. All you need to do is change the drizzle.config.ts file to work with Turso.

Note: Be aware of possible data loss statements that may truncate data you don’t want to lose. Drizzle will warn you and ask for your confirmation.

import { Config } from "drizzle-kit";

export default {
  schema: "./schema.ts",
  out: "./migrations",
  driver: "turso",
  dbCredentials: {
    url: '',
    authToken: ''
  }
} satisfies Config;

Don’t hesitate to hit us a star on GitHub and subscribe on Twitter!