sqlacodegen

You can select different generators using the --generator flag. The available built-in generators are:

• tables: Only generates Table objects (useful if you don't want to use the ORM).
• declarative (default): Generates classes inheriting from declarative_base().
• dataclasses: Generates dataclass-based models (requires SQLAlchemy 1.4+).
• sqlmodels: Generates model classes for SQLModel.

To use sqlacodegen, provide a database URL (compatible with SQLAlchemy's create_engine()). The tool reads the database structure and generates SQLAlchemy model code.

Basic usage examples:

# Basic usage with a local PostgreSQL database
sqlacodegen postgresql:///some_local_db

# Using a specific generator (e.g., tables) with MySQL
sqlacodegen --generator tables mysql+pymysql://user:password@localhost/dbname

# Generating dataclass-based models
sqlacodegen --generator dataclasses sqlite:///database.db

# Passing engine arguments (parsed via ast.literal_eval)
sqlacodegen oracle+oracledb://user:pass@127.0.0.1:1521/XE --engine-arg thick_mode=True

# Passing complex engine arguments like connect_args
sqlacodegen oracle+oracledb://user:pass@127.0.0.1:1521/XE --engine-arg thick_mode=True --engine-arg connect_args='{"user": "user", "dsn": "..."}'

To implement custom generation logic, subclass one of the existing code generator classes and override the necessary methods.

To make your custom generator available via the CLI:

1. Implement your class.
2. Register it as an entry point in the sqlacodegen.generators namespace.
3. Install your package (e.g., via pip install .).
4. Invoke it using the --generator flag:

sqlacodegen --generator <your_entry_point_name> <database_url>

Install the base package using pip:

pip install sqlacodegen

Depending on your database requirements, you may need to install specific extras for PostgreSQL support:

• CITEXT extension: pip install sqlacodegen[citext]
• PostgreSQL Geometry/Raster: pip install sqlacodegen[geoalchemy2]
• PGVECTOR extension: pip install sqlacodegen[pgvector]

The generator uses several strategies to name relationship attributes in the resulting Python classes:

1. Default: Uses the target table name or a derived name.
2. ID Stripping: If noidsuffix is not set, it attempts to strip _id from foreign key column names (e.g., user_id becomes user).
3. FK Qualification: If nofknames is not set, it may use foreign key column names to qualify relationships, especially for multi-column foreign keys.
4. M2M Qualification: For many-to-many relationships with multiple junction tables, it uses the junction table name as a qualifier.
5. Inflection: If use_inflect is enabled, it will singularize ONE_TO_ONE and MANY_TO_ONE relationships and pluralize ONE_TO_MANY and MANY_TO_MANY relationships.

By default, sqlacodegen can detect CHECK constraints on String/VARCHAR columns and convert them into Python enum.Enum classes.

For example, a column with a constraint like CHECK (status IN ('active', 'inactive')) will be transformed into:

class Status(str, enum.Enum):
    ACTIVE = 'active'
    INACTIVE = 'inactive'

# ... in the model
status = mapped_column(Enum(Status, ...))

To disable this behavior and keep the column as a standard string, use the nosyntheticenums option.

The generator attempts to adapt database-specific types to standard SQLAlchemy types unless keep_dialect_types is enabled.

Special handling is included for:

• Enums: Handles native database ENUM types (like PostgreSQL ENUM) and ensures they are correctly represented.
• Arrays: Handles ARRAY columns, including those containing Enums (e.g., ARRAY(ENUM) in PostgreSQL), while preserving dialect-specific ARRAY subclasses to maintain functionality like .contains().
• PostgreSQL Sequences: Detects sequences from server_default in PostgreSQL and adds explicit sqlalchemy.Sequence objects.
• Domains: Includes specific logic to adapt PostgreSQL DOMAIN types.

The sqlacodegen command-line interface generates SQLAlchemy model code from an existing database using a specified SQLAlchemy URL. By default, it outputs the generated code to stdout, but you can specify an output file using --outfile.

Basic usage pattern:

sqlcodegen <DATABASE_URL> [OPTIONS]