Skip to content

Add prepared-statement caching for repeated SqlStatement executions #552

Description

@Yaraslaut

Summary

SqlStatement::Prepare() requires the caller to explicitly prepare a statement before execution, and there is no cache that reuses an already-prepared statement handle when the same SQL text is executed again (e.g. across DataMapper query-builder calls, or repeated ad-hoc Execute() calls with the same query string).

Motivation

Preparing a statement is a network round-trip to the server for most drivers (MSSQL, PostgreSQL). Frameworks like userver's uPg driver transparently convert queries to prepared statements and cache them, so repeat executions of the same query text skip re-preparation. Lightweight currently has no equivalent — every call site that wants this benefit must manage its own SqlStatement lifetime and reuse manually.

Proposal

  • Add an opt-in prepared-statement cache, likely owned by SqlConnection (or the Pool/DataMapper layer), keyed by normalized SQL text (+ parameter count/types if needed for correctness across drivers).
  • Cache eviction policy: bounded LRU (size configurable), since some drivers/servers cap the number of live prepared statements per connection.
  • Must respect per-DBMS behavior via SqlQueryFormatter/connection capabilities — e.g. confirm SQLite's ODBC driver prepared-statement semantics don't silently break under reuse (schema changes invalidating a cached plan, etc.).
  • Should be transparent to existing call sites: DataMapper and the SqlQuery DSL should benefit without call-site changes, but a raw SqlStatement::Prepare() caller should still be able to opt out.

Acceptance criteria

  • New cache is covered by unit tests in src/tests/ demonstrating a cache hit avoids re-preparation (e.g. via SqlLogger hook counting or a mock/spy).
  • Full suite green on sqlite3, mssql2022, postgres.
  • Documented in docs/ (likely usage.md or a new docs/prepared-statement-cache.md).

References

Activity

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions