0
# pytest-postgresql
1

2
A pytest plugin providing comprehensive PostgreSQL database fixtures and fixture factories for testing applications that require a real PostgreSQL database instance. The plugin handles the complete lifecycle of PostgreSQL instances during testing including startup, initialization, connection management, and teardown, with built-in support for connection pooling and transaction isolation.
3

4
## Package Information
5

6
- **Package Name**: pytest-postgresql
7
- **Package Type**: pypi
8
- **Language**: Python
9
- **Installation**: `pip install pytest-postgresql`
10
- **Dependencies**: pytest (>=7.2), port-for (>=0.7.3), mirakuru (>=2.6.0), packaging, psycopg (>=3.0.0)
11
- **Python Version**: >=3.9
12
- **PostgreSQL Version**: >=10
13

14
## Core Imports
15

16
```python
17
from pytest_postgresql import factories
18
```
19

20
For creating custom fixtures:
21

22
```python
23
from pytest_postgresql.factories import postgresql_proc, postgresql_noproc, postgresql
24
```
25

26
For accessing utility classes and functions:
27

28
```python
29
from pytest_postgresql.executor import PostgreSQLExecutor
30
from pytest_postgresql.executor_noop import NoopExecutor
31
from pytest_postgresql.janitor import DatabaseJanitor
32
from pytest_postgresql.exceptions import ExecutableMissingException, PostgreSQLUnsupported
33
from pytest_postgresql.loader import build_loader, sql
34
from pytest_postgresql.retry import retry
35
from pytest_postgresql.factories import PortType
36
```
37

38
## Basic Usage
39

40
### Using Default Fixtures
41

42
The plugin automatically provides three main fixtures when installed:
43

44
```python
45
def test_example_postgres(postgresql):
46
    """Test using the default postgresql fixture."""
47
    cur = postgresql.cursor()
48
    cur.execute("CREATE TABLE test (id serial PRIMARY KEY, num integer, data varchar);")
49
    postgresql.commit()
50
    cur.close()
51

52
def test_postgresql_process(postgresql_proc):
53
    """Test the PostgreSQL process fixture."""
54
    assert postgresql_proc.running() is True
55
```
56

57
### Creating Custom Fixtures
58

59
```python
60
from pytest_postgresql import factories
61

62
# Create custom process fixture
63
postgresql_my_proc = factories.postgresql_proc(
64
    port=None, 
65
    unixsocketdir='/var/run'
66
)
67

68
# Create custom client fixture using the custom process
69
postgresql_my = factories.postgresql('postgresql_my_proc')
70
```
71

72
### Pre-populating Database
73

74
```python
75
# Using SQL file
76
postgresql_with_data = factories.postgresql_proc(
77
    load=['/path/to/schema.sql', '/path/to/data.sql']
78
)
79

80
# Using Python function
81
def load_test_data(**kwargs):
82
    import psycopg
83
    with psycopg.connect(**kwargs) as conn:
84
        with conn.cursor() as cur:
85
            cur.execute("INSERT INTO users (name) VALUES ('test_user')")
86
        conn.commit()
87

88
postgresql_with_func = factories.postgresql_proc(
89
    load=[load_test_data]
90
)
91
```
92

93
## Architecture
94

95
The plugin is organized around three main patterns:
96

97
- **Factory Functions**: Create pytest fixtures with configurable parameters
98
- **Executor Classes**: Manage PostgreSQL process lifecycle (start/stop/configure)  
99
- **Database Management**: Handle database creation, population, and cleanup
100

101
This design enables flexible testing scenarios from single-use databases to shared instances across test sessions, with automatic cleanup and isolation between tests.
102

103
## Capabilities
104

105
### Process Fixture Factory
106

107
Creates session-scoped fixtures that start and manage PostgreSQL server processes, with full control over server configuration, data directory management, and initialization.
108

109
```python { .api }
110
def postgresql_proc(
111
    executable: Optional[str] = None,
112
    host: Optional[str] = None,
113
    port: Optional[PortType] = -1,
114
    user: Optional[str] = None,
115
    password: Optional[str] = None,
116
    dbname: Optional[str] = None,
117
    options: str = "",
118
    startparams: Optional[str] = None,
119
    unixsocketdir: Optional[str] = None,
120
    postgres_options: Optional[str] = None,
121
    load: Optional[List[Union[Callable, str, Path]]] = None,
122
) -> Callable[[FixtureRequest, TempPathFactory], Iterator[PostgreSQLExecutor]]: ...
123
```
124

125
[Process Management](./process-management.md)
126

127
### No-Process Fixture Factory
128

129
Creates fixtures for connecting to existing PostgreSQL servers, ideal for containerized environments, CI systems, or when PostgreSQL is managed externally.
130

131
```python { .api }
132
def postgresql_noproc(
133
    host: Optional[str] = None,
134
    port: Union[str, int, None] = None,
135
    user: Optional[str] = None,
136
    password: Optional[str] = None,
137
    dbname: Optional[str] = None,
138
    options: str = "",
139
    load: Optional[List[Union[Callable, str, Path]]] = None,
140
) -> Callable[[FixtureRequest], Iterator[NoopExecutor]]: ...
141
```
142

143
[External Server Integration](./external-server.md)
144

145
### Client Fixture Factory
146

147
Creates database connection fixtures with automatic cleanup, transaction isolation, and database management between tests.
148

149
```python { .api }
150
def postgresql(
151
    process_fixture_name: str,
152
    dbname: Optional[str] = None,
153
    isolation_level: Optional[psycopg.IsolationLevel] = None,
154
) -> Callable[[FixtureRequest], Iterator[Connection]]: ...
155
```
156

157
[Database Connections](./database-connections.md)
158

159
### Configuration System
160

161
Comprehensive configuration system supporting command-line options, pytest.ini settings, and programmatic configuration for all aspects of PostgreSQL testing.
162

163
```python { .api }
164
class PostgresqlConfigDict(TypedDict):
165
    exec: str
166
    host: str
167
    port: Optional[str]
168
    port_search_count: int
169
    user: str
170
    password: str
171
    options: str
172
    startparams: str
173
    unixsocketdir: str
174
    dbname: str
175
    load: List[Union[Path, str]]
176
    postgres_options: str
177
    drop_test_database: bool
178

179
def get_config(request: FixtureRequest) -> PostgresqlConfigDict: ...
180
```
181

182
[Configuration Options](./configuration.md)
183

184
### Data Loading and Initialization
185

186
Flexible data loading system supporting SQL files, Python callables, and import strings for database initialization and test data setup.
187

188
```python { .api }
189
def build_loader(load: Union[Callable, str, Path]) -> Callable: ...
190

191
def sql(sql_filename: Path, **kwargs: Any) -> None: ...
192
```
193

194
[Data Loading](./data-loading.md)
195

196
## Types
197

198
```python { .api }
199
from typing import Union, List, Optional, Callable, Iterator, TypedDict
200
from pathlib import Path
201
from port_for import PortType
202
import psycopg
203
from psycopg import Connection
204
from pytest import FixtureRequest, TempPathFactory
205

206
# Exception types
207
class ExecutableMissingException(FileNotFoundError): ...
208
class PostgreSQLUnsupported(Exception): ...
209
```