| 1 |
SQLAlchemy Models & Repository |
| 2 |
============================== |
| 3 |
|
| 4 |
Litestar comes with a built-in repository class |
| 5 |
(:class:`SQLAlchemyAsyncRepository <advanced_alchemy.repository.SQLAlchemyAsyncRepository>`) |
| 6 |
for `SQLAlchemy <https://docs.sqlalchemy.org/>`_ to make CRUD operations easier. |
| 7 |
|
| 8 |
Features |
| 9 |
-------- |
| 10 |
|
| 11 |
* Pre-configured ``DeclarativeBase`` for :doc:`SQLAlchemy <sqlalchemy:index>` 2.0 that includes a |
| 12 |
UUID or Big Integer based primary-key, |
| 13 |
a `sentinel column <https://docs.sqlalchemy.org/en/20/core/connections.html#configuring-sentinel-columns>`_, and |
| 14 |
an optional version with audit columns. |
| 15 |
* Generic synchronous and asynchronous repositories for select, insert, update, and delete operations on SQLAlchemy models |
| 16 |
* Implements optimized methods for bulk inserts, updates, and deletes and uses `lambda_stmt <https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.lambda_stmt>`_ when possible. |
| 17 |
* Integrated counts, pagination, sorting, filtering with ``LIKE``, ``IN``, and dates before and/or after. |
| 18 |
* Tested support for multiple database backends including: |
| 19 |
|
| 20 |
- SQLite via `aiosqlite <https://aiosqlite.omnilib.dev/en/stable/>`_ or `sqlite <https://docs.python.org/3/library/sqlite3.html>`_ |
| 21 |
- Postgres via `asyncpg <https://magicstack.github.io/asyncpg/current/>`_ or `psycopg3 (async or sync) <https://www.psycopg.org/psycopg3/>`_ |
| 22 |
- MySQL via `asyncmy <https://github.com/long2ice/asyncmy>`_ |
| 23 |
- Oracle via `oracledb <https://oracle.github.io/python-oracledb/>`_ |
| 24 |
- Google Spanner via `spanner-sqlalchemy <https://github.com/googleapis/python-spanner-sqlalchemy/>`_ |
| 25 |
- DuckDB via `duckdb_engine <https://github.com/Mause/duckdb_engine>`_ |
| 26 |
- Microsoft SQL Server via `pyodbc <https://github.com/mkleehammer/pyodbc>`_ |
| 27 |
|
| 28 |
Basic Use |
| 29 |
--------- |
| 30 |
|
| 31 |
To use the :class:`SQLAlchemyAsyncRepository <advanced_alchemy.repository.SQLAlchemyAsyncRepository>` |
| 32 |
repository, you must first define your models using one of the included built-in ``DeclarativeBase`` ORM base |
| 33 |
implementations: |
| 34 |
|
| 35 |
* :class:`UUIDBase <advanced_alchemy.base.UUIDBase>` |
| 36 |
* :class:`UUIDAuditBase <advanced_alchemy.base.UUIDAuditBase>` |
| 37 |
|
| 38 |
Both include a ``UUID`` based primary key |
| 39 |
and ``UUIDAuditBase`` includes ``updated_at`` and ``created_at`` timestamp columns. |
| 40 |
|
| 41 |
The ``UUID`` will be a native ``UUID``/``GUID`` type on databases that support it such as Postgres. For other engines without |
| 42 |
a native UUID data type, the UUID is stored as a 16-byte ``BYTES`` or ``RAW`` field. |
| 43 |
|
| 44 |
* :class:`BigIntBase <advanced_alchemy.base.BigIntBase>` |
| 45 |
* :class:`BigIntAuditBase <advanced_alchemy.base.BigIntAuditBase>` |
| 46 |
|
| 47 |
Both include a ``BigInteger`` based primary key |
| 48 |
and ``BigIntAuditBase`` includes ``updated_at`` and ``created_at`` timestamp columns. |
| 49 |
|
| 50 |
Models using these bases also include the following enhancements: |
| 51 |
|
| 52 |
* Auto-generated snake-case table name from class name |
| 53 |
* Pydantic BaseModel and Dict classes map to an optimized JSON type that is |
| 54 |
:class:`JSONB <sqlalchemy.dialects.postgresql.JSONB>` for Postgres, |
| 55 |
`VARCHAR` or `BYTES` with JSON check constraint for Oracle, and |
| 56 |
:class:`JSON <sqlalchemy.types.JSON>` for other dialects. |
| 57 |
|
| 58 |
.. literalinclude:: /examples/contrib/sqlalchemy/sqlalchemy_declarative_models.py |
| 59 |
:caption: ``sqlalchemy_declarative_models.py`` |
| 60 |
:language: python |
| 61 |
|
| 62 |
Basic Controller Integration |
| 63 |
----------------------------- |
| 64 |
|
| 65 |
Once you have declared your models, you are ready to use the ``SQLAlchemyAsyncRepository`` class with |
| 66 |
your controllers and function based routes. |
| 67 |
|
| 68 |
.. literalinclude:: /examples/contrib/sqlalchemy/sqlalchemy_async_repository.py |
| 69 |
:caption: ``sqlalchemy_async_repository.py`` |
| 70 |
:language: python |
| 71 |
|
| 72 |
Alternately, you may use the ``SQLAlchemySyncRepository`` class for your synchronous database connection. |
| 73 |
|
| 74 |
.. literalinclude:: /examples/contrib/sqlalchemy/sqlalchemy_sync_repository.py |
| 75 |
:caption: ``sqlalchemy_sync_repository.py`` |
| 76 |
:language: python |
| 77 |
|
| 78 |
.. seealso:: |
| 79 |
|
| 80 |
* :doc:`/tutorials/repository-tutorial/index` |