Tests Are SQL Files Too | NpgsqlRest

Skip to content

Appearance

MenuReturn to top

Comments<br>More Blog Posts

Tests Are SQL Files Too ​<br>July 2026 · NpgsqlRestPostgreSQLStoryTestingWatch Modev3.19.0<br>Introduction ​<br>Features in this last version 3.19.0 got me excited in a way I haven't been since 3.12.0 and I wanted to write about it personally. So let's do a quick recap:<br>Version 3.12.0 introduced the idea that a SQL file can be a REST endpoint. That was a big deal, and it changed how I write APIs.<br>Major shift is that I don't have to deploy anything or run a migration to add a new endpoint. Just write a simple SQL file, like a script and that is it, NpgsqlRest happily creates the endpoint for you from that file. Nothing deployed, nothing migrated. You probably already have a bunch of SQL scripts sitting around. You just need to add a comment with the HTTP method and path, and NpgsqlRest will serve it as an endpoint:<br>sqlsql/*<br>HTTP GET<br>@param $1 from_date<br>@param $2 to_date<br>@authorize admin<br>*/<br>select id, title, created_at<br>from reports<br>where created_at between $1 and $2;1

That is it. Simple as it gets.<br>Version 3.19.0 closes this story loop in several ways:<br>1) Named Parameters in SQL Files<br>Example above can now be written like this:<br>sqlsql/*<br>HTTP GET<br>@authorize admin<br>*/<br>select id, title, created_at<br>from reports<br>where created_at between :from_date and :to_date;1

Small but sweet improvement.<br>2) Watch Mode<br>Initially developed for the test runner, it was expanded during the development for all modes, including SQL file endpoints as well as function/procedure endpoints.<br>It is particularly useful for SQL file endpoints, SQL files from the beginning included native parser validation against real database schema on startup which serves as a type checker for your SQL files. Now with watch mode, you can get that validation on every save, just as you type. This is big in terms of development experience.<br>3) Native Test Runner<br>And finally, the big one: native test runner. This implementation shares the same approach as SQL file endpoints, meaning you can write a test in a SQL file, and HTTP blocks in comments will be executed against the real endpoint, in-process, inside the test's own transaction. The fixture rolls back. Nothing is left behind. The full walkthrough is in the Testing Guide.<br>Let's go into more details.<br>TL;DR Test Runner ​<br>Quick example of a test file:<br>sqlsql-- optional setup to ensure isolation<br>begin;

-- ARRANGE: insert a fixture user into the database

insert into users (email)<br>values ('fixture@example.com');

-- ACT: call the real endpoint, in-process, inside the test's own transaction and simulate an authenticated request with a claim user_id=1

/*<br>GET /api/get-users<br># @claim user_id=1<br>*/

-- ASSERT: assert on the response

select status = 200, 'authenticated caller gets 200'<br>from _response;

select body::jsonb @> '[{"email": "fixture@example.com"}]', 'fixture is listed'<br>from _response;

-- cleanup: rollback the transaction to clean up the fixture and keep isolation for the next test<br>rollback;1<br>10<br>11<br>12<br>13<br>14<br>15<br>16<br>17<br>18<br>19<br>20<br>21<br>22<br>23<br>24<br>25

consoleconsole$ npgsqlrest ./config.json --test

PASS tests/get_users.test.sql (2 assertions, 52ms)<br>19 passed, 0 failed, 0 error(s) — 19 assertions in 9 files<br>endpoint coverage: 2/2 (100%)1

That's the real endpoint — routing, authorization, parameter binding, JSON serialization — invoked in-process, inside the test's own transaction . The fixture rolls back. Nothing is left behind. The full walkthrough is in the Testing Guide.<br>And here is something worth saying up front: you don't need NpgsqlRest endpoints to use this. The HTTP blocks are optional. Boolean-SELECT assertions and DO-block asserts against your functions, views, and schema work on their own — so even if you never expose a single REST endpoint, you now have a fast, isolated, zero-framework unit test runner for plain PostgreSQL scripts. Point it at a directory of .sql tests and go.<br>Database Testing Is Impossible (They Said) ​<br>There is a widespread belief in this industry that testing against a real database is somewhere between painful and impossible. That belief is why we have in-memory database fakes that never work, endless mocks, and entire testing philosophies built around not touching the database — which is a strange way to test software whose primary job is to talk to a database.<br>And here is the thing: the belief is not entirely wrong. But it may be database-specific where some databases make it harder than others. PostgreSQL is one of the databases that makes it easy.<br>For example, testing against SQL Server without containers is hard. Even with containers, it is still hard.<br>No deferrable constraints — every foreign key is checked immediately, always, so a test fixture must satisfy the entire dependency graph before it can insert one interesting row. Creating a throwaway database per test run is a heavyweight operation. The standard answer was tSQLt — a framework you install into the database,...