Debian Codegraph @

docs/usage/databases/sqlalchemy/models_and_repository.rst

raw
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`