Skip to content

Commit 8da9235

Browse files
tiangolotiangolo
authored
✨ Add direct support for PostgreSQL, MySQL, and MariaDB, include in CI tests (#2103)
1 parent 72bca1e commit 8da9235

98 files changed

Lines changed: 733 additions & 628 deletions

File tree

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

‎.github/workflows/test.yml‎

Lines changed: 26 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -22,16 +22,26 @@ env:
2222

2323
jobs:
2424
test:
25+
name: Test (${{ matrix.os }}, Python ${{ matrix.python-version }}, ${{ matrix.uv-resolution }}, ${{ matrix.database || 'sqlite' }})
2526
strategy:
2627
matrix:
2728
os: [ ubuntu-latest, windows-latest, macos-latest ]
2829
python-version: [ "3.14" ]
2930
uv-resolution:
3031
- highest
3132
include:
33+
- os: ubuntu-latest
34+
python-version: "3.14"
35+
database: postgres
36+
database-url: postgresql+psycopg://sqlmodel:[email protected]:15432/sqlmodel_test
3237
- os: ubuntu-latest
3338
python-version: "3.10"
3439
uv-resolution: lowest-direct
40+
database: mysql
41+
database-url: mysql+pymysql://sqlmodel:[email protected]:13306/sqlmodel_test
42+
- os: ubuntu-latest
43+
python-version: "3.11"
44+
uv-resolution: highest
3545
- os: macos-latest
3646
python-version: "3.11"
3747
uv-resolution: highest
@@ -41,6 +51,8 @@ jobs:
4151
- os: ubuntu-latest
4252
python-version: "3.13"
4353
uv-resolution: highest
54+
database: mariadb
55+
database-url: mariadb+pymysql://sqlmodel:[email protected]:13307/sqlmodel_test
4456
- os: macos-latest
4557
python-version: "3.13"
4658
uv-resolution: highest
@@ -50,6 +62,8 @@ jobs:
5062
env:
5163
UV_PYTHON: ${{ matrix.python-version }}
5264
UV_RESOLUTION: ${{ matrix.uv-resolution }}
65+
DATABASE: ${{ matrix.database || 'sqlite' }}
66+
DATABASE_URL: ${{ matrix.database-url || 'sqlite://' }}
5367
steps:
5468
- uses: actions/checkout@3d3c42e5aac5ba805825da76410c181273ba90b1 # v7.0.1
5569
with:
@@ -74,18 +88,27 @@ jobs:
7488
limit-access-to-actor: true
7589
- name: Install Dependencies
7690
run: uv sync --no-dev --group tests
91+
- name: Start Database
92+
if: matrix.database
93+
run: docker compose up --wait --wait-timeout 120 "$DATABASE"
7794
- run: mkdir coverage
7895
- name: Test
7996
run: uv run bash scripts/test.sh
8097
env:
81-
COVERAGE_FILE: coverage/.coverage.${{ runner.os }}-py${{ matrix.python-version }}-${{ matrix.pydantic-version }}
82-
CONTEXT: ${{ runner.os }}-py${{ matrix.python-version }}
98+
COVERAGE_FILE: coverage/.coverage.${{ runner.os }}-py${{ matrix.python-version }}-${{ matrix.database || 'sqlite' }}
99+
CONTEXT: ${{ runner.os }}-py${{ matrix.python-version }}-${{ matrix.database || 'sqlite' }}
83100
- name: Store coverage files
84101
uses: actions/upload-artifact@043fb46d1a93c77aae656e7c1c64a875d1fc6a0a # v7.0.1
85102
with:
86-
name: coverage-${{ runner.os }}-${{ matrix.python-version }}-${{ matrix.pydantic-version }}
103+
name: coverage-${{ runner.os }}-${{ matrix.python-version }}-${{ matrix.database || 'sqlite' }}
87104
path: coverage
88105
include-hidden-files: true
106+
- name: Show Database Logs
107+
if: ${{ failure() && matrix.database }}
108+
run: docker compose logs --no-color "$DATABASE"
109+
- name: Stop Database
110+
if: ${{ always() && matrix.database }}
111+
run: docker compose down --volumes
89112

90113
coverage-combine:
91114
needs:

‎compose.yml‎

Lines changed: 50 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,50 @@
1+
services:
2+
postgres:
3+
image: postgres:18
4+
environment:
5+
POSTGRES_USER: sqlmodel
6+
POSTGRES_PASSWORD: sqlmodel
7+
POSTGRES_DB: sqlmodel_test
8+
ports:
9+
- "127.0.0.1:15432:5432"
10+
healthcheck:
11+
test: ["CMD", "pg_isready", "-h", "127.0.0.1", "-U", "sqlmodel", "-d", "sqlmodel_test"]
12+
interval: 2s
13+
timeout: 5s
14+
retries: 30
15+
16+
mysql:
17+
image: mysql:8.4
18+
environment:
19+
MYSQL_ROOT_PASSWORD: sqlmodel
20+
MYSQL_USER: sqlmodel
21+
MYSQL_PASSWORD: sqlmodel
22+
MYSQL_DATABASE: sqlmodel_test
23+
ports:
24+
- "127.0.0.1:13306:3306"
25+
healthcheck:
26+
test: ["CMD", "mysql", "--host=127.0.0.1", "--user=sqlmodel", "--password=sqlmodel", "--database=sqlmodel_test", "--execute=SELECT 1"]
27+
interval: 2s
28+
timeout: 5s
29+
retries: 30
30+
31+
mariadb:
32+
image: mariadb:11.8
33+
environment:
34+
MARIADB_ROOT_PASSWORD: sqlmodel
35+
MARIADB_USER: sqlmodel
36+
MARIADB_PASSWORD: sqlmodel
37+
MARIADB_DATABASE: sqlmodel_test
38+
ports:
39+
- "127.0.0.1:13307:3306"
40+
healthcheck:
41+
test: ["CMD", "healthcheck.sh", "--connect", "--innodb_initialized"]
42+
interval: 2s
43+
timeout: 5s
44+
retries: 30
45+
46+
adminer:
47+
image: adminer:5
48+
profiles: ["debug"]
49+
ports:
50+
- "127.0.0.1:18080:8080"

‎docs/contributing.md‎

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -5,3 +5,11 @@ First, you might want to see the basic ways to [help SQLModel and get help](help
55
## Developing
66

77
To contribute code to the project, please follow the guidelines in [tiangolo.com - Contributing](https://tiangolo.com/open-source/contributing/).
8+
9+
## Database tests
10+
11+
Start local PostgreSQL, MySQL, and MariaDB databases for tests with Docker Compose:
12+
13+
```console
14+
docker compose up --wait postgres mysql mariadb
15+
```

‎docs/tutorial/relationship-attributes/cascade-delete-relationships.md‎

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -438,7 +438,7 @@ If you know your database would be able to correctly handle the deletes or updat
438438

439439
To be able to test this out with SQLite, we first need to enable foreign key support.
440440

441-
{* ./docs_src/tutorial/relationship_attributes/cascade_delete_relationships/tutorial003_py310.py ln[30:33] hl[33] *}
441+
{* ./docs_src/tutorial/relationship_attributes/cascade_delete_relationships/tutorial003_py310.py ln[30:34] hl[32,34] *}
442442

443443
/// note
444444

@@ -516,7 +516,7 @@ For SQLite, this also needs enabling foreign key support.
516516

517517
As `ondelete="RESTRICT"` is mainly a database-level constraint, let's enable foreign key support in SQLite first to be able to test it.
518518

519-
{* ./docs_src/tutorial/relationship_attributes/cascade_delete_relationships/tutorial004_py310.py ln[30:33] hl[33] *}
519+
{* ./docs_src/tutorial/relationship_attributes/cascade_delete_relationships/tutorial004_py310.py ln[30:34] hl[32,34] *}
520520

521521
### Use `ondelete="RESTRICT"`
522522

@@ -593,7 +593,7 @@ Calling `team.heroes.clear()` is very similar to what SQLModel (actually SQLAlch
593593

594594
///
595595

596-
{* ./docs_src/tutorial/relationship_attributes/cascade_delete_relationships/tutorial005_py310.py ln[80:88] hl[84] *}
596+
{* ./docs_src/tutorial/relationship_attributes/cascade_delete_relationships/tutorial005_py310.py ln[81:89] hl[85] *}
597597

598598
### Run the Program Deleting Heroes First
599599

‎docs_src/tutorial/relationship_attributes/cascade_delete_relationships/tutorial003_py310.py‎

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -29,8 +29,9 @@ class Hero(SQLModel, table=True):
2929

3030
def create_db_and_tables():
3131
SQLModel.metadata.create_all(engine)
32-
with engine.connect() as connection:
33-
connection.execute(text("PRAGMA foreign_keys=ON")) # for SQLite only
32+
if engine.dialect.name == "sqlite":
33+
with engine.connect() as connection:
34+
connection.execute(text("PRAGMA foreign_keys=ON"))
3435

3536

3637
def create_heroes():

‎docs_src/tutorial/relationship_attributes/cascade_delete_relationships/tutorial004_py310.py‎

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -29,8 +29,9 @@ class Hero(SQLModel, table=True):
2929

3030
def create_db_and_tables():
3131
SQLModel.metadata.create_all(engine)
32-
with engine.connect() as connection:
33-
connection.execute(text("PRAGMA foreign_keys=ON")) # for SQLite only
32+
if engine.dialect.name == "sqlite":
33+
with engine.connect() as connection:
34+
connection.execute(text("PRAGMA foreign_keys=ON"))
3435

3536

3637
def create_heroes():

‎docs_src/tutorial/relationship_attributes/cascade_delete_relationships/tutorial005_py310.py‎

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -29,8 +29,9 @@ class Hero(SQLModel, table=True):
2929

3030
def create_db_and_tables():
3131
SQLModel.metadata.create_all(engine)
32-
with engine.connect() as connection:
33-
connection.execute(text("PRAGMA foreign_keys=ON")) # for SQLite only
32+
if engine.dialect.name == "sqlite":
33+
with engine.connect() as connection:
34+
connection.execute(text("PRAGMA foreign_keys=ON"))
3435

3536

3637
def create_heroes():

‎pyproject.toml‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -82,6 +82,8 @@ tests = [
8282
"fastapi >=0.128.0",
8383
"httpx >=0.28.1",
8484
"jinja2 >=3.1.6",
85+
"psycopg[binary] >=3.2.10",
86+
"pymysql[rsa] >=1.1.1",
8587
"pytest >=7.0.1",
8688
"ruff >=0.15.6",
8789
"ty>=0.0.25",

‎sqlmodel/sql/sqltypes.py‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -56,6 +56,6 @@ class AutoString(types.TypeDecorator):
5656

5757
def load_dialect_impl(self, dialect: Dialect) -> "types.TypeEngine[Any]":
5858
impl = cast(types.String, self.impl)
59-
if impl.length is None and dialect.name == "mysql":
59+
if impl.length is None and dialect.name in {"mysql", "mariadb"}:
6060
return dialect.type_descriptor(types.String(self.mysql_default_length))
6161
return super().load_dialect_impl(dialect)

‎tests/conftest.py‎

Lines changed: 24 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,3 +1,4 @@
1+
import os
12
import shutil
23
import subprocess
34
import sys
@@ -9,7 +10,10 @@
910

1011
import pytest
1112
from pydantic import BaseModel
12-
from sqlmodel import SQLModel
13+
from sqlalchemy import Engine
14+
from sqlalchemy.engine import make_url
15+
from sqlalchemy.pool import StaticPool
16+
from sqlmodel import SQLModel, create_engine
1317
from sqlmodel.main import default_registry
1418

1519
top_level_path = Path(__file__).resolve().parent.parent
@@ -27,6 +31,25 @@ def clear_sqlmodel() -> Any:
2731
default_registry.dispose()
2832

2933

34+
@pytest.fixture()
35+
def database_engine(clear_sqlmodel: Any) -> Generator[Engine, None, None]:
36+
url = make_url(os.environ.get("DATABASE_URL", "sqlite://"))
37+
if url.get_backend_name() == "sqlite":
38+
engine = create_engine(
39+
url, connect_args={"check_same_thread": False}, poolclass=StaticPool
40+
)
41+
else:
42+
engine = create_engine(url)
43+
try:
44+
yield engine
45+
finally:
46+
# Drop tables before clear_sqlmodel removes their metadata.
47+
try:
48+
SQLModel.metadata.drop_all(engine)
49+
finally:
50+
engine.dispose()
51+
52+
3053
@pytest.fixture()
3154
def cov_tmp_path(tmp_path: Path) -> Generator[Path, None, None]:
3255
yield tmp_path

0 commit comments

Comments
 (0)