Tables (and columns)

Tables and columns are declared via SQLAlchemy, a proven, robust tool that this package is built on. You can optionally declare tables and columns via SQLModel, a project that combines a lot of the benefits of SQLA with Pydantic, resulting in highly reusable models for both data definition and strong typing throughout your application. In either case, you declare tables and columns through another library, then simply refer to them from DbDeclare via the DatabaseContent class.

We'll go over the basics and examine the DatabaseContent class, then we'll continue to build our example. You can also skip ahead to the example.

The DatabaseContent class

You can import DatabaseContent like so:

from dbdeclare.entities import DatabaseContent

DatabaseContent is a wrapper around a SQLAlchemy base. It is a database-wide entity, which means it must be uniquely named within a database. The __init__ args are a combination of inherited utility and some necessary references: you must refer to which Database this content belongs to, and which schemas (if non-default) it depends on.

Take a look at the class docstrings for more detail, like an explanation of the __init__ args, the various methods defined, what classes it inherits from, and more. DatabaseContent is slightly odd because it does not have 1-to-1 correspondence with a Postgres entity, and many of the internal methods call SQLA table methods in turn. The source code is well worth looking at for this one.

Example

Let's keep building our example. We have our databases, roles, and extra schemas. Let's declare our tables and columns via SQLA then refer to them via the DatabaseContent class:

from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column

from dbdeclare.entities import Database, DatabaseContent, Role, Schema


class ExampleBase(DeclarativeBase):
    pass


class Article(ExampleBase):
    __tablename__ = "article"
    id: Mapped[int] = mapped_column(primary_key=True)


class Keyword(ExampleBase):
    __tablename__ = "keyword"
    id: Mapped[int] = mapped_column(primary_key=True)


class Cluster(ExampleBase):
    __tablename__ = "cluster"
    id: Mapped[int] = mapped_column(primary_key=True)


class BadRequest(ExampleBase):
    __tablename__ = "bad_request"
    __table_args__ = {"schema": "log"}
    id: Mapped[int] = mapped_column(primary_key=True)


class GoodRequest(ExampleBase):
    __tablename__ = "good_request"
    __table_args__ = {"schema": "log"}

# omitted code below

In the code above, we import DatabaseContent from dbdeclare and we import DeclarativeBase from sqlalchemy. We then define a base and a bunch of tables. Please refer to SQLA documentation for more information on how to declare tables and columns. Now let's refer to the base:

# omitted code above


def declare_stage(stage: str) -> None:

    # omitted code between


    # create extra schemas
    log_schema = Schema(name="log", database=db)

    # create db content

# omitted code below

We update the schema definition so that we store a reference to it in log_schema. We then declare our DatabaseContent and have it point to db, ExampleBase, and log_schema so that Postgres will know exactly where to put these tables and what entities need to exist before these tables can exist.

This code now declares the databases, roles, schemas, tables, and columns we need. Apart from actually creating all this in the cluster, all we need to do is grant privileges.