Coverage for src / lexigram / contracts / data / sql / database.py: 3%
115 statements
« prev ^ index » next coverage.py v7.13.5, created at 2026-08-19 05:41 +0800
1"""Database provider protocols.
3These protocols define the contract for database implementations,
4enabling swappable database backends (PostgreSQL, MySQL, SQLite, MongoDB).
5"""
7from __future__ import annotations
9from dataclasses import dataclass
10from enum import StrEnum
11from typing import TYPE_CHECKING, Any, Protocol, runtime_checkable
13if TYPE_CHECKING:
14 from collections.abc import Iterator
15 from contextlib import AbstractAsyncContextManager
16 from datetime import datetime
18 from lexigram.contracts.core import HealthCheckResult
21class IsolationLevel(StrEnum):
22 """Standard SQL transaction isolation levels.
24 These map directly to the ANSI SQL standard isolation levels.
25 Drivers translate them to the appropriate driver-specific syntax.
27 Note:
28 Not all levels are supported by every database engine.
29 SQLite maps levels to its ``DEFERRED``/``IMMEDIATE``/``EXCLUSIVE``
30 semantics as a best-effort approximation.
31 """
33 READ_UNCOMMITTED = "READ UNCOMMITTED"
34 READ_COMMITTED = "READ COMMITTED"
35 REPEATABLE_READ = "REPEATABLE READ"
36 SERIALIZABLE = "SERIALIZABLE"
39@dataclass(frozen=True)
40class QueryResult:
41 """Result of a database query.
43 Implements the iterator and sequence protocols so that code expecting
44 a plain ``list[dict]`` from a query result continues to work after the
45 return type is normalised to ``QueryResult``.
47 Example::
49 result = await provider.execute_query("SELECT * FROM users")
50 for row in result: # iterate directly
51 print(row["email"])
52 if not result: # bool coercion
53 raise LookupError("no rows")
54 first = result[0] # index access
55 rows = list(result) # convert to plain list
56 """
58 rows: list[dict[str, Any]]
59 row_count: int
60 execution_time: float
61 success: bool
62 error_message: str | None = None
64 # ------------------------------------------------------------------
65 # Sequence-like helpers
66 # ------------------------------------------------------------------
68 def __iter__(self) -> Iterator[dict[str, Any]]:
69 """Iterate over query rows."""
70 return iter(self.rows)
72 def __len__(self) -> int:
73 """Return the number of rows in the result."""
74 return len(self.rows)
76 def __bool__(self) -> bool:
77 """Return ``True`` when the query succeeded and returned at least one row."""
78 return self.success and bool(self.rows)
80 def __getitem__(self, index: int) -> dict[str, Any]:
81 """Return the row at *index*.
83 Args:
84 index: Zero-based row index.
86 Returns:
87 Row dict at the requested index.
88 """
89 return self.rows[index]
92@runtime_checkable
93class ConnectionProtocol(Protocol):
94 """Protocol for database connections.
96 Represents an active database connection that can execute queries.
97 """
99 async def execute(
100 self,
101 query: str,
102 *args: Any,
103 timeout: float | None = None,
104 ) -> QueryResult:
105 """Execute a SQL query.
107 Args:
108 query: SQL query string with positional parameters ($1, $2, ...).
109 *args: Positional arguments for the query.
110 timeout: Optional timeout in seconds.
112 Returns:
113 QueryResult with rows and metadata.
114 """
115 ...
117 async def fetchrow(
118 self,
119 query: str,
120 *args: Any,
121 timeout: float | None = None,
122 ) -> dict[str, Any] | None:
123 """Fetch a single row from the database.
125 Args:
126 query: SQL query string.
127 *args: Positional arguments for the query.
128 timeout: Optional timeout in seconds.
130 Returns:
131 Row dictionary or None if not found.
132 """
133 ...
135 async def close(self) -> None:
136 """Close the connection."""
137 ...
139 async def fetch(
140 self,
141 query: str,
142 *args: Any,
143 **kwargs: Any,
144 ) -> list[dict[str, Any]]:
145 """Fetch rows from the database.
147 Args:
148 query: SQL query string.
149 *args: Positional query parameters.
150 **kwargs: Named query parameters.
152 Returns:
153 List of row dictionaries.
154 """
155 ...
158@runtime_checkable
159class DatabaseProviderProtocol(Protocol):
160 """Protocol for database providers.
162 This defines the interface that all database providers must implement,
163 regardless of the underlying database technology.
165 Example:
166 ```python
167 class PostgresProvider:
168 async def connect(self) -> None:
169 self._pool = await asyncpg.create_pool(self._dsn)
171 async def execute_query(
172 self,
173 sql: str,
174 params: list[Any] | None = None,
175 ) -> QueryResult:
176 async with self._pool.acquire() as conn:
177 rows = await conn.fetch(sql, *params or [])
178 return QueryResult(rows=list(map(dict, rows)), ...)
179 ```
180 """
182 # Connection lifecycle
183 async def connect(self) -> None:
184 """Establish connection to the database."""
185 ...
187 async def disconnect(self) -> None:
188 """Close connection to the database."""
189 ...
191 async def is_connected(self) -> bool:
192 """Check if database is connected."""
193 ...
195 async def get_primary_pool(self) -> ConnectionPoolProtocol:
196 """Return the primary connection pool.
198 For multi-backend setups, returns the backend marked `primary: true`.
199 Raises NoPrimaryBackendError if no primary is marked or zero backends.
201 Returns:
202 The primary connection pool.
204 Raises:
205 NoPrimaryBackendError: If no primary backend is configured.
206 """
207 ...
209 # Query execution
210 async def execute_query(
211 self,
212 sql: str,
213 params: list[Any] | None = None,
214 **kwargs: Any,
215 ) -> QueryResult:
216 """Execute a SELECT query.
218 Args:
219 sql: SQL query string.
220 params: Query parameters.
221 **kwargs: Additional options.
223 Returns:
224 QueryResult with rows and execution metadata.
225 """
226 ...
228 async def execute_insert(
229 self,
230 table: str,
231 data: dict[str, Any],
232 **kwargs: Any,
233 ) -> InsertResult:
234 """Execute an INSERT operation.
236 Args:
237 table: Table name.
238 data: Column-value mapping.
239 **kwargs: Additional options.
241 Returns:
242 InsertResult with inserted ID and influenced rows.
243 """
244 ...
246 async def execute_update(
247 self,
248 table: str,
249 data: dict[str, Any],
250 where_clause: str,
251 where_params: list[Any] | None = None,
252 **kwargs: Any,
253 ) -> UpdateResult:
254 """Execute an UPDATE operation.
256 Args:
257 table: Table name.
258 data: Column-value updates.
259 where_clause: WHERE condition.
260 where_params: Parameters for WHERE clause.
261 **kwargs: Additional options.
263 Returns:
264 UpdateResult with affected rows.
265 """
266 ...
268 async def execute_delete(
269 self,
270 table: str,
271 where_clause: str,
272 where_params: list[Any] | None = None,
273 **kwargs: Any,
274 ) -> DeleteResult:
275 """Execute a DELETE operation.
277 Args:
278 table: Table name.
279 where_clause: WHERE condition.
280 where_params: Parameters for WHERE clause.
281 **kwargs: Additional options.
283 Returns:
284 DeleteResult with affected rows.
285 """
286 ...
288 async def execute(
289 self,
290 sql: str,
291 params: Any = None,
292 ) -> QueryResult:
293 """Execute a raw SQL query with parameters.
295 Args:
296 sql: SQL query string.
297 params: Query parameters.
299 Returns:
300 QueryResult with execution results.
301 """
302 ...
304 # Transaction management
305 def transaction(
306 self, isolation_level: IsolationLevel | None = None
307 ) -> AbstractAsyncContextManager[Any]:
308 """Context manager for transactions.
310 Args:
311 isolation_level: Optional ANSI SQL isolation level. When ``None``
312 the driver's default isolation level is used.
314 Example:
315 ```python
316 async with db.transaction(isolation_level=IsolationLevel.SERIALIZABLE):
317 await db.execute("INSERT INTO ...")
318 await db.execute("UPDATE ...")
319 ```
320 """
321 ...
323 async def begin_transaction(self) -> None:
324 """Begin a transaction."""
325 ...
327 async def commit_transaction(self) -> None:
328 """Commit current transaction."""
329 ...
331 async def rollback_transaction(self) -> None:
332 """Rollback current transaction."""
333 ...
335 # Schema operations
336 async def table_exists(self, table_name: str) -> bool:
337 """Check if a table exists."""
338 ...
340 # Health check
341 async def health_check(self, timeout: float = 5.0) -> HealthCheckResult:
342 """Perform health check on database connection."""
343 ...
345 # Scoped session management
346 def scoped_context(self) -> AbstractAsyncContextManager[Any]:
347 """Return an async context manager that establishes a scoped session.
349 The scoped context binds a database session to the current
350 async context so that ``get_scoped_connection`` can retrieve it
351 without passing the connection around explicitly.
353 Returns:
354 Async context manager that yields no value (or the session).
355 """
356 ...
358 async def get_scoped_connection(self) -> ConnectionProtocol:
359 """Return the connection bound to the current scoped context.
361 Must be called within an active :meth:`scoped_context` block.
363 Returns:
364 Active :class:`ConnectionProtocol` for the current scope.
365 """
366 ...
368 # Connection pool acquisition (low-level)
369 async def acquire(self) -> ConnectionProtocol:
370 """Acquire a connection from the pool for manual management.
372 Use this when you need fine-grained control over connection lifecycle,
373 but prefer :meth:`scoped_context` when possible for automatic cleanup.
375 Returns:
376 An acquired connection that must be released via :meth:`release`.
378 Example:
379 ```python
380 conn = await db.acquire()
381 try:
382 result = await conn.execute("SELECT * FROM users")
383 finally:
384 await db.release(conn)
385 ```
386 """
387 ...
389 async def release(self, connection: ConnectionProtocol) -> None:
390 """Release a connection back to the pool.
392 Args:
393 connection: Connection acquired via :meth:`acquire`.
394 """
395 ...
398@dataclass(frozen=True)
399class InsertResult:
400 """Result of an insert operation."""
402 inserted_id: Any | None
403 affected_rows: int
404 execution_time: float
405 success: bool
406 error_message: str | None = None
409@dataclass(frozen=True)
410class UpdateResult:
411 """Result of an update operation."""
413 affected_rows: int
414 execution_time: float
415 success: bool
416 error_message: str | None = None
419@dataclass(frozen=True)
420class DeleteResult:
421 """Result of a delete operation."""
423 affected_rows: int
424 execution_time: float
425 success: bool
426 error_message: str | None = None
429@runtime_checkable
430class ConnectionPoolProtocol(Protocol):
431 """Protocol for connection pools."""
433 @property
434 def max_connections(self) -> int: ...
436 @property
437 def connection_timeout(self) -> float: ...
439 async def initialize(self) -> None:
440 """Initialize the connection pool."""
441 ...
443 async def shutdown(self) -> None:
444 """Shutdown the connection pool."""
445 ...
447 def get_connection(self) -> AbstractAsyncContextManager[Any]:
448 """Get a connection from the pool."""
449 ...
451 async def get_pool_stats(self) -> dict[str, Any]:
452 """Get pool statistics."""
453 ...
455 async def health_check(self, timeout: float = 5.0) -> HealthCheckResult:
456 """Check pool health."""
457 ...
459 async def get_query_stats(self, time_range_seconds: int = 3600) -> dict[str, Any]:
460 """Get query statistics."""
461 ...
463 async def warm(self, count: int | None = None) -> None:
464 """Pre-create *count* connections to avoid cold-start latency.
466 Args:
467 count: Number of connections to open. Defaults to ``min_connections``
468 (or the pool minimum) if not specified.
469 """
470 ...
472 async def validate_connections(self) -> int:
473 """Validate all idle connections in the pool, evicting dead ones.
475 Returns:
476 Number of valid connections remaining after validation.
477 """
478 ...
481@dataclass
482class MigrationRecord:
483 """Record of a database migration."""
485 version: str
486 name: str
487 applied_at: datetime
488 success: bool
489 error_message: str | None
492@runtime_checkable
493class MigrationManagerProtocol(Protocol):
494 """Protocol for migration management."""
496 async def initialize_migration_table(self) -> None:
497 """Initialize the migration tracking table."""
498 ...
500 async def get_applied_migrations(self) -> list[MigrationRecord]:
501 """Get list of applied migrations."""
502 ...
504 async def apply_migration(self, version: str, name: str, sql: str) -> bool:
505 """Apply a migration."""
506 ...
508 async def rollback_migration(self, version: str) -> bool:
509 """Rollback a migration."""
510 ...
512 async def get_pending_migrations(
513 self,
514 available_migrations: list[str],
515 ) -> list[str]:
516 """Get migrations that haven't been applied yet."""
517 ...
520# ---------------------------------------------------------------------------
521# Focused sub-protocols (D1.2)
522#
523# Code that only needs one concern should depend on the narrowest protocol:
524#
525# - TransactionManagerProtocol — begin / commit / rollback only
526# - SchemaManagerProtocol — DDL / table inspection
527# - CrudOperationsProtocol — raw query execution
528# - HealthMonitorProtocol — health-check only
529#
530# DatabaseProviderProtocol satisfies all four.
531# ---------------------------------------------------------------------------
534@runtime_checkable
535class TransactionManagerProtocol(Protocol):
536 """Minimal protocol for managing database transactions.
538 Consume this instead of :class:`DatabaseProviderProtocol` when you only
539 need transaction demarcation (e.g. a Unit of Work decorator).
540 """
542 async def begin_transaction(self) -> None:
543 """Begin a new transaction."""
544 ...
546 async def commit_transaction(self) -> None:
547 """Commit the current transaction."""
548 ...
550 async def rollback_transaction(self) -> None:
551 """Roll back the current transaction."""
552 ...
555@runtime_checkable
556class SchemaManagerProtocol(Protocol):
557 """Minimal protocol for DDL / schema inspection.
559 Consume this instead of :class:`DatabaseProviderProtocol` when you only
560 need to inspect or modify the schema (e.g. a migration runner).
561 """
563 async def table_exists(self, table_name: str) -> bool:
564 """Return ``True`` if the named table exists."""
565 ...
568@runtime_checkable
569class CrudOperationsProtocol(Protocol):
570 """Minimal protocol for raw CRUD query execution.
572 Consume this instead of :class:`DatabaseProviderProtocol` when you only
573 need to execute queries and DML statements (e.g. a generic repository).
575 All methods return typed result objects so callers do not need to parse
576 raw driver-specific return values.
577 """
579 async def execute_query(
580 self,
581 query: str,
582 params: Any = None,
583 *,
584 timeout: float | None = None,
585 ) -> QueryResult:
586 """Execute a SELECT (or any read) query."""
587 ...
589 async def execute_insert(
590 self,
591 table: str,
592 data: dict[str, Any],
593 *,
594 returning: list[str] | None = None,
595 ) -> InsertResult:
596 """Execute an INSERT statement."""
597 ...
599 async def execute_update(
600 self,
601 table: str,
602 data: dict[str, Any],
603 conditions: dict[str, Any],
604 *,
605 returning: list[str] | None = None,
606 ) -> UpdateResult:
607 """Execute an UPDATE statement."""
608 ...
610 async def execute_delete(
611 self,
612 table: str,
613 conditions: dict[str, Any],
614 ) -> DeleteResult:
615 """Execute a DELETE statement."""
616 ...
619@runtime_checkable
620class HealthMonitorProtocol(Protocol):
621 """Minimal protocol for database health checking.
623 Consume this instead of :class:`DatabaseProviderProtocol` when you only
624 need to probe liveness (e.g. a health endpoint or a readiness probe).
625 """
627 async def health_check(self, timeout: float = 5.0) -> HealthCheckResult:
628 """Return the current health of the database connection."""
629 ...
632@runtime_checkable
633class DatabaseMetricsProtocol(Protocol):
634 """Minimal protocol for exposing database connection-pool metrics.
636 Consume this instead of :class:`DatabaseProviderProtocol` when you only
637 need to collect pool telemetry (e.g. a metrics exporter or health dashboard).
639 Implementations should return at least the following keys in the dict
640 returned by :meth:`get_pool_stats`, though they may include additional
641 driver-specific entries:
643 * ``active_connections`` – number of connections currently in use.
644 * ``idle_connections`` – number of connections available in the pool.
645 * ``wait_time_ms`` – average time (ms) callers waited for a connection.
646 """
648 async def get_pool_stats(self) -> dict[str, int | float]:
649 """Return pool statistics keyed by metric name.
651 Returns:
652 A dict containing at minimum ``active_connections``,
653 ``idle_connections``, and ``wait_time_ms`` as ``int`` or
654 ``float`` values.
655 """
656 ...
659__all__ = [
660 "ConnectionPoolProtocol",
661 "ConnectionProtocol",
662 "CrudOperationsProtocol",
663 "DatabaseMetricsProtocol",
664 "DatabaseProviderProtocol",
665 "DeleteResult",
666 "HealthMonitorProtocol",
667 "InsertResult",
668 "IsolationLevel",
669 "MigrationManagerProtocol",
670 "MigrationRecord",
671 "QueryResult",
672 "SchemaManagerProtocol",
673 "TransactionManagerProtocol",
674 "UpdateResult",
675]