All in one CLI tool for the database | SQLite, libSQL, Turso, PostgreSQL, MySQL, MariaDB

Install/upgrade latest version

curl -sSfL https://raw.githubusercontent.com/gigagrug/schema/main/install.sh | sh -s

Install specific version

curl -sSfL https://raw.githubusercontent.com/gigagrug/schema/main/install.sh | sh -s 0.1.0

Init project (default: db=sqlite url=./schema/dev.db)

schema i

Init project using another db and url

schema i -db "postgres" -url "postgresql://postgres:postgres@localhost:5432/postgres"

Init project with different root directory

schema i -rdir "schema2"

Nessesary if using existing database

schema pull

Create a SQL file

schema create "initschema"

Go to ./schema/migrations/1_initschema.sql (This SQL is for sqlite)

CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL UNIQUE,
    password TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS posts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- schema rollback
DROP TABLE users;
DROP TABLE posts;

Migrates all the sql files not migrated

schema migrate

Migrates specific sql file

schema migrate "1_initschema"

Rollbacks last migrated file

schema rollback

schema rollback "1_initschema"

Removes if file not migrated

schema remove "1_initschema"

Doesn't save in _schema_migrations table if not in migrations dir so they can be reused

schema create "insertdata" -dir "inserts"

Insert based on the SQL schema above.

WITH RECURSIVE generate_users AS (
  SELECT ABS(RANDOM() % 10000) AS random_number, 1 AS row_number
  UNION ALL
  SELECT ABS(RANDOM() % 10000), row_number + 1
  FROM generate_users
  WHERE row_number < 5
)
INSERT INTO users (username, email, password)
SELECT 
  'user_' || random_number, 
  'user_' || random_number || '@example.com', 
  'password'
FROM generate_users;

WITH RECURSIVE user_list AS (
  SELECT id, ROW_NUMBER() OVER () AS rn
  FROM users
  ORDER BY RANDOM()
  LIMIT 5
),
post_insert AS (
  SELECT
    'Post #' || ABS(RANDOM() % 10000) AS title,
    'This is a post about random topic #' || ABS(RANDOM() % 10000) AS content,
    id AS user_id
  FROM user_list
)
INSERT INTO posts (user_id, title, content)
SELECT user_id, title, content FROM post_insert;

schema sql "0_insertdata.sql" -dir "inserts"

schema sql "SELECT * FROM users;"

schema studio

schema lsp

If you don't want to use .env and schema.db Example:

schema studio -db "sqlite" -url "./schema/dev.db"

version, v: Shows current and latest version
init, i: Initializes project
pull: Pulls database schema 
migrate: Migrates pending migrations 
rollback: Rollbacks last migration
studio: Launch SQL TUI Studio
lsp: Connect to your editor
rollback "[filename]" Rollback a specific migration
migrate "[filename under migrations/]" Run a specific migration
sql "[filename or sql query]" Run SQL directly or from a file
create "[filename]": Create a new migration file 
remove "[filename]", rm: Remove an unmigrated file from disk and db 
config: Edit config files for db type and db url

db="[db type]" (default sqlite)
url="[db url]" (default ./schema/dev.db)
dir="[dir under rdir]" (default migration)
rdir="[root directory]" (default schema)