Debian Codegraph @

docs/tutorials/repository-tutorial/02-repository-introduction.rst

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