Database migrations with Geni and libSQL

Database migrations are a common practice in application development, and database management. Migrations allow you to manage changes to your database schema over time, and keep track of those changes in a structured way.

Migrations are also a great way to collaborate with others on your project. Each new pull request that includes changes to the application code, and database can be reviewed, and database changes can be applied in a controlled manner.

Geni is a simple and lightweight database migration tool. Geni uses SQL files to define your migrations, and provides a simple CLI to apply and rollback migrations.

In this tutorial, you'll learn how to use Geni to manage your database migrations.

#Install Geni

Begin by installing the Geni CLI so that we can use it locally:

sudo curl -fsSL -o /usr/local/bin/geni https://github.com/emilpriver/geni/releases/latest/download/geni-linux-amd64
sudo chmod +x /usr/local/bin/geni

Or, if you're using Homebrew on macOS:

brew install geni

There are other options for Nix, Cargo, and Docker users — learn more.

#Your database

It's likely you already have a Turso Database, but if you don't — create one.

Once you have your database, you'll need to set the DATABASE_URL and DATABASE_TOKEN environment variables. You can find these in your Turso Database dashboard, or using the CLI:

turso db show <database-name> --url
turso db tokens create <database-name>

export DATABASE_URL=libsql://...
export DATABASE_TOKEN=...

If you want to follow along using just a SQLite file, you can do that by setting DATABASE_URL to sqlite://./path/to/your/db.sqlite.

#Create your first migration

Let's begin by creating our first migration. We'll create a migration to create a users table in our database.

geni new create_users_table

You should now see the files X_create_users_table.up.sql and X_create_users_table.down.sql in your migrations directory.

Open the up.sql file and add the following SQL to create the users table:

CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  username TEXT NOT NULL,
  email TEXT NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

It's important we also add the SQL to drop the users table in the down.sql file:

DROP TABLE users;

This is used when we rollback the migration.

#Apply your first migration

Run the following command to apply the migration:

geni up

Migrations are applied using database transactions. If the migration fails, the transaction is rolled back and the database is left in its original state.

#Modifying the users table

Let's say we want to add a age field to the users table. We can create a new migration to do this.

geni new add_age_to_users

Open the up.sql file and add the following SQL to add the age field:

ALTER TABLE users
ADD COLUMN age INTEGER;

Add the SQL to remove the age field in the down.sql file:

ALTER TABLE users
DROP COLUMN age;

Run the following command to apply the migration:

geni up

#Rollback a migration

To rollback the last migration, run the following command:

geni down

This will run the down.sql file for the last migration. You can pass the -a argument with the number of migrations to rollback if you messed up and need to rollback multiple migrations.

#Checking the status of migrations

You can check the status of any pending migrations by running the following command:

geni status

That's it! You now know how to manage your database migrations with Geni.

#Automating migrations

Just like merging your code changes can be automated to production with CI/CD, you can do the same with your database migrations.

Geni provides a GitHub Action that you can use to automate your migrations. Here's what it looks like using GitHub Actions to create a migration workflow:

// .github/workflows/migration.yml
name: Migrate Database

on:
  push:
    branches:
      - main

jobs:
  migrate:
    runs-on: ubuntu-latest

    steps:
      - uses: actions/checkout@v2
      - name: Apply
        uses: emilpriver/geni@main
        with:
          database_url: ${{ secrets.DATABASE_URL }}
          database_token: ${{ secrets.DATABASE_TOKEN }}

You'll need to add DATABASE_URL and DATABASE_TOKEN to your GitHub repository secrets.