Why sqlmeta?

“Why not just use SQLAlchemy directly?”

This is a common and fair question. While SQLAlchemy is an excellent tool for ORM and database operations, sqlmeta solves fundamentally different problems.

Schema Comparison & Drift Detection

SQLAlchemy represents schemas for your application - it’s designed to help your application interact with a database.

sqlmeta compares schemas from different sources - it’s designed to detect and analyze differences:

Compare SQL scripts against live databases
Detect drift between dev, staging, and production environments
Validate that migrations were applied correctly
Compare schemas across different database vendors
Generate reports on schema differences with severity levels

# This is what sqlmeta excels at - SQLAlchemy doesn't provide this
from sqlmeta.comparison.comparator import ObjectComparator

comparator = ObjectComparator(dialect="postgresql")
diff = comparator.compare_tables(source_table, target_table)

if diff.has_diffs:
    print(f"Schema drift detected! Severity: {diff.severity}")
    print(f"Missing columns: {diff.missing_columns}")
    print(f"Modified columns: {len(diff.modified_columns)}")

Real-world scenario: You deploy a service that depends on a specific database schema. How do you verify that production has the right schema? sqlmeta can compare your expected schema against the live database and tell you exactly what’s different.

Lightweight & Serializable

SQLAlchemy metadata is tightly coupled to engines, connections, and sessions. It’s designed for runtime database operations.

sqlmeta is pure data - just Python dataclasses and enums:

Zero dependencies for core functionality (no database drivers required)
JSON serializable - store schemas in files, databases, or APIs
Language agnostic - share schema definitions between services in different languages
Version control friendly - track schema changes in git as readable JSON/YAML

import json
from sqlmeta import Table, SqlColumn

# Define schema
table = Table("users", columns=[
    SqlColumn("id", "INTEGER", is_primary_key=True),
    SqlColumn("email", "VARCHAR(255)"),
])

# Serialize to JSON
schema_json = json.dumps(table.to_dict(), indent=2)

# Store anywhere - file, S3, database, Redis
with open('schema.json', 'w') as f:
    f.write(schema_json)

# Recreate from JSON anywhere, anytime
with open('schema.json', 'r') as f:
    schema_dict = json.load(f)
table_copy = Table.from_dict(schema_dict)

Real-world scenario: You have microservices in Python, Go, and Node.js that all need to know about the database schema. Store the schema as JSON and each service can read it, no Python required.

Broader Database Object Support

SQLAlchemy focuses primarily on tables, columns, and constraints - the objects needed for ORM operations.

sqlmeta represents the full database catalog:

Stored procedures and functions with parameters and return types
Packages (Oracle)
Triggers with timing, events, and full metadata
Database links (Oracle) and Linked servers (SQL Server)
Foreign data wrappers and foreign servers (PostgreSQL)
Extensions (PostgreSQL)
Events (MySQL scheduled tasks)
Synonyms (Oracle, SQL Server)
Partitioning strategies with full metadata
User-defined types
And much more…

from sqlmeta.objects.procedure import Procedure
from sqlmeta.objects.trigger import Trigger
from sqlmeta.objects.package import Package

# Oracle package
pkg = Package(
    name="user_management",
    schema="public",
    spec="PROCEDURE create_user(p_name VARCHAR2, p_email VARCHAR2);",
    body="...",
    dialect="oracle"
)

# MySQL event
from sqlmeta.objects.event import Event
cleanup = Event(
    name="daily_cleanup",
    schedule="EVERY 1 DAY",
    body="DELETE FROM logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY)",
    dialect="mysql"
)

Real-world scenario: You’re migrating from Oracle to PostgreSQL. You need to catalog all procedures, packages, and triggers to plan the migration. sqlmeta can represent all these objects in a uniform way.

Multi-Dialect Schema Translation

SQLAlchemy supports multiple dialects for runtime operations, but schema definitions are still tied to specific database features.

sqlmeta is designed for schema translation - define once, generate for any dialect:

from sqlmeta import Table, SqlColumn

# Define a dialect-agnostic schema
users = Table("users", columns=[
    SqlColumn("id", "INTEGER", is_primary_key=True),
    SqlColumn("email", "VARCHAR(255)", is_nullable=False),
    SqlColumn("created_at", "TIMESTAMP"),
])

# Generate DDL for different databases
pg_ddl = users.to_sql(dialect="postgresql")
# CREATE TABLE users (
#     id SERIAL PRIMARY KEY,
#     email VARCHAR(255) NOT NULL,
#     created_at TIMESTAMP
# );

mysql_ddl = users.to_sql(dialect="mysql")
# CREATE TABLE users (
#     id INT AUTO_INCREMENT PRIMARY KEY,
#     email VARCHAR(255) NOT NULL,
#     created_at TIMESTAMP
# );

Real-world scenario: You’re building a product that customers can deploy on their choice of database (PostgreSQL, MySQL, or SQL Server). sqlmeta lets you maintain one schema definition and generate correct DDL for each database.

Integration Hub

sqlmeta acts as a universal adapter between different tools in the Python ecosystem:

Parse SQL scripts → convert to SQLAlchemy → generate Pydantic models
Extract schema from database A → compare with schema B → generate Alembic migrations
Read from SQLAlchemy → store as JSON → recreate in another tool
Load schema from JSON → generate FastAPI models → create database tables

from sqlmeta import Table
from sqlmeta.adapters.sqlalchemy import to_sqlalchemy, from_sqlalchemy
from sqlmeta.adapters.pydantic import to_pydantic
from sqlmeta.adapters.alembic import generate_operations

# Start with any source
table = Table(...)  # or from SQL parser, or from database introspection

# Convert to SQLAlchemy for database operations
sa_table = to_sqlalchemy(table, metadata)

# Generate Pydantic model for API
UserModel = to_pydantic(table)

# Compare with another schema and generate migration
operations = generate_operations(old_table, table)

Real-world scenario: You’re building a schema management tool that needs to work with multiple frameworks. sqlmeta provides a common format that can convert to/from each one.

When to Use What?

Use Case	Tool
ORM for your application	SQLAlchemy
Schema comparison & drift detection	sqlmeta
Database queries and transactions	SQLAlchemy
Cross-database schema translation	sqlmeta
Schema versioning and serialization	sqlmeta
Representing procedures, triggers, packages	sqlmeta
Integration hub between tools	sqlmeta
Runtime database operations	SQLAlchemy

Use Them Together!

sqlmeta and SQLAlchemy are complementary, not competing tools:

from sqlmeta import Table, SqlColumn
from sqlmeta.adapters.sqlalchemy import to_sqlalchemy
from sqlalchemy import create_engine, MetaData
from sqlalchemy.orm import sessionmaker

# 1. Define schema in sqlmeta (serializable, versionable)
users = Table("users", columns=[
    SqlColumn("id", "INTEGER", is_primary_key=True),
    SqlColumn("email", "VARCHAR(255)", is_nullable=False),
])

# 2. Store in version control as JSON
with open('schemas/users.json', 'w') as f:
    json.dump(users.to_dict(), f)

# 3. Convert to SQLAlchemy for runtime operations
metadata = MetaData()
sa_table = to_sqlalchemy(users, metadata)

# 4. Use SQLAlchemy for database operations
engine = create_engine("postgresql://localhost/mydb")
Session = sessionmaker(bind=engine)
session = Session()

# Now you have:
# - Schema in git (sqlmeta JSON)
# - Runtime database access (SQLAlchemy)
# - Ability to compare schemas (sqlmeta)
# - Ability to generate migrations (sqlmeta + Alembic)

Comparison with Other Tools

sqlmeta vs Alembic

Alembic is a migration tool - it helps you change schemas over time.

sqlmeta helps you understand and compare schemas. You can use sqlmeta to generate Alembic migrations by comparing two schema states.

They work great together: sqlmeta detects what changed, Alembic applies the changes.

sqlmeta vs Django ORM

Django ORM is tightly integrated with the Django framework and focuses on Python-defined models.

sqlmeta is framework-agnostic and focuses on SQL-first schemas. It can represent schemas that exist independently of any application code.

sqlmeta vs schema-crawler / SchemaSpy

These are schema documentation tools - they extract and visualize existing schemas.

sqlmeta is a schema manipulation library - it helps you compare, transform, and migrate schemas programmatically.

Real-World Use Cases

CI/CD Schema Validation

In your deployment pipeline, compare the schema your application expects against the target database to catch issues before deployment.
Multi-Tenant Schema Management

Maintain a canonical schema definition and verify all tenant databases match it. Detect and fix drift automatically.
Database Migration Projects

Moving from Oracle to PostgreSQL? Catalog all database objects in sqlmeta format, then generate PostgreSQL-compatible DDL.
Schema as Code

Store your database schema in git as JSON/YAML. Generate SQLAlchemy models, Pydantic models, and DDL from a single source of truth.
Cross-Service Schema Sharing

Microservices in different languages need to know the database schema. Export once from sqlmeta, consume in Python, Go, Node.js, etc.
Compliance and Auditing

Track schema changes over time by storing sqlmeta snapshots. Generate reports showing what changed and when.

Conclusion

Use SQLAlchemy when: You need to interact with a database from your Python application.

Use sqlmeta when: You need to understand, compare, version, or translate database schemas.

Use both when: You want the best of both worlds - schema management with sqlmeta, database operations with SQLAlchemy.

sqlmeta fills a gap in the Python ecosystem by providing a lightweight, serializable, framework-agnostic way to work with SQL metadata. It’s the missing piece between “define a schema” and “use a database.”