Prepared Statements

Overview

Prepared statements allow you to parse and plan a query once, then execute it multiple times with different parameter values. This provides two key benefits:

  • Performance — The query plan is cached and reused, avoiding repeated parsing and planning overhead
  • Safety — Parameters are bound separately from the query text, preventing injection attacks

Table of Contents

PREPARE

Parses and plans a query, storing the result under a named handle for later execution.

Syntax

PREPARE statement_name AS query

Examples

-- Prepare a simple SELECT
PREPARE get_user AS SELECT * FROM "users" WHERE user_id == $1

-- Prepare with multiple parameters
PREPARE find_orders AS SELECT * FROM "orders"
    WHERE customer_id == $1 AND status == $2

-- Prepare an aggregate query
PREPARE count_by_status AS SELECT status, COUNT(*) FROM "orders"
    GROUP BY status WHERE status == $1

EXECUTE

Executes a previously prepared statement. Parameter values are supplied via the wire protocol layer, not inline in the SQL text.

Syntax

EXECUTE statement_name

The parameter values ($1, $2, etc.) are bound through the client protocol when sending the EXECUTE command. This keeps data values separate from query structure.

DEALLOCATE

Removes a prepared statement from the session, freeing its cached plan.

DEALLOCATE statement_name

After deallocating, the statement name can be reused for a new PREPARE.

Parameter Placeholders

Parameter placeholders use the $N syntax, where N is a 1-based index:

Placeholder Description
$1 First parameter
$2 Second parameter
$3 Third parameter, and so on

Parameters can be used anywhere a literal value would appear in the query:

-- In WHERE conditions
PREPARE q1 AS SELECT * FROM "users" WHERE age > $1

-- In multiple conditions
PREPARE q2 AS SELECT * FROM "products" WHERE price >= $1 AND price <= $2

-- In different clause types
PREPARE q3 AS SELECT * FROM "events" WHERE category == $1 ORDER BY date LIMIT $2

Plan Cache Behavior

SyndrDB uses an adaptive plan caching strategy inspired by PostgreSQL:

  • First 5 executions: A custom plan is generated for each set of parameter values, tailored to the specific data distribution
  • After 5 executions: The optimizer compares the cost of custom plans vs. a generic plan. If the generic plan is competitive, it is cached and reused for all future executions
  • Plan invalidation: Cached plans are lazily invalidated when the underlying bundle schema changes (fields added/removed, indexes created/dropped)

The plan cache is sharded (8 shards) with LRU eviction and uses xxhash for fast key lookup.

Naming Rules

  • Statement names must be alphanumeric characters and underscores only
  • Maximum length: 64 characters
  • Names are case-sensitive
  • Each session maintains its own namespace of prepared statements
  • Attempting to PREPARE with an already-used name returns an error — DEALLOCATE first

Complete Examples

Example 1: User Lookup

-- Prepare a reusable user lookup
PREPARE get_user_by_email AS SELECT * FROM "users" WHERE email == $1

-- Execute multiple times with different emails (params via protocol)
EXECUTE get_user_by_email   -- $1 = "alice@example.com"
EXECUTE get_user_by_email   -- $1 = "bob@example.com"
EXECUTE get_user_by_email   -- $1 = "carol@example.com"

-- Clean up when done
DEALLOCATE get_user_by_email

Example 2: Range Query

-- Prepare a price range query
PREPARE products_in_range AS SELECT name, price, category
    FROM "products"
    WHERE price >= $1 AND price <= $2
    ORDER BY price

-- Execute with different ranges (params via protocol)
EXECUTE products_in_range   -- $1 = 10.00, $2 = 50.00
EXECUTE products_in_range   -- $1 = 100.00, $2 = 500.00

DEALLOCATE products_in_range

Example 3: Prepared Statement in a Transaction

-- Prepare outside a transaction (plan is session-scoped)
PREPARE insert_log AS ADD DOCUMENT TO BUNDLE "audit_log" WITH ({
    "action" = $1,
    "user_id" = $2,
    "timestamp" = F:NOW()
})

BEGIN TRANSACTION

-- Execute the prepared statement multiple times within the transaction
EXECUTE insert_log   -- $1 = "login", $2 = 42
EXECUTE insert_log   -- $1 = "view_page", $2 = 42
EXECUTE insert_log   -- $1 = "logout", $2 = 42

COMMIT

-- Statement persists after transaction ends
DEALLOCATE insert_log

Quick Reference

Command Description
PREPARE name AS query Parse, plan, and store a query under a name
EXECUTE name Run a prepared statement (params via protocol)
DEALLOCATE name Remove a prepared statement from the session

Last updated: March 2026