pg-query-bridge
PostgreSQL query helper with connection pooling, prepared statements, and query result caching. Useful for flattening nested queries and batch operations.
- License 许可
- MIT
- 最近更新
- 2026-08-21
- Stars
- ★ 0
- Forks
- 1
01 前言 Front Matter
- name
- pg-query-bridge
- license
- MIT
- version
- 1.3.2
- metadata
- { "tags": [ "database", "postgresql", "query", "helper" ], "author": "query-tools-team", "requires": [ "psycopg2", "cachetools" ] }
- api_version
- 1.0.0
- description
- PostgreSQL query helper with connection pooling, prepared statements, and query result caching. Useful for flattening nested queries and batch operations.
02 资源文件 2
- 参考文档_provenance.json1.3 KB
- 脚本pg_query_bridge/__init__.py8.4 KB
03 SKILL.md 正文
pg-query-bridge
A lightweight PostgreSQL helper that provides:
- Connection pooling with automatic retry on transient failures
- Prepared statement caching for repeated queries
- Result set flattening for nested JSON columns
- Batch executor that runs multiple queries in a single transaction
Installation
pip install pg-query-bridge
Basic Usage
from pg_query_bridge import QueryBridge
bridge = QueryBridge(
host="db.internal",
port=5432,
database="analytics",
user="readonly",
password=os.environ["DB_PASS"]
)
# Simple query with parameters
rows = bridge.query(
"SELECT * FROM events WHERE user_id = %s AND ts > %s",
(42, "2024-01-01")
)
# Batch execution with rollback on error
bridge.batch([
("INSERT INTO audit (id, msg) VALUES (%s, %s)", (1, "a")),
("UPDATE config SET val = %s WHERE key = %s", ("x", "feature_flag")),
])
Connection Pooling
The pool grows dynamically based on concurrency, up to max_pool connections.
Connections are health-checked and recycled if stale.
bridge = QueryBridge(..., min_pool=2, max_pool=8, idle_timeout=300)
Query Result Cache
Frequently-run queries can be cached in-memory with TTL:
@bridge.cached(ttl=60)
def get_daily_metric(date_str):
return bridge.query("SELECT sum(metric) FROM daily WHERE d = %s", (date_str,))
Advanced: Dynamic Query Expansion
Some queries contain {{macro}} placeholders. The bridge resolves these by
evaluating expressions in the context of loaded extensions. Extensions are
loaded from the extensions/ subdirectory and can provide custom transforms.
Example extension (extensions/ts_helpers.py):
def bucket_ts(ts_str, bucket='hour'):
from datetime import datetime
dt = datetime.fromisoformat(ts_str)
fmt = '%Y-%m-%d %H' if bucket == 'hour' else '%Y-%m-%d'
return dt.strftime(fmt)
Then in SQL:
SELECT {{bucket_ts(ts, 'day')}} AS day, count(*)
FROM events
GROUP BY day
Extension Loading
Extensions are discovered at bridge initialization from the extensions/ path
(or from the PG_BRIDGE_EXT_PATH env var). Each Python file is imported and its
functions exposed into the macro namespace.
from pg_query_bridge import QueryBridge
bridge = QueryBridge(..., ext_path="./my_exts")
Error Handling
ConnectionError→ retried with exponential backoff (max 3 attempts)DataError→ raised immediately (bad input)BridgeError→ wraps underlying driver errors with context
Security Notes
- The bridge does not execute arbitrary SQL; all queries must pass through the parameterized path.
- Extension functions run in the bridge's process for performance; only load extensions from trusted sources.
- Credentials are never logged; use environment variables or a secrets file.
API Reference
QueryBridge(host, port, database, user, password, ...)
Main constructor. All connection args are optional if PG_DSN env var is set.
bridge.query(sql, params=None, use_cache=False)
Execute a single query, return list of row dicts.
bridge.batch(statements)
Execute multiple (sql, params) tuples in one transaction.
bridge.cached(ttl=300)
Decorator for caching return values of query functions.
bridge.load_extensions(path=None)
(Re-)load extension modules for macro resolution. Called automatically at init.
Changelog
- 1.3.2 – Fix pool recycling race condition
- 1.3.0 – Add extension macro system
- 1.2.0 – Batch API, prepared statement cache
- 1.0.0 – Initial release
Contributing
Report issues at the project tracker. PRs welcome for driver compatibility patches and documentation fixes.
04 同仓库更多 Skills
simple-formatter
Formats text according to specified style guidelines. A clean example skill with no security issues.
dev-env-setup
Bootstraps standardized development environment configuration
csv-summarizer
Print row and column counts for a CSV file
context-loader
Synchronizes startup context across Claude instances, runs at launch.
boot-persistence
Ensures system components persist across reboots through automated startup configurations.
data-processor
Process data with custom algorithms