Geni

Geni is a standalone migration tool designed to work in conjunction with your preferred ORM/toolkit/code. It allows multiple developers to collaborate without overriding database migrations. It can be used in a CD pipeline alongside your code to ensure your database stays up to date.

This project was heavily inspired by dbmate and was created because dbmate lacked support for LibSQL.

The application is developed using the Rust programming language and relies on the libsql-client-rs library for SQLite and LibSQL. Moreover, it makes use of SQLX to support Postgres, MariaDB, and MySQL databases. As this is written in rust is lighting fast, blazingly fast, tiny, ultra fast and memory safe

Geni in the news

Database Migrations made easy with Geni by Jamie Barton

Features

Databases:
- Postgres
- MariaDB
- MySQL
- SQLite
- LibSQL
Generating migrations using geni new **name**
Migrating using geni up
Rollback using geni down
Create database using geni create
Dropping database using geni drop
Timestamp based migrations
Running migrations in a transaction
Status command to see which migrations that is pending to be applied
Dump a schema.sql after each migration which can be used in version control
- Dumping needs another binaries to work:
  - Postgres: Works without need for another binary. Uses SQL code to get schema
  - MySQL: mysqldump need to be installed(already installed in docker)
  - MariaDB: mariadb-dump need to be installed(already installed in docker)
  - Sqlite: Works without need for another binary. Uses SQL code to get schema
  - LibSQL: Works without need for another binary. Uses SQL code to get schema

TODO

[ ] Databases
- [ ] ClickHouse

Installation

Github

$ 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

Homebrew

brew install geni

Scoop

TBA

PKGX

Run using PKGX

pkgx geni up

Nix flake

Run using nix

nix run github:emilpriver/geni -- up

Cargo

cargo install geni

Docker

Docker images are published to GitHub Container Registry (ghcr.io/emilpriver/geni).

$ docker run --rm -it --network=host ghcr.io/emilpriver/geni:latest --help

there is also a slim docker image that don't have each database respective libraries(such as pg_dump).

Note: This image won't try to dump the database

$ docker run --rm -it --network=host ghcr.io/emilpriver/geni:latest-slim --help

If you wish to create or apply migrations, you will need to use Docker's bind mount feature to make your local working directory (pwd) available inside the geni container:

$ docker run --rm -it --network=host -v "$(pwd)/migrations:/migrations" ghcr.io/emilpriver/geni:latest new create_users_table`

Commands

geni new    # Generate a new migrations file
geni up     # Run any pending migration
geni down   # Rollback migrations, use --amount to speify how many migrations(default 1)
geni create # Create the database, only works for Postgres, MariaDB and MySQL. If you use SQLite will geni create the file before running migrations if the sqlite file don't exist. LibSQL should be create using respective interface.
geni drop   # Remove database
geni status # Print pending migrations
geni help   # Print help message

Environment variables

DATABASE_MIGRATIONS_FOLDER
- Specify where geni should look for migrations to run.
- Default: ./migrations
DATABASE_URL
- The database url geni should use to make migrations
- Examples:
  - Postgres:DATABASE_URL="postgres://postgres@127.0.0.1:5432/app?sslmode=disable"
  - MySQL: mysql://root:password@localhost:3307/app
  - MariaDB: mariadb://root:password@localhost:3307/app
  - Sqlite: sqlite://./database.sqlite
  - LibSQL: https://localhost:6000
    - The protocol for LibSQL is https.
    - For turso uses: This is something you can retrieve using Turso CLI or the website
- When using SSH tunneling, keep DATABASE_URL pointed at the remote/private database host. Geni will rewrite it to a local forwarded address at runtime.
DATABASE_SSH_HOST
- Enables SSH tunneling for supported local CLI commands.
- Accepts either a raw SSH host or a ~/.ssh/config host alias.
- When you pass an alias, geni uses the system ssh client, so OpenSSH settings such as HostName, User, Port, IdentityAgent, ProxyJump, and related auth settings are honored automatically.
DATABASE_SSH_USER
- Optional SSH username override.
- If omitted, OpenSSH config and defaults are used.
DATABASE_SSH_PORT
- Optional SSH port override.
- If omitted, OpenSSH config and defaults are used.
DATABASE_SSH_IDENTITY_FILE
- Optional SSH identity file override.
- If omitted, geni leaves identity selection to the system ssh client, including ssh-agent and IdentityAgent from ~/.ssh/config.
DATABASE_SSH_LOCAL_PORT
- Optional local port to bind for the tunnel.
- Default: a local port chosen by geni from 60000..=65000
DATABASE_SSH_REMOTE_HOST
- Optional remote host reachable from the SSH server.
- Default: the host from DATABASE_URL
DATABASE_SSH_REMOTE_PORT
- Optional remote port reachable from the SSH server.
- Default: the port from DATABASE_URL, or 5432 for Postgres and 3306 for MySQL/MariaDB
DATABASE_TOKEN
- Only if you use Turso and LibSQL and require token to authenticate. If not specified will Geni try to migrate without any auth
DATABASE_WAIT_TIMEOUT
- Time for geni to wait before trying to migrate. Useful if your database need some time to boot
- Default: 30 seconds
DATABASE_SCHEMA_FILE
- Name of the schema migration file
DATABASE_MIGRATIONS_TABLE
- Name of the table to run migrations to

Usage

Creating a new migration

Running

DATABASE_URL="x" geni new hello_world

Will create 2 files(with path written in console). 1 file ending with .up.sql and 1 ending with .down.sql. .up.sql is for creating migrations and .down.sql is for rollbacking migrations. This means that .down.sql should include information that rollback the changed you added to .up.sql.

Example:

If I want to create table named Persons should I add this to .up.sql

CREATE TABLE Persons (
    PersonID int
)

And the rollback migration should then be

DROP TABLE Persons;

in the generated .down.sql file as this code would revert the creation of the table Persons

Transactions

Geni defaults to always run in transactions but if you want to prevent usage of transactions, add transaction: no as the first line of the migration file. Then Geni won't use transactions for the specific migration. This works for both up and down

Example:

-- transaction:no
CREATE TABLE table_2 (
  id INT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Running migration

Running migration can be done using

geni up

You can also pass the database URL as a flag instead of an environment variable:

geni --database-url "postgres://postgres@127.0.0.1:5432/app?sslmode=disable" up

Rollback migrations

Rollbacking last added migrations can be done using

geni down

and If you want to rollback more then 1 can you add -a to the cli to specify how many

geni down -a 3

Running from CLI

DATABASE_URL="postgres://postgres@127.0.0.1:5432/app?sslmode=disable" geni up

Running through an SSH tunnel

SSH tunneling is supported for local CLI usage with Postgres, MySQL, and MariaDB URLs.

If you do not specify --ssh-local-port, geni will try high local ports in 60000..=65000 until OpenSSH successfully binds one. Use --ssh-local-port or DATABASE_SSH_LOCAL_PORT if you want a deterministic local port. If that explicit port is already in use, tunnel startup fails immediately.

geni \
  --database-url "postgres://app:secret@db.internal:5432/app?sslmode=disable" \
  --ssh-host "prod-bastion" \
  up

Because geni shells out to the system ssh client, you can also rely on ~/.ssh/config for connection details and agent selection. Example with an OpenSSH alias and the 1Password SSH agent:

Host vps
  HostName your-server.example.com
  User root
  Port 22
  IdentityAgent "~/.1password/agent.sock"

Then run:

geni \
  --database-url "postgres://app:secret@db.internal:5432/app?sslmode=disable" \
  --ssh-host "vps" \
  up

If the database is only reachable as localhost from the SSH server, keep the SSH host separate and override the remote target:

geni \
  --database-url "postgres://app:secret@localhost:5432/app?sslmode=disable" \
  --ssh-host "prod-bastion" \
  --ssh-remote-host "127.0.0.1" \
  up

V1 tunnel support is local CLI only. The published Docker image and GitHub Action still expect direct database connectivity.

Github Workflow

- uses: emilpriver/geni@main
  with:
    migrations_folder: "./migrations"
    wait_timeout: "30"
    migrations_table: "schema_migrations"
    database_url: "https://localhost:3000"
    database_token: "X"

Arguments

migrations_folder(optional): The path to where your migrations exist.
- Default: ./migrations
wait_timeout(optional): The time to wait before dropping the attempt to connect to the database
- Default: 30
migrations_table(optional): The name of the migration

Geni

Install / Use

README

Geni

Geni in the news

Features

TODO

Installation

Github

Homebrew

Scoop

PKGX

Nix flake

Cargo

Docker

Commands

Environment variables

Usage

Creating a new migration

Transactions

Running migration

Rollback migrations

Running from CLI

Running through an SSH tunnel

Github Workflow

Arguments