| 1 |
Interacting with repositories |
| 2 |
----------------------------- |
| 3 |
Now that we've covered the modelling basics, we are able to create our first repository |
| 4 |
class. The repository classes include all of the standard CRUD operations as well as a |
| 5 |
few advanced features such as pagination, filtering and bulk operations. |
| 6 |
|
| 7 |
.. tip:: The full code for this tutorial can be found below in the :ref:`Full Code <02-repo-full-code>` section. |
| 8 |
|
| 9 |
Before we jump in to the code, let's take a look at the available functions available in |
| 10 |
the the synchronous and asynchronous repositories. |
| 11 |
|
| 12 |
.. dropdown:: Available Functions in the Repositories |
| 13 |
|
| 14 |
+---------------------+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
| 15 |
| Function | Category | Description | |
| 16 |
+=====================+================+=============================================================================================================================================================================================================================================+ |
| 17 |
| ``get`` | Selecting Data | Select a single record by primary key. Raises an exception when no record is found. | |
| 18 |
+---------------------+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
| 19 |
| ``get_one`` | Selecting Data | Select a single record specified by the ``kwargs`` parameters. Raises an exception when no record is found. | |
| 20 |
+---------------------+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
| 21 |
| ``get_one_or_none`` | Selecting Data | Select a single record specified by the ``kwargs`` parameters. Returns ``None`` when no record is found. | |
| 22 |
+---------------------+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
| 23 |
| ``list`` | Selecting Data | Select a list of records specified by the ``kwargs`` parameters. Optionally it can be filtered by the included ``FilterTypes`` args. | |
| 24 |
+---------------------+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
| 25 |
| ``list_and_count`` | Selecting Data | Select a list of records specified by the ``kwargs`` parameters. Optionally it can be filtered by the included ``FilterTypes`` args. Results are returned as a 2 value tuple that includes the rows selected and the total count of records.| |
| 26 |
+---------------------+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
| 27 |
| ``get_or_create`` | Creating Data | Select a single record specified by the the ``kwargs`` parameters. If no record is found, one is created with the given values. There's an optional attribute to filter on a subset of the supplied parameters and to merge updates. | |
| 28 |
+---------------------+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
| 29 |
| ``create`` | Creating Data | Create a new record in the database. | |
| 30 |
+---------------------+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
| 31 |
| ``create_many`` | Creating Data | Create one or more rows in the database. | |
| 32 |
+---------------------+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
| 33 |
| ``update`` | Updating Data | Update an existing record in the database. | |
| 34 |
+---------------------+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
| 35 |
| ``update_many`` | Updating Data | Update one or more rows in the database. | |
| 36 |
+---------------------+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
| 37 |
| ``upsert`` | Updating Data | A single operation that updates or inserts a record based whether or not the primary key value on the model object is populated. | |
| 38 |
+---------------------+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
| 39 |
| ``upsert_many`` | Updating Data | Updates or inserts multiple records based whether or not the primary key value on the model object is populated. | |
| 40 |
+---------------------+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
| 41 |
| ``delete`` | Removing Data | Remove a single record from the database. | |
| 42 |
+---------------------+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
| 43 |
| ``delete_many`` | Removing Data | Remove one or more records from the database. | |
| 44 |
+---------------------+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
| 45 |
|
| 46 |
.. note:: |
| 47 |
|
| 48 |
- All three of the bulk DML operations will leverage dialect-specific enhancements |
| 49 |
to be as efficient as possible. In addition to using efficient bulk inserts binds, |
| 50 |
the repository will optionally leverage the multi-row ``RETURNING`` support where |
| 51 |
possible. The repository will automatically detect this support from the |
| 52 |
SQLAlchemy driver, so no additional interaction is required to enable this. |
| 53 |
|
| 54 |
- SQL engines generally have a limit to the number of elements that can be appended |
| 55 |
into an `IN` clause. The repository operations will automatically break lists that |
| 56 |
exceed this limit into multiple queries that are concatenated together before |
| 57 |
return. You do not need to account for this in your own code. |
| 58 |
|
| 59 |
In the following examples, we'll cover a few ways that you can use the repository within |
| 60 |
your applications. |
| 61 |
|
| 62 |
Model Repository |
| 63 |
^^^^^^^^^^^^^^^^ |
| 64 |
|
| 65 |
Here we import the |
| 66 |
:class:`SQLAlchemyAsyncRepository <advanced_alchemy.repository.SQLAlchemyAsyncRepository>` |
| 67 |
class and create an ``AuthorRepository`` repository class. This is all that's required |
| 68 |
to include all of the integrated repository features. |
| 69 |
|
| 70 |
.. literalinclude:: /examples/contrib/sqlalchemy/sqlalchemy_repository_crud.py |
| 71 |
:language: python |
| 72 |
:caption: ``app.py`` |
| 73 |
:lines: 14, 7-30 |
| 74 |
:linenos: |
| 75 |
|
| 76 |
Repository Context Manager |
| 77 |
^^^^^^^^^^^^^^^^^^^^^^^^^^ |
| 78 |
|
| 79 |
Since we'll be using the repository outside of a Litestar application in this script, |
| 80 |
we'll make a simple context manager to automatically handle the creation (and cleanup) |
| 81 |
of our Author repository. |
| 82 |
|
| 83 |
The ``repository_factory`` method will do the following for us: |
| 84 |
- Automatically create a new DB session from the SQLAlchemy configuration. |
| 85 |
- Rollback session when any exception occurs. |
| 86 |
- Automatically commit after function call completes. |
| 87 |
|
| 88 |
|
| 89 |
.. literalinclude:: /examples/contrib/sqlalchemy/sqlalchemy_repository_crud.py |
| 90 |
:language: python |
| 91 |
:caption: ``app.py`` |
| 92 |
:lines: 39-47 |
| 93 |
:linenos: |
| 94 |
|
| 95 |
|
| 96 |
Creating, Updating and Removing Data |
| 97 |
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ |
| 98 |
|
| 99 |
To illustrate a few ways you can manipulate data in your database, we'll go through the |
| 100 |
various CRUD operations: |
| 101 |
|
| 102 |
Creating Data: Here's a simple insert operation to populate our new Author table: |
| 103 |
|
| 104 |
.. literalinclude:: /examples/contrib/sqlalchemy/sqlalchemy_repository_crud.py |
| 105 |
:language: python |
| 106 |
:caption: ``app.py`` |
| 107 |
:lines: 52-61 |
| 108 |
:linenos: |
| 109 |
|
| 110 |
Updating Data: The ``update`` method will ensure any updates made to the model object are executed on the database: |
| 111 |
|
| 112 |
.. literalinclude:: /examples/contrib/sqlalchemy/sqlalchemy_repository_crud.py |
| 113 |
:language: python |
| 114 |
:caption: ``app.py`` |
| 115 |
:lines: 64-68 |
| 116 |
:linenos: |
| 117 |
|
| 118 |
Removing Data: The ``delete`` method accepts the primary key of the row you want to delete: |
| 119 |
|
| 120 |
.. literalinclude:: /examples/contrib/sqlalchemy/sqlalchemy_repository_crud.py |
| 121 |
:language: python |
| 122 |
:caption: ``app.py`` |
| 123 |
:lines: 71-75 |
| 124 |
:linenos: |
| 125 |
|
| 126 |
Now that we've seen how to do single-row operations, let's look at the bulk methods we |
| 127 |
can use. |
| 128 |
|
| 129 |
|
| 130 |
Working with Bulk Data Operations |
| 131 |
--------------------------------- |
| 132 |
In this section, we delve into the powerful capabilities of the repository classes for |
| 133 |
handling bulk data operations. Our example illustrates how we can efficiently manage |
| 134 |
data in our database. Specifically, we'll use a JSON file containing information about |
| 135 |
US states and their abbreviations. |
| 136 |
|
| 137 |
Here's what we're going to cover: |
| 138 |
|
| 139 |
Fixture Data Loading |
| 140 |
^^^^^^^^^^^^^^^^^^^^ |
| 141 |
|
| 142 |
We will introduce a method for loading fixture data. Fixture data is sample data that |
| 143 |
populates your database and helps test the behavior of your application under realistic |
| 144 |
conditions. This pattern can be extended and adjusted to meet your needs. |
| 145 |
|
| 146 |
.. literalinclude:: /examples/contrib/sqlalchemy/sqlalchemy_repository_bulk_operations.py |
| 147 |
:language: python |
| 148 |
:caption: ``app.py`` |
| 149 |
:lines: 1-3, 34-54 |
| 150 |
:linenos: |
| 151 |
|
| 152 |
You can review the JSON source file here: |
| 153 |
|
| 154 |
.. dropdown:: US State Lookup JSON |
| 155 |
|
| 156 |
You can download it: :download:`/examples/contrib/sqlalchemy/us_state_lookup.json` |
| 157 |
or view below: |
| 158 |
|
| 159 |
.. literalinclude:: /examples/contrib/sqlalchemy/us_state_lookup.json |
| 160 |
:language: json |
| 161 |
:caption: ``us_state_lookup.json`` |
| 162 |
|
| 163 |
|
| 164 |
Bulk Insert |
| 165 |
^^^^^^^^^^^ |
| 166 |
|
| 167 |
We'll use our fixture data to demonstrate a bulk insert operation. This operation allows |
| 168 |
you to add multiple records to your database in a single transaction, improving |
| 169 |
performance when working with larger data sets. |
| 170 |
|
| 171 |
.. literalinclude:: /examples/contrib/sqlalchemy/sqlalchemy_repository_bulk_operations.py |
| 172 |
:language: python |
| 173 |
:caption: ``app.py`` |
| 174 |
:lines: 5-11, 13, 14-16, 18-26, 27-33, 55-70 |
| 175 |
:linenos: |
| 176 |
|
| 177 |
Paginated Data Selection |
| 178 |
^^^^^^^^^^^^^^^^^^^^^^^^ |
| 179 |
|
| 180 |
Next, let's explore how to select multiple records with pagination. This functionality |
| 181 |
is useful for handling large amounts of data by breaking the data into manageable |
| 182 |
'pages' or subsets. ``LimitOffset`` is one of several filter types you can use with the |
| 183 |
repository. |
| 184 |
|
| 185 |
.. literalinclude:: /examples/contrib/sqlalchemy/sqlalchemy_repository_bulk_operations.py |
| 186 |
:language: python |
| 187 |
:caption: ``app.py`` |
| 188 |
:lines: 10, 55-56, 72-74 |
| 189 |
:linenos: |
| 190 |
|
| 191 |
Bulk Delete |
| 192 |
^^^^^^^^^^^ |
| 193 |
|
| 194 |
Here we demonstrate how to perform a bulk delete operation. Just as with the bulk |
| 195 |
insert, deleting multiple records with the batch record methods is more efficient than |
| 196 |
executing row-by-row. |
| 197 |
|
| 198 |
.. literalinclude:: /examples/contrib/sqlalchemy/sqlalchemy_repository_bulk_operations.py |
| 199 |
:language: python |
| 200 |
:caption: ``app.py`` |
| 201 |
:lines: 76-78 |
| 202 |
:linenos: |
| 203 |
|
| 204 |
Counts |
| 205 |
^^^^^^ |
| 206 |
|
| 207 |
Finally, we'll demonstrate how to count the number of records remaining in the database. |
| 208 |
|
| 209 |
.. literalinclude:: /examples/contrib/sqlalchemy/sqlalchemy_repository_bulk_operations.py |
| 210 |
:language: python |
| 211 |
:caption: ``app.py`` |
| 212 |
:lines: 80-82 |
| 213 |
:linenos: |
| 214 |
|
| 215 |
Now that we have demonstrated how to interact with the repository objects outside of a |
| 216 |
Litestar application, our next example will use dependency injection to add this |
| 217 |
functionality to a :class:`~litestar.controller.Controller`! |
| 218 |
|
| 219 |
.. _02-repo-full-code: |
| 220 |
|
| 221 |
Full Code |
| 222 |
--------- |
| 223 |
|
| 224 |
.. dropdown:: Full Code (click to toggle) |
| 225 |
|
| 226 |
.. literalinclude:: /examples/contrib/sqlalchemy/sqlalchemy_repository_crud.py |
| 227 |
:language: python |
| 228 |
:caption: ``app.py`` |
| 229 |
:emphasize-lines: 14, 27-30, 37-55, 61, 64-71, 71-75, 77-79, 81-83 |
| 230 |
:linenos: |