When to Use Database Logic vs Application Services—and How to Test Both

Learning outcomes

Server-side programming is most effective when each rule is placed at the layer that can enforce it clearly. ServiceHub should not turn PostgreSQL into an opaque application server, and it should not move transaction-critical integrity out to every caller. This lesson builds a decision model and repeatable tests for one database routine so correctness can survive refactoring between database and application layers.

1. Choose the lowest layer that can express the invariant clearly

A useful heuristic is “constraint first, explicit routine second, trigger only when implicit every-writer behavior is necessary, application service for orchestration.” This is a starting point, not a universal architecture law.

2. A routine contract should be testable without knowing its implementation

Use app.ch18_cancel_work_order from Lesson 2. Its observable contract is: a valid queued/assigned work order becomes cancelled, one audit row is written, short reasons fail with SQLSTATE P1802, completed orders fail with P1803, and missing IDs raise PostgreSQL's no-data-found path unless intentionally translated.

BEGIN;INSERT INTO app.ch18_work_order(work_order_id,status,labor_minutes,hourly_rate)VALUES (18901,'assigned',30,100.00);DO $$DECLARE  v_result app.ch18_work_order;  v_audit_count integer;BEGIN  v_result := app.ch18_cancel_work_order(18901,'integration test cancellation');  IF v_result.status <> 'cancelled' THEN    RAISE EXCEPTION 'TEST FAIL: expected cancelled, got %', v_result.status;  END IF;  SELECT count(*) INTO v_audit_count  FROM app.ch18_work_order_change  WHERE work_order_id = 18901    AND new_status = 'cancelled';  IF v_audit_count <> 1 THEN    RAISE EXCEPTION 'TEST FAIL: expected one audit row, got %', v_audit_count;  END IF;  RAISE NOTICE 'TEST PASS: successful cancellation contract';END$$;ROLLBACK;

The transaction rollback keeps the test idempotent. ON_ERROR_STOP in psql (shown later) turns the first failed assertion into a failing test command instead of continuing silently.

3. Test failure contracts by SQLSTATE, not message text

DO $$BEGIN  BEGIN    PERFORM app.ch18_cancel_work_order(18002,'bad');    RAISE EXCEPTION 'TEST FAIL: expected P1802';  EXCEPTION    WHEN SQLSTATE 'P1802' THEN      RAISE NOTICE 'TEST PASS: short reason rejected with P1802';  END;END$$;

Messages can be reworded or localized. SQLSTATE is the durable machine-facing contract. If you invent application-specific codes, document them and avoid category-style codes ending in three zeroes.

4. Privilege tests are part of behavior

SELECT  has_function_privilege(    'servicehub_app',    'app.ch18_app_cancel_work_order(bigint,text)',    'EXECUTE'  ) AS app_can_execute,  EXISTS (    SELECT 1    FROM pg_proc AS p    CROSS JOIN LATERAL aclexplode(      coalesce(p.proacl, acldefault('f', p.proowner))    ) AS x    WHERE p.oid = 'app.ch18_app_cancel_work_order(bigint,text)'::regprocedure      AND x.grantee = 0      AND x.privilege_type = 'EXECUTE'  ) AS public_can_execute;DO $$DECLARE  v_public_execute boolean;BEGIN  IF NOT has_function_privilege(      'servicehub_app',      'app.ch18_app_cancel_work_order(bigint,text)',      'EXECUTE') THEN    RAISE EXCEPTION 'TEST FAIL: servicehub_app lacks EXECUTE';  END IF;  SELECT EXISTS (    SELECT 1    FROM pg_proc AS p    CROSS JOIN LATERAL aclexplode(      coalesce(p.proacl, acldefault('f', p.proowner))    ) AS x    WHERE p.oid = 'app.ch18_app_cancel_work_order(bigint,text)'::regprocedure      AND x.grantee = 0      AND x.privilege_type = 'EXECUTE'  ) INTO v_public_execute;  IF v_public_execute THEN    RAISE EXCEPTION 'TEST FAIL: PUBLIC unexpectedly has EXECUTE';  END IF;END$$;

Security regressions are interface regressions. Test owner, ACL, prosecdef, and routine-level search_path settings alongside result rows.

5. Catalog tests catch metadata regressions

SELECT p.oid::regprocedure,       p.provolatile,       p.proparallel,       p.prosecdef,       p.proconfigFROM pg_proc AS pWHERE p.oid IN (  'app.ch18_labor_cost(integer,numeric)'::regprocedure,  'app.ch18_app_cancel_work_order(bigint,text)'::regprocedure);DO $$DECLARE  v_volatility "char";BEGIN  SELECT provolatile INTO v_volatility  FROM pg_proc  WHERE oid = 'app.ch18_labor_cost(integer,numeric)'::regprocedure;  IF v_volatility <> 'i' THEN    RAISE EXCEPTION 'TEST FAIL: labor cost must remain IMMUTABLE';  END IF;END$$;

A code review that checks only function text can miss an accidental ALTER FUNCTION ... VOLATILE, ownership change, or PUBLIC EXECUTE grant. Catalog assertions bring deployment metadata into the test surface.

6. Run SQL tests as a repeatable command

Put the SQL assertions in a file such as tests/ch18_routine_contract.sql. The lesson does not require pgTAP or any proprietary test framework.

psql   --dbname="service=servicehub-lab-admin"   --set=ON_ERROR_STOP=1   --file=tests/ch18_routine_contract.sql

The process exit code becomes the CI signal: zero means all SQL assertions completed; nonzero means at least one SQL error/RAISE stopped the script. Capture server/client versions in CI logs so behavior changes are attributable.

7. Application contract test: test the database boundary, not PL/pgSQL internals

An application test should invoke the same public routine through the real driver identity and assert result/error behavior. The example below uses Psycopg 3, an open-source PostgreSQL driver, as an optional application-layer example; the mandatory chapter remains runnable with core PostgreSQL/psql alone.

import psycopgDSN = "service=servicehub-lab-app"with psycopg.connect(DSN) as conn:    with conn.cursor() as cur:        cur.execute("BEGIN")        cur.execute(            "INSERT INTO app.ch18_work_order "            "(work_order_id,status,labor_minutes,hourly_rate) "            "VALUES (%s,%s,%s,%s)",            (18902, "assigned", 25, 95.00),        )        cur.execute(            "SELECT app.ch18_app_cancel_work_order(%s,%s)",            (18902, "application contract test"),        )        assert cur.fetchone()[0] == 18902        conn.rollback()try:    with psycopg.connect(DSN) as conn:        with conn.cursor() as cur:            cur.execute(                "SELECT app.ch18_app_cancel_work_order(%s,%s)",                (18002, "bad"),            )except psycopg.Error as exc:    assert exc.sqlstate == "P1802"

Values remain bound parameters, the connection uses the application role/service, and the assertion checks SQLSTATE rather than text. Do not expose table-owner credentials to make application tests easier.

8. Wrong architecture test: mock away the database invariant

Conversely, a database-only test cannot prove HTTP retries, idempotency keys, observability, connection-pool behavior, or service authorization. Test each boundary where it actually lives.

9. Deployment and observability checklist

SELECT p.oid::regprocedure AS routine,       pg_get_userbyid(p.proowner) AS owner,       p.prokind,       p.prosecdef,       p.proacl,       p.proconfigFROM pg_proc AS pJOIN pg_namespace AS n ON n.oid = p.pronamespaceWHERE n.nspname = 'app'  AND p.proname LIKE 'ch18_%'ORDER BY routine::text;SELECT pg_describe_object(d.classid, d.objid, d.objsubid) AS dependent_object,       d.deptype,       d.refobjid::regprocedure AS referenced_routineFROM pg_depend AS dWHERE d.refclassid = 'pg_proc'::regclass  AND d.refobjid IN (    SELECT p.oid FROM pg_proc AS p    JOIN pg_namespace AS n ON n.oid=p.pronamespace    WHERE n.nspname='app' AND p.proname LIKE 'ch18_%'  )ORDER BY referenced_routine::text, dependent_object;

Deployment review should include function signatures, ownership/ACLs, volatility/parallel labels, routine-level settings, dependent triggers/views, migration order, rollback plan, and application compatibility. CREATE OR REPLACE FUNCTION preserves ownership/permissions, but changing argument or return types often requires drop/recreate and dependency handling.

10. Cleanup and bridge to extensibility

DROP VIEW IF EXISTS app.ch18_work_order_queue CASCADE;DROP TABLE IF EXISTS app.ch18_trigger_audit CASCADE;DROP TABLE IF EXISTS app.ch18_trigger_order CASCADE;DROP TABLE IF EXISTS app.ch18_work_order_change CASCADE;DROP TABLE IF EXISTS app.ch18_work_order CASCADE;DROP TABLE IF EXISTS app.ch18_rate_config CASCADE;DROP FUNCTION IF EXISTS app.ch18_app_cancel_work_order(bigint,text);DROP FUNCTION IF EXISTS app.ch18_cancel_with_diagnostics(bigint,text);DROP FUNCTION IF EXISTS app.ch18_cancel_work_order(bigint,text);DROP FUNCTION IF EXISTS app.ch18_count_orders_by(text,text);DROP FUNCTION IF EXISTS app.ch18_count_orders_unsafe(text);DROP FUNCTION IF EXISTS app.ch18_status_summary();DROP FUNCTION IF EXISTS app.ch18_rate(text);DROP FUNCTION IF EXISTS app.ch18_rate_bad(text);DROP FUNCTION IF EXISTS app.ch18_orders_by_status(text);DROP FUNCTION IF EXISTS app.ch18_labor_cost(integer,numeric);DROP FUNCTION IF EXISTS app.ch18_close_work_order(bigint);DROP FUNCTION IF EXISTS app.ch18_touch_order();DROP FUNCTION IF EXISTS app.ch18_audit_order_update_statement();DROP FUNCTION IF EXISTS app.ch18_audit_status_row();DROP FUNCTION IF EXISTS app.ch18_queue_insert();DROP FUNCTION IF EXISTS app.ch18_completed_requires_closed_at();DROP FUNCTION IF EXISTS app.ch18_recursive_touch_bad();DROP PROCEDURE IF EXISTS app.ch18_complete_zero_labor_queue();

Chapter 19 expands the same discipline beyond built-in SQL/PLpgSQL routines into extensions, Foreign Data Wrappers, procedural languages, custom operators/types, and extension lifecycle/security. The key principle carries forward: code that enters the PostgreSQL server process or optimizer becomes part of the database trust and upgrade surface.

Authoritative references

Routines, trigger timing, privileges, and planner promises are version-sensitive. These PostgreSQL 18 primary sources define the behavior used in this lesson.

• PostgreSQL 18 — Function Security
• PostgreSQL 18 — PostgreSQL Error Codes / SQLSTATE
• PostgreSQL 18 — System Catalog pg_proc
• PostgreSQL 18 — Dependency Tracking
• Psycopg 3 — Official Documentation (optional application test)