Testing stored procedures (2026)

TL;DR

Testing stored procedures means controlling the starting data, calling the procedure through the same driver used by production, and asserting outputs, database side effects, errors, permissions, and concurrency behavior. Favor deterministic state assertions over timing-only checks, then run the suite against the exact database engine in CI.

Key Takeaways

• Treat a stored procedure as a contract with inputs, outputs, side effects, errors, and permissions.
• Run every test from a known database state and roll back test data whenever isolation allows it.
• Verify persisted rows, audit events, and rejected changes, not only return values.
• Use separate database sessions for lock, race, deadlock, and isolation tests.
• Check execution plans and query counts with representative data instead of fragile wall-clock limits.
• Run migration, procedure, and integration checks together in CI against the real database engine.

A reliable approach to testing stored procedures is most effective when you treat each procedure as a public database contract, not as a hidden SQL script. Start from known data, call the procedure through a real database connection, and verify its returned values, committed changes, rejected changes, errors, audit records, permissions, and behavior under concurrent calls.

A green happy-path query is only the beginning. Procedures often concentrate financial rules, inventory changes, bulk imports, and authorization boundaries. A small mistake can leave several tables inconsistent even when the caller receives a success response. This guide builds a repeatable approach for PostgreSQL examples, while clearly identifying the checks that transfer to SQL Server, MySQL, and Oracle.

TL;DR

The core loop is Arrange -> CALL -> Assert -> Clean up. Test both what changed and what must remain unchanged.

1. A contract-first approach to testing stored procedures

Before writing cases, define what the caller can observe. A stored procedure contract includes input types, accepted ranges, default handling, output parameters or result sets, affected tables, transactional guarantees, error codes, and required privileges. Documentation that says only apply credit is not testable enough. State whether a zero amount is rejected, whether a missing account returns no rows or raises an error, and whether an audit row is written in the same transaction.

Build a small contract table during refinement:

This contract prevents a common testing failure: asserting implementation details while missing business consequences. Column scan order or a particular join is rarely the contract. The final balance, audit entry, error classification, and access boundary usually are. When requirements are unclear, encode the accepted decision in the test name so a later reviewer can see the intended rule.

For broader database fundamentals, review database constraint testing techniques. Constraints and procedures should reinforce each other, but they test different failure boundaries.

2. Build a deterministic stored procedure test fixture

A deterministic fixture creates only the rows needed by a scenario and uses stable identifiers. Avoid copying a full production backup into a routine test suite. Large shared datasets hide dependencies, slow feedback, and make failures depend on execution order. Use migrations to create the schema, then seed a narrow baseline inside the test transaction.

The following PostgreSQL setup is runnable in psql. It defines a procedure whose balance update and audit insert must succeed or fail together.

CREATE TABLE accounts (
  account_id bigint PRIMARY KEY,
  balance numeric(12, 2) NOT NULL CHECK (balance >= 0)
);

CREATE TABLE account_audit (
  audit_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  account_id bigint NOT NULL REFERENCES accounts(account_id),
  amount numeric(12, 2) NOT NULL,
  actor text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE OR REPLACE PROCEDURE apply_credit(
  p_account_id bigint,
  p_amount numeric
)
LANGUAGE plpgsql
AS $
BEGIN
  IF p_amount <= 0 THEN
    RAISE EXCEPTION USING
      ERRCODE = '22023',
      MESSAGE = 'amount must be positive';
  END IF;

  UPDATE accounts
     SET balance = balance + p_amount
   WHERE account_id = p_account_id;

  IF NOT FOUND THEN
    RAISE EXCEPTION USING
      ERRCODE = 'P0002',
      MESSAGE = 'account not found';
  END IF;

  INSERT INTO account_audit(account_id, amount, actor)
  VALUES (
    p_account_id,
    p_amount,
    COALESCE(NULLIF(current_setting('app.actor', true), ''), 'system')
  );
END;
$;

Keep schema creation in migrations rather than duplicating production DDL inside tests. The example is presented together for learning, but a real suite should apply the same migration artifact used by deployment. Seed data can remain test-owned. SQL test data setup and teardown covers reusable cleanup patterns.

3. Design SQL stored procedure test cases by risk

A useful stored procedure testing checklist is risk-based, not a list of random values. Partition inputs into valid classes, boundaries, invalid classes, and relationship failures. For an amount, test the smallest supported positive value, a typical value, the numeric precision boundary, zero, a negative value, and a value that exceeds the declared precision. For an identifier, test an existing row, a missing row, and a value belonging to a different tenant if the schema is multi-tenant.

Map each branch to a business outcome:

• Normal call changes every intended row exactly once.
• Boundary call preserves decimal precision and rounding rules.
• Invalid amount produces the agreed SQLSTATE and no side effects.
• Missing parent produces the agreed not-found behavior.
• A downstream constraint failure rolls back earlier statements.
• A repeated call either applies again or is rejected according to the idempotency contract.
• Null inputs follow the declared rule rather than accidental three-valued SQL logic.

Do not use code coverage as the only completion criterion. A procedure can execute every line while never proving atomicity, role isolation, or correct totals. Pair branch coverage with mutation thinking: if the update operator changed from plus to minus, which assertion would fail? If the audit insert disappeared, would the suite notice? If the WHERE clause lost the account ID, would a test detect updates to other accounts?

Test names should state the condition and outcome, such as negative_amount_rejected_without_balance_or_audit_change. That name is a compact specification and speeds triage in CI.

4. Execute direct SQL tests inside a rollback boundary

Direct SQL tests give the fastest feedback because they remove application serialization and service layers. Wrap the scenario in a transaction, arrange the fixture, call the routine, query all affected objects, and roll back. This keeps a developer database clean and makes the test repeatable.

BEGIN;

INSERT INTO accounts(account_id, balance) VALUES (1001, 100.00);
SET LOCAL app.actor = 'qa-sql-test';

CALL apply_credit(1001, 25.00);

DO $
DECLARE
  actual_balance numeric(12, 2);
  audit_count integer;
BEGIN
  SELECT balance INTO actual_balance
    FROM accounts WHERE account_id = 1001;
  SELECT count(*) INTO audit_count
    FROM account_audit WHERE account_id = 1001;

  IF actual_balance <> 125.00 THEN
    RAISE EXCEPTION 'expected balance 125.00, got %', actual_balance;
  END IF;
  IF audit_count <> 1 THEN
    RAISE EXCEPTION 'expected one audit row, got %', audit_count;
  END IF;
END $;

ROLLBACK;

For negative paths, capture SQLSTATE in a nested PL/pgSQL block and fail if the expected error does not occur. Always assert state afterward. Merely seeing an exception does not prove an earlier update was reversed. If the procedure controls transactions internally, the rollback strategy may differ by database engine. SQL Server tests often use an outer transaction when the procedure does not commit independently. Oracle callers need to account for procedures that explicitly commit. PostgreSQL transaction control is allowed only in procedures called in suitable top-level contexts, so keep transaction ownership explicit in the design.

A database unit framework such as pgTAP or tSQLt can make assertions easier, but the essential pattern does not depend on a framework. The suite must fail with a nonzero process result when an assertion fails.

5. Test stored procedures with Python and a production-grade driver

Driver-level tests catch parameter binding, decimal conversion, result handling, connection behavior, and SQLSTATE mapping that direct scripts cannot. With PostgreSQL, Psycopg 3 and pytest provide a concise integration layer. Install dependencies with python -m pip install pytest psycopg[binary], set TEST_DATABASE_URL, and run pytest.

import os
from decimal import Decimal

import psycopg
import pytest


@pytest.fixture
def db():
    conn = psycopg.connect(os.environ["TEST_DATABASE_URL"])
    conn.execute("BEGIN")
    yield conn
    conn.rollback()
    conn.close()


def test_apply_credit_updates_balance_and_audit(db):
    db.execute(
        "INSERT INTO accounts(account_id, balance) VALUES (%s, %s)",
        (1001, Decimal("100.00")),
    )
    db.execute("SET LOCAL app.actor = %s", ("pytest",))

    db.execute("CALL apply_credit(%s, %s)", (1001, Decimal("25.00")))

    balance = db.execute(
        "SELECT balance FROM accounts WHERE account_id = %s", (1001,)
    ).fetchone()[0]
    audit = db.execute(
        "SELECT amount, actor FROM account_audit WHERE account_id = %s",
        (1001,),
    ).fetchone()

    assert balance == Decimal("125.00")
    assert audit == (Decimal("25.00"), "pytest")


def test_apply_credit_rejects_negative_amount_without_side_effects(db):
    db.execute(
        "INSERT INTO accounts(account_id, balance) VALUES (%s, %s)",
        (1002, Decimal("80.00")),
    )

    with pytest.raises(psycopg.Error) as error:
        db.execute("CALL apply_credit(%s, %s)", (1002, Decimal("-1.00")))

    assert error.value.sqlstate == "22023"
    db.rollback()

The negative test rolls back after the exception because PostgreSQL marks the transaction as failed. For a stronger atomicity assertion, use a savepoint around the call, roll back to that savepoint after catching the error, then query the fixture in the outer transaction. Never build the CALL string with interpolation. Bound parameters protect the test itself and reproduce application behavior accurately.

6. Verify result sets, output parameters, and side effects

Different engines expose procedure results differently. SQL Server procedures may emit result sets, return codes, and output parameters. MySQL procedures can return multiple result sets. Oracle commonly uses OUT parameters or reference cursors. PostgreSQL procedures use INOUT parameters, while functions are more common when rows must be returned. Your test harness must consume every expected result in the same order as the production driver.

Snapshotting an entire result set can be useful for a stable reporting procedure, but targeted assertions usually produce better diagnostics. Assert mandatory columns by name, decimal and timestamp types, null semantics, and deterministic ordering only when the contract promises an order. SQL does not guarantee row order without ORDER BY.

For side effects, take a before and after view of every table the procedure owns. Check row counts, business keys, audit metadata, and rows that must not change. If events are placed in an outbox table, verify the event type, aggregate ID, payload schema, and uniqueness key in the same transaction as the domain update. This closes the gap between database correctness and downstream integration.

7. Prove transaction rollback and failure recovery

Atomicity tests deliberately force a later statement to fail after an earlier statement could have changed data. For example, create an audit constraint that rejects a test actor, call the procedure, then prove the account balance stayed unchanged. Another approach is to call a procedure with data that passes initial validation but violates a downstream foreign key or unique constraint. The chosen fault must be controlled and understandable, not a random database outage.

Use four assertions for a rollback scenario:

1. The caller receives the expected error category.
2. The primary table remains at its pre-call state.
3. Secondary and audit tables contain no partial rows.
4. A later valid transaction succeeds, proving the connection or pool recovers correctly.

Also test caller-driven rollback. A procedure may succeed, but the surrounding service transaction can fail afterward. The procedure's changes should disappear if it participates in the caller transaction. If the routine intentionally uses an autonomous transaction, document and test that exception because it changes recovery semantics.

Avoid asserting full vendor error text. Messages vary across engine versions and can include object names. Prefer a stable SQLSTATE, vendor error number, or application-defined code, then check only a safe message fragment if users depend on it. This same approach improves API error and negative testing when database errors are translated at a service boundary.

Finally, rerun the valid call after the induced failure. Many defects appear not in the failed operation, but in leaked session settings, unclosed cursors, stale temporary tables, or a connection returned to the pool in an aborted state.

8. Test concurrency, locking, and isolation behavior

Concurrency testing requires multiple independent connections. Two cursors on one transaction do not model competing users. Define the invariant first, such as ten successful credits of 2.00 increase the balance by exactly 20.00. Then release several callers together, record each outcome, and assert the final database state.

from concurrent.futures import ThreadPoolExecutor
from decimal import Decimal
import os
import psycopg


def credit_once(account_id: int) -> None:
    with psycopg.connect(os.environ["TEST_DATABASE_URL"]) as conn:
        conn.execute("CALL apply_credit(%s, %s)", (account_id, Decimal("2.00")))


def test_concurrent_credits_are_not_lost(admin_db):
    admin_db.execute(
        "INSERT INTO accounts(account_id, balance) VALUES (%s, %s)",
        (2001, Decimal("100.00")),
    )
    admin_db.commit()

    with ThreadPoolExecutor(max_workers=5) as pool:
        list(pool.map(credit_once, [2001] * 10))

    balance = admin_db.execute(
        "SELECT balance FROM accounts WHERE account_id = %s", (2001,)
    ).fetchone()[0]
    assert balance == Decimal("120.00")

This example assumes admin_db points to a disposable database and performs explicit cleanup because commits are necessary for cross-session visibility. A production suite should generate a unique account ID and delete it in a finally block.

Add scenarios for two transfers in opposite order, lock timeouts, serialization failures, and retry behavior when those risks exist. Do not declare any deadlock a product failure. Databases can legitimately abort one transaction to resolve a cycle. The contract should state whether the application retries safe operations, how many attempts are allowed, and whether an idempotency key prevents double application. Measure final invariants and classified outcomes, not thread completion order, because scheduling is nondeterministic.

9. Cover permissions, injection resistance, and sensitive data

Stored procedures are often a security boundary. Test with the actual application role, a read-only role, and an unauthorized role. Confirm the application can execute only the intended routines, cannot directly mutate protected tables if the design forbids it, and cannot access data for another tenant. A test run as the schema owner proves functionality but says nothing about deployment permissions.

Review whether the routine runs with invoker or definer rights. Definer-rights routines need special scrutiny of search_path, object qualification, and dynamic SQL. If dynamic identifiers are necessary, use the engine's safe identifier quoting functions and an allowlist. Bound values do not make a dynamically interpolated table or column name safe.

Security cases should include:

• Attempted execution by a role without EXECUTE.
• Cross-tenant identifiers paired with a valid user session.
• Text containing quotes and SQL metacharacters passed as data.
• Oversized inputs and unexpected Unicode.
• Error paths that must not reveal SQL text, secrets, or internal schema names.
• Audit attribution that cannot be freely forged by an untrusted caller.

Run destructive security cases only in an authorized test environment. The objective is to prove controls, not to spray generic payloads at shared systems. A procedure is not automatically safe simply because callers use CALL; unsafe dynamic SQL inside the routine remains injectable. Pair these checks with SQL injection testing guidance for service-layer coverage.

10. Assess performance without brittle stopwatch assertions

Performance tests should detect plan regressions and scaling problems without failing because a shared CI runner was briefly slow. Create representative data distributions, including skew, nulls, hot keys, and realistic row counts. Warm up connections, record the query plan, and separate database execution from network and fixture time.

For PostgreSQL, run the underlying statements or a representative call with EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) in a dedicated non-production environment. Inspect whether intended indexes are used, row estimates are plausible, and buffer reads grow as expected. SQL Server teams can capture actual execution plans and Query Store evidence. Oracle teams can use execution plans and SQL trace tooling appropriate to their environment.

Prefer stable gates such as these:

• No unexpected sequential scan on a high-cardinality production-sized table.
• Logical reads remain within an agreed budget for a fixed dataset.
• Batch size growth does not cause superlinear work without explanation.
• Lock duration and blocked-session count remain acceptable under a controlled load.
• Plans remain correct for common and skewed parameter values.

Wall-clock percentiles still matter in a performance environment, but do not invent universal thresholds. Establish a baseline on controlled infrastructure, version the dataset, and compare like with like. Include procedure definition hash, database version, plan, and dataset identifier in artifacts so a regression can be reproduced. Test parameter-sensitive plans with several meaningful values rather than a single convenient ID.

11. Automating testing stored procedures in CI and migrations

Testing stored procedures in CI should begin from an empty, disposable instance of the same engine and major version used in production. Apply every migration in order, load the smallest reference data, run direct database tests, run driver integration tests, and destroy the instance. A container is useful for PostgreSQL, SQL Server, MySQL, and Oracle Free when licensing and runner resources permit it. An in-memory substitute does not reproduce stored procedure syntax, optimizer behavior, locking, or permissions.

A practical pipeline order is:

1. Start the database and wait for a real readiness query.
2. Apply migrations using the production migration tool.
3. Create test roles and grants through versioned scripts.
4. Run fast SQL contract tests.
5. Run driver and service integration tests.
6. Run selected concurrency and plan checks.
7. Publish logs, failed SQLSTATE values, and plans with secrets removed.
8. Destroy all test data even when the job fails.

Also test upgrade paths. A clean install can pass while an ALTER PROCEDURE, changed signature, or replaced dependent view fails against an older schema. For high-risk releases, restore a sanitized snapshot at the previous schema version, apply new migrations, and run the same contracts. Detect callers that still use an old parameter order or expect a removed result column.

Keep procedure tests close to migrations in ownership and review. A schema change and its new assertions should land together. This turns the suite into executable compatibility documentation rather than a separate QA afterthought.

Interview Questions and Answers

Q: How would you approach testing stored procedures?

I define the routine's inputs, outputs, errors, side effects, transaction behavior, and permissions as a contract. I create minimal deterministic fixtures, call the procedure through direct SQL and the production driver, and assert both changed and unchanged state. I then add boundary, failure, concurrency, security, and migration cases according to risk.

Q: Why is checking only the return value insufficient?

A successful return can hide partial updates, missing audit rows, or changes to unrelated records. Stored procedures commonly own several database side effects. The test must query each owned table and verify atomicity and scope.

Q: How do you test rollback behavior?

I induce a controlled failure after an earlier statement could have executed, capture the stable error code, and query all affected tables from a usable transaction. I verify that no partial state remains and that a later valid call succeeds. Savepoints are helpful when the database marks the transaction failed after an error.

Q: How do you test a stored procedure for race conditions?

I use multiple independent connections, synchronize calls around a known starting state, and assert a business invariant in the final state. I record successful calls, classified retries, and failures rather than expecting a deterministic thread order. I also exercise lock timeout or serialization handling when the application promises retries.

Q: Should stored procedure tests use mocks?

Mocks can test application branching around a database adapter, but they cannot validate SQL syntax, plans, constraints, locks, or transaction behavior. The procedure itself should be tested on the real engine. A disposable container or isolated schema is usually the right integration boundary.

Q: What makes a stored procedure performance assertion reliable?

A reliable check uses a versioned representative dataset and controlled infrastructure. It examines plans, logical work, row estimates, and scaling behavior before adding elapsed-time gates. The evidence must include enough environment and plan information to reproduce a regression.

Q: How do you prevent stored procedure tests from polluting data?

I use a disposable database when possible. Fast component tests run inside transactions that roll back, while committed concurrency tests use unique keys and guaranteed cleanup. Suites never share mutable fixture identifiers across parallel workers.

Common Mistakes

• Testing only a normal input and calling the routine covered.
• Running every test as a database owner, which hides missing or excessive grants.
• Asserting an exception without proving earlier statements rolled back.
• Sharing mutable seed rows across parallel tests.
• Comparing full vendor error messages instead of stable codes.
• Using SQLite or a mock to represent engine-specific stored procedure behavior.
• Setting tight wall-clock thresholds on noisy CI runners.
• Ignoring unchanged neighboring rows, audit data, and outbox events.
• Assuming repeated execution is safe without an explicit idempotency rule.
• Updating procedure code without testing upgrade compatibility from the previous schema.

A strong review asks, What bad database state could this procedure create? Each credible answer should map to an assertion or an operational control. Keep test data recognizable, minimal, and isolated so a failure explains itself.

Conclusion

Testing stored procedures well means proving a database contract across values, side effects, transactions, roles, concurrent sessions, and migrations. Direct SQL checks provide fast feedback, driver tests expose integration errors, and controlled concurrency and plan tests cover risks that mocks cannot reproduce.

Choose one high-value procedure, document its observable contract, and build a deterministic happy path plus a rollback case first. Add security, concurrency, and performance evidence based on its production risk, then run the complete suite against a clean instance in CI.

Interview Questions and Answers

Frequently Asked Questions

Related Guides

• Accessibility testing checklist (2026)
• Accessibility testing with Cypress (2026)
• Accessibility testing with Playwright (2026)
• Agentic testing with tool calling (2026)
• Agile testing quadrants (2026)
• AI assisted exploratory testing (2026)