| 1 |
Introduction to Database Modelling and Repository Features |
| 2 |
---------------------------------------------------------- |
| 3 |
In this tutorial, we will cover the integrated repository features in Litestar, starting |
| 4 |
with database modelling using the included SQLAlchemy declarative model helpers. These |
| 5 |
are a series of classes and mixins that incorporate commonly used functions/column types |
| 6 |
to make working with models easier. |
| 7 |
|
| 8 |
.. tip:: The full code for this tutorial can be found below in the :ref:`Full Code <01-repo-full-code>` section. |
| 9 |
|
| 10 |
Modelling |
| 11 |
--------- |
| 12 |
|
| 13 |
We'll begin by modelling the entities and relationships between authors and books. |
| 14 |
We'll start by creating the ``Author`` table, utilizing the |
| 15 |
:class:`UUIDBase <advanced_alchemy.base.UUIDBase>` class. To keep things |
| 16 |
simple, our first model will encompass only three fields: ``id``, ``name``, and ``dob``. |
| 17 |
|
| 18 |
.. literalinclude:: /examples/contrib/sqlalchemy/sqlalchemy_declarative_models.py |
| 19 |
:language: python |
| 20 |
:caption: ``app.py`` |
| 21 |
:lines: 9, 11, 18-21 |
| 22 |
:linenos: |
| 23 |
|
| 24 |
The book entity is not considered a "strong" entity and therefore always requires an |
| 25 |
author to be created. We need to configure our SQLAlchemy classes so that it is aware |
| 26 |
of this relationship. We will extend the ``Author`` model by incorporating a ``Book`` |
| 27 |
relationship. This allows each ``Author`` record to possess multiple ``Book`` records. |
| 28 |
By configuring it this way, SQLAlchemy will automatically include the necessary foreign |
| 29 |
key constraints when using the ``author_id`` field in each ``Book`` record. |
| 30 |
|
| 31 |
.. literalinclude:: /examples/contrib/sqlalchemy/sqlalchemy_declarative_models.py |
| 32 |
:language: python |
| 33 |
:caption: ``app.py`` |
| 34 |
:lines: 9, 11, 18-23, 28-31 |
| 35 |
:linenos: |
| 36 |
|
| 37 |
By using the audit model, we can automatically record the time a record was created and |
| 38 |
last updated. |
| 39 |
|
| 40 |
To implement this, we will define a new ``Book`` model via the |
| 41 |
:class:`UUIDAuditBase <advanced_alchemy.base.UUIDAuditBase>` class. Observe |
| 42 |
that the only modification here is the parent class from which we inherit. This minor |
| 43 |
change endows the `book` table with automatic timestamp columns |
| 44 |
(`created` and `updated`) upon deployment! |
| 45 |
|
| 46 |
.. note:: |
| 47 |
|
| 48 |
If your application requires integer-based primary keys, equivalent base model |
| 49 |
and base audit model implementations can be found at |
| 50 |
:class:`BigIntBase <advanced_alchemy.base.BigIntAuditBase>` and |
| 51 |
:class:`BigIntAuditBase <advanced_alchemy.base.UUIDAuditBase>` |
| 52 |
respectively. |
| 53 |
|
| 54 |
.. important:: |
| 55 |
`Spanner <https://cloud.google.com/spanner>`_ only: |
| 56 |
|
| 57 |
Using monotonically changing primary keys is considered an |
| 58 |
anti-pattern in Spanner and leads to performance problems. Additionally, Spanner |
| 59 |
does not currently include an idiom comparable to the ``Sequence`` object. This |
| 60 |
means the ``BigIntBase`` and ``BigIntAuditBase`` are not currently supported for |
| 61 |
Spanner. |
| 62 |
|
| 63 |
Additional features provided by the built-in base models include: |
| 64 |
|
| 65 |
- Synchronous and Asynchronous repository implementations have been tried and tested |
| 66 |
with various popular database engines. As of now, six database engines are supported: |
| 67 |
Postgres, SQLite, MySQL, DuckDB, Oracle, and Spanner. |
| 68 |
- Automatic table name deduction from model name. For instance, a model named |
| 69 |
``EventLog`` would correspond to the ``event_log`` database table. |
| 70 |
- A :class:`GUID <advanced_alchemy.types.GUID>` database type that |
| 71 |
establishes a native UUID in supported engines or a ``Binary(16)`` as a fallback. |
| 72 |
- A ``BigInteger`` variant |
| 73 |
:class:`BigIntIdentity <advanced_alchemy.types.BigIntIdentity>` that |
| 74 |
reverts to an ``Integer`` for unsupported variants. |
| 75 |
- A custom :class:`JsonB <advanced_alchemy.types.JsonB>` type that uses |
| 76 |
native ``JSONB`` where possible and ``Binary`` or ``Blob`` as an alternative. |
| 77 |
- A custom :class:`EncryptedString <advanced_alchemy.types.EncryptedString>` encrypted string that supports multiple cryptography backends. |
| 78 |
|
| 79 |
Let's build on this as we look at the repository classes. |
| 80 |
|
| 81 |
.. _01-repo-full-code: |
| 82 |
|
| 83 |
Full Code |
| 84 |
--------- |
| 85 |
|
| 86 |
.. dropdown:: Full Code (click to toggle) |
| 87 |
|
| 88 |
.. literalinclude:: /examples/contrib/sqlalchemy/sqlalchemy_declarative_models.py |
| 89 |
:language: python |
| 90 |
:caption: ``app.py`` |
| 91 |
:emphasize-lines: 9, 19-22, 28-31 |
| 92 |
:linenos: |