mirror of
https://github.com/fastapi-users/fastapi-users.git
synced 2025-08-15 11:11:16 +08:00
69 lines
2.8 KiB
Markdown
69 lines
2.8 KiB
Markdown
# SQLAlchemy
|
|
|
|
**FastAPI Users** provides the necessary tools to work with SQL databases thanks to [SQLAlchemy ORM with asyncio](https://docs.sqlalchemy.org/en/14/orm/extensions/asyncio.html).
|
|
|
|
## Asynchronous driver
|
|
|
|
To work with your DBMS, you'll need to install the corresponding asyncio driver. The common choices are:
|
|
|
|
* For PostgreSQL: `pip install asyncpg`
|
|
* For SQLite: `pip install aiosqlite`
|
|
|
|
Examples of `DB_URL`s are:
|
|
|
|
* PostgreSQL: `postgresql+asyncpg://user:password@host:port/name`
|
|
* SQLite: `sqlite+aiosqlite:///name.db`
|
|
|
|
For the sake of this tutorial from now on, we'll use a simple SQLite database.
|
|
|
|
!!! warning
|
|
When using asynchronous sessions, ensure `Session.expire_on_commit` is set to `False` as recommended by the [SQLAlchemy docs on asyncio](https://docs.sqlalchemy.org/en/20/orm/extensions/asyncio.html#asyncio-orm-avoid-lazyloads). The examples on this documentation already have this setting correctly defined to `False` when using the `async_sessionmaker` factory.
|
|
|
|
## Create the User model
|
|
|
|
As for any SQLAlchemy ORM model, we'll create a `User` model.
|
|
|
|
```py hl_lines="15-16"
|
|
--8<-- "docs/src/db_sqlalchemy.py"
|
|
```
|
|
|
|
As you can see, **FastAPI Users** provides a base class that will include base fields for our `User` table. You can of course add you own fields there to fit to your needs!
|
|
|
|
!!! tip "Primary key is defined as UUID"
|
|
By default, we use UUID as a primary key ID for your user. If you want to use another type, like an auto-incremented integer, you can use `SQLAlchemyBaseUserTable` as base class and define your own `id` column.
|
|
|
|
```py
|
|
class User(SQLAlchemyBaseUserTable[int], Base):
|
|
id: Mapped[int] = mapped_column(Integer, primary_key=True)
|
|
```
|
|
|
|
Notice that `SQLAlchemyBaseUserTable` expects a generic type to define the actual type of ID you use.
|
|
|
|
## Implement a function to create the tables
|
|
|
|
We'll now create an utility function to create all the defined tables.
|
|
|
|
```py hl_lines="23-25"
|
|
--8<-- "docs/src/db_sqlalchemy.py"
|
|
```
|
|
|
|
This function can be called, for example, during the initialization of your FastAPI app.
|
|
|
|
!!! warning
|
|
In production, it's strongly recommended to setup a migration system to update your SQL schemas. See [Alembic](https://alembic.sqlalchemy.org/en/latest/).
|
|
|
|
## Create the database adapter dependency
|
|
|
|
The database adapter of **FastAPI Users** makes the link between your database configuration and the users logic. It should be generated by a FastAPI dependency.
|
|
|
|
```py hl_lines="28-34"
|
|
--8<-- "docs/src/db_sqlalchemy.py"
|
|
```
|
|
|
|
Notice that we define first a `get_async_session` dependency returning us a fresh SQLAlchemy session to interact with the database.
|
|
|
|
It's then used inside the `get_user_db` dependency to generate our adapter. Notice that we pass it two things:
|
|
|
|
* The `session` instance we just injected.
|
|
* The `User` class, which is the actual SQLAlchemy model.
|