SQLift is a simple CLI migration tool for SQL databases. It is designed to be easy to use and now supports SQLite and PostgreSQL databases.

Installation

pip install SQLift

Usage

First you need to create a 'migrations' directory where you will store your migration files. Migrations are simple SQL files that contain the SQL commands to be executed for up and down migrations like below. This file needs two sections, one for the up migration and one for the down migration, separated by --DOWN.

Example migration file

migrations/001_create_table.sql 
--UP
CREATE TABLE IF NOT EXISTS test (
    id INTEGER PRIMARY KEY
);

--DOWN
DROP TABLE IF EXISTS test;

You can find more examples of migration files in the migrations directory of this repository.

After you have created your migration files, you can run the following command to apply the migrations to your database. (It is recommended to start the migration files with a number to keep them in order)

sqlift up

This will apply all the migrations that have not been applied yet. If you want migrate to a specific version, you can pass the version as an argument.

sqlift up 001_create_table

To rollback all migrations, you can run the following command.

sqlift down

To rollback to a specific version, you can pass the version

sqlift down 001_create_table

Configuration

SQLift uses environment variables to configure the database connection. You can set the following environment variables to configure the database connection.

DB_URL=postgresql://user:password@localhost:5432/database # PostgreSQL
DB_URL=sqlite:///path/to/database.db                      # SQLite

If you don't set the DB_URL environment variable, SQLift will default to using SQLite with a database file named db.sqlite in the current directory.

Contributing

Contributions are welcome! Feel free to open an issue or submit a pull request. I would like to keep the library to be safe as possible, so i would appreciate if you cover any new feature with tests to maintain 100% coverage.

Install in a development environment

1. First, clone the repository:

   git clone git@github.com:SpaceShaman/SQLift.git

2. Install poetry if you don't have, here you can find the instructions

3. Create a virtual environment and install the dependencies:

   cd SQLift
   poetry install --no-root

4. Activate the virtual environment:

   poetry shell

Run tests

First, you need to run databases and you can do it simply with docker-compose:

docker-compose up -d

Then you can run the tests with pytest:

pytest

You can also run the tests with coverage:

pytest --cov=sqlift

Database clients

If you want to add support for a new database, you need to create a new client class that is consistent with the Client protocol class from sqlift.clients and implement the execute method. Look at the SQLiteClient and PostgreSQLClient classes for reference.