How to unit test ParadeDB in PostgreSQL

How do I write and run unit tests for ParadeDB in PostgreSQL?

Unit testing in ParadeDB verifies that vector-search functions, embeddings, and index operations behave as expected before code reaches production.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

Why should I unit-test ParadeDB functions?

Unit tests catch logic errors in vector similarity searches, ensure embedding pipelines stay deterministic, and prevent regressions when indexes or extensions are upgraded.

What tool is best for ParadeDB unit tests?

pgTAP integrates seamlessly with PostgreSQL and ParadeDB, providing assertion helpers (ok, is, throws_ok) that run inside transactions and roll back automatically.

How do I install pgTAP for ParadeDB?

Run CREATE EXTENSION pgtap; in the target database, then grant usage to your CI user. No ParadeDB-specific binaries are required.

How do I write a ParadeDB unit test?

Wrap assertions between SELECT plan(n); and SELECT finish();. Use ParadeDB functions inside assertions to validate vector distances, ANN index usage, or embedding correctness.

Syntax breakdown

ok(boolean, note) asserts truth; is(actual, expected, note) checks equality; is_deeply(jsonb, jsonb, note) compares JSON; throws_ok(sql, pattern, note) expects errors.

Can I test inside a transaction?

Yes. pgTAP automatically runs each file in its own savepoint. Add an explicit ROLLBACK; after tests if you script manually.

How do I integrate tests with CI?

Use pg_prove or pg_tap_runner in GitHub Actions. Point it at test/ SQL files; it will fail the build on any non-zero tap result.

Best practices for ParadeDB testing

Seed small, deterministic fixtures; use SET enable_seqscan = off; to force index usage; clean up with TRUNCATE inside the test.

Common mistakes to avoid

Do not forget plan(); missing it marks every test as TODO. Avoid leaving data committed—wrap tests in transactions.

Why How to unit test ParadeDB in PostgreSQL is important

How to unit test ParadeDB in PostgreSQL Example Usage


-- tests/product_search.sql
BEGIN;
SELECT plan(2);
-- Assert that a similarity search returns the expected product ID
SELECT is(
    (SELECT id FROM Products ORDER BY embedding <=> parade_embed('wireless mouse') LIMIT 1),
    7,
    'Top match is product 7');
-- Confirm stock decrement function fails on negative quantity
SELECT throws_ok(
    $$SELECT decrement_stock(7, -1);$$,
    'quantity must be positive',
    'Prevent negative stock');
SELECT finish();
ROLLBACK;

How to unit test ParadeDB in PostgreSQL Syntax


-- Basic pgTAP pattern
BEGIN;
SELECT plan(3);
-- 1. Check embedding length
SELECT is(o.embedding <-> p.embedding < 0.2, true, 'Vectors are close');
-- 2. Ensure ANN index is used for Products table
EXPLAIN (format json) SELECT * FROM Products
 WHERE embedding <=> parade_embed('laptop') < 0.5
 ORDER BY embedding <=> parade_embed('laptop') LIMIT 5;
SELECT like(
    (SELECT json_agg(p->'Plan'->>'Index Name') FROM (
      SELECT jsonb_array_elements(plan) p) s),
    '%products_embedding_idx%',
    'Uses products_embedding_idx');
-- 3. Validate order total calculation
SELECT is((SELECT total_amount FROM Orders WHERE id=42), 199.99, 'Correct order total');
SELECT finish();
ROLLBACK;

Common Mistakes

Forgetting to call SELECT plan(n); before assertions. pgTAP requires a declared test count; omitting it marks tests as TODO and hides failures. Fix by adding plan.
Committing fixture data during tests. Leaving data committed pollutes dev databases and makes tests order-dependent. Always wrap tests in BEGIN/ROLLBACK or use pgTAP's automatic savepoints.