ul-db-utils 6.0.0

UL db utils Python package

Generic library db-utils

Provides common database-related functionality that can be used across different services.

Contains all database-related packages as dependencies. If you need to use some package that is not available in your service, you should add it here.

Common functionality

This section describes some classes or methods that are awailable for use in all services that use db-utils.

CustomQuery

As a default this class inherit from flask_sqlalchemy Query and adds additional filters.

1. Filtering by only non-deleted (marked as is_alive=True) records.
2. Joining with/without deleted (marked as is_alive=False) records.

If you want to add some additional by-default behavior to all services than you have to add it here.

transaction_commit

This context manager allows us to perform a database transaction at ORM level. You should use it like this:

with transaction_commit():
  query.perform(something)

Abstract models

This section describes all available abstract models from which we can inherit in our services.

BaseModel

class BaseModel(DbModel, SerializerMixin):

    __abstract__ = True

    query_class = CustomQuery

    id = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    date_created = mapped_column(db.DateTime(), default=datetime.utcnow(), nullable=False)
    date_modified = mapped_column(db.DateTime(), default=datetime.utcnow(), nullable=False)
    is_alive = mapped_column(db.Boolean(), default=True, nullable=False)

Provides UUID, record creation/modification datetime and is_alive field used for soft-deletion.

BaseUndeletableModel

class BaseUndeletableModel(DbModel, SerializerMixin):

    __abstract__ = True

    query_class = Query

    id = mapped_column(PG_UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    date_created = mapped_column(db.DateTime(), default=datetime.utcnow(), nullable=False)
    date_modified = mapped_column(db.DateTime(), default=datetime.utcnow(), nullable=False)

The same thing as BaseModel but models that will inherit from this model won't be able to soft-delete records.

BaseMaterializedPGViewModel

class BaseMaterializedPGView(DbModel, SerializerMixin):
    id = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    last_refresh_date = mapped_column(db.DateTime(), nullable=False)

    sql = ''
    refresh_by_tables: List[str] = []

    query_class = Query

    __table_args__ = {'info': {'skip_autogenerate': True}}

    _index_format = '{table}_{field}_index'
    _pkey_format = '{table}_pkey'

    __abstract__ = True

Provides a way to create materialized views in PostgreSQL, add indexes, triggers, etc.

BaseImmutableModel

class BaseImmutableModel(DbModel, SerializerMixin):
    __abstract__ = True

    query_class = Query

    id = mapped_column(PG_UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    date_created = mapped_column(db.DateTime(), default=datetime.utcnow(), nullable=False)
    user_created_id = mapped_column(PG_UUID(as_uuid=True), nullable=False)

Models that are going to inherit from this one won't have update record functionality.

ApiUser

class ApiUser(BaseModel):
    __tablename__ = 'api_user'

    date_expiration = mapped_column(db.DateTime(), nullable=False)
    name = mapped_column(db.String(255), unique=True, nullable=False)
    note = mapped_column(db.Text(), nullable=False)
    permissions = mapped_column(ARRAY(db.Integer()), nullable=False)

Every APIUser Model record has an array of permissions.

Exceptions

Exception	Desription
ComparisonToNullError	Raised when a client attempts to use a filter object that compares a resource's attribute to NULL using == operator instead of using is_null.
DBFiltersError	Raised when a client attempts to filter with invalid query params.
DBSortError	Raised when a client attempts to sort with invalid query params.
MultipleObjectsReturnedError	When only one object expected to be returned, but DB returned multiple objects.
UnknownFieldError	When user tries to reference the non-existent model field.
DeletionNotAllowedError	Raised when db obj deletion not allowed.
UpdateColumnNotAllowedError	Raised when db table column update not allowed.
UpdateNotAllowedError	Raised when db table update not allowed.
DbError	Generic DB error.

Adding new database-related package

First, try to understand why do you need this library and what exactly can you do with it. Look at the list of already existing libraries and think if they can fulfill your needs.

Check this library for deprecation, does it have enough maintenance, library dependencies. If all above satisfies you, perform next steps:

1. Add the package name and version to Pipfile under [packages] section. Example: alembic = "==1.8.1".
2. Run pipenv install.
3. Add the package name and version to setup.py to install-requires section.
4. Commit changes. git commit -m "Add dependency *library-name*".
5. Run version patch: pipenv run version_patch.
6. Push changes directly to dev git push origin dev --tags or raise MR for your changes to be reviewed.