sql-generation.md

Generating SQL schema definitions

It is often useful to store data in a SQL-based database server. dataframely aims to make this easy by providing a simple mechanism for translating your dataframely schemas to SQL table definitions.

There are many different flavors of SQL syntax. To avoid reinventing the wheel, we use sqlalchemy as an abstraction layer between python and SQL.

Individual tables

The main functionality dataframely offers is that it converts your dy.Schema to a collection of sqlalchemy.Column:

import dataframely as dy
import sqlalchemy as sa


class MySchema(dy.Schema):
    x = dy.Int64(primary_key=True)
    y = dy.String(nullable=False)


engine = sa.create_engine(...)
columns: list[sa.Column] = MySchema.to_sqlalchemy_columns(engine.dialect)

You can then do with the columns what you please. Most likely, you want to create a table with them:

my_table = sa.Table("myTable", sa.MetaData(), *columns)
my_table.create(engine)

You can also inspect the SQL code that sqlalchemy would execute:

from sqlalchemy.schema import CreateTable

print(CreateTable(my_table).compile())

In the example case, this renders to:

CREATE TABLE "myTable"
(
    x BIGINT  NOT NULL,
    y VARCHAR NOT NULL,
    PRIMARY KEY (x)
)

Uploading data can then be handled by {meth}polars.DataFrame.write_database:

df: dy.DataFrame[MySchema]

df.write_database(
    connection=engine,
    table_name=my_table.name,
    if_table_exists="append"
)

**Why do you need to pass in the SQL dialect?** Even though `sqlalchemy` handles most dialect dependencies, we sometimes still need to intervene. For example, when using Microsoft SQL Server, `sqlalchemy` will render the `sqlalchemy.Date` type into a raw SQL `DATETIME`, while we think that `DATE` would be more appropriate.

**Implementation:** The choice of `sqlalchemy` type is implemented in {meth}`~dataframely.Column.sqlalchemy_dtype`, which is overwritten by each of the subtypes of {class}`~dataframely.Column`. For example, the implementation for {class}`~dataframely.Date` is {meth}`~dataframely.Date.sqlalchemy_dtype`.

**Constraints:** The nullability and primary key constraints you define in `dataframely` are translated to SQL. Custom filters and rules are not.

**Length of string columns:** For string columns, `dataframely` will attempt to pass information about the maximal length into the SQL definition. This is trivial if `max_length` is set. Otherwise, if a `regex` is provided,
the maximal length of the string is inferred from the regular expression if possible. Note that having inferable
maximal lengths can be particularly important for primary key columns. Some database systems, such as Microsoft SQL Server, do not allow `VARCHAR(max)` columns (unbounded strings) to be used as primary keys.

Native SQL enums (optional)

By default, {class}~dataframely.Enum maps to fixed-length CHAR or VARCHAR columns so stored values remain plain strings. For PostgreSQL setups that use database-level ENUM types (for example with Alembic autogenerate), set sqlalchemy_use_enum=True:

from enum import Enum

import dataframely as dy


class Status(str, Enum):
    PENDING = "pending"
    APPROVED = "approved"


class Staged(dy.Schema):
    status = dy.Enum(Status, sqlalchemy_use_enum=True)

When categories is a Python enum.Enum subclass, SQLAlchemy uses the enum class name (lowercased) as the database enum type name. For string category lists, the SQL column name is used by default; override it with sqlalchemy_enum_name if needed. On dialects without native enums (such as Microsoft SQL Server), SQLAlchemy falls back to VARCHAR with a check constraint.

Collections of multiple tables

If you have an entire dy.Collection, it's also easy to generate one table for each member table of the collection. sqlalchemy.MetaData is a commonly used container in such scenarios:

MyCollection: dy.Collection
meta = sa.MetaData()
for name, dy_schema in MyCollection.member_schemas().items():
    sa.Table(
        name,
        meta,
        *dy_schema.to_sqlalchemy_columns(dialect=engine.dialect),
    )
meta.create_all()