Backend Migration Example

Migrating data between SQLite and PostgreSQL with zero query changes.

Full Source

  1"""
  2Backend Migration Example - sqbooster
  3
  4Migrating data between SQLite and PostgreSQL with zero query changes.
  5"""
  6
  7import sqbooster
  8from sqbooster.schema import Column
  9from sqbooster.types import Integer, Text, Float, Boolean
 10
 11
 12USERS_SCHEMA = [
 13    Column("id", Integer(), primary_key=True, autoincrement=True),
 14    Column("name", Text(), nullable=False),
 15    Column("email", Text(), unique=True, nullable=False),
 16    Column("age", Integer()),
 17    Column("active", Boolean(), default=True),
 18]
 19
 20PRODUCTS_SCHEMA = [
 21    Column("id", Integer(), primary_key=True, autoincrement=True),
 22    Column("name", Text(), nullable=False),
 23    Column("price", Float(), nullable=False),
 24    Column("stock", Integer(), default=0),
 25]
 26
 27
 28def setup_database(db):
 29    db.create_table("users", USERS_SCHEMA)
 30    db.create_table("products", PRODUCTS_SCHEMA)
 31
 32
 33def seed_database(db):
 34    db.insert_many("users", [
 35        {"name": "Alice", "email": "alice@example.com", "age": 30},
 36        {"name": "Bob", "email": "bob@example.com", "age": 25},
 37        {"name": "Charlie", "email": "charlie@example.com", "age": 35},
 38    ])
 39    db.insert_many("products", [
 40        {"name": "Widget", "price": 9.99, "stock": 100},
 41        {"name": "Gadget", "price": 24.99, "stock": 50},
 42    ])
 43
 44
 45def migrate(source, target):
 46    for table_name in source.get_tables():
 47        print(f"  Migrating table: {table_name}")
 48        schema = source.get_schema(table_name)
 49        target.create_table(table_name, schema)
 50
 51        rows = source.query(table_name).all()
 52        if rows:
 53            target.insert_many(table_name, rows)
 54            print(f"    -> {len(rows)} rows copied")
 55
 56
 57def verify(db, label):
 58    print(f"\n--- {label} ---")
 59    for table in db.get_tables():
 60        rows = db.query(table).all()
 61        print(f"  {table}: {len(rows)} rows")
 62        for row in rows:
 63            print(f"    {row}")
 64
 65
 66def main():
 67    print("=== Backend Migration Example ===\n")
 68
 69    print("1. Creating SQLite database...")
 70    sqlite_db = sqbooster.sqlite()
 71    setup_database(sqlite_db)
 72    seed_database(sqlite_db)
 73    verify(sqlite_db, "SQLite (source)")
 74
 75    print("\n2. Creating target database...")
 76    target_db = sqbooster.sqlite()
 77
 78    print("\n3. Migrating data...")
 79    migrate(sqlite_db, target_db)
 80
 81    verify(target_db, "Target (after migration)")
 82
 83    print("\n4. Querying migrated data...")
 84    active_users = target_db.query("users").filter(active=True).order_by("name").all()
 85    print(f"  Active users: {[u['name'] for u in active_users]}")
 86
 87    expensive = target_db.query("products").filter(price__gt=15).all()
 88    print(f"  Products > $15: {[p['name'] for p in expensive]}")
 89
 90    sqlite_db.close()
 91    target_db.close()
 92
 93    print("\n--- PostgreSQL Migration ---")
 94    print("To migrate to PostgreSQL, simply replace:")
 95    print("  target_db = sqbooster.sqlite()")
 96    print("  target_db = sqbooster.postgresql(host='...', name='...', user='...')")
 97    print("Everything else stays exactly the same!")
 98
 99    print("\nDone!")
100
101
102if __name__ == "__main__":
103    main()

What It Demonstrates

  • Identical code works across backends

  • Schema portability between SQL databases

  • Data migration with minimal code changes

  • DatabaseBackend ABC in action