Bamboo Database

A CLI tool for managing database structure (schema/migrations) and seed data across multiple database backends using raw SQL.

Features

• Multi-Database Support: PostgreSQL, MySQL, and SQLite
• Raw SQL Migrations: Full control with database-specific syntax
• Combined Migrations: Schema changes and seed data in single atomic transactions
• Per-Database Configuration: Separate migration folders for each database profile
• Schema Generation: Auto-generate organized schema files from migrations
• AI-Friendly Rules: Generate migration guidelines for AI assistants

Installation

# Basic installation (SQLite only)
pip install bamboo-database

# With PostgreSQL support
pip install bamboo-database[postgresql]

# With MySQL support
pip install bamboo-database[mysql]

# With all database backends
pip install bamboo-database[all]

# Development installation
pip install bamboo-database[dev]

Quick Start

1. Create Configuration

Create bamboo_database.toml in your project root:

[databases.default]
type = "postgresql"
host = "localhost"
port = 5432
database = "my_database"
user = "postgres"
password = "secret"
migrations_path = "./migrations/default"

[databases.analytics]
type = "mysql"
host = "localhost"
port = 3306
database = "analytics_db"
user = "root"
password = "secret"
migrations_path = "./migrations/analytics"

[databases.cache]
type = "sqlite"
path = "./cache.db"
migrations_path = "./migrations/cache"

2. Create Migration Files

Create migration files in your migrations folder:

-- migrations/default/0001_migrate_create_users.sql
BEGIN;

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    name VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO users (id, email, name) VALUES
    (1, 'admin@example.com', 'Admin User')
ON CONFLICT (id) DO NOTHING;

COMMIT;

3. Run Migrations

# Migrate all databases
bamboodb migrate

# Migrate specific database
bamboodb migrate --database default

# Check migration status
bamboodb status

# List configured databases
bamboodb list-databases

CLI Commands

Common Options

• --database NAME - Target specific database (can be used multiple times)
• --help - Show help for any command

Migration File Naming

Files are named with type prefixes:

• {version}_migrate_{description}.sql - Schema changes (CREATE, ALTER, DROP)
• {version}_seed_{description}.sql - Initial/reference data
• {version}_index_{description}.sql - Index creation

Version format: 4-digit zero-padded (0001, 0002, 0003...)

Examples:

• 0001_migrate_create_users.sql
• 0002_seed_insert_admin_user.sql
• 0003_index_users_email_idx.sql

Idempotent Seed Data

Use database-specific syntax for idempotent inserts:

PostgreSQL:

INSERT INTO users (id, email) VALUES (1, 'admin@example.com')
ON CONFLICT (id) DO NOTHING;

MySQL:

INSERT IGNORE INTO users (id, email) VALUES (1, 'admin@example.com');

SQLite:

INSERT OR IGNORE INTO users (id, email) VALUES (1, 'admin@example.com');

Python API

from bamboo_database import load_config, create_adapter

# Load configuration
config = load_config()

# Get a database profile
profile = config.get_database("default")

# Create an adapter
with create_adapter(profile) as adapter:
    adapter.execute("SELECT * FROM users")
    results = adapter.fetch_all("SELECT * FROM users")

Requirements

• Python 3.10+
• Database drivers (optional):
  • PostgreSQL: psycopg[binary]
  • MySQL: mysql-connector-python
  • SQLite: Built-in

License

MIT License - see LICENSE for details.