# Database migrations with Geni and libSQL

[Jamie Barton](/content/author/jamie-barton/index.html)

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](https://github.com/emilpriver/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:

```bash
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:

```bash
brew install geni
```

There are other options for Nix, Cargo, and Docker users — [learn more](https://github.com/emilpriver/geni/blob/main/README.md#installation).

## Your database

It's likely you already have a Turso Database, but if you don't — [create one](https://sqlite.new/).

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](https://docs.turso.tech/cli/db/show#flags):

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

```bash
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.

```bash
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:

```sql
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:

```sql
DROP TABLE users;
```

This is used when we rollback the migration.

## Apply your first migration

Run the following command to apply the migration:

```bash
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.

```bash
geni new add_age_to_users
```

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

```sql
ALTER TABLE users
ADD COLUMN age INTEGER;
```

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

```sql
ALTER TABLE users
DROP COLUMN age;
```

Run the following command to apply the migration:

```bash
geni up
```

## Rollback a migration

To rollback the last migration, run the following command:

```bash
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:

```bash
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:

```yaml
// .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.