Database Tools

SQLModel

SQLModel is our all-in-one SQL toolkit, ORM library, and data validation library. It combines Pydantic and SQLAlchemy into a single package.

Defining a Construct

Use base class defined in concrete.orm.models to define a construct.

from concrete.orm.models import Base

class my_table(Base, table=True):
    __tablename__ = "my_table" # Unnecessary; defaults to class_name.lower()
    id: int = Field(primary_key=True) # Unnecessary; defaults to autoincrementing id

    # Columns
    my_column: str = Field(max_length=32)

DB Operations

Use concrete.db.orm.Session to get a session context manager. Use this session to perform DB operations. Best practice is to use one session per one transaction. By default, sessions will not flush or commit.

from concrete.db.orm import Session

# The following solutions achieve the same thing, but with different approaches
# ORM Centric solution

def delete_my_table_orm():
    with Session() as session:
        deleted_count = session.query(my_table).where(my_column == "my_value").delete()
        session.commit()
        return deleted_count

def delete_my_table_core():
    with Session() as session:
        stmt = delete(my_table).where(my_column == "my_value")
        result = session.execute(stmt)
        deleted_count = result.rowcount
        session.commit()
        return deleted_count

Connection to DB

SQLModel requires a database URL. It's constructed using sqlalchemy.URL using environment variables. By default, the constructed URL will be a SQLite database. For a dockerized Postgres database, place the following into your .env file.

DB_DRIVER=postgresql+psycopg
DB_USERNAME=local_user
DB_PASSWORD=local_password
DB_PORT=5432
DB_HOST=localhost 
DB_DATABASE=local_db

Start the postgres server using

make run-postgres

When developing locally outside of docker, DB_HOST should be set to localhost. When developing inside docker, DB_HOST should be set to host.docker.internal.

Alembic

We use Alembic to manage database migrations and schema creation for Postgres, our choice of database.

Migration scripts are tracked with git, and can be used to recreate database schemas at a particular point in time. This can be especially useful for testing staging/prod migrations, because we can recreate their schemas locally.

Usage

SQLModel models are used to define migration scripts. To set up your system for autogenerated migrations:

1. Import all defined models in migrations/env.py, e.g. from concrete.db.orm.models import *.

2. Configure target metadata in migrations/env.py, e.g. target_metadata = SQLModel.metadata.

3. Import sqlmodel in script.py.mako (this is a template file for generating scripts), e.g. from sqlmodel import SQLModel.

4. Add database URL to alembic.ini file, e.g. sqlalchemy.url = postgresql+psycopg://local_user:local_password@localhost:5432/local_db

To create a new migration script, run

alembic revision --autogenerate -m 'migration name'

This will generate a migration script taking the existing database schema to whatever schema is defined by the SQLModel models.

To apply the migration script, run alembic upgrade head. This will alter the database schema. You can also use relative migration numbers, e.g. alembic upgrade +1, or alembic downgrade -2. Similarly, you can use alembic downgrade partial_migration_number.

By default, make run-postgres applies all migrations to the database, initializing it with the latest schema.

Manual Script Adjustments

It's sometimes necessary to manually adjust the autogenerated Alembic scripts. Here are some common patterns and their solutions:

• Adding a unique, non-nullable column name to a table user with existing rows:

# add column as nullable first to avoid supplying nonunique default
op.add_column('user', sa.Column('name', sqlmodel.sql.sqltypes.AutoString(length=64), nullable=True))

# define view on table with relevant columns, e.g. primary key and desired column
old_user = sa.Table(
    'user',
    sa.MetaData(),
    sa.Column('id', sa.Uuid()),
    sa.Column('name', sqlmodel.sql.sqltypes.AutoString(length=64)),
)
connection = op.get_bind()

# select relevant (aka all) existing rows for column insertion
results = connection.execute(
    sa.select(
        old_tools.c.id,
    )
).fetchall()

# update every existing record with a unique value in desired column
for i, (id,) in enumerate(results):
    new_name = id
    connection.execute(old_tools.update().where(old_tools.c.id == id).values(name=new_name))

# alter column to become non-nullable as was originally desired
op.alter_column('tool', 'name', nullable=False)

• Renaming an existing column a to b in table alphabet:

op.alter_column('operator', 'a', new_column_name='b')

• Concatenating the first_name and last_name colummns of table user into a full_name column:

connection = op.get_bind()
connection.execute(
    """
    UPDATE user
    SET full_name = CONCAT(first_name, ' ', last_name)
    """
)

# (Optional) If you'd like to remove the first_name and last_name columns
# op.drop_column('user', 'first_name')
# op.drop_column('user', 'last_name')

For a deeper dive, please examine the alembic operations reference.

---

Last Updated: 2024-12-04 09:21:32 UTC

Lines Changed: +5, -2