Prepared Statements

In the previous lesson, you learned that parameterized queries separate SQL structure from data, making injection impossible. Prepared statements take this a step further by adding a performance optimization: the database compiles the SQL template once, then reuses the compiled plan for every subsequent execution with different parameters.

-- Step 1: Prepare the template (happens once)
PREPARE get_user AS SELECT * FROM users WHERE id = $1;

-- Step 2: Execute with different values (happens thousands of times)
EXECUTE get_user(1);   -- Returns Alice
EXECUTE get_user(2);   -- Returns Bob
EXECUTE get_user(3);   -- Returns Charlie
-- ... 10,000 more executions, each skipping the parse step

The first execution pays the full cost: parsing SQL, resolving references, choosing indexes, and generating an execution plan. This overhead is typically 0.5-2ms for simple queries and 5-10ms for complex joins. Every subsequent execution skips all of that and goes straight to execution. For a query running 10,000 times per second (a user lookup in a busy API), eliminating 10,000 parse cycles saves significant CPU time on the database server.

Most database drivers use prepared statements automatically when you use parameterized queries. The pg driver for Node.js, psycopg2 for Python, and JDBC for Java all prepare statements transparently. The distinction matters mainly for understanding: parameterization is the security mechanism, preparation is the performance optimization. In practice, they almost always come together.

Three strategies for running SQL in production, each with different performance, security, and operational characteristics.

Dynamic queries send the full SQL text on every call. The database parses, validates, plans, and executes from scratch each time. Parse cost: 0.5-2ms per query. This is fine for one-off admin queries or interactive SQL tools. For hot-path API endpoints handling thousands of requests per second, the parse overhead adds up.

Prepared statements compile the SQL template once and reuse the plan. Parse cost drops to effectively zero for subsequent executions. The plan is cached per connection, meaning it persists for the lifetime of the database connection. At 10,000 executions per second, you save 10,000 parse cycles, freeing substantial CPU on the database server.

Stored procedures live on the database server as named functions. They are compiled when created (CREATE FUNCTION), not on first call. Stored procedures can contain transactions, conditionals, loops, and error handling. They run inside the database, so complex multi-step operations avoid network round trips. The trade-off: business logic in the database is harder to test, harder to version control, and ties you to a specific database vendor.

Each has a place. Use dynamic queries for admin tools. Use prepared statements (which most drivers do automatically) for application queries. Use stored procedures when complex multi-step transactions must be atomic and you want to enforce business rules at the data layer.