# How to Use FastCRUD with Multiple Databases: PostgreSQL, MySQL, and SQLite

> Learn how to use FastCRUD with PostgreSQL, MySQL, and SQLite using SQLAlchemy 2.0. FastCRUD unifies CRUD operations across databases with automatic dialect detection for seamless integration.

- Repository: [Benav Labs/fastcrud](https://github.com/benavlabs/fastcrud)
- Tags: how-to-guide
- Published: 2026-02-26

---

**FastCRUD supports PostgreSQL, MySQL/MariaDB, and SQLite through SQLAlchemy 2.0 async engines, automatically detecting the database dialect at runtime to handle differences in upsert syntax while exposing a unified API for all CRUD operations.**

FastCRUD is a SQLAlchemy 2.0-based CRUD generator designed for FastAPI applications that works seamlessly across multiple database backends. Whether you are connecting to PostgreSQL, MySQL/MariaDB, or SQLite, the library abstracts dialect-specific implementations so you can use identical code patterns across all supported databases. This guide explains how to configure FastCRUD for each database backend and leverage its automatic dialect detection for portable database operations.

## How FastCRUD Handles Multiple Database Backends

FastCRUD is built on SQLAlchemy 2.0's async ORM capabilities, which provides native support for PostgreSQL, MySQL, and SQLite through different async driver packages.

### SQLAlchemy 2.0 Foundation

The library expects an `AsyncSession` provider regardless of the underlying database. In [`fastcrud/crud/fast_crud.py`](https://github.com/benavlabs/fastcrud/blob/main/fastcrud/crud/fast_crud.py), all CRUD operations—including `create`, `get`, `get_multi`, `update`, `delete`, and `upsert`—are implemented using SQLAlchemy's core and ORM APIs that translate to dialect-specific SQL at execution time.

### Automatic Dialect Detection

When performing database-specific operations like bulk upserts, FastCRUD detects the dialect at runtime using `db.bind.dialect.name`. This detection occurs in [`fastcrud/crud/database_specific.py`](https://github.com/benavlabs/fastcrud/blob/main/fastcrud/crud/database_specific.py), where the library routes to the appropriate implementation:

```python
if db.bind.dialect.name == "postgresql":
    statement, params = await upsert_multi_postgresql(...)
elif db.bind.dialect.name == "sqlite":
    statement, params = await upsert_multi_sqlite(...)
elif db.bind.dialect.name in ["mysql", "mariadb"]:
    statement, params = await upsert_multi_mysql(...)

```

### Database-Specific Upsert Logic

The upsert implementations handle the syntactic differences between databases:

- **PostgreSQL**: Uses `postgresql.insert(...).on_conflict_do_update(...)` with full `RETURNING` clause support.
- **SQLite**: Uses `sqlite.insert(...).on_conflict_do_update(...)` with `RETURNING` clause support.
- **MySQL/MariaDB**: Uses `mysql.insert(...).on_duplicate_key_update(...)`. This dialect does not support the `RETURNING` clause, so FastCRUD disables returning data for MySQL upserts.

## Configuring Database Connections

To use FastCRUD with different databases, you only need to change the connection URL when creating the async engine.

### PostgreSQL Setup

Use the `postgresql+asyncpg` driver scheme:

```python
DATABASE_URL = "postgresql+asyncpg://user:password@localhost:5432/dbname"
engine = create_async_engine(DATABASE_URL, echo=True)

```

### MySQL/MariaDB Setup

Use the `mysql+aiomysql` driver scheme:

```python
DATABASE_URL = "mysql+aiomysql://user:password@localhost:3306/dbname"
engine = create_async_engine(DATABASE_URL, echo=True)

```

### SQLite Setup

Use the `sqlite+aiosqlite` driver scheme for async support:

```python
DATABASE_URL = "sqlite+aiosqlite:///./test.db"
engine = create_async_engine(DATABASE_URL, echo=True)

```

### Environment-Based Configuration

Store the database URL in an environment variable to switch backends without code changes:

```python
import os
from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession
from sqlalchemy.orm import sessionmaker

DATABASE_URL = os.getenv("DATABASE_URL", "sqlite+aiosqlite:///./default.db")
engine = create_async_engine(DATABASE_URL, echo=True)
async_session = sessionmaker(engine, class_=AsyncSession, expire_on_commit=False)

async def get_session():
    async with async_session() as session:
        yield session

```

## Creating Database-Agnostic CRUD Endpoints

The `crud_router` function in [`fastcrud/endpoint/crud_router.py`](https://github.com/benavlabs/fastcrud/blob/main/fastcrud/endpoint/crud_router.py) generates FastAPI endpoints without requiring database-specific configuration.

### Using crud_router

Pass the async session dependency and your SQLAlchemy or SQLModel class:

```python
from fastapi import FastAPI
from fastcrud import crud_router
from myapp.models import Item
from myapp.schemas import CreateItemSchema, UpdateItemSchema, ReadItemSchema
from myapp.db import get_session

app = FastAPI()

item_router = crud_router(
    session=get_session,
    model=Item,
    create_schema=CreateItemSchema,
    update_schema=UpdateItemSchema,
    path="/items",
    tags=["Items"],
    select_schema=ReadItemSchema,
)

app.include_router(item_router)

```

The router works identically whether `get_session` provides a PostgreSQL, MySQL, or SQLite connection. All generated endpoints (`POST /items`, `GET /items/{id}`, `PATCH /items/{id}`, etc.) issue the appropriate SQL for the current dialect.

## Handling Upserts Across Different Databases

When performing bulk upserts with `upsert_multi`, FastCRUD automatically adapts to database capabilities.

### PostgreSQL and SQLite RETURNING Support

Both PostgreSQL and SQLite support the `RETURNING` clause, allowing FastCRUD to return the inserted or updated rows:

```python
from fastcrud.examples.item.schemas import ReadItemSchema

items_to_upsert = [
    CreateItemSchema(name="Widget", price=9.99),
    CreateItemSchema(name="Gadget", price=19.99),
]

result = await item_crud.upsert_multi(
    db,
    instances=items_to_upsert,
    schema_to_select=ReadItemSchema,
    return_as_model=True,
)

# Returns: {"data": {"created": [ReadItemSchema(...)], "updated": []}}

```

### MySQL Limitations

MySQL and MariaDB do not support `RETURNING` on insert operations. When using `upsert_multi` with MySQL, FastCRUD disables the returning functionality:

```python

# MySQL: Operation succeeds but returns None for data

result = await item_crud.upsert_multi(
    db,
    instances=items,
    schema_to_select=ReadItemSchema,  # Ignored for MySQL

    return_as_model=True,
)

# Returns: {"data": None} or empty response

```

This behavior is handled transparently in [`fastcrud/crud/database_specific.py`](https://github.com/benavlabs/fastcrud/blob/main/fastcrud/crud/database_specific.py), where the MySQL implementation uses `mysql.insert(...).on_duplicate_key_update(...)` without requesting return data.

## Summary

- FastCRUD supports **PostgreSQL**, **MySQL/MariaDB**, and **SQLite** through SQLAlchemy 2.0 async engines.
- The library detects the database dialect at runtime using `db.bind.dialect.name` to route operations correctly.
- Configure your database by changing the connection URL scheme (`postgresql+asyncpg`, `mysql+aiomysql`, `sqlite+aiosqlite`).
- All standard CRUD operations work identically across databases; only **bulk upserts** require dialect-specific handling in [`fastcrud/crud/database_specific.py`](https://github.com/benavlabs/fastcrud/blob/main/fastcrud/crud/database_specific.py).
- PostgreSQL and SQLite support `RETURNING` clauses for upserts; MySQL does not, so upsert operations return data only for the former two.

## Frequently Asked Questions

### Does FastCRUD require different code for PostgreSQL versus MySQL?

No. FastCRUD abstracts database differences through SQLAlchemy 2.0. You use the same `FastCRUD` class and `crud_router` function regardless of whether you connect to PostgreSQL, MySQL, or SQLite. The library detects the dialect at runtime and routes database-specific operations like upserts to the appropriate implementation in [`fastcrud/crud/database_specific.py`](https://github.com/benavlabs/fastcrud/blob/main/fastcrud/crud/database_specific.py).

### Which async drivers does FastCRUD support?

FastCRUD works with any SQLAlchemy 2.0 compatible async driver. The recommended configurations are:
- **PostgreSQL**: `postgresql+asyncpg`
- **MySQL/MariaDB**: `mysql+aiomysql`
- **SQLite**: `sqlite+aiosqlite`

You specify the driver in your `DATABASE_URL` connection string when creating the async engine with `create_async_engine`.

### Why do upsert operations return data for PostgreSQL but not MySQL?

PostgreSQL and SQLite support the SQL `RETURNING` clause, which allows the database to send back the inserted or updated rows after an upsert operation. MySQL and MariaDB do not support `RETURNING` on insert operations. FastCRUD handles this transparently: when using `upsert_multi` with MySQL, the operation completes successfully but returns `None` for the data field, whereas PostgreSQL and SQLite return the created or updated records.