-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathtesting.html
More file actions
221 lines (219 loc) · 182 KB
/
Copy pathtesting.html
File metadata and controls
221 lines (219 loc) · 182 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
<!DOCTYPE html>
<html lang="en-US" dir="ltr">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width,initial-scale=1">
<title>Testing Guide | NpgsqlRest</title>
<meta name="description" content="Test NpgsqlRest endpoints with plain SQL files. In-process endpoint invocation, transactional isolation, test databases, template clones, migrations, Docker, watch mode, coverage, and CI.">
<meta name="generator" content="VitePress v1.6.4">
<link rel="preload stylesheet" href="/assets/style.Dq4B5IkV.css" as="style">
<link rel="preload stylesheet" href="/vp-icons.css" as="style">
<script type="module" src="/assets/app.Ds8YbgIC.js"></script>
<link rel="preload" href="/assets/inter-roman-latin.Di8DUHzh.woff2" as="font" type="font/woff2" crossorigin="">
<link rel="modulepreload" href="/assets/chunks/theme.kqgpP4eL.js">
<link rel="modulepreload" href="/assets/chunks/framework.CgT1UzWm.js">
<link rel="modulepreload" href="/assets/guide_testing.md.Mc_wQPBP.lean.js">
<link rel="icon" type="image/x-icon" href="/favicon.ico">
<link rel="shortcut icon" href="/favicon.ico">
<link rel="apple-touch-icon" sizes="180x180" href="/favicon.ico">
<meta property="og:url" content="https://npgsqlrest.github.io">
<meta name="twitter:card" content="summary">
<meta name="twitter:title" content="NpgsqlRest - Automatic PostgreSQL Web Server">
<meta name="twitter:description" content="Create REST APIs for PostgreSQL databases in minutes. Zero code, zero config, automatic endpoints from your database schema.">
<script defer data-id="101498393" src="//static.getclicky.com/js"></script>
<script id="check-dark-mode">(()=>{const e=localStorage.getItem("vitepress-theme-appearance")||"auto",a=window.matchMedia("(prefers-color-scheme: dark)").matches;(!e||e==="auto"?a:e==="dark")&&document.documentElement.classList.add("dark")})();</script>
<script id="check-mac-os">document.documentElement.classList.toggle("mac",/Mac|iPhone|iPod|iPad/i.test(navigator.platform));</script>
<meta name="keywords" content="npgsqlrest testing, sql test runner, postgresql api testing, test database, template database, test isolation, junit, endpoint coverage, watch mode">
<meta property="og:title" content="NpgsqlRest Testing Guide">
<meta property="og:description" content="Test NpgsqlRest endpoints with plain SQL files — in-process, transactional, CI-ready.">
<meta property="og:type" content="article">
</head>
<body>
<div id="app"><div class="Layout" data-v-5d98c3a5><!--[--><!--]--><!--[--><span tabindex="-1" data-v-0b0ada53></span><a href="#VPContent" class="VPSkipLink visually-hidden" data-v-0b0ada53>Skip to content</a><!--]--><!----><header class="VPNav" data-v-5d98c3a5 data-v-ae24b3ad><div class="VPNavBar" data-v-ae24b3ad data-v-6aa21345><div class="wrapper" data-v-6aa21345><div class="container" data-v-6aa21345><div class="title" data-v-6aa21345><div class="VPNavBarTitle has-sidebar" data-v-6aa21345 data-v-1168a8e4><a class="title" href="/" data-v-1168a8e4><!--[--><!--]--><!--[--><img class="VPImage logo" src="/favicon.ico" alt data-v-8426fc1a><!--]--><span data-v-1168a8e4>NpgsqlRest</span><!--[--><!--]--></a></div></div><div class="content" data-v-6aa21345><div class="content-body" data-v-6aa21345><!--[--><!--]--><div class="VPNavBarSearch search" data-v-6aa21345><!--[--><!----><div id="local-search"><button type="button" class="DocSearch DocSearch-Button" aria-label="Search"><span class="DocSearch-Button-Container"><span class="vp-icon DocSearch-Search-Icon"></span><span class="DocSearch-Button-Placeholder">Search</span></span><span class="DocSearch-Button-Keys"><kbd class="DocSearch-Button-Key"></kbd><kbd class="DocSearch-Button-Key">K</kbd></span></button></div><!--]--></div><nav aria-labelledby="main-nav-aria-label" class="VPNavBarMenu menu" data-v-6aa21345 data-v-dc692963><span id="main-nav-aria-label" class="visually-hidden" data-v-dc692963> Main Navigation </span><!--[--><!--[--><div class="VPFlyout VPNavBarMenuGroup active" data-v-dc692963 data-v-cf11d7a2><button type="button" class="button" aria-haspopup="true" aria-expanded="false" data-v-cf11d7a2><span class="text" data-v-cf11d7a2><!----><span data-v-cf11d7a2>Guide</span><span class="vpi-chevron-down text-icon" data-v-cf11d7a2></span></span></button><div class="menu" data-v-cf11d7a2><div class="VPMenu" data-v-cf11d7a2 data-v-b98bc113><div class="items" data-v-b98bc113><!--[--><!--[--><div class="VPMenuGroup" data-v-b98bc113 data-v-69e747b5><p class="title" data-v-69e747b5>Getting Started</p><!--[--><!--[--><div class="VPMenuLink" data-v-69e747b5 data-v-35975db6><a class="VPLink link" href="/guide/" data-v-35975db6><!--[--><span data-v-35975db6>Overview</span><!--]--></a></div><!--]--><!--[--><div class="VPMenuLink" data-v-69e747b5 data-v-35975db6><a class="VPLink link" href="/guide/installation.html" data-v-35975db6><!--[--><span data-v-35975db6>Installation</span><!--]--></a></div><!--]--><!--[--><div class="VPMenuLink" data-v-69e747b5 data-v-35975db6><a class="VPLink link" href="/guide/quick-start.html" data-v-35975db6><!--[--><span data-v-35975db6>Quick Start</span><!--]--></a></div><!--]--><!--]--></div><!--]--><!--[--><div class="VPMenuGroup" data-v-b98bc113 data-v-69e747b5><p class="title" data-v-69e747b5>Topics</p><!--[--><!--[--><div class="VPMenuLink" data-v-69e747b5 data-v-35975db6><a class="VPLink link" href="/guide/sql-files.html" data-v-35975db6><!--[--><span data-v-35975db6>SQL File Endpoints</span><!--]--></a></div><!--]--><!--[--><div class="VPMenuLink" data-v-69e747b5 data-v-35975db6><a class="VPLink link" href="/guide/configuration.html" data-v-35975db6><!--[--><span data-v-35975db6>Configuration Guide</span><!--]--></a></div><!--]--><!--[--><div class="VPMenuLink" data-v-69e747b5 data-v-35975db6><a class="VPLink link" href="/guide/annotations.html" data-v-35975db6><!--[--><span data-v-35975db6>Comment Annotations Guide</span><!--]--></a></div><!--]--><!--[--><div class="VPMenuLink" data-v-69e747b5 data-v-35975db6><a class="VPLink link" href="/guide/authentication.html" data-v-35975db6><!--[--><span data-v-35975db6>Authentication</span><!--]--></a></div><!--]--><!--[--><div class="VPMenuLink" data-v-69e747b5 data-v-35975db6><a class="VPLink link" href="/guide/sse.html" data-v-35975db6><!--[--><span data-v-35975db6>Server-Sent Events</span><!--]--></a></div><!--]--><!--[--><div class="VPMenuLink" data-v-69e747b5 data-v-35975db6><a class="VPLink link" href="/guide/http-types.html" data-v-35975db6><!--[--><span data-v-35975db6>HTTP Custom Types</span><!--]--></a></div><!--]--><!--[--><div class="VPMenuLink" data-v-69e747b5 data-v-35975db6><a class="VPLink link" href="/guide/proxy.html" data-v-35975db6><!--[--><span data-v-35975db6>Proxy Endpoints</span><!--]--></a></div><!--]--><!--[--><div class="VPMenuLink" data-v-69e747b5 data-v-35975db6><a class="VPLink link active" href="/guide/testing.html" data-v-35975db6><!--[--><span data-v-35975db6>Testing</span><!--]--></a></div><!--]--><!--[--><div class="VPMenuLink" data-v-69e747b5 data-v-35975db6><a class="VPLink link" href="/guide/logging.html" data-v-35975db6><!--[--><span data-v-35975db6>Logging</span><!--]--></a></div><!--]--><!--]--></div><!--]--><!--[--><div class="VPMenuGroup" data-v-b98bc113 data-v-69e747b5><p class="title" data-v-69e747b5>Reference</p><!--[--><!--[--><div class="VPMenuLink" data-v-69e747b5 data-v-35975db6><a class="VPLink link" href="/guide/faq.html" data-v-35975db6><!--[--><span data-v-35975db6>FAQ & Troubleshooting</span><!--]--></a></div><!--]--><!--[--><div class="VPMenuLink" data-v-69e747b5 data-v-35975db6><a class="VPLink link" href="/guide/changelog/" data-v-35975db6><!--[--><span data-v-35975db6>Changelog</span><!--]--></a></div><!--]--><!--]--></div><!--]--><!--]--></div><!--[--><!--]--></div></div></div><!--]--><!--[--><a class="VPLink link VPNavBarMenuLink" href="/examples/" tabindex="0" data-v-dc692963 data-v-e56f3d57><!--[--><span data-v-e56f3d57>Examples</span><!--]--></a><!--]--><!--[--><div class="VPFlyout VPNavBarMenuGroup" data-v-dc692963 data-v-cf11d7a2><button type="button" class="button" aria-haspopup="true" aria-expanded="false" data-v-cf11d7a2><span class="text" data-v-cf11d7a2><!----><span data-v-cf11d7a2>Reference</span><span class="vpi-chevron-down text-icon" data-v-cf11d7a2></span></span></button><div class="menu" data-v-cf11d7a2><div class="VPMenu" data-v-cf11d7a2 data-v-b98bc113><div class="items" data-v-b98bc113><!--[--><!--[--><div class="VPMenuLink" data-v-b98bc113 data-v-35975db6><a class="VPLink link" href="/annotations/" data-v-35975db6><!--[--><span data-v-35975db6>Annotations</span><!--]--></a></div><!--]--><!--[--><div class="VPMenuLink" data-v-b98bc113 data-v-35975db6><a class="VPLink link" href="/config/" data-v-35975db6><!--[--><span data-v-35975db6>Configuration</span><!--]--></a></div><!--]--><!--]--></div><!--[--><!--]--></div></div></div><!--]--><!--[--><a class="VPLink link VPNavBarMenuLink" href="/blog/" tabindex="0" data-v-dc692963 data-v-e56f3d57><!--[--><span data-v-e56f3d57>Blog</span><!--]--></a><!--]--><!--]--></nav><!----><div class="VPNavBarAppearance appearance" data-v-6aa21345 data-v-6c893767><button class="VPSwitch VPSwitchAppearance" type="button" role="switch" title aria-checked="false" data-v-6c893767 data-v-5337faa4 data-v-1d5665e3><span class="check" data-v-1d5665e3><span class="icon" data-v-1d5665e3><!--[--><span class="vpi-sun sun" data-v-5337faa4></span><span class="vpi-moon moon" data-v-5337faa4></span><!--]--></span></span></button></div><div class="VPSocialLinks VPNavBarSocialLinks social-links" data-v-6aa21345 data-v-0394ad82 data-v-7bc22406><!--[--><a class="VPSocialLink no-icon" href="https://github.com/NpgsqlRest/NpgsqlRest" aria-label="github" target="_blank" rel="noopener" data-v-7bc22406 data-v-bd121fe5><span class="vpi-social-github"></span></a><a class="VPSocialLink no-icon" href="https://patreon.com/vbconsulting" aria-label="Patreon" target="_blank" rel="noopener" data-v-7bc22406 data-v-bd121fe5><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 24 24" fill="currentColor"><path d="M22.957 7.21c-.004-3.064-2.391-5.576-5.191-6.482-3.478-1.125-8.064-.962-11.384.604C2.357 3.231 1.093 7.391 1.046 11.54c-.039 3.411.302 7.463 3.097 9.746 2.602 2.124 6.19 2.078 9.402 1.836 1.883-.142 3.939-.358 5.477-1.528 1.564-1.19 1.873-3.298 1.972-5.151.134-2.51.187-5.016-.037-7.233zm-2.55 7.703c-.078 1.593-.383 3.473-1.795 4.334-1.351.824-3.151.973-4.686 1.083-2.891.207-6.063.276-8.514-1.399C3.106 17.39 2.924 14.34 2.95 11.67c.03-3.206.678-6.721 3.654-8.375 2.756-1.531 6.556-1.727 9.544-.872 2.339.669 4.351 2.678 4.355 5.217.007 2.583-.016 5.168-.095 7.273z"/><path d="M16.088 5.19c-1.008-.542-2.607-.48-3.441.388-.568.591-.773 1.389-.8 2.172-.034.993.068 2.013.361 2.953.307.985 1.034 1.848 2.077 2.012.878.138 1.83-.088 2.458-.732.7-.718.88-1.81.877-2.779-.003-.989-.084-1.99-.423-2.91-.268-.726-.654-1.328-1.109-1.104zm-.294 5.482c-.182.462-.581.771-1.091.696-.532-.078-.859-.533-1.04-1.005-.248-.647-.327-1.375-.312-2.06.01-.46.095-.939.371-1.313.345-.467.993-.447 1.426-.115.41.315.607.835.74 1.32.18.66.211 1.675-.094 2.477zM8.4 10.394c.233 1.084.755 2.299 1.907 2.579.934.227 1.993-.165 2.434-1.048.455-.91.346-1.995.166-2.972-.165-.893-.46-1.885-1.207-2.445-.745-.559-1.832-.457-2.479.192-.84.843-.958 2.138-.955 3.277.001.147.119.364.134.417zm1.363-2.808c.302-.321.805-.336 1.14-.054.445.375.618 1.015.75 1.558.131.54.212 1.156.082 1.703-.1.423-.378.821-.84.882-.469.062-.886-.306-1.088-.704-.326-.644-.401-1.426-.381-2.148.013-.465.104-.993.337-1.237z"/></svg></a><a class="VPSocialLink no-icon" href="https://buymeacoffee.com/vbilopavu" aria-label="Buy Me a Coffee" target="_blank" rel="noopener" data-v-7bc22406 data-v-bd121fe5><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 24 24" fill="currentColor"><path d="M20.216 6.415l-.132-.666c-.119-.598-.388-1.163-1.001-1.379-.197-.069-.42-.098-.57-.241-.152-.143-.196-.366-.231-.572-.065-.378-.125-.756-.192-1.133-.057-.325-.102-.69-.25-.987-.195-.4-.597-.634-.996-.788a5.723 5.723 0 00-.626-.194c-1-.263-2.05-.36-3.077-.416a25.834 25.834 0 00-3.7.062c-.915.083-1.88.184-2.75.5-.318.116-.646.256-.888.501-.297.302-.393.77-.177 1.146.154.267.415.456.692.58.36.162.737.284 1.123.366 1.075.238 2.189.331 3.287.37 1.218.05 2.437.01 3.65-.118.299-.033.598-.073.896-.119.352-.054.578-.513.474-.834-.124-.383-.457-.531-.834-.473-.466.074-.96.108-1.382.146-1.177.08-2.358.082-3.536.006a22.228 22.228 0 01-1.157-.107c-.086-.01-.18-.025-.258-.036-.243-.036-.484-.08-.724-.13-.111-.027-.111-.185 0-.212h.005c.277-.06.557-.108.838-.147h.002c.131-.009.263-.032.394-.048a25.076 25.076 0 013.426-.12c.674.019 1.347.067 2.017.144l.228.031c.267.04.533.088.798.145.392.085.895.113 1.07.542.055.137.08.288.111.431l.319 1.484a.237.237 0 01-.199.284h-.003c-.037.006-.075.01-.112.015a36.704 36.704 0 01-4.743.295 37.059 37.059 0 01-4.699-.304c-.14-.017-.293-.042-.417-.06-.326-.048-.649-.108-.973-.161-.393-.065-.768-.032-1.123.161-.29.16-.527.404-.675.701-.154.316-.199.66-.267 1-.069.34-.176.707-.135 1.056.087.753.613 1.365 1.37 1.502a39.69 39.69 0 0011.343.376.483.483 0 01.535.53l-.071.697-1.018 9.907c-.041.41-.047.832-.125 1.237-.122.637-.553 1.028-1.182 1.171-.577.131-1.165.2-1.756.205-.656.004-1.31-.025-1.966-.022-.699.004-1.556-.06-2.095-.58-.475-.458-.54-1.174-.605-1.793l-.731-7.013-.322-3.094c-.037-.351-.286-.695-.678-.678-.336.015-.718.3-.678.679l.228 2.185.949 9.112c.147 1.344 1.174 2.068 2.446 2.272.742.12 1.503.144 2.257.156.966.016 1.942.053 2.892-.122 1.408-.258 2.465-1.198 2.616-2.657.34-3.332.683-6.663 1.024-9.995l.215-2.087a.484.484 0 01.39-.426c.402-.078.787-.212 1.074-.518.455-.488.546-1.124.385-1.766zm-1.478.772c-.145.137-.363.201-.578.233-2.416.359-4.866.54-7.308.46-1.748-.06-3.477-.254-5.207-.498-.17-.024-.353-.055-.47-.18-.22-.236-.111-.71-.054-.995.052-.26.152-.609.463-.646.484-.057 1.046.148 1.526.22.577.088 1.156.159 1.737.212 2.48.226 5.002.19 7.472-.14.45-.06.899-.13 1.345-.21.399-.072.84-.206 1.08.206.166.281.188.657.162.974a.544.544 0 01-.169.364z"/></svg></a><!--]--></div><div class="VPFlyout VPNavBarExtra extra" data-v-6aa21345 data-v-bb2aa2f0 data-v-cf11d7a2><button type="button" class="button" aria-haspopup="true" aria-expanded="false" aria-label="extra navigation" data-v-cf11d7a2><span class="vpi-more-horizontal icon" data-v-cf11d7a2></span></button><div class="menu" data-v-cf11d7a2><div class="VPMenu" data-v-cf11d7a2 data-v-b98bc113><!----><!--[--><!--[--><!----><div class="group" data-v-bb2aa2f0><div class="item appearance" data-v-bb2aa2f0><p class="label" data-v-bb2aa2f0>Appearance</p><div class="appearance-action" data-v-bb2aa2f0><button class="VPSwitch VPSwitchAppearance" type="button" role="switch" title aria-checked="false" data-v-bb2aa2f0 data-v-5337faa4 data-v-1d5665e3><span class="check" data-v-1d5665e3><span class="icon" data-v-1d5665e3><!--[--><span class="vpi-sun sun" data-v-5337faa4></span><span class="vpi-moon moon" data-v-5337faa4></span><!--]--></span></span></button></div></div></div><div class="group" data-v-bb2aa2f0><div class="item social-links" data-v-bb2aa2f0><div class="VPSocialLinks social-links-list" data-v-bb2aa2f0 data-v-7bc22406><!--[--><a class="VPSocialLink no-icon" href="https://github.com/NpgsqlRest/NpgsqlRest" aria-label="github" target="_blank" rel="noopener" data-v-7bc22406 data-v-bd121fe5><span class="vpi-social-github"></span></a><a class="VPSocialLink no-icon" href="https://patreon.com/vbconsulting" aria-label="Patreon" target="_blank" rel="noopener" data-v-7bc22406 data-v-bd121fe5><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 24 24" fill="currentColor"><path d="M22.957 7.21c-.004-3.064-2.391-5.576-5.191-6.482-3.478-1.125-8.064-.962-11.384.604C2.357 3.231 1.093 7.391 1.046 11.54c-.039 3.411.302 7.463 3.097 9.746 2.602 2.124 6.19 2.078 9.402 1.836 1.883-.142 3.939-.358 5.477-1.528 1.564-1.19 1.873-3.298 1.972-5.151.134-2.51.187-5.016-.037-7.233zm-2.55 7.703c-.078 1.593-.383 3.473-1.795 4.334-1.351.824-3.151.973-4.686 1.083-2.891.207-6.063.276-8.514-1.399C3.106 17.39 2.924 14.34 2.95 11.67c.03-3.206.678-6.721 3.654-8.375 2.756-1.531 6.556-1.727 9.544-.872 2.339.669 4.351 2.678 4.355 5.217.007 2.583-.016 5.168-.095 7.273z"/><path d="M16.088 5.19c-1.008-.542-2.607-.48-3.441.388-.568.591-.773 1.389-.8 2.172-.034.993.068 2.013.361 2.953.307.985 1.034 1.848 2.077 2.012.878.138 1.83-.088 2.458-.732.7-.718.88-1.81.877-2.779-.003-.989-.084-1.99-.423-2.91-.268-.726-.654-1.328-1.109-1.104zm-.294 5.482c-.182.462-.581.771-1.091.696-.532-.078-.859-.533-1.04-1.005-.248-.647-.327-1.375-.312-2.06.01-.46.095-.939.371-1.313.345-.467.993-.447 1.426-.115.41.315.607.835.74 1.32.18.66.211 1.675-.094 2.477zM8.4 10.394c.233 1.084.755 2.299 1.907 2.579.934.227 1.993-.165 2.434-1.048.455-.91.346-1.995.166-2.972-.165-.893-.46-1.885-1.207-2.445-.745-.559-1.832-.457-2.479.192-.84.843-.958 2.138-.955 3.277.001.147.119.364.134.417zm1.363-2.808c.302-.321.805-.336 1.14-.054.445.375.618 1.015.75 1.558.131.54.212 1.156.082 1.703-.1.423-.378.821-.84.882-.469.062-.886-.306-1.088-.704-.326-.644-.401-1.426-.381-2.148.013-.465.104-.993.337-1.237z"/></svg></a><a class="VPSocialLink no-icon" href="https://buymeacoffee.com/vbilopavu" aria-label="Buy Me a Coffee" target="_blank" rel="noopener" data-v-7bc22406 data-v-bd121fe5><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 24 24" fill="currentColor"><path d="M20.216 6.415l-.132-.666c-.119-.598-.388-1.163-1.001-1.379-.197-.069-.42-.098-.57-.241-.152-.143-.196-.366-.231-.572-.065-.378-.125-.756-.192-1.133-.057-.325-.102-.69-.25-.987-.195-.4-.597-.634-.996-.788a5.723 5.723 0 00-.626-.194c-1-.263-2.05-.36-3.077-.416a25.834 25.834 0 00-3.7.062c-.915.083-1.88.184-2.75.5-.318.116-.646.256-.888.501-.297.302-.393.77-.177 1.146.154.267.415.456.692.58.36.162.737.284 1.123.366 1.075.238 2.189.331 3.287.37 1.218.05 2.437.01 3.65-.118.299-.033.598-.073.896-.119.352-.054.578-.513.474-.834-.124-.383-.457-.531-.834-.473-.466.074-.96.108-1.382.146-1.177.08-2.358.082-3.536.006a22.228 22.228 0 01-1.157-.107c-.086-.01-.18-.025-.258-.036-.243-.036-.484-.08-.724-.13-.111-.027-.111-.185 0-.212h.005c.277-.06.557-.108.838-.147h.002c.131-.009.263-.032.394-.048a25.076 25.076 0 013.426-.12c.674.019 1.347.067 2.017.144l.228.031c.267.04.533.088.798.145.392.085.895.113 1.07.542.055.137.08.288.111.431l.319 1.484a.237.237 0 01-.199.284h-.003c-.037.006-.075.01-.112.015a36.704 36.704 0 01-4.743.295 37.059 37.059 0 01-4.699-.304c-.14-.017-.293-.042-.417-.06-.326-.048-.649-.108-.973-.161-.393-.065-.768-.032-1.123.161-.29.16-.527.404-.675.701-.154.316-.199.66-.267 1-.069.34-.176.707-.135 1.056.087.753.613 1.365 1.37 1.502a39.69 39.69 0 0011.343.376.483.483 0 01.535.53l-.071.697-1.018 9.907c-.041.41-.047.832-.125 1.237-.122.637-.553 1.028-1.182 1.171-.577.131-1.165.2-1.756.205-.656.004-1.31-.025-1.966-.022-.699.004-1.556-.06-2.095-.58-.475-.458-.54-1.174-.605-1.793l-.731-7.013-.322-3.094c-.037-.351-.286-.695-.678-.678-.336.015-.718.3-.678.679l.228 2.185.949 9.112c.147 1.344 1.174 2.068 2.446 2.272.742.12 1.503.144 2.257.156.966.016 1.942.053 2.892-.122 1.408-.258 2.465-1.198 2.616-2.657.34-3.332.683-6.663 1.024-9.995l.215-2.087a.484.484 0 01.39-.426c.402-.078.787-.212 1.074-.518.455-.488.546-1.124.385-1.766zm-1.478.772c-.145.137-.363.201-.578.233-2.416.359-4.866.54-7.308.46-1.748-.06-3.477-.254-5.207-.498-.17-.024-.353-.055-.47-.18-.22-.236-.111-.71-.054-.995.052-.26.152-.609.463-.646.484-.057 1.046.148 1.526.22.577.088 1.156.159 1.737.212 2.48.226 5.002.19 7.472-.14.45-.06.899-.13 1.345-.21.399-.072.84-.206 1.08.206.166.281.188.657.162.974a.544.544 0 01-.169.364z"/></svg></a><!--]--></div></div></div><!--]--><!--]--></div></div></div><!--[--><!--]--><button type="button" class="VPNavBarHamburger hamburger" aria-label="mobile navigation" aria-expanded="false" aria-controls="VPNavScreen" data-v-6aa21345 data-v-e5dd9c1c><span class="container" data-v-e5dd9c1c><span class="top" data-v-e5dd9c1c></span><span class="middle" data-v-e5dd9c1c></span><span class="bottom" data-v-e5dd9c1c></span></span></button></div></div></div></div><div class="divider" data-v-6aa21345><div class="divider-line" data-v-6aa21345></div></div></div><!----></header><div class="VPLocalNav has-sidebar empty" data-v-5d98c3a5 data-v-a6f0e41e><div class="container" data-v-a6f0e41e><button class="menu" aria-expanded="false" aria-controls="VPSidebarNav" data-v-a6f0e41e><span class="vpi-align-left menu-icon" data-v-a6f0e41e></span><span class="menu-text" data-v-a6f0e41e>Menu</span></button><div class="VPLocalNavOutlineDropdown" style="--vp-vh:0px;" data-v-a6f0e41e data-v-8a42e2b4><button data-v-8a42e2b4>Return to top</button><!----></div></div></div><aside class="VPSidebar" data-v-5d98c3a5 data-v-319d5ca6><div class="curtain" data-v-319d5ca6></div><nav class="nav" id="VPSidebarNav" aria-labelledby="sidebar-aria-label" tabindex="-1" data-v-319d5ca6><span class="visually-hidden" id="sidebar-aria-label" data-v-319d5ca6> Sidebar Navigation </span><!--[--><!--]--><!--[--><div class="no-transition group" data-v-c40bc020><section class="VPSidebarItem level-0" data-v-c40bc020 data-v-b3fd67f8><div class="item" role="button" tabindex="0" data-v-b3fd67f8><div class="indicator" data-v-b3fd67f8></div><h2 class="text" data-v-b3fd67f8>Getting Started</h2><!----></div><div class="items" data-v-b3fd67f8><!--[--><div class="VPSidebarItem level-1 is-link" data-v-b3fd67f8 data-v-b3fd67f8><div class="item" data-v-b3fd67f8><div class="indicator" data-v-b3fd67f8></div><a class="VPLink link link" href="/guide/" data-v-b3fd67f8><!--[--><p class="text" data-v-b3fd67f8>Overview</p><!--]--></a><!----></div><!----></div><div class="VPSidebarItem level-1 is-link" data-v-b3fd67f8 data-v-b3fd67f8><div class="item" data-v-b3fd67f8><div class="indicator" data-v-b3fd67f8></div><a class="VPLink link link" href="/guide/installation.html" data-v-b3fd67f8><!--[--><p class="text" data-v-b3fd67f8>Installation</p><!--]--></a><!----></div><!----></div><div class="VPSidebarItem level-1 is-link" data-v-b3fd67f8 data-v-b3fd67f8><div class="item" data-v-b3fd67f8><div class="indicator" data-v-b3fd67f8></div><a class="VPLink link link" href="/guide/quick-start.html" data-v-b3fd67f8><!--[--><p class="text" data-v-b3fd67f8>Quick Start</p><!--]--></a><!----></div><!----></div><!--]--></div></section></div><div class="no-transition group" data-v-c40bc020><section class="VPSidebarItem level-0 has-active" data-v-c40bc020 data-v-b3fd67f8><div class="item" role="button" tabindex="0" data-v-b3fd67f8><div class="indicator" data-v-b3fd67f8></div><h2 class="text" data-v-b3fd67f8>Topics</h2><!----></div><div class="items" data-v-b3fd67f8><!--[--><div class="VPSidebarItem level-1 is-link" data-v-b3fd67f8 data-v-b3fd67f8><div class="item" data-v-b3fd67f8><div class="indicator" data-v-b3fd67f8></div><a class="VPLink link link" href="/guide/sql-files.html" data-v-b3fd67f8><!--[--><p class="text" data-v-b3fd67f8>SQL File Endpoints</p><!--]--></a><!----></div><!----></div><div class="VPSidebarItem level-1 is-link" data-v-b3fd67f8 data-v-b3fd67f8><div class="item" data-v-b3fd67f8><div class="indicator" data-v-b3fd67f8></div><a class="VPLink link link" href="/guide/configuration.html" data-v-b3fd67f8><!--[--><p class="text" data-v-b3fd67f8>Configuration Guide</p><!--]--></a><!----></div><!----></div><div class="VPSidebarItem level-1 is-link" data-v-b3fd67f8 data-v-b3fd67f8><div class="item" data-v-b3fd67f8><div class="indicator" data-v-b3fd67f8></div><a class="VPLink link link" href="/guide/annotations.html" data-v-b3fd67f8><!--[--><p class="text" data-v-b3fd67f8>Comment Annotations Guide</p><!--]--></a><!----></div><!----></div><div class="VPSidebarItem level-1 is-link" data-v-b3fd67f8 data-v-b3fd67f8><div class="item" data-v-b3fd67f8><div class="indicator" data-v-b3fd67f8></div><a class="VPLink link link" href="/guide/authentication.html" data-v-b3fd67f8><!--[--><p class="text" data-v-b3fd67f8>Authentication</p><!--]--></a><!----></div><!----></div><div class="VPSidebarItem level-1 is-link" data-v-b3fd67f8 data-v-b3fd67f8><div class="item" data-v-b3fd67f8><div class="indicator" data-v-b3fd67f8></div><a class="VPLink link link" href="/guide/sse.html" data-v-b3fd67f8><!--[--><p class="text" data-v-b3fd67f8>Server-Sent Events</p><!--]--></a><!----></div><!----></div><div class="VPSidebarItem level-1 is-link" data-v-b3fd67f8 data-v-b3fd67f8><div class="item" data-v-b3fd67f8><div class="indicator" data-v-b3fd67f8></div><a class="VPLink link link" href="/guide/http-types.html" data-v-b3fd67f8><!--[--><p class="text" data-v-b3fd67f8>HTTP Custom Types</p><!--]--></a><!----></div><!----></div><div class="VPSidebarItem level-1 is-link" data-v-b3fd67f8 data-v-b3fd67f8><div class="item" data-v-b3fd67f8><div class="indicator" data-v-b3fd67f8></div><a class="VPLink link link" href="/guide/proxy.html" data-v-b3fd67f8><!--[--><p class="text" data-v-b3fd67f8>Proxy Endpoints</p><!--]--></a><!----></div><!----></div><div class="VPSidebarItem level-1 is-link" data-v-b3fd67f8 data-v-b3fd67f8><div class="item" data-v-b3fd67f8><div class="indicator" data-v-b3fd67f8></div><a class="VPLink link link" href="/guide/testing.html" data-v-b3fd67f8><!--[--><p class="text" data-v-b3fd67f8>Testing</p><!--]--></a><!----></div><!----></div><div class="VPSidebarItem level-1 is-link" data-v-b3fd67f8 data-v-b3fd67f8><div class="item" data-v-b3fd67f8><div class="indicator" data-v-b3fd67f8></div><a class="VPLink link link" href="/guide/logging.html" data-v-b3fd67f8><!--[--><p class="text" data-v-b3fd67f8>Logging</p><!--]--></a><!----></div><!----></div><!--]--></div></section></div><div class="no-transition group" data-v-c40bc020><section class="VPSidebarItem level-0" data-v-c40bc020 data-v-b3fd67f8><div class="item" role="button" tabindex="0" data-v-b3fd67f8><div class="indicator" data-v-b3fd67f8></div><h2 class="text" data-v-b3fd67f8>Reference</h2><!----></div><div class="items" data-v-b3fd67f8><!--[--><div class="VPSidebarItem level-1 is-link" data-v-b3fd67f8 data-v-b3fd67f8><div class="item" data-v-b3fd67f8><div class="indicator" data-v-b3fd67f8></div><a class="VPLink link link" href="/guide/faq.html" data-v-b3fd67f8><!--[--><p class="text" data-v-b3fd67f8>FAQ & Troubleshooting</p><!--]--></a><!----></div><!----></div><div class="VPSidebarItem level-1 is-link" data-v-b3fd67f8 data-v-b3fd67f8><div class="item" data-v-b3fd67f8><div class="indicator" data-v-b3fd67f8></div><a class="VPLink link link" href="/guide/changelog/" data-v-b3fd67f8><!--[--><p class="text" data-v-b3fd67f8>Changelog</p><!--]--></a><!----></div><!----></div><div class="VPSidebarItem level-1 is-link" data-v-b3fd67f8 data-v-b3fd67f8><div class="item" data-v-b3fd67f8><div class="indicator" data-v-b3fd67f8></div><a class="VPLink link link" href="/examples/" data-v-b3fd67f8><!--[--><p class="text" data-v-b3fd67f8>Examples</p><!--]--></a><!----></div><!----></div><div class="VPSidebarItem level-1 is-link" data-v-b3fd67f8 data-v-b3fd67f8><div class="item" data-v-b3fd67f8><div class="indicator" data-v-b3fd67f8></div><a class="VPLink link link" href="/annotations/" data-v-b3fd67f8><!--[--><p class="text" data-v-b3fd67f8>Annotations Reference</p><!--]--></a><!----></div><!----></div><div class="VPSidebarItem level-1 is-link" data-v-b3fd67f8 data-v-b3fd67f8><div class="item" data-v-b3fd67f8><div class="indicator" data-v-b3fd67f8></div><a class="VPLink link link" href="/config/" data-v-b3fd67f8><!--[--><p class="text" data-v-b3fd67f8>Configuration Reference</p><!--]--></a><!----></div><!----></div><!--]--></div></section></div><!--]--><!--[--><!--]--></nav></aside><div class="VPContent has-sidebar" id="VPContent" data-v-5d98c3a5 data-v-1428d186><div class="VPDoc has-sidebar has-aside" data-v-1428d186 data-v-39a288b8><!--[--><!--]--><div class="container" data-v-39a288b8><div class="aside" data-v-39a288b8><div class="aside-curtain" data-v-39a288b8></div><div class="aside-container" data-v-39a288b8><div class="aside-content" data-v-39a288b8><div class="VPDocAside" data-v-39a288b8 data-v-3f215769><!--[--><!--]--><!--[--><!--]--><nav aria-labelledby="doc-outline-aria-label" class="VPDocAsideOutline" data-v-3f215769 data-v-a5bbad30><div class="content" data-v-a5bbad30><div class="outline-marker" data-v-a5bbad30></div><div aria-level="2" class="outline-title" id="doc-outline-aria-label" role="heading" data-v-a5bbad30>On this page</div><ul class="VPDocOutlineItem root" data-v-a5bbad30 data-v-b933a997><!--[--><!--]--></ul></div></nav><!--[--><!--[--><!--[--><!--[--><div class="comments-outline-link" data-v-6357a5e2><a href="#comments" class="outline-link" data-v-6357a5e2>Comments</a></div><!--]--><!--]--><!--]--><!--]--><div class="spacer" data-v-3f215769></div><!--[--><!--]--><!----><!--[--><!--]--><!--[--><!--]--></div></div></div></div><div class="content" data-v-39a288b8><div class="content-container" data-v-39a288b8><!--[--><!--[--><!--[--><div class="claude-badge" data-v-344998d8><a href="/about" title="AI-assisted, verified against source" data-v-344998d8><img src="https://img.shields.io/badge/AI--assisted-verified_against_source-cc785c?logo=anthropic" alt="AI-assisted, verified against source" data-v-344998d8></a><!----></div><!--]--><!--]--><!--]--><main class="main" data-v-39a288b8><div style="position:relative;" class="vp-doc _guide_testing" data-v-39a288b8><div><h1 id="testing" tabindex="-1">Testing <a class="header-anchor" href="#testing" aria-label="Permalink to "Testing""></a></h1><p>NpgsqlRest ships a built-in <strong>SQL test runner</strong>: write tests for your endpoints as plain <code>.sql</code> files and run them with <code>npgsqlrest --test</code>. A test arranges data with ordinary SQL, invokes a real endpoint <strong>in-process</strong> (the complete pipeline — routing, authorization, parameter binding, execution, serialization — with no network and no running server), captures the response into a temp table, and asserts on it with ordinary SQL. Everything happens inside the test's own transaction, so tests leave no trace.</p><details class="code-collapsible" open><summary>sql</summary><div class="language-sql vp-adaptive-theme line-numbers-mode"><button title="Copy Code" class="copy"></button><span class="lang">sql</span><pre class="shiki shiki-themes catppuccin-latte catppuccin-mocha vp-code" tabindex="0"><code><span class="line"><span style="--shiki-light:#9CA0B0;--shiki-light-font-style:italic;--shiki-dark:#6C7086;--shiki-dark-font-style:italic;">-- tests/get_users_excludes_caller.test.sql</span></span>
<span class="line"><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;">begin</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;">;</span></span>
<span class="line"></span>
<span class="line"><span style="--shiki-light:#9CA0B0;--shiki-light-font-style:italic;--shiki-dark:#6C7086;--shiki-dark-font-style:italic;">-- ARRANGE</span></span>
<span class="line"></span>
<span class="line"><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;">insert into</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;"> app.users (id, email, </span><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;">name</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;">) </span><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;">values</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;"> (</span><span style="--shiki-light:#FE640B;--shiki-dark:#FAB387;">100</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;">, </span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;">'x@example.com'</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;">, </span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;">'Fixture'</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;">);</span></span>
<span class="line"></span>
<span class="line"><span style="--shiki-light:#9CA0B0;--shiki-light-font-style:italic;--shiki-dark:#6C7086;--shiki-dark-font-style:italic;">-- ACT</span></span>
<span class="line"></span>
<span class="line"><span style="--shiki-light:#9CA0B0;--shiki-light-font-style:italic;--shiki-dark:#6C7086;--shiki-dark-font-style:italic;">/*</span></span>
<span class="line"><span style="--shiki-light:#9CA0B0;--shiki-light-font-style:italic;--shiki-dark:#6C7086;--shiki-dark-font-style:italic;">GET /api/get-users</span></span>
<span class="line"><span style="--shiki-light:#9CA0B0;--shiki-light-font-style:italic;--shiki-dark:#6C7086;--shiki-dark-font-style:italic;"># @claim user_id=1</span></span>
<span class="line"><span style="--shiki-light:#9CA0B0;--shiki-light-font-style:italic;--shiki-dark:#6C7086;--shiki-dark-font-style:italic;">*/</span></span>
<span class="line"></span>
<span class="line"><span style="--shiki-light:#9CA0B0;--shiki-light-font-style:italic;--shiki-dark:#6C7086;--shiki-dark-font-style:italic;">-- ASSERT</span></span>
<span class="line"></span>
<span class="line"><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;">select</span><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;"> status</span><span style="--shiki-light:#179299;--shiki-dark:#94E2D5;"> =</span><span style="--shiki-light:#FE640B;--shiki-dark:#FAB387;"> 200</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;">, </span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;">'authenticated caller gets 200'</span></span>
<span class="line"><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;">from</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;"> _response;</span></span>
<span class="line"><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;">select</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;"> body::jsonb @</span><span style="--shiki-light:#179299;--shiki-dark:#94E2D5;">></span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> '[{"email": "x@example.com"}]'</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;">, </span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;">'the fixture user is listed'</span></span>
<span class="line"><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;">from</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;"> _response;</span></span>
<span class="line"></span>
<span class="line"><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;">rollback</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;">;</span></span></code></pre><div class="line-numbers-wrapper" aria-hidden="true"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br><span class="line-number">22</span><br></div></div></details><details class="code-collapsible" open><summary>code</summary><div class="language- vp-adaptive-theme line-numbers-mode"><button title="Copy Code" class="copy"></button><span class="lang"></span><pre class="shiki shiki-themes catppuccin-latte catppuccin-mocha vp-code" tabindex="0"><code><span class="line"><span>NpgsqlRest test runner — 9 file(s)</span></span>
<span class="line"><span>PASS tests/get_users_excludes_caller.test.sql (2 assertions, 52ms)</span></span>
<span class="line"><span>...</span></span>
<span class="line"><span>19 passed, 0 failed, 0 error(s) — 19 assertions in 9 files</span></span>
<span class="line"><span></span></span>
<span class="line"><span>endpoint coverage: 2/2 (100%)</span></span></code></pre><div class="line-numbers-wrapper" aria-hidden="true"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br></div></div></details><p>This guide covers the full feature. The configuration reference lives at <a href="./../config/test-runner.html">Test Runner configuration</a>; three complete working projects live in the repository: <a href="https://github.com/NpgsqlRest/npgsqlrest-docs/tree/master/examples/19_testing_basic" target="_blank" rel="noreferrer"><code>examples/19_testing_basic</code></a>, <a href="https://github.com/NpgsqlRest/npgsqlrest-docs/tree/master/examples/20_testing_newdb" target="_blank" rel="noreferrer"><code>examples/20_testing_newdb</code></a>, and <a href="https://github.com/NpgsqlRest/npgsqlrest-docs/tree/master/examples/21_testing_isolation" target="_blank" rel="noreferrer"><code>examples/21_testing_isolation</code></a>.</p><h2 id="quick-start" tabindex="-1">Quick start <a class="header-anchor" href="#quick-start" aria-label="Permalink to "Quick start""></a></h2><ol><li>Put a test file next to your endpoint SQL (the <em>co-located</em> layout):</li></ol><details class="code-collapsible" open><summary>sql</summary><div class="language-sql vp-adaptive-theme line-numbers-mode"><button title="Copy Code" class="copy"></button><span class="lang">sql</span><pre class="shiki shiki-themes catppuccin-latte catppuccin-mocha vp-code" tabindex="0"><code><span class="line"><span style="--shiki-light:#9CA0B0;--shiki-light-font-style:italic;--shiki-dark:#6C7086;--shiki-dark-font-style:italic;">-- sql/normalize_email.sql (the endpoint)</span></span>
<span class="line"><span style="--shiki-light:#9CA0B0;--shiki-light-font-style:italic;--shiki-dark:#6C7086;--shiki-dark-font-style:italic;">/*</span></span>
<span class="line"><span style="--shiki-light:#9CA0B0;--shiki-light-font-style:italic;--shiki-dark:#6C7086;--shiki-dark-font-style:italic;">HTTP GET</span></span>
<span class="line"><span style="--shiki-light:#9CA0B0;--shiki-light-font-style:italic;--shiki-dark:#6C7086;--shiki-dark-font-style:italic;">*/</span></span>
<span class="line"><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;">select</span><span style="--shiki-light:#1E66F5;--shiki-light-font-style:italic;--shiki-dark:#89B4FA;--shiki-dark-font-style:italic;"> lower</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;">(</span><span style="--shiki-light:#1E66F5;--shiki-light-font-style:italic;--shiki-dark:#89B4FA;--shiki-dark-font-style:italic;">trim</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;">(:email)) </span><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;">as</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;"> normalized;</span></span></code></pre><div class="line-numbers-wrapper" aria-hidden="true"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div></details><details class="code-collapsible" open><summary>sql</summary><div class="language-sql vp-adaptive-theme line-numbers-mode"><button title="Copy Code" class="copy"></button><span class="lang">sql</span><pre class="shiki shiki-themes catppuccin-latte catppuccin-mocha vp-code" tabindex="0"><code><span class="line"><span style="--shiki-light:#9CA0B0;--shiki-light-font-style:italic;--shiki-dark:#6C7086;--shiki-dark-font-style:italic;">-- sql/normalize_email.test.sql (the test)</span></span>
<span class="line"><span style="--shiki-light:#9CA0B0;--shiki-light-font-style:italic;--shiki-dark:#6C7086;--shiki-dark-font-style:italic;">/*</span></span>
<span class="line"><span style="--shiki-light:#9CA0B0;--shiki-light-font-style:italic;--shiki-dark:#6C7086;--shiki-dark-font-style:italic;">GET /api/normalize-email?email=%20X%40Y.z%20</span></span>
<span class="line"><span style="--shiki-light:#9CA0B0;--shiki-light-font-style:italic;--shiki-dark:#6C7086;--shiki-dark-font-style:italic;">*/</span></span>
<span class="line"><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;">select</span><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;"> status</span><span style="--shiki-light:#179299;--shiki-dark:#94E2D5;"> =</span><span style="--shiki-light:#FE640B;--shiki-dark:#FAB387;"> 200</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;">, </span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;">'endpoint responds'</span><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;"> from</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;"> _response;</span></span>
<span class="line"><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;">select</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;"> body::jsonb </span><span style="--shiki-light:#179299;--shiki-dark:#94E2D5;">->></span><span style="--shiki-light:#FE640B;--shiki-dark:#FAB387;"> 0</span><span style="--shiki-light:#179299;--shiki-dark:#94E2D5;"> =</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> 'x@y.z'</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;">, </span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;">'trims and lowercases'</span><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;"> from</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;"> _response;</span></span></code></pre><div class="line-numbers-wrapper" aria-hidden="true"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br></div></div></details><ol start="2"><li>Point the runner at the tests:</li></ol><details class="code-collapsible" open><summary>json</summary><div class="language-json vp-adaptive-theme line-numbers-mode"><button title="Copy Code" class="copy"></button><span class="lang">json</span><pre class="shiki shiki-themes catppuccin-latte catppuccin-mocha vp-code" tabindex="0"><code><span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">{</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">TestRunner</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> {</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">FilePattern</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> "./sql/**/*.test.sql"</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> }</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">}</span></span></code></pre><div class="line-numbers-wrapper" aria-hidden="true"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div></details><ol start="3"><li>Run:</li></ol><details class="code-collapsible" open><summary>sh</summary><div class="language-sh vp-adaptive-theme line-numbers-mode"><button title="Copy Code" class="copy"></button><span class="lang">sh</span><pre class="shiki shiki-themes catppuccin-latte catppuccin-mocha vp-code" tabindex="0"><code><span class="line"><span style="--shiki-light:#1E66F5;--shiki-light-font-style:italic;--shiki-dark:#89B4FA;--shiki-dark-font-style:italic;">npgsqlrest</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> ./config.json</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> --test</span></span></code></pre><div class="line-numbers-wrapper" aria-hidden="true"><span class="line-number">1</span><br></div></div></details><p>That's the whole setup. Test files are automatically <strong>excluded from endpoint discovery</strong> — <a href="./../config/sql-file-source.html"><code>SqlFileSource.SkipPattern</code></a> defaults to <code>"*.test.sql"</code> — so an HTTP block inside a test is never mistaken for an endpoint annotation.</p><h2 id="how-it-works" tabindex="-1">How it works <a class="header-anchor" href="#how-it-works" aria-label="Permalink to "How it works""></a></h2><p>In <code>--test</code> mode the client builds the full endpoint middleware exactly as in normal operation — endpoints from database routines and/or SQL files, authentication, custom parameters, everything — but instead of starting the web server it runs the test files and exits with a result code.</p><p>The critical property is <strong>connection affinity</strong>: an endpoint invoked from a test runs on the <em>test's own connection</em>, inside the <em>test's own transaction</em>. A test can <code>begin</code>, insert fixture rows, call an endpoint that <strong>sees those uncommitted rows</strong>, assert on the response, and <code>rollback</code> — the database is untouched afterwards. Each test file gets its own <strong>non-pooled</strong> physical connection (fresh session: no temp-table, GUC, or prepared-statement carryover), and files run <strong>in parallel</strong> (<code>MaxParallelism</code>, default = processor count). If a file never rolls back, closing its physical connection aborts the open transaction — that is the safety net.</p><p>Test-mode invariants, applied automatically:</p><ul><li><code>WrapInTransaction</code> is forced off — the test file owns transaction control; the runner never injects <code>BEGIN</code>/<code>COMMIT</code>/<code>ROLLBACK</code>.</li><li>Response caching is disabled — a test never sees another test's cached response.</li><li>Code generation (HTTP files, TypeScript client, OpenAPI) is skipped — a test run never rewrites generated artifacts.</li></ul><h2 id="test-file-anatomy" tabindex="-1">Test file anatomy <a class="header-anchor" href="#test-file-anatomy" aria-label="Permalink to "Test file anatomy""></a></h2><p>A test file is a sequence of SQL statements and HTTP blocks, executed strictly <strong>in order</strong>, <strong>statement by statement</strong> (like <code>psql</code>): each statement runs in autocommit unless the file opens its own transaction. Semicolon splitting understands comments, string literals with <code>''</code> escapes, and dollar-quoted bodies — a <code>do $$ … $$;</code> block stays whole.</p><h3 id="assertions" tabindex="-1">Assertions <a class="header-anchor" href="#assertions" aria-label="Permalink to "Assertions""></a></h3><p>A reported test is one of:</p><p><strong>A boolean-returning <code>SELECT</code>.</strong> If the first column is <code>boolean</code>, the statement is an assertion: the first row's value must be true (<code>false</code> or <code>null</code> fails; zero rows passes vacuously). The optional <strong>second column is the assertion's name</strong>, shown in the report and used as the JUnit test-case name:</p><details class="code-collapsible" open><summary>sql</summary><div class="language-sql vp-adaptive-theme line-numbers-mode"><button title="Copy Code" class="copy"></button><span class="lang">sql</span><pre class="shiki shiki-themes catppuccin-latte catppuccin-mocha vp-code" tabindex="0"><code><span class="line"><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;">select</span><span style="--shiki-light:#1E66F5;--shiki-light-font-style:italic;--shiki-dark:#89B4FA;--shiki-dark-font-style:italic;"> count</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;">(</span><span style="--shiki-light:#179299;--shiki-dark:#94E2D5;">*</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;">) </span><span style="--shiki-light:#179299;--shiki-dark:#94E2D5;">=</span><span style="--shiki-light:#FE640B;--shiki-dark:#FAB387;"> 3</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;">, </span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;">'exactly three users are seeded'</span><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;"> from</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;"> app.users;</span></span></code></pre><div class="line-numbers-wrapper" aria-hidden="true"><span class="line-number">1</span><br></div></div></details><p><strong>A <code>do</code> block.</strong> Passes unless it raises — <code>assert</code> inside a DO block raises SQLSTATE <code>P0004</code>, reported as a failure with the assert message. One DO block = one reported test:</p><details class="code-collapsible" open><summary>sql</summary><div class="language-sql vp-adaptive-theme line-numbers-mode"><button title="Copy Code" class="copy"></button><span class="lang">sql</span><pre class="shiki shiki-themes catppuccin-latte catppuccin-mocha vp-code" tabindex="0"><code><span class="line"><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;">do $$ </span><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;">begin</span></span>
<span class="line"><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;"> assert app.normalize_email(</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;">' X@Y.z '</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;">) </span><span style="--shiki-light:#179299;--shiki-dark:#94E2D5;">=</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> 'x@y.z'</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;">, </span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;">'should trim and lowercase'</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;">;</span></span>
<span class="line"><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;">end</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;"> $$;</span></span></code></pre><div class="line-numbers-wrapper" aria-hidden="true"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div></details><p>Any other statement is <em>arrange/act</em> — not counted; it only surfaces if it errors. Any SQL error (other than an assert) is reported as an <strong>error</strong> with its SQLSTATE, message, statement text, and <code>file:line</code>.</p><p>Failing behavior is <strong>fail-fast per file</strong>: after the first failed/errored assertion the rest of the file does not run. Assertions that passed before the failure are still credited.</p><h3 id="http-blocks-invoking-endpoints" tabindex="-1">HTTP blocks: invoking endpoints <a class="header-anchor" href="#http-blocks-invoking-endpoints" aria-label="Permalink to "HTTP blocks: invoking endpoints""></a></h3><p>An HTTP request is embedded in a <strong>block comment</strong> whose first content line is a request line — a single-request subset of the standard <code>.http</code> file syntax:</p><details class="code-collapsible" open><summary>sql</summary><div class="language-sql vp-adaptive-theme line-numbers-mode"><button title="Copy Code" class="copy"></button><span class="lang">sql</span><pre class="shiki shiki-themes catppuccin-latte catppuccin-mocha vp-code" tabindex="0"><code><span class="line"><span style="--shiki-light:#9CA0B0;--shiki-light-font-style:italic;--shiki-dark:#6C7086;--shiki-dark-font-style:italic;">/*</span></span>
<span class="line"><span style="--shiki-light:#9CA0B0;--shiki-light-font-style:italic;--shiki-dark:#6C7086;--shiki-dark-font-style:italic;">POST /api/create-user</span></span>
<span class="line"><span style="--shiki-light:#9CA0B0;--shiki-light-font-style:italic;--shiki-dark:#6C7086;--shiki-dark-font-style:italic;">Content-Type: application/json</span></span>
<span class="line"><span style="--shiki-light:#9CA0B0;--shiki-light-font-style:italic;--shiki-dark:#6C7086;--shiki-dark-font-style:italic;"># @claim user_id=42</span></span>
<span class="line"><span style="--shiki-light:#9CA0B0;--shiki-light-font-style:italic;--shiki-dark:#6C7086;--shiki-dark-font-style:italic;"># @claim roles=admin</span></span>
<span class="line"><span style="--shiki-light:#9CA0B0;--shiki-light-font-style:italic;--shiki-dark:#6C7086;--shiki-dark-font-style:italic;"># @response created</span></span>
<span class="line"></span>
<span class="line"><span style="--shiki-light:#9CA0B0;--shiki-light-font-style:italic;--shiki-dark:#6C7086;--shiki-dark-font-style:italic;">{"name": "Grace Hopper", "email": "grace@example.com"}</span></span>
<span class="line"><span style="--shiki-light:#9CA0B0;--shiki-light-font-style:italic;--shiki-dark:#6C7086;--shiki-dark-font-style:italic;">*/</span></span></code></pre><div class="line-numbers-wrapper" aria-hidden="true"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br></div></div></details><ul><li><strong>Request line:</strong> <code>[HTTP] METHOD /path[?query] [HTTP/x]</code> — method is <code>GET</code>/<code>POST</code>/<code>PUT</code>/<code>DELETE</code>; the path must equal the endpoint's full path <strong>including <code>UrlPathPrefix</code></strong> (default <code>/api</code>). A block comment whose first line is not a valid request line is an ordinary comment — ignored.</li><li><strong>Headers:</strong> <code>Name: Value</code> lines after the request line.</li><li><strong>Directives</strong> (before the body): <a href="./../annotations/test-claim.html"><code># @claim name=value</code></a> sets the acting principal (repeatable; no <code>@claim</code> = anonymous), <a href="./../annotations/test-response.html"><code># @response name</code></a> names the captured response table.</li><li><strong>Body:</strong> everything after the first <strong>blank line</strong>, verbatim.</li></ul><p>An HTTP block is an <strong>act</strong> step, not an assertion — the assertions are the SQL statements that follow it. One request per block; use multiple blocks for multiple calls.</p><p>Endpoint kinds that cannot work in-process are rejected with a clear error: <strong>SSE</strong>, <strong>upload</strong>, <strong>login/logout</strong> (inject the principal with <code># @claim</code> instead), and <strong>outbound proxy/HTTP-type</strong> endpoints (tests must not call external services). A request whose path matches <strong>no endpoint</strong> still runs — a test may assert a 404 deliberately — but logs a warning, since the most common cause is a path typo or a missing <code>/api</code> prefix.</p><h3 id="the-response-table" tabindex="-1">The response table <a class="header-anchor" href="#the-response-table" aria-label="Permalink to "The response table""></a></h3><p>Each HTTP block's response lands in its own fresh temp table on the test's connection — default <code>_response</code> (one block per file) or <code>_response_1</code>, <code>_response_2</code>, … (several). Columns: <code>status int</code>, <code>body text</code>, <code>content_type text</code>, <code>headers jsonb</code>, <code>is_success boolean</code> — all <a href="./../config/test-runner.html#responsetemptable">configurable</a>. Need to <em>see</em> a captured response after the run (temp tables vanish with the rollback)? Set <a href="./../config/test-runner.html#debugtable-inspect-responses-after-the-run"><code>ResponseTempTable.DebugTable</code></a> to mirror every response into a permanent, query-editor-friendly table.</p><details class="code-collapsible" open><summary>sql</summary><div class="language-sql vp-adaptive-theme line-numbers-mode"><button title="Copy Code" class="copy"></button><span class="lang">sql</span><pre class="shiki shiki-themes catppuccin-latte catppuccin-mocha vp-code" tabindex="0"><code><span class="line"><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;">select</span><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;"> status</span><span style="--shiki-light:#179299;--shiki-dark:#94E2D5;"> =</span><span style="--shiki-light:#FE640B;--shiki-dark:#FAB387;"> 200</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;">, </span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;">'status ok'</span><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;"> from</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;"> _response;</span></span>
<span class="line"><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;">select</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;"> body::jsonb </span><span style="--shiki-light:#179299;--shiki-dark:#94E2D5;">->></span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> 'email'</span><span style="--shiki-light:#179299;--shiki-dark:#94E2D5;"> =</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> 'x@y.z'</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;">, </span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;">'right user returned'</span><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;"> from</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;"> _response;</span></span>
<span class="line"><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;">select</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;"> headers </span><span style="--shiki-light:#179299;--shiki-dark:#94E2D5;">->></span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> 'Content-Type'</span><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;"> like</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> 'application/json%'</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;">, </span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;">'json response'</span><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;"> from</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;"> _response;</span></span></code></pre><div class="line-numbers-wrapper" aria-hidden="true"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div></details><h3 id="transactions-when-to-begin-rollback" tabindex="-1">Transactions: when to <code>begin</code>/<code>rollback</code> <a class="header-anchor" href="#transactions-when-to-begin-rollback" aria-label="Permalink to "Transactions: when to `begin`/`rollback`""></a></h3><p>The file owns transaction control. Two patterns:</p><ul><li><strong>The test writes something</strong> → wrap it: <code>begin; … rollback;</code>. Everything — fixtures <em>and</em> endpoint writes — is discarded.</li><li><strong>The test only reads</strong> → no transaction needed at all. Don't cargo-cult <code>begin</code>/<code>rollback</code> onto read-only tests.</li></ul><p>One caveat worth knowing: <strong>sequences are non-transactional</strong>. Every <code>nextval()</code> sticks even through <code>rollback</code>, so on a shared database a generated id depends on what ran before. Don't assert generated ids on a shared database — or give the test its own database (see <a href="#scenario-template-database-and-per-test-isolation">per-test isolation</a> below).</p><h3 id="fixtures-without-inserting-the-whole-database-deferrable-constraints" tabindex="-1">Fixtures without inserting the whole database: deferrable constraints <a class="header-anchor" href="#fixtures-without-inserting-the-whole-database-deferrable-constraints" aria-label="Permalink to "Fixtures without inserting the whole database: deferrable constraints""></a></h3><p>The classic fixture problem: to insert one <code>orders</code> row you need a <code>user</code>, which needs a <code>company</code>, which needs a <code>country</code>… and suddenly every test starts by populating half the schema. PostgreSQL solves this elegantly — and the rollback-based test pattern is exactly the situation the solution was made for.</p><p>Declare foreign keys <strong><code>deferrable</code></strong> in your schema:</p><details class="code-collapsible" open><summary>sql</summary><div class="language-sql vp-adaptive-theme line-numbers-mode"><button title="Copy Code" class="copy"></button><span class="lang">sql</span><pre class="shiki shiki-themes catppuccin-latte catppuccin-mocha vp-code" tabindex="0"><code><span class="line"><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;">create</span><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;"> table</span><span style="--shiki-light:#1E66F5;--shiki-light-font-style:italic;--shiki-dark:#89B4FA;--shiki-dark-font-style:italic;"> posts</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;"> (</span></span>
<span class="line"><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;"> id </span><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;">int</span><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;"> primary key</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;">,</span></span>
<span class="line"><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;"> user_id </span><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;">int</span><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;"> references</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;"> users (user_id) deferrable,</span></span>
<span class="line"><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;"> content </span><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;">text</span><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;"> not null</span></span>
<span class="line"><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;">);</span></span></code></pre><div class="line-numbers-wrapper" aria-hidden="true"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div></details><p>Then a test defers the checks and inserts <strong>only what it needs</strong> — in any order, referencing rows that never exist:</p><details class="code-collapsible" open><summary>sql</summary><div class="language-sql vp-adaptive-theme line-numbers-mode"><button title="Copy Code" class="copy"></button><span class="lang">sql</span><pre class="shiki shiki-themes catppuccin-latte catppuccin-mocha vp-code" tabindex="0"><code><span class="line"><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;">begin</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;">;</span></span>
<span class="line"></span>
<span class="line"><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;">set</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;"> constraints all deferred;</span></span>
<span class="line"></span>
<span class="line"><span style="--shiki-light:#9CA0B0;--shiki-light-font-style:italic;--shiki-dark:#6C7086;--shiki-dark-font-style:italic;">-- one post by a user that is never inserted — legal, because the FK check</span></span>
<span class="line"><span style="--shiki-light:#9CA0B0;--shiki-light-font-style:italic;--shiki-dark:#6C7086;--shiki-dark-font-style:italic;">-- would run at COMMIT, and this transaction never commits</span></span>
<span class="line"><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;">insert into</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;"> posts (id, user_id, content) </span><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;">values</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;"> (</span><span style="--shiki-light:#FE640B;--shiki-dark:#FAB387;">1</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;">, </span><span style="--shiki-light:#FE640B;--shiki-dark:#FAB387;">999</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;">, </span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;">'fixture post'</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;">);</span></span>
<span class="line"></span>
<span class="line"><span style="--shiki-light:#9CA0B0;--shiki-light-font-style:italic;--shiki-dark:#6C7086;--shiki-dark-font-style:italic;">/*</span></span>
<span class="line"><span style="--shiki-light:#9CA0B0;--shiki-light-font-style:italic;--shiki-dark:#6C7086;--shiki-dark-font-style:italic;">GET /api/get-posts</span></span>
<span class="line"><span style="--shiki-light:#9CA0B0;--shiki-light-font-style:italic;--shiki-dark:#6C7086;--shiki-dark-font-style:italic;">*/</span></span>
<span class="line"><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;">select</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;"> body::jsonb </span><span style="--shiki-light:#179299;--shiki-dark:#94E2D5;">-></span><span style="--shiki-light:#FE640B;--shiki-dark:#FAB387;"> 0</span><span style="--shiki-light:#179299;--shiki-dark:#94E2D5;"> ->></span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> 'content'</span><span style="--shiki-light:#179299;--shiki-dark:#94E2D5;"> =</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> 'fixture post'</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;">, </span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;">'fixture is served'</span><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;"> from</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;"> _response;</span></span>
<span class="line"></span>
<span class="line"><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;">rollback</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;">;</span></span></code></pre><div class="line-numbers-wrapper" aria-hidden="true"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br></div></div></details><p>Deferrable constraints are checked at <strong><code>COMMIT</code></strong> — and a test that ends in <code>rollback</code> never gets there, so the checks simply never run. No fixture factories, no dependency-ordered builders, no "insert the world" preamble: each test states exactly the rows it is about, and the endpoint's <code>LEFT JOIN</code>s resolve the missing references to <code>null</code> just as they would for genuinely absent data.</p><p>Two things to know:</p><ul><li>The constraint must be <strong>declared</strong> <code>deferrable</code> — <code>set constraints all deferred</code> has no effect on the default <code>NOT DEFERRABLE</code> constraints. Making FKs deferrable is a one-time schema decision that costs nothing in production (they still check at commit).</li><li>This composes with the ordinary fixture style: insert the full graph in dependency order when the test is <em>about</em> the graph, and defer when it isn't. <a href="https://github.com/NpgsqlRest/npgsqlrest-docs/tree/main/examples/20_testing_newdb" target="_blank" rel="noreferrer">Example 20</a> demonstrates both side by side, and the <a href="/blog/end-to-end-static-type-checking-postgresql-typescript.html">end-to-end type checking post</a> covers the technique in depth.</li></ul><h2 id="reusing-sql-includes" tabindex="-1">Reusing SQL: includes <a class="header-anchor" href="#reusing-sql-includes" aria-label="Permalink to "Reusing SQL: includes""></a></h2><p>Test files support psql-style includes: <code>\i path</code> (cwd-relative) and <code>\ir path</code> (relative to the including file). Semantics are <strong>as if you pasted the content yourself</strong>: SQL statements <em>and</em> HTTP blocks are spliced in place, run on the test's connection inside its transaction, and HTTP blocks participate in response-table numbering.</p><details class="code-collapsible" open><summary>sql</summary><div class="language-sql vp-adaptive-theme line-numbers-mode"><button title="Copy Code" class="copy"></button><span class="lang">sql</span><pre class="shiki shiki-themes catppuccin-latte catppuccin-mocha vp-code" tabindex="0"><code><span class="line"><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;">begin</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;">;</span></span>
<span class="line"></span>
<span class="line"><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;">\ir fixtures</span><span style="--shiki-light:#179299;--shiki-dark:#94E2D5;">/</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;">extra_users.sql </span><span style="--shiki-light:#9CA0B0;--shiki-light-font-style:italic;--shiki-dark:#6C7086;--shiki-dark-font-style:italic;">-- reusable fixture, rolls back with the test</span></span>
<span class="line"></span>
<span class="line"><span style="--shiki-light:#9CA0B0;--shiki-light-font-style:italic;--shiki-dark:#6C7086;--shiki-dark-font-style:italic;">/*</span></span>
<span class="line"><span style="--shiki-light:#9CA0B0;--shiki-light-font-style:italic;--shiki-dark:#6C7086;--shiki-dark-font-style:italic;">GET /api/get-users</span></span>
<span class="line"><span style="--shiki-light:#9CA0B0;--shiki-light-font-style:italic;--shiki-dark:#6C7086;--shiki-dark-font-style:italic;"># @claim user_id=1</span></span>
<span class="line"><span style="--shiki-light:#9CA0B0;--shiki-light-font-style:italic;--shiki-dark:#6C7086;--shiki-dark-font-style:italic;">*/</span></span>
<span class="line"><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;">select</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;"> jsonb_array_length(body::jsonb) </span><span style="--shiki-light:#179299;--shiki-dark:#94E2D5;">=</span><span style="--shiki-light:#FE640B;--shiki-dark:#FAB387;"> 5</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;">,</span></span>
<span class="line"><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> 'three seeded + two fixture users are listed'</span></span>
<span class="line"><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;">from</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;"> _response;</span></span>
<span class="line"></span>
<span class="line"><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;">rollback</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;">;</span></span></code></pre><div class="line-numbers-wrapper" aria-hidden="true"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br></div></div></details><p>An included file that contains <strong>only comments</strong> is an <em>annotation profile</em>: included in a file's header, its annotations (<code>@setup</code>, <code>@teardown</code>, <code>@connection</code>, <code>@tag</code>) count as if written in-place — one shared profile can configure a whole family of tests. Includes nest (up to 16 levels), and cycles are detected and reported.</p><p><strong>Pattern: schema-relaxing system scripts.</strong> Because PostgreSQL DDL is <strong>transactional</strong>, an include can temporarily <em>reshape the schema</em> for the test — and the rollback restores everything. The classic use: a shared script that drops <code>NOT NULL</code> from columns that are irrelevant to most tests, so fixtures only mention the columns they are actually about:</p><details class="code-collapsible" open><summary>sql</summary><div class="language-sql vp-adaptive-theme line-numbers-mode"><button title="Copy Code" class="copy"></button><span class="lang">sql</span><pre class="shiki shiki-themes catppuccin-latte catppuccin-mocha vp-code" tabindex="0"><code><span class="line"><span style="--shiki-light:#9CA0B0;--shiki-light-font-style:italic;--shiki-dark:#6C7086;--shiki-dark-font-style:italic;">-- fixtures/relax_users.sql — make the noise columns optional for this test only</span></span>
<span class="line"><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;">alter</span><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;"> table</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;"> users </span><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;">alter</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;"> column legal_name </span><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;">drop</span><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;"> not null</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;">;</span></span>
<span class="line"><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;">alter</span><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;"> table</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;"> users </span><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;">alter</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;"> column billing_address </span><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;">drop</span><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;"> not null</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;">;</span></span>
<span class="line"><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;">alter</span><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;"> table</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;"> users </span><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;">alter</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;"> column marketing_consent </span><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;">drop</span><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;"> not null</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;">;</span></span></code></pre><div class="line-numbers-wrapper" aria-hidden="true"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div></details><details class="code-collapsible" open><summary>sql</summary><div class="language-sql vp-adaptive-theme line-numbers-mode"><button title="Copy Code" class="copy"></button><span class="lang">sql</span><pre class="shiki shiki-themes catppuccin-latte catppuccin-mocha vp-code" tabindex="0"><code><span class="line"><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;">begin</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;">;</span></span>
<span class="line"><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;">\ir fixtures</span><span style="--shiki-light:#179299;--shiki-dark:#94E2D5;">/</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;">relax_users.sql</span></span>
<span class="line"></span>
<span class="line"><span style="--shiki-light:#9CA0B0;--shiki-light-font-style:italic;--shiki-dark:#6C7086;--shiki-dark-font-style:italic;">-- insert ONLY what this test is about — the relaxed columns stay null</span></span>
<span class="line"><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;">insert into</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;"> users (id, email) </span><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;">values</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;"> (</span><span style="--shiki-light:#FE640B;--shiki-dark:#FAB387;">100</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;">, </span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;">'fixture@example.com'</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;">);</span></span>
<span class="line"></span>
<span class="line"><span style="--shiki-light:#9CA0B0;--shiki-light-font-style:italic;--shiki-dark:#6C7086;--shiki-dark-font-style:italic;">/*</span></span>
<span class="line"><span style="--shiki-light:#9CA0B0;--shiki-light-font-style:italic;--shiki-dark:#6C7086;--shiki-dark-font-style:italic;">GET /api/get-users</span></span>
<span class="line"><span style="--shiki-light:#9CA0B0;--shiki-light-font-style:italic;--shiki-dark:#6C7086;--shiki-dark-font-style:italic;"># @claim user_id=1</span></span>
<span class="line"><span style="--shiki-light:#9CA0B0;--shiki-light-font-style:italic;--shiki-dark:#6C7086;--shiki-dark-font-style:italic;">*/</span></span>
<span class="line"><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;">select</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;"> body::jsonb @</span><span style="--shiki-light:#179299;--shiki-dark:#94E2D5;">></span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> '[{"email": "fixture@example.com"}]'</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;">, </span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;">'fixture listed'</span><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;"> from</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;"> _response;</span></span>
<span class="line"></span>
<span class="line"><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;">rollback</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;">; </span><span style="--shiki-light:#9CA0B0;--shiki-light-font-style:italic;--shiki-dark:#6C7086;--shiki-dark-font-style:italic;">-- the ALTERs roll back too — the schema is untouched</span></span></code></pre><div class="line-numbers-wrapper" aria-hidden="true"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br></div></div></details><p>This composes with <a href="#fixtures-without-inserting-the-whole-database-deferrable-constraints">deferrable constraints</a>: defer the FKs, relax the <code>NOT NULL</code>s, and a fixture shrinks to exactly the columns and rows under test. One caveat: <code>ALTER TABLE</code> takes an exclusive lock until the transaction ends, so on a <strong>shared</strong> test database this serializes parallel tests touching the same table — it shines with <a href="#scenario-template-database-and-per-test-isolation">per-test isolated databases</a>, where the lock contends with nobody.</p><h2 id="per-file-annotations" tabindex="-1">Per-file annotations <a class="header-anchor" href="#per-file-annotations" aria-label="Permalink to "Per-file annotations""></a></h2><p>Four header annotations (leading <code>--</code> comments before the first statement) configure an individual file — see their reference pages for details:</p><div class="table-container"><div class="table-wrapper"><table tabindex="0"><thead><tr><th>Annotation</th><th>Effect</th></tr></thead><tbody><tr><td><a href="./../annotations/test-setup.html"><code>-- @setup Step [Step ...]</code></a></td><td>Run named steps before this file.</td></tr><tr><td><a href="./../annotations/test-teardown.html"><code>-- @teardown Step [Step ...]</code></a></td><td>Run named steps after this file — always.</td></tr><tr><td><a href="./../annotations/test-connection.html"><code>-- @connection Name</code></a></td><td>Run this file (SQL + endpoint calls) on a named connection.</td></tr><tr><td><a href="./../annotations/test-tag.html"><code>-- @tag name [name ...]</code></a></td><td>Tag the file for <code>Tag</code>/<code>ExcludeTag</code> filtering.</td></tr></tbody></table></div></div><p>All are repeatable; names may be whitespace- or comma-separated; steps run in written order.</p><h2 id="setup-teardown-and-named-steps" tabindex="-1">Setup, Teardown, and named steps <a class="header-anchor" href="#setup-teardown-and-named-steps" aria-label="Permalink to "Setup, Teardown, and named steps""></a></h2><p><code>TestRunner.Setup</code> runs once <strong>before endpoint discovery</strong>; <code>TestRunner.Teardown</code> runs once at the end — <strong>always</strong>, best-effort, even on failure, Ctrl+C, SIGTERM, or a hard startup error. Steps run in the exact order written. Each step is either an inline object or a <strong>name</strong> from the reusable <code>Steps</code> registry:</p><details class="code-collapsible" open><summary>json</summary><div class="language-json vp-adaptive-theme line-numbers-mode"><button title="Copy Code" class="copy"></button><span class="lang">json</span><pre class="shiki shiki-themes catppuccin-latte catppuccin-mocha vp-code" tabindex="0"><code><span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">{</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">TestRunner</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> {</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">Steps</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> {</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">CreateDatabase</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> {</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">Sql</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> "create database app_test_{rnd5}"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">,</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">ConnectionName</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> "Admin"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> },</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">ApplyMigrations</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> {</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">Command</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> "bun db up"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">,</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">WorkingDirectory</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> "."</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> },</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">DropDatabase</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> {</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">Sql</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> "drop database if exists app_test_{rnd5} with (force)"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">,</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">ConnectionName</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> "Admin"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> }</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> },</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">Setup</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> [</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> "CreateDatabase"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">,</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> "ApplyMigrations"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> ],</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">Teardown</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> [</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> "DropDatabase"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> ]</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> }</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">}</span></span></code></pre><div class="line-numbers-wrapper" aria-hidden="true"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br></div></div></details><p>Three step shapes:</p><ul><li><code>{ "Sql": "..." }</code> — SQL text, statement by statement, on the test connection or any named <code>ConnectionStrings</code> entry (<code>"ConnectionName"</code>). This is how <code>create database</code> works as a plain step — the runner never issues DDL on its own.</li><li><code>{ "SqlFile": "..." }</code> — same, from a file.</li><li><code>{ "Command": "...", "WorkingDirectory": "..." }</code> — an OS shell command.</li></ul><p>Every step also has an <code>"Enabled"</code> flag (default <code>true</code>): a disabled step is <strong>ignored wherever referenced</strong> — never an error. That's how the default configuration ships ready-made <a href="./../config/test-runner.html#steps">example steps</a> (create/drop a test database, apply a schema file, run a migration tool, start/stop a Docker PostgreSQL) that you copy and flip on instead of typing.</p><p><strong>Random tokens:</strong> <code>{rnd1}</code>…<code>{rnd10}</code> are random lowercase tokens (length = the digit), generated once, <strong>stable for the entire run</strong>, and substituted in connection strings and Setup/Teardown SQL alike — so the same unique database name lands in the connection string, the create step, and the drop step. <code>{rndN_1}</code>…<code>{rndN_9}</code> are independent instances for when several distinct names of the same length are needed.</p><p>The scenarios below are all combinations of these pieces.</p><h2 id="scenario-dedicated-test-database-per-run" tabindex="-1">Scenario: dedicated test database per run <a class="header-anchor" href="#scenario-dedicated-test-database-per-run" aria-label="Permalink to "Scenario: dedicated test database per run""></a></h2><p>Run every test against a <strong>fresh database created for this run</strong> — the app's real database is never touched. This is <a href="https://github.com/NpgsqlRest/npgsqlrest-docs/tree/master/examples/20_testing_newdb" target="_blank" rel="noreferrer"><code>examples/20_testing_newdb</code></a> in full:</p><details class="code-collapsible" open><summary>json</summary><div class="language-json vp-adaptive-theme line-numbers-mode"><button title="Copy Code" class="copy"></button><span class="lang">json</span><pre class="shiki shiki-themes catppuccin-latte catppuccin-mocha vp-code" tabindex="0"><code><span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">{</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">ConnectionStrings</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> {</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">Admin</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> "Host=localhost;Database=postgres;Username=postgres;Password=..."</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">,</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">Test</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> "Host=localhost;Database=app_test_{rnd5};Username=postgres;Password=..."</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> },</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">TestRunner</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> {</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">FilePattern</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> "./tests/**/*.test.sql"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">,</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">ConnectionName</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> "Test"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">,</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">Steps</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> {</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">CreateDatabase</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> {</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">Sql</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> "create database app_test_{rnd5}"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">,</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">ConnectionName</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> "Admin"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> },</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">ApplyMigrations</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> {</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">Command</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> "bun db up --config=./db.js"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> },</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">DropDatabase</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> {</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">Sql</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> "drop database if exists app_test_{rnd5} with (force)"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">,</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">ConnectionName</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> "Admin"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> }</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> },</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">Setup</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> [</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> "CreateDatabase"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">,</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> "ApplyMigrations"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> ],</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">Teardown</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> [</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> "DropDatabase"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> ]</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> }</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">}</span></span></code></pre><div class="line-numbers-wrapper" aria-hidden="true"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br></div></div></details><p>The flow: Setup creates <code>app_test_xxxxx</code> on the Admin connection and migrates it → endpoints are discovered and type-checked <strong>against that database</strong> (<code>ConnectionName: "Test"</code>) → tests run → Teardown drops it. <code>{rnd5}</code> guarantees parallel CI jobs never collide.</p><p>Keep the test configuration in a separate overlay file so the same project runs normally without it:</p><details class="code-collapsible" open><summary>sh</summary><div class="language-sh vp-adaptive-theme line-numbers-mode"><button title="Copy Code" class="copy"></button><span class="lang">sh</span><pre class="shiki shiki-themes catppuccin-latte catppuccin-mocha vp-code" tabindex="0"><code><span class="line"><span style="--shiki-light:#1E66F5;--shiki-light-font-style:italic;--shiki-dark:#89B4FA;--shiki-dark-font-style:italic;">npgsqlrest</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> ./config.json</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> ./test-config.json</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> --test</span></span></code></pre><div class="line-numbers-wrapper" aria-hidden="true"><span class="line-number">1</span><br></div></div></details><h2 id="scenario-template-database-and-per-test-isolation" tabindex="-1">Scenario: template database and per-test isolation <a class="header-anchor" href="#scenario-template-database-and-per-test-isolation" aria-label="Permalink to "Scenario: template database and per-test isolation""></a></h2><p>For tests that need <strong>complete isolation</strong> — deterministic sequence ids, exclusive locks, destructive DDL — clone a <strong>template database</strong> per test file. This is <a href="https://github.com/NpgsqlRest/npgsqlrest-docs/tree/master/examples/21_testing_isolation" target="_blank" rel="noreferrer"><code>examples/21_testing_isolation</code></a>:</p><details class="code-collapsible" open><summary>json</summary><div class="language-json vp-adaptive-theme line-numbers-mode"><button title="Copy Code" class="copy"></button><span class="lang">json</span><pre class="shiki shiki-themes catppuccin-latte catppuccin-mocha vp-code" tabindex="0"><code><span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">{</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">ConnectionStrings</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> {</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">Admin</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> "...Database=postgres..."</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">,</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">Test</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> "...Database=app_test_{rnd5}..."</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">,</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">Isolated1</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> "...Database=app_iso_{rnd5_1}..."</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">,</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">Isolated2</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> "...Database=app_iso_{rnd5_2}..."</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> },</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">TestRunner</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> {</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">ConnectionName</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> "Test"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">,</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">Steps</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> {</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">CreateTemplate</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> {</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">Sql</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> "create database app_template_{rnd5}"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">,</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">ConnectionName</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> "Admin"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> },</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">MigrateTemplate</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> {</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">Command</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> "bun db up --db=app_template_{rnd5}"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> },</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">CreateRunDb</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> {</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">Sql</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> "create database app_test_{rnd5} template app_template_{rnd5}"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">,</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">ConnectionName</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> "Admin"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> },</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">CreateIsolatedDb1</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> {</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">Sql</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> "create database app_iso_{rnd5_1} template app_template_{rnd5}"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">,</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">ConnectionName</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> "Admin"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> },</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">DropIsolatedDb1</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> {</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">Sql</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> "drop database if exists app_iso_{rnd5_1} with (force)"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">,</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">ConnectionName</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> "Admin"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> }</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> },</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">Setup</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> [</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> "CreateTemplate"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">,</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> "MigrateTemplate"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">,</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> "CreateRunDb"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> ],</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">Teardown</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> [</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> "DropTestDb"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">,</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> "DropTemplate"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> ]</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> }</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">}</span></span></code></pre><div class="line-numbers-wrapper" aria-hidden="true"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br></div></div></details><p>Migrations run <strong>once</strong> (into the template); every clone is a byte-identical, instant copy (<code>CREATE DATABASE ... TEMPLATE</code> is a file-level copy — milliseconds for a schema-sized database). Most tests share the run database; a test that needs isolation attaches its own clone with header annotations:</p><details class="code-collapsible" open><summary>sql</summary><div class="language-sql vp-adaptive-theme line-numbers-mode"><button title="Copy Code" class="copy"></button><span class="lang">sql</span><pre class="shiki shiki-themes catppuccin-latte catppuccin-mocha vp-code" tabindex="0"><code><span class="line"><span style="--shiki-light:#9CA0B0;--shiki-light-font-style:italic;--shiki-dark:#6C7086;--shiki-dark-font-style:italic;">-- @setup CreateIsolatedDb1</span></span>
<span class="line"><span style="--shiki-light:#9CA0B0;--shiki-light-font-style:italic;--shiki-dark:#6C7086;--shiki-dark-font-style:italic;">-- @teardown DropIsolatedDb1</span></span>
<span class="line"><span style="--shiki-light:#9CA0B0;--shiki-light-font-style:italic;--shiki-dark:#6C7086;--shiki-dark-font-style:italic;">-- @connection Isolated1</span></span>
<span class="line"><span style="--shiki-light:#9CA0B0;--shiki-light-font-style:italic;--shiki-dark:#6C7086;--shiki-dark-font-style:italic;">-- @tag isolation, slow</span></span>
<span class="line"></span>
<span class="line"><span style="--shiki-light:#9CA0B0;--shiki-light-font-style:italic;--shiki-dark:#6C7086;--shiki-dark-font-style:italic;">/*</span></span>
<span class="line"><span style="--shiki-light:#9CA0B0;--shiki-light-font-style:italic;--shiki-dark:#6C7086;--shiki-dark-font-style:italic;">POST /api/create-user</span></span>
<span class="line"><span style="--shiki-light:#9CA0B0;--shiki-light-font-style:italic;--shiki-dark:#6C7086;--shiki-dark-font-style:italic;">Content-Type: application/json</span></span>
<span class="line"></span>
<span class="line"><span style="--shiki-light:#9CA0B0;--shiki-light-font-style:italic;--shiki-dark:#6C7086;--shiki-dark-font-style:italic;">{"name": "Ada", "email": "ada@example.com"}</span></span>
<span class="line"><span style="--shiki-light:#9CA0B0;--shiki-light-font-style:italic;--shiki-dark:#6C7086;--shiki-dark-font-style:italic;">*/</span></span>
<span class="line"><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;">select</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;"> body::jsonb </span><span style="--shiki-light:#179299;--shiki-dark:#94E2D5;">->></span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> 'id'</span><span style="--shiki-light:#179299;--shiki-dark:#94E2D5;"> =</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> '4'</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;">,</span></span>
<span class="line"><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> 'sequence ids are deterministic in a fresh clone'</span></span>
<span class="line"><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;">from</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;"> _response;</span></span></code></pre><div class="line-numbers-wrapper" aria-hidden="true"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br></div></div></details><p>The classic motivation is <strong>sequences</strong>: <code>nextval()</code> survives <code>rollback</code>, so on a shared database this assertion would depend on run order — in a private clone it is exact. The indexed tokens (<code>{rnd5_1}</code>, <code>{rnd5_2}</code>) let several isolated tests hold their own clone <em>simultaneously</em> under parallel execution. Put the three annotations in a shared profile (<code>\ir shared/isolated_database.sql</code>) and attaching isolation to a test becomes a one-liner.</p><h2 id="scenario-external-migration-runners" tabindex="-1">Scenario: external migration runners <a class="header-anchor" href="#scenario-external-migration-runners" aria-label="Permalink to "Scenario: external migration runners""></a></h2><p><code>Command</code> steps run anything — so any migration tool works as-is. The step inherits the process environment plus the run's <code>{rnd}</code> substitutions in its command line:</p><details class="code-collapsible" open><summary>json</summary><div class="language-json vp-adaptive-theme line-numbers-mode"><button title="Copy Code" class="copy"></button><span class="lang">json</span><pre class="shiki shiki-themes catppuccin-latte catppuccin-mocha vp-code" tabindex="0"><code><span class="line"><span style="--shiki-light:#9CA0B0;--shiki-light-font-style:italic;--shiki-dark:#6C7086;--shiki-dark-font-style:italic;">// EF Core</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">{</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">Command</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> "dotnet ef database update --connection </span><span style="--shiki-light:#EA76CB;--shiki-dark:#F5C2E7;">\"</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;">Host=localhost;Database=app_test_{rnd5};...</span><span style="--shiki-light:#EA76CB;--shiki-dark:#F5C2E7;">\"</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> }</span></span>
<span class="line"></span>
<span class="line"><span style="--shiki-light:#9CA0B0;--shiki-light-font-style:italic;--shiki-dark:#6C7086;--shiki-dark-font-style:italic;">// Django</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">{</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">Command</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> "python manage.py migrate"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">,</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">WorkingDirectory</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> "./backend"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> }</span></span>
<span class="line"></span>
<span class="line"><span style="--shiki-light:#9CA0B0;--shiki-light-font-style:italic;--shiki-dark:#6C7086;--shiki-dark-font-style:italic;">// Flyway</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">{</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">Command</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> "flyway -url=jdbc:postgresql://localhost/app_test_{rnd5} migrate"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> }</span></span>
<span class="line"></span>
<span class="line"><span style="--shiki-light:#9CA0B0;--shiki-light-font-style:italic;--shiki-dark:#6C7086;--shiki-dark-font-style:italic;">// psql — plain SQL migrations, no tooling at all</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">{</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">Command</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> "psql -d app_test_{rnd5} -f ./migrations/schema.sql"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> }</span></span></code></pre><div class="line-numbers-wrapper" aria-hidden="true"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br></div></div></details><p>Or skip external tools entirely: <code>SqlFile</code> steps run migration scripts statement-by-statement on any named connection — no client tooling required in the CI image.</p><h2 id="scenario-docker" tabindex="-1">Scenario: Docker <a class="header-anchor" href="#scenario-docker" aria-label="Permalink to "Scenario: Docker""></a></h2><p>Because <code>Setup</code>/<code>Teardown</code> are ordered shell commands, the runner can own the <strong>entire database lifecycle</strong>, container included:</p><details class="code-collapsible" open><summary>json</summary><div class="language-json vp-adaptive-theme line-numbers-mode"><button title="Copy Code" class="copy"></button><span class="lang">json</span><pre class="shiki shiki-themes catppuccin-latte catppuccin-mocha vp-code" tabindex="0"><code><span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">{</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">TestRunner</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> {</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">Setup</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> [</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> {</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">Command</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> "docker run -d --name npgsqlrest-test-pg -e POSTGRES_PASSWORD=test -p 54329:5432 postgres:17"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> },</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> {</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">Command</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> "until docker exec npgsqlrest-test-pg pg_isready -U postgres; do sleep 0.3; done"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> },</span></span>
<span class="line"><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> "CreateDatabase"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">,</span></span>
<span class="line"><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> "ApplyMigrations"</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> ],</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">Teardown</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> [</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> {</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">Command</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> "docker rm -f npgsqlrest-test-pg"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> }</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> ]</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> }</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">}</span></span></code></pre><div class="line-numbers-wrapper" aria-hidden="true"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br></div></div></details><p>Point the connection strings at <code>Port=54329</code> and the whole test run is hermetic: <code>npgsqlrest --test</code> starts PostgreSQL, builds the schema, runs the tests, and removes the container — pass or fail.</p><h2 id="scenario-testing-least-privilege-polp-setups" tabindex="-1">Scenario: testing least-privilege (PoLP) setups <a class="header-anchor" href="#scenario-testing-least-privilege-polp-setups" aria-label="Permalink to "Scenario: testing least-privilege (PoLP) setups""></a></h2><p>When the application connects as a restricted role, use <strong>two connections deliberately</strong>: fixtures and DDL on the Admin connection (via <code>@setup</code> steps or <code>Setup</code>), while the tests — and the endpoints they invoke — run as the restricted application role (<code>TestRunner.ConnectionName</code>). A test then proves not just behavior but <strong>permissions</strong>: if the app role is missing a grant, the endpoint fails in the test exactly as it would in production. An expected-denial test asserts the error directly:</p><details class="code-collapsible" open><summary>sql</summary><div class="language-sql vp-adaptive-theme line-numbers-mode"><button title="Copy Code" class="copy"></button><span class="lang">sql</span><pre class="shiki shiki-themes catppuccin-latte catppuccin-mocha vp-code" tabindex="0"><code><span class="line"><span style="--shiki-light:#9CA0B0;--shiki-light-font-style:italic;--shiki-dark:#6C7086;--shiki-dark-font-style:italic;">/*</span></span>
<span class="line"><span style="--shiki-light:#9CA0B0;--shiki-light-font-style:italic;--shiki-dark:#6C7086;--shiki-dark-font-style:italic;">POST /api/admin-only-report</span></span>
<span class="line"><span style="--shiki-light:#9CA0B0;--shiki-light-font-style:italic;--shiki-dark:#6C7086;--shiki-dark-font-style:italic;"># @claim user_id=7</span></span>
<span class="line"><span style="--shiki-light:#9CA0B0;--shiki-light-font-style:italic;--shiki-dark:#6C7086;--shiki-dark-font-style:italic;">*/</span></span>
<span class="line"><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;">select</span><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;"> status</span><span style="--shiki-light:#179299;--shiki-dark:#94E2D5;"> =</span><span style="--shiki-light:#FE640B;--shiki-dark:#FAB387;"> 404</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;">, </span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;">'restricted role cannot reach the admin endpoint'</span></span>
<span class="line"><span style="--shiki-light:#8839EF;--shiki-dark:#CBA6F7;">from</span><span style="--shiki-light:#4C4F69;--shiki-dark:#CDD6F4;"> _response;</span></span></code></pre><div class="line-numbers-wrapper" aria-hidden="true"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br></div></div></details><h2 id="filtering-and-tags" tabindex="-1">Filtering and tags <a class="header-anchor" href="#filtering-and-tags" aria-label="Permalink to "Filtering and tags""></a></h2><p>Iterating on one test — <code>Filter</code> matches the cwd-relative path (substring, or glob with wildcards):</p><details class="code-collapsible" open><summary>sh</summary><div class="language-sh vp-adaptive-theme line-numbers-mode"><button title="Copy Code" class="copy"></button><span class="lang">sh</span><pre class="shiki shiki-themes catppuccin-latte catppuccin-mocha vp-code" tabindex="0"><code><span class="line"><span style="--shiki-light:#1E66F5;--shiki-light-font-style:italic;--shiki-dark:#89B4FA;--shiki-dark-font-style:italic;">npgsqlrest</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> ./config.json</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> --test</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> --testrunner:filter=login</span></span></code></pre><div class="line-numbers-wrapper" aria-hidden="true"><span class="line-number">1</span><br></div></div></details><p>Suites — files declare <a href="./../annotations/test-tag.html"><code>-- @tag</code></a> and runs narrow by tag (case-insensitive; exclude wins; composes with <code>Filter</code>):</p><details class="code-collapsible" open><summary>sh</summary><div class="language-sh vp-adaptive-theme line-numbers-mode"><button title="Copy Code" class="copy"></button><span class="lang">sh</span><pre class="shiki shiki-themes catppuccin-latte catppuccin-mocha vp-code" tabindex="0"><code><span class="line"><span style="--shiki-light:#1E66F5;--shiki-light-font-style:italic;--shiki-dark:#89B4FA;--shiki-dark-font-style:italic;">npgsqlrest</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> ./config.json</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> --test</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> --testrunner:tag=smoke</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> --testrunner:excludetag=slow</span></span></code></pre><div class="line-numbers-wrapper" aria-hidden="true"><span class="line-number">1</span><br></div></div></details><h2 id="watch-mode" tabindex="-1">Watch mode <a class="header-anchor" href="#watch-mode" aria-label="Permalink to "Watch mode""></a></h2><details class="code-collapsible" open><summary>sh</summary><div class="language-sh vp-adaptive-theme line-numbers-mode"><button title="Copy Code" class="copy"></button><span class="lang">sh</span><pre class="shiki shiki-themes catppuccin-latte catppuccin-mocha vp-code" tabindex="0"><code><span class="line"><span style="--shiki-light:#1E66F5;--shiki-light-font-style:italic;--shiki-dark:#89B4FA;--shiki-dark-font-style:italic;">npgsqlrest</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> ./config.json</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> --test</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> --watch</span></span></code></pre><div class="line-numbers-wrapper" aria-hidden="true"><span class="line-number">1</span><br></div></div></details><p>Runs everything once, then re-runs on changes until Ctrl+C (<code>--watch</code> is the shorthand for the top-level <a href="./../config/watch.html"><code>Watch:Enabled</code></a> setting):</p><ul><li>a changed <strong>test file</strong> re-runs alone (typically tens of milliseconds);</li><li>a changed <strong>endpoint file</strong> rebuilds the endpoints <strong>in-process</strong> and re-runs everything, printing the endpoint delta — break an endpoint's SQL and you immediately see <code>- GET /api/get-users (endpoint dropped — check its SQL file for errors)</code> plus the failing tests; fix it and the next save brings it back. No restart, ever;</li><li>a <strong>database routine change</strong> — <code>create or replace</code>/<code>drop</code>/<code>comment on</code> a function in psql, or a migration touching routines — rebuilds endpoints and re-runs everything too (<code>— change detected (database) —</code>). Detection polls the routine discovery query itself, hashed server-side (every <code>2s</code> by default; <a href="./../config/watch.html#databasepollinginterval"><code>Watch:DatabasePollingInterval</code></a>), so it fires exactly when the discovered endpoints change and never on unrelated tables or temp objects;</li><li>any other changed <code>.sql</code> under the test tree (a fixture whose dependents are unknown) re-runs everything.</li></ul><p>Teardown runs once on exit — Ctrl+C and SIGTERM are intercepted and the test database is still dropped, even under wrappers like <code>bun run</code>/<code>npm run</code>. A graceful stop exits <code>0</code>: watch is a dev loop, not a CI gate.</p><div class="tip custom-block"><p class="custom-block-title">Server watch</p><p>The same flag without <code>--test</code> watches the <strong>running server</strong>: <code>npgsqlrest ./config.json --watch</code> restarts it on SQL file, configuration, and database routine changes, regenerating the TypeScript client and HTTP files on every cycle. See <a href="./../config/watch.html">Watch Mode configuration</a>.</p></div><h2 id="endpoint-coverage" tabindex="-1">Endpoint coverage <a class="header-anchor" href="#endpoint-coverage" aria-label="Permalink to "Endpoint coverage""></a></h2><p>The runner knows the <strong>entire API surface</strong> it built <em>and</em> records every endpoint the tests invoked — so after a full run it reports the API-level analogue of code coverage, naming the endpoints no test touches:</p><details class="code-collapsible" open><summary>code</summary><div class="language- vp-adaptive-theme line-numbers-mode"><button title="Copy Code" class="copy"></button><span class="lang"></span><pre class="shiki shiki-themes catppuccin-latte catppuccin-mocha vp-code" tabindex="0"><code><span class="line"><span>endpoint coverage: 3/4 (75%)</span></span>
<span class="line"><span> untested: POST /api/delete-user</span></span></code></pre><div class="line-numbers-wrapper" aria-hidden="true"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div></details><p>On by default for full runs (one line); suppressed automatically when the run is narrowed by <code>Filter</code>/<code>Tag</code>; forced with <code>Coverage: true</code> / silenced with <code>false</code>. <strong><code>CoverageThreshold: 100</code></strong> turns it into a CI gate: an otherwise-green run that misses an endpoint exits <code>2</code> — forgetting to write a test for a new endpoint fails the build, by name. "Covered" means invoked at least once; untestable kinds (SSE, upload, login/logout, outbound proxy) are excluded from the ratio.</p><h2 id="reporting-logging-ci" tabindex="-1">Reporting, logging, CI <a class="header-anchor" href="#reporting-logging-ci" aria-label="Permalink to "Reporting, logging, CI""></a></h2><p><strong>Console report:</strong> <code>PASS</code>/<code>FAIL</code>/<code>ERROR</code> per file with per-assertion detail on failure (name, <code>file:line</code>, failing statement). <code>DetailedReport: true</code> additionally lists passed assertions, full failing SQL, and captured <code>raise notice</code> output. Colors match the Serilog console theme and are stripped automatically when output is piped.</p><p><strong>Log channel:</strong> the runner logs on its own <code>NpgsqlRestTest</code> channel — discovery at <code>Debug</code>, every executed statement and HTTP invocation at <code>Verbose</code>, <code>raise notice</code> by severity. Typical dev setup — mute the app, watch the tests:</p><details class="code-collapsible" open><summary>json</summary><div class="language-json vp-adaptive-theme line-numbers-mode"><button title="Copy Code" class="copy"></button><span class="lang">json</span><pre class="shiki shiki-themes catppuccin-latte catppuccin-mocha vp-code" tabindex="0"><code><span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">{</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">Log</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> {</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">MinimalLevels</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> {</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">NpgsqlRest</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> "Off"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">,</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">NpgsqlRestClient</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> "Off"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">,</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> "</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;">NpgsqlRestTest</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">:</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> "Verbose"</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> }</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> }</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> }</span></span></code></pre><div class="line-numbers-wrapper" aria-hidden="true"><span class="line-number">1</span><br></div></div></details><p><strong>JUnit XML</strong> for CI (<code>JUnitOutput: "./test-results.xml"</code>) — assertion names become test-case names. <strong>Exit codes</strong>: <code>0</code> pass · <code>1</code> failures · <code>2</code> errors / coverage gate · <code>3</code> setup/config error · <code>4</code> no tests found. A minimal GitHub Actions job:</p><details class="code-collapsible" open><summary>yaml</summary><div class="language-yaml vp-adaptive-theme line-numbers-mode"><button title="Copy Code" class="copy"></button><span class="lang">yaml</span><pre class="shiki shiki-themes catppuccin-latte catppuccin-mocha vp-code" tabindex="0"><code><span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">-</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;"> run</span><span style="--shiki-light:#179299;--shiki-dark:#94E2D5;">:</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> npgsqlrest ./config.json ./test-config.json --test --testrunner:junitoutput=results.xml</span></span>
<span class="line"><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">-</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;"> uses</span><span style="--shiki-light:#179299;--shiki-dark:#94E2D5;">:</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> dorny/test-reporter@v1</span></span>
<span class="line"><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;"> if</span><span style="--shiki-light:#179299;--shiki-dark:#94E2D5;">:</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> always()</span></span>
<span class="line"><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;"> with</span><span style="--shiki-light:#179299;--shiki-dark:#94E2D5;">:</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> {</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;"> name</span><span style="--shiki-light:#179299;--shiki-dark:#94E2D5;">:</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> SQL tests</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">,</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;"> path</span><span style="--shiki-light:#179299;--shiki-dark:#94E2D5;">:</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> results.xml</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;">,</span><span style="--shiki-light:#1E66F5;--shiki-dark:#89B4FA;"> reporter</span><span style="--shiki-light:#179299;--shiki-dark:#94E2D5;">:</span><span style="--shiki-light:#40A02B;--shiki-dark:#A6E3A1;"> java-junit</span><span style="--shiki-light:#7C7F93;--shiki-dark:#9399B2;"> }</span></span></code></pre><div class="line-numbers-wrapper" aria-hidden="true"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div></details><h2 id="troubleshooting" tabindex="-1">Troubleshooting <a class="header-anchor" href="#troubleshooting" aria-label="Permalink to "Troubleshooting""></a></h2><ul><li><strong><code>no endpoint matches GET /api/x — the response will be a 404</code></strong> — path typo or missing <code>UrlPathPrefix</code> (default <code>/api</code>) in the request line.</li><li><strong><code>PASS ... (no assertions)</code></strong> (flagged) — the file ran but contained no boolean-<code>SELECT</code>/DO-block assertion; check that your assert's <em>first column</em> is a boolean.</li><li><strong>A test passes alone but fails in the full run</strong> — shared-state leak: an uncommitted-fixture assumption, a committed write without <code>rollback</code>, or a sequence-id assertion on a shared database. Wrap writes in <code>begin</code>/<code>rollback</code>, or isolate the test with a per-file clone.</li><li><strong>Unsupported endpoint error</strong> — SSE/upload/login/logout/proxy endpoints cannot be invoked in-process by design; test login flows by injecting <code># @claim</code> instead.</li><li><strong>Leftover <code>*_{rnd}</code> databases</strong> — a run was killed with SIGKILL (nothing can intercept that), or <code>Keep: true</code> was on. Drop them manually; every graceful path (including Ctrl+C, SIGTERM, and hard startup errors) tears down automatically.</li></ul><h2 id="reference" tabindex="-1">Reference <a class="header-anchor" href="#reference" aria-label="Permalink to "Reference""></a></h2><ul><li><a href="./../config/test-runner.html">Test Runner configuration</a> — every option</li><li>Annotations: <a href="./../annotations/test-setup.html"><code>@setup</code></a> · <a href="./../annotations/test-teardown.html"><code>@teardown</code></a> · <a href="./../annotations/test-connection.html"><code>@connection</code></a> · <a href="./../annotations/test-tag.html"><code>@tag</code></a> · <a href="./../annotations/test-claim.html"><code>@claim</code></a> · <a href="./../annotations/test-response.html"><code>@response</code></a></li><li><a href="./sql-files.html">SQL File Endpoints</a> — the endpoints being tested</li><li><a href="./changelog/v3.19.0.html">Changelog v3.19.0</a> — the complete feature announcement</li></ul></div></div></main><footer class="VPDocFooter" data-v-39a288b8 data-v-e257564d><!--[--><!--]--><div class="edit-info" data-v-e257564d><div class="edit-link" data-v-e257564d><a class="VPLink link vp-external-link-icon no-icon edit-link-button" href="https://github.com/NpgsqlRest/npgsqlrest-docs/edit/main/docs/guide/testing.md" target="_blank" rel="noreferrer" data-v-e257564d><!--[--><span class="vpi-square-pen edit-link-icon" data-v-e257564d></span> Edit this page on GitHub<!--]--></a></div><!----></div><nav class="prev-next" aria-labelledby="doc-footer-aria-label" data-v-e257564d><span class="visually-hidden" id="doc-footer-aria-label" data-v-e257564d>Pager</span><div class="pager" data-v-e257564d><a class="VPLink link pager-link prev" href="/guide/proxy.html" data-v-e257564d><!--[--><span class="desc" data-v-e257564d>Previous page</span><span class="title" data-v-e257564d>Proxy Endpoints</span><!--]--></a></div><div class="pager" data-v-e257564d><a class="VPLink link pager-link next" href="/guide/logging.html" data-v-e257564d><!--[--><span class="desc" data-v-e257564d>Next page</span><span class="title" data-v-e257564d>Logging</span><!--]--></a></div></nav></footer><!--[--><!--[--><!--[--><div id="comments" class="giscus-wrapper" data-v-5337483f><h2 class="comments-heading" data-v-5337483f>Comments</h2><!----></div><div class="sponsor-footer" data-v-b93f2dbd><div class="sponsor-footer-content" data-v-b93f2dbd><span class="sponsor-heart" data-v-b93f2dbd>❤️</span><span class="sponsor-text" data-v-b93f2dbd>Support this project:</span><a href="https://patreon.com/vbconsulting" target="_blank" class="sponsor-link" data-v-b93f2dbd><svg xmlns="http://www.w3.org/2000/svg" width="16" height="16" viewBox="0 0 24 24" fill="currentColor" data-v-b93f2dbd><path d="M22.957 7.21c-.004-3.064-2.391-5.576-5.191-6.482-3.478-1.125-8.064-.962-11.384.604C2.357 3.231 1.093 7.391 1.046 11.54c-.039 3.411.302 7.463 3.097 9.746 2.602 2.124 6.19 2.078 9.402 1.836 1.883-.142 3.939-.358 5.477-1.528 1.564-1.19 1.873-3.298 1.972-5.151.134-2.51.187-5.016-.037-7.233zm-2.55 7.703c-.078 1.593-.383 3.473-1.795 4.334-1.351.824-3.151.973-4.686 1.083-2.891.207-6.063.276-8.514-1.399C3.106 17.39 2.924 14.34 2.95 11.67c.03-3.206.678-6.721 3.654-8.375 2.756-1.531 6.556-1.727 9.544-.872 2.339.669 4.351 2.678 4.355 5.217.007 2.583-.016 5.168-.095 7.273z" data-v-b93f2dbd></path><path d="M16.088 5.19c-1.008-.542-2.607-.48-3.441.388-.568.591-.773 1.389-.8 2.172-.034.993.068 2.013.361 2.953.307.985 1.034 1.848 2.077 2.012.878.138 1.83-.088 2.458-.732.7-.718.88-1.81.877-2.779-.003-.989-.084-1.99-.423-2.91-.268-.726-.654-1.328-1.109-1.104zm-.294 5.482c-.182.462-.581.771-1.091.696-.532-.078-.859-.533-1.04-1.005-.248-.647-.327-1.375-.312-2.06.01-.46.095-.939.371-1.313.345-.467.993-.447 1.426-.115.41.315.607.835.74 1.32.18.66.211 1.675-.094 2.477zM8.4 10.394c.233 1.084.755 2.299 1.907 2.579.934.227 1.993-.165 2.434-1.048.455-.91.346-1.995.166-2.972-.165-.893-.46-1.885-1.207-2.445-.745-.559-1.832-.457-2.479.192-.84.843-.958 2.138-.955 3.277.001.147.119.364.134.417zm1.363-2.808c.302-.321.805-.336 1.14-.054.445.375.618 1.015.75 1.558.131.54.212 1.156.082 1.703-.1.423-.378.821-.84.882-.469.062-.886-.306-1.088-.704-.326-.644-.401-1.426-.381-2.148.013-.465.104-.993.337-1.237z" data-v-b93f2dbd></path></svg> Patreon </a><span class="sponsor-separator" data-v-b93f2dbd>·</span><a href="https://buymeacoffee.com/vbilopavu" target="_blank" class="sponsor-link" data-v-b93f2dbd><svg xmlns="http://www.w3.org/2000/svg" width="16" height="16" viewBox="0 0 24 24" fill="currentColor" data-v-b93f2dbd><path d="M20.216 6.415l-.132-.666c-.119-.598-.388-1.163-1.001-1.379-.197-.069-.42-.098-.57-.241-.152-.143-.196-.366-.231-.572-.065-.378-.125-.756-.192-1.133-.057-.325-.102-.69-.25-.987-.195-.4-.597-.634-.996-.788a5.723 5.723 0 00-.626-.194c-1-.263-2.05-.36-3.077-.416a25.834 25.834 0 00-3.7.062c-.915.083-1.88.184-2.75.5-.318.116-.646.256-.888.501-.297.302-.393.77-.177 1.146.154.267.415.456.692.58.36.162.737.284 1.123.366 1.075.238 2.189.331 3.287.37 1.218.05 2.437.01 3.65-.118.299-.033.598-.073.896-.119.352-.054.578-.513.474-.834-.124-.383-.457-.531-.834-.473-.466.074-.96.108-1.382.146-1.177.08-2.358.082-3.536.006a22.228 22.228 0 01-1.157-.107c-.086-.01-.18-.025-.258-.036-.243-.036-.484-.08-.724-.13-.111-.027-.111-.185 0-.212h.005c.277-.06.557-.108.838-.147h.002c.131-.009.263-.032.394-.048a25.076 25.076 0 013.426-.12c.674.019 1.347.067 2.017.144l.228.031c.267.04.533.088.798.145.392.085.895.113 1.07.542.055.137.08.288.111.431l.319 1.484a.237.237 0 01-.199.284h-.003c-.037.006-.075.01-.112.015a36.704 36.704 0 01-4.743.295 37.059 37.059 0 01-4.699-.304c-.14-.017-.293-.042-.417-.06-.326-.048-.649-.108-.973-.161-.393-.065-.768-.032-1.123.161-.29.16-.527.404-.675.701-.154.316-.199.66-.267 1-.069.34-.176.707-.135 1.056.087.753.613 1.365 1.37 1.502a39.69 39.69 0 0011.343.376.483.483 0 01.535.53l-.071.697-1.018 9.907c-.041.41-.047.832-.125 1.237-.122.637-.553 1.028-1.182 1.171-.577.131-1.165.2-1.756.205-.656.004-1.31-.025-1.966-.022-.699.004-1.556-.06-2.095-.58-.475-.458-.54-1.174-.605-1.793l-.731-7.013-.322-3.094c-.037-.351-.286-.695-.678-.678-.336.015-.718.3-.678.679l.228 2.185.949 9.112c.147 1.344 1.174 2.068 2.446 2.272.742.12 1.503.144 2.257.156.966.016 1.942.053 2.892-.122 1.408-.258 2.465-1.198 2.616-2.657.34-3.332.683-6.663 1.024-9.995l.215-2.087a.484.484 0 01.39-.426c.402-.078.787-.212 1.074-.518.455-.488.546-1.124.385-1.766zm-1.478.772c-.145.137-.363.201-.578.233-2.416.359-4.866.54-7.308.46-1.748-.06-3.477-.254-5.207-.498-.17-.024-.353-.055-.47-.18-.22-.236-.111-.71-.054-.995.052-.26.152-.609.463-.646.484-.057 1.046.148 1.526.22.577.088 1.156.159 1.737.212 2.48.226 5.002.19 7.472-.14.45-.06.899-.13 1.345-.21.399-.072.84-.206 1.08.206.166.281.188.657.162.974a.544.544 0 01-.169.364z" data-v-b93f2dbd></path></svg> Buy Me a Coffee </a></div><div class="footer-license" data-v-b93f2dbd>Released under the MIT License.</div><div class="footer-copyright" data-v-b93f2dbd>Copyright © 2024-2026 VB Consulting</div></div><!--]--><!--]--><!--]--></div></div></div><!--[--><!--]--></div></div><!----><!--[--><!--]--></div></div>
<script>window.__VP_HASH_MAP__=JSON.parse("{\"about.md\":\"tQJT3Mf7\",\"annotations_allow-anonymous.md\":\"B0UuFGg2\",\"annotations_authorize.md\":\"CK7v-SQu\",\"annotations_basic-auth-command.md\":\"Csb6mha2\",\"annotations_basic-auth-realm.md\":\"DsMX0SDT\",\"annotations_basic-auth.md\":\"ClrT2h43\",\"annotations_body-parameter-name.md\":\"BfhRegXI\",\"annotations_buffer-rows.md\":\"BrfXbHRV\",\"annotations_cache-expires-in.md\":\"BRoExkyw\",\"annotations_cache-profile.md\":\"YD0xPH6k\",\"annotations_cached.md\":\"Z17IaMQ8\",\"annotations_column-names.md\":\"BgCUEwzC\",\"annotations_command-timeout.md\":\"DGZAbOuK\",\"annotations_connection.md\":\"kVRk6UjZ\",\"annotations_custom-parameters.md\":\"C_JoJInz\",\"annotations_define-param.md\":\"BEpTOvZh\",\"annotations_disabled.md\":\"DLiSD84Q\",\"annotations_enabled.md\":\"w_f0uh0_\",\"annotations_encrypt-decrypt.md\":\"BVL3DPHk\",\"annotations_error-code-policy.md\":\"CcDC1Uqp\",\"annotations_http-type.md\":\"Yj3KYiqW\",\"annotations_http.md\":\"DPCDKDD2\",\"annotations_index.md\":\"DSU3OO0a\",\"annotations_internal.md\":\"yWqX_Gop\",\"annotations_interval-format.md\":\"qinH4egR\",\"annotations_login.md\":\"CNtMBAwP\",\"annotations_logout.md\":\"Cle7WE1K\",\"annotations_mcp.md\":\"CUGE4v1n\",\"annotations_nested.md\":\"G3R18jm5\",\"annotations_new-line.md\":\"9HsK47GA\",\"annotations_openapi.md\":\"CYi2hImP\",\"annotations_param.md\":\"DCLc58O5\",\"annotations_parameter-hash.md\":\"DAN4SbBB\",\"annotations_parameter-substitution.md\":\"BAnY-Etv\",\"annotations_path.md\":\"4EcrWuEH\",\"annotations_proxy-out.md\":\"C6XnOMaD\",\"annotations_proxy.md\":\"C1XKqZ1u\",\"annotations_query-string-null-handling.md\":\"bDnQmkU1\",\"annotations_rate-limiter-policy.md\":\"C6rNxcxq\",\"annotations_raw.md\":\"mVR52W_-\",\"annotations_request-headers-mode.md\":\"CiBM2WBe\",\"annotations_request-headers-parameter-name.md\":\"B1w65j2m\",\"annotations_request-param-type.md\":\"D2ADbicS\",\"annotations_resolved-parameters.md\":\"CtIRul_U\",\"annotations_response-headers.md\":\"BjZbt4Z0\",\"annotations_response-null-handling.md\":\"BXujxmf9\",\"annotations_result-name.md\":\"oSFavaHs\",\"annotations_retry-strategy.md\":\"DJkjr3eT\",\"annotations_returns.md\":\"BXcLtPWm\",\"annotations_security-sensitive.md\":\"iGNlRYXu\",\"annotations_separator.md\":\"Bh3F0ZIY\",\"annotations_single.md\":\"6tuGsQhF\",\"annotations_skip.md\":\"Dh_dVMzK\",\"annotations_sse-events-level.md\":\"BzejI_6s\",\"annotations_sse-events-scope.md\":\"DSBEdrmZ\",\"annotations_sse.md\":\"VtYFy1W4\",\"annotations_table-format.md\":\"BKXSdBmi\",\"annotations_tags.md\":\"DmTy6McN\",\"annotations_test-claim.md\":\"B8Axvirh\",\"annotations_test-connection.md\":\"CBXd7WXg\",\"annotations_test-response.md\":\"BIQYW_7j\",\"annotations_test-setup.md\":\"CEjyi1f2\",\"annotations_test-tag.md\":\"CzXuyAz7\",\"annotations_test-teardown.md\":\"DrTOzo2w\",\"annotations_tsclient.md\":\"BNgVXSoY\",\"annotations_upload.md\":\"BJPkIDDG\",\"annotations_user-context.md\":\"D9DNdqIU\",\"annotations_user-parameters.md\":\"RuNgxxKs\",\"annotations_validate.md\":\"BYjsje_G\",\"annotations_void.md\":\"CdJ8Ec9f\",\"blog_case-study-zero-backend-code.md\":\"BnEeD2xb\",\"blog_csv-excel-ingestion-postgresql-npgsqlrest.md\":\"Rjqy9NyI\",\"blog_custom-types-multiset-rest-api.md\":\"BEa032jW\",\"blog_database-level-security-postgresql-authentication.md\":\"CI9eBklt\",\"blog_draft-anniversary-vietnam-of-computer-science.md\":\"D7rY_yx-\",\"blog_draft-npgsqlrest-vs-sqlpage.md\":\"DbSL1Z8X\",\"blog_end-to-end-static-type-checking-postgresql-typescript.md\":\"B_IRfXH-\",\"blog_excel-export-table-format-postgresql-npgsqlrest.md\":\"BuWwG6CQ\",\"blog_external-api-calls-postgresql-http-types.md\":\"C9zYiqiX\",\"blog_index.md\":\"CTEJ27sE\",\"blog_mcp-server-postgresql-ai-tools-npgsqlrest.md\":\"DvISemRM\",\"blog_multiple-auth-schemes-rbac-external-providers.md\":\"PAYr3KYB\",\"blog_npgsqlrest-3.13-production-patterns.md\":\"jTlztVH-\",\"blog_npgsqlrest-3.19-sql-test-runner-watch-mode.md\":\"CeVn9yDN\",\"blog_npgsqlrest-vs-postgrest-supabase-comparison.md\":\"00RFbAF5\",\"blog_optimization-labels-101.md\":\"0ZBmELgS\",\"blog_passkey-sql-auth.md\":\"BRkr8TRo\",\"blog_performance-scalability-high-availability-npgsqlrest.md\":\"73MKg7Tu\",\"blog_postgresql-bi-server-excel-csv-basic-auth.md\":\"C9mEJphA\",\"blog_postgresql-rest-api-benchmark-2024.md\":\"DwEQmIoA\",\"blog_postgresql-rest-api-benchmark-2025.md\":\"Tbw6Blag\",\"blog_postgresql-rest-api-benchmark-2026.md\":\"CJcg5B7z\",\"blog_real-time-chat-postgresql-sse-npgsqlrest.md\":\"Fd5M97qI\",\"blog_reverse-proxy-postgresql-ai-service-npgsqlrest.md\":\"BtaYnMBE\",\"blog_secure-image-uploads-postgresql-typescript.md\":\"BCA4okme\",\"blog_sql-file-source-rest-api-from-plain-sql.md\":\"czCJfcgj\",\"blog_sql-rest-api.md\":\"Dv5ncwRQ\",\"blog_the-backend-that-writes-itself-presentation.md\":\"gFR1Oe0-\",\"blog_the-power-of-simplicity.md\":\"DP76HzpZ\",\"blog_typescript-codegen-walkthrough.md\":\"DU5mkobw\",\"blog_web-scraping-postgresql-http-types-xml.md\":\"CCovU_63\",\"blog_what-have-stored-procedures-ever-done-for-us.md\":\"DbQNwRp9\",\"config_antiforgery.md\":\"7miFkqvJ\",\"config_auth.md\":\"BTlaq10R\",\"config_authentication-options.md\":\"CzhPcVla\",\"config_basic-auth-config.md\":\"DZCrOE7U\",\"config_cache-options.md\":\"Cq5Q0JXd\",\"config_claims-mapping.md\":\"DxUSHFxx\",\"config_codegen.md\":\"opHl7x9x\",\"config_command-retry.md\":\"BgAKKMgf\",\"config_config-section.md\":\"CwHYEVn1\",\"config_connection.md\":\"OYNJshxp\",\"config_cors.md\":\"Cusa--1Y\",\"config_data-protection.md\":\"Djdf76Tb\",\"config_error-handling.md\":\"DjvdMMV4\",\"config_external-auth.md\":\"bAqNP7C3\",\"config_forwarded-headers.md\":\"CGTpUREz\",\"config_health-checks.md\":\"BpWdFu-y\",\"config_http-client.md\":\"dRXgMQQQ\",\"config_http-files.md\":\"B6K6vnkA\",\"config_index.md\":\"CXfhm2PI\",\"config_latest.md\":\"BHuOeOrq\",\"config_logging.md\":\"B2g4l4DC\",\"config_mcp.md\":\"D08gYjRL\",\"config_npgsqlrest.md\":\"CnfMgb40\",\"config_openapi.md\":\"BCg7CKgl\",\"config_passkey-auth.md\":\"DHQzx-GX\",\"config_proxy.md\":\"DOTosU06\",\"config_rate-limiter.md\":\"wLNPwvhE\",\"config_response-compression.md\":\"CMmom6qe\",\"config_routine-options.md\":\"VEEBtjYd\",\"config_security-headers.md\":\"D2EP-6xq\",\"config_server.md\":\"BCPr-8rO\",\"config_sql-file-source.md\":\"5_GwGm0C\",\"config_static-files.md\":\"CxiUeSZr\",\"config_stats.md\":\"CXkhOJdL\",\"config_table-format.md\":\"CKOmZ6yI\",\"config_test-runner.md\":\"D9PY_12B\",\"config_thread-pool.md\":\"CQCaprkR\",\"config_top-level.md\":\"F9S_nf2B\",\"config_uploads.md\":\"DoJGjd4G\",\"config_validation.md\":\"lHNUsfic\",\"config_watch.md\":\"B_iL-Wg4\",\"examples_index.md\":\"D4o4oSds\",\"guide_annotations.md\":\"DVRiz6k_\",\"guide_authentication.md\":\"BaKZBV2r\",\"guide_changelog_index.md\":\"B7c1xVM5\",\"guide_changelog_v3.0.0.md\":\"BCZYSJDP\",\"guide_changelog_v3.0.1.md\":\"C5QoKstF\",\"guide_changelog_v3.1.0.md\":\"Xl_94wGw\",\"guide_changelog_v3.1.1.md\":\"CP1dTmO5\",\"guide_changelog_v3.1.2.md\":\"DaCWRd65\",\"guide_changelog_v3.1.3.md\":\"BMixLZ4Y\",\"guide_changelog_v3.10.0.md\":\"CCimYYDV\",\"guide_changelog_v3.11.0.md\":\"yn587hBX\",\"guide_changelog_v3.11.1.md\":\"DxsV24Au\",\"guide_changelog_v3.12.0.md\":\"BDVbkMod\",\"guide_changelog_v3.13.0.md\":\"kmKD3jrx\",\"guide_changelog_v3.14.0.md\":\"DqJvVHhw\",\"guide_changelog_v3.15.0.md\":\"DiRcMMNc\",\"guide_changelog_v3.15.1.md\":\"EipfUUTu\",\"guide_changelog_v3.15.2.md\":\"B_9nMiuK\",\"guide_changelog_v3.16.0.md\":\"z16WdNnJ\",\"guide_changelog_v3.16.1.md\":\"twyBHeJH\",\"guide_changelog_v3.16.2.md\":\"DJyi_HNy\",\"guide_changelog_v3.16.3.md\":\"BaS-l5LJ\",\"guide_changelog_v3.17.0.md\":\"DCqJkXNp\",\"guide_changelog_v3.18.0.md\":\"DhzJiwQ7\",\"guide_changelog_v3.18.1.md\":\"BUjRzlAL\",\"guide_changelog_v3.18.2.md\":\"D9Kg93qi\",\"guide_changelog_v3.19.0.md\":\"CnLwz7zb\",\"guide_changelog_v3.2.0.md\":\"DqMIq3GM\",\"guide_changelog_v3.2.1.md\":\"jsATl_CJ\",\"guide_changelog_v3.2.2.md\":\"DCWOjWem\",\"guide_changelog_v3.2.3.md\":\"BU0QClul\",\"guide_changelog_v3.2.4.md\":\"DlHy9dKX\",\"guide_changelog_v3.2.6.md\":\"Be7WrEBG\",\"guide_changelog_v3.2.7.md\":\"DnOXNKXQ\",\"guide_changelog_v3.3.0.md\":\"D_74S82V\",\"guide_changelog_v3.3.1.md\":\"DBVEhTOX\",\"guide_changelog_v3.4.0.md\":\"DWaLq7jU\",\"guide_changelog_v3.4.1.md\":\"BjkyM2ol\",\"guide_changelog_v3.4.2.md\":\"CwgXLNC8\",\"guide_changelog_v3.4.3.md\":\"CwIJ6zQw\",\"guide_changelog_v3.4.4.md\":\"Dun3dnAP\",\"guide_changelog_v3.4.5.md\":\"CCdH9iK4\",\"guide_changelog_v3.4.6.md\":\"DfISyzwO\",\"guide_changelog_v3.4.7.md\":\"PS46_sBY\",\"guide_changelog_v3.4.8.md\":\"C6Us0ANY\",\"guide_changelog_v3.5.0.md\":\"COe0eWbp\",\"guide_changelog_v3.6.0.md\":\"p7Q-OsI9\",\"guide_changelog_v3.6.1.md\":\"CCDqHGJF\",\"guide_changelog_v3.6.2.md\":\"Lg_0h_OI\",\"guide_changelog_v3.6.3.md\":\"CcLrwLcH\",\"guide_changelog_v3.7.0.md\":\"BsBWtBOJ\",\"guide_changelog_v3.8.0.md\":\"D7bLiXbw\",\"guide_changelog_v3.9.0.md\":\"Cm0S1Ft1\",\"guide_configuration.md\":\"CAstKOPi\",\"guide_faq.md\":\"Bxla4fTX\",\"guide_http-types.md\":\"C0G-MJo7\",\"guide_index.md\":\"C-0777ru\",\"guide_installation.md\":\"KDi2i5hY\",\"guide_logging.md\":\"CT7Uqiy7\",\"guide_proxy.md\":\"CkN5fnQf\",\"guide_quick-start.md\":\"DtKtFUQX\",\"guide_sql-files.md\":\"yAmqICyN\",\"guide_sse.md\":\"CiPMjFtJ\",\"guide_testing.md\":\"Mc_wQPBP\",\"index.md\":\"CcMKxpaP\"}");window.__VP_SITE_DATA__=JSON.parse("{\"lang\":\"en-US\",\"dir\":\"ltr\",\"title\":\"NpgsqlRest - Automatic PostgreSQL Web Server\",\"description\":\"Automatic PostgreSQL Web Server - Create REST APIs for PostgreSQL databases in minutes.\",\"base\":\"/\",\"head\":[],\"router\":{\"prefetchLinks\":true},\"appearance\":true,\"themeConfig\":{\"logo\":\"/favicon.ico\",\"siteTitle\":\"NpgsqlRest\",\"nav\":[{\"text\":\"Guide\",\"items\":[{\"text\":\"Getting Started\",\"items\":[{\"text\":\"Overview\",\"link\":\"/guide/\"},{\"text\":\"Installation\",\"link\":\"/guide/installation\"},{\"text\":\"Quick Start\",\"link\":\"/guide/quick-start\"}]},{\"text\":\"Topics\",\"items\":[{\"text\":\"SQL File Endpoints\",\"link\":\"/guide/sql-files\"},{\"text\":\"Configuration Guide\",\"link\":\"/guide/configuration\"},{\"text\":\"Comment Annotations Guide\",\"link\":\"/guide/annotations\"},{\"text\":\"Authentication\",\"link\":\"/guide/authentication\"},{\"text\":\"Server-Sent Events\",\"link\":\"/guide/sse\"},{\"text\":\"HTTP Custom Types\",\"link\":\"/guide/http-types\"},{\"text\":\"Proxy Endpoints\",\"link\":\"/guide/proxy\"},{\"text\":\"Testing\",\"link\":\"/guide/testing\"},{\"text\":\"Logging\",\"link\":\"/guide/logging\"}]},{\"text\":\"Reference\",\"items\":[{\"text\":\"FAQ & Troubleshooting\",\"link\":\"/guide/faq\"},{\"text\":\"Changelog\",\"link\":\"/guide/changelog/\"}]}]},{\"text\":\"Examples\",\"link\":\"/examples/\"},{\"text\":\"Reference\",\"items\":[{\"text\":\"Annotations\",\"link\":\"/annotations/\"},{\"text\":\"Configuration\",\"link\":\"/config/\"}]},{\"text\":\"Blog\",\"link\":\"/blog/\"}],\"sidebar\":{\"/blog/\":[{\"text\":\"Tutorials\",\"collapsed\":false,\"items\":[{\"text\":\"Tests Are SQL Files Too\",\"link\":\"/blog/npgsqlrest-3.19-sql-test-runner-watch-mode\"},{\"text\":\"Case Study: 74 Endpoints, Zero Backend Code\",\"link\":\"/blog/case-study-zero-backend-code\"},{\"text\":\"TypeScript Code Generation Walkthrough\",\"link\":\"/blog/typescript-codegen-walkthrough\"},{\"text\":\"NpgsqlRest 3.13.0: Production Patterns\",\"link\":\"/blog/npgsqlrest-3.13-production-patterns\"},{\"text\":\"SQL REST API\",\"link\":\"/blog/sql-rest-api\"},{\"text\":\"SQL File Source\",\"link\":\"/blog/sql-file-source-rest-api-from-plain-sql\"},{\"text\":\"Database-Level Security\",\"link\":\"/blog/database-level-security-postgresql-authentication\"},{\"text\":\"Multiple Auth Schemes & RBAC\",\"link\":\"/blog/multiple-auth-schemes-rbac-external-providers\"},{\"text\":\"Passkey SQL Auth\",\"link\":\"/blog/passkey-sql-auth\"},{\"text\":\"End-to-End Type Checking\",\"link\":\"/blog/end-to-end-static-type-checking-postgresql-typescript\"},{\"text\":\"Real-Time Chat with SSE\",\"link\":\"/blog/real-time-chat-postgresql-sse-npgsqlrest\"},{\"text\":\"External API Calls\",\"link\":\"/blog/external-api-calls-postgresql-http-types\"},{\"text\":\"Web Scraping with HTTP Types\",\"link\":\"/blog/web-scraping-postgresql-http-types-xml\"},{\"text\":\"Reverse Proxy & AI Service\",\"link\":\"/blog/reverse-proxy-postgresql-ai-service-npgsqlrest\"},{\"text\":\"Secure Image Uploads\",\"link\":\"/blog/secure-image-uploads-postgresql-typescript\"},{\"text\":\"CSV & Excel Ingestion\",\"link\":\"/blog/csv-excel-ingestion-postgresql-npgsqlrest\"},{\"text\":\"PostgreSQL BI Server\",\"link\":\"/blog/postgresql-bi-server-excel-csv-basic-auth\"}]},{\"text\":\"Feature Deep Dives\",\"collapsed\":false,\"items\":[{\"text\":\"Excel Exports Done Right\",\"link\":\"/blog/excel-export-table-format-postgresql-npgsqlrest\"},{\"text\":\"Custom Types & Multiset\",\"link\":\"/blog/custom-types-multiset-rest-api\"},{\"text\":\"Performance & High Availability\",\"link\":\"/blog/performance-scalability-high-availability-npgsqlrest\"},{\"text\":\"Optimization Labels 101\",\"link\":\"/blog/optimization-labels-101\"},{\"text\":\"What Have Stored Procedures Done for Us?\",\"link\":\"/blog/what-have-stored-procedures-ever-done-for-us\"},{\"text\":\"The Power of Simplicity\",\"link\":\"/blog/the-power-of-simplicity\"}]},{\"text\":\"Benchmarks & Comparisons\",\"collapsed\":false,\"items\":[{\"text\":\"Benchmark 2026\",\"link\":\"/blog/postgresql-rest-api-benchmark-2026\"},{\"text\":\"NpgsqlRest vs PostgREST vs Supabase\",\"link\":\"/blog/npgsqlrest-vs-postgrest-supabase-comparison\"}]},{\"text\":\"Other Links\",\"collapsed\":false,\"items\":[{\"text\":\"Guide\",\"link\":\"/guide/\"},{\"text\":\"Annotations Reference\",\"link\":\"/annotations/\"},{\"text\":\"Configuration Reference\",\"link\":\"/config/\"}]}],\"/guide/changelog/\":[{\"text\":\"Changelog\",\"items\":[{\"text\":\"Overview\",\"link\":\"/guide/changelog/\"},{\"text\":\"v3.19.0 (Latest)\",\"link\":\"/guide/changelog/v3.19.0\"},{\"text\":\"v3.18.2\",\"link\":\"/guide/changelog/v3.18.2\"},{\"text\":\"v3.18.1\",\"link\":\"/guide/changelog/v3.18.1\"},{\"text\":\"v3.18.0\",\"link\":\"/guide/changelog/v3.18.0\"},{\"text\":\"v3.17.0\",\"link\":\"/guide/changelog/v3.17.0\"},{\"text\":\"v3.16.3\",\"link\":\"/guide/changelog/v3.16.3\"},{\"text\":\"v3.16.2\",\"link\":\"/guide/changelog/v3.16.2\"},{\"text\":\"v3.16.1\",\"link\":\"/guide/changelog/v3.16.1\"},{\"text\":\"v3.16.0\",\"link\":\"/guide/changelog/v3.16.0\"},{\"text\":\"v3.15.2\",\"link\":\"/guide/changelog/v3.15.2\"},{\"text\":\"v3.15.1\",\"link\":\"/guide/changelog/v3.15.1\"},{\"text\":\"v3.15.0\",\"link\":\"/guide/changelog/v3.15.0\"},{\"text\":\"v3.14.0\",\"link\":\"/guide/changelog/v3.14.0\"},{\"text\":\"v3.13.0\",\"link\":\"/guide/changelog/v3.13.0\"},{\"text\":\"v3.12.0\",\"link\":\"/guide/changelog/v3.12.0\"},{\"text\":\"v3.11.1\",\"link\":\"/guide/changelog/v3.11.1\"},{\"text\":\"v3.11.0\",\"link\":\"/guide/changelog/v3.11.0\"},{\"text\":\"v3.10.0\",\"link\":\"/guide/changelog/v3.10.0\"},{\"text\":\"v3.9.0\",\"link\":\"/guide/changelog/v3.9.0\"},{\"text\":\"v3.8.0\",\"link\":\"/guide/changelog/v3.8.0\"},{\"text\":\"v3.7.0\",\"link\":\"/guide/changelog/v3.7.0\"},{\"text\":\"v3.6.3\",\"link\":\"/guide/changelog/v3.6.3\"},{\"text\":\"v3.6.2\",\"link\":\"/guide/changelog/v3.6.2\"},{\"text\":\"v3.6.1\",\"link\":\"/guide/changelog/v3.6.1\"},{\"text\":\"v3.6.0\",\"link\":\"/guide/changelog/v3.6.0\"},{\"text\":\"v3.5.0\",\"link\":\"/guide/changelog/v3.5.0\"},{\"text\":\"v3.4.8\",\"link\":\"/guide/changelog/v3.4.8\"},{\"text\":\"v3.4.7\",\"link\":\"/guide/changelog/v3.4.7\"},{\"text\":\"v3.4.6\",\"link\":\"/guide/changelog/v3.4.6\"},{\"text\":\"v3.4.5\",\"link\":\"/guide/changelog/v3.4.5\"},{\"text\":\"v3.4.4\",\"link\":\"/guide/changelog/v3.4.4\"},{\"text\":\"v3.4.3\",\"link\":\"/guide/changelog/v3.4.3\"},{\"text\":\"v3.4.2\",\"link\":\"/guide/changelog/v3.4.2\"},{\"text\":\"v3.4.1\",\"link\":\"/guide/changelog/v3.4.1\"},{\"text\":\"v3.4.0\",\"link\":\"/guide/changelog/v3.4.0\"},{\"text\":\"v3.3.1\",\"link\":\"/guide/changelog/v3.3.1\"},{\"text\":\"v3.3.0\",\"link\":\"/guide/changelog/v3.3.0\"},{\"text\":\"v3.2.7\",\"link\":\"/guide/changelog/v3.2.7\"},{\"text\":\"v3.2.6\",\"link\":\"/guide/changelog/v3.2.6\"},{\"text\":\"v3.2.4\",\"link\":\"/guide/changelog/v3.2.4\"},{\"text\":\"v3.2.3\",\"link\":\"/guide/changelog/v3.2.3\"},{\"text\":\"v3.2.2\",\"link\":\"/guide/changelog/v3.2.2\"},{\"text\":\"v3.2.1\",\"link\":\"/guide/changelog/v3.2.1\"},{\"text\":\"v3.2.0\",\"link\":\"/guide/changelog/v3.2.0\"},{\"text\":\"v3.1.3\",\"link\":\"/guide/changelog/v3.1.3\"},{\"text\":\"v3.1.2\",\"link\":\"/guide/changelog/v3.1.2\"},{\"text\":\"v3.1.1\",\"link\":\"/guide/changelog/v3.1.1\"},{\"text\":\"v3.1.0\",\"link\":\"/guide/changelog/v3.1.0\"},{\"text\":\"v3.0.1\",\"link\":\"/guide/changelog/v3.0.1\"},{\"text\":\"v3.0.0\",\"link\":\"/guide/changelog/v3.0.0\"}]},{\"text\":\"Other Links\",\"collapsed\":false,\"items\":[{\"text\":\"Guide\",\"link\":\"/guide/\"},{\"text\":\"Annotations Reference\",\"link\":\"/annotations/\"},{\"text\":\"Configuration Reference\",\"link\":\"/config/\"}]}],\"/examples/\":[{\"text\":\"Examples\",\"items\":[{\"text\":\"Overview\",\"link\":\"/examples/\"}]},{\"text\":\"Reference\",\"items\":[{\"text\":\"Guide\",\"link\":\"/guide/\"},{\"text\":\"Annotations Reference\",\"link\":\"/annotations/\"},{\"text\":\"Configuration Reference\",\"link\":\"/config/\"},{\"text\":\"Changelog\",\"link\":\"/guide/changelog/\"}]}],\"/guide/\":[{\"text\":\"Getting Started\",\"items\":[{\"text\":\"Overview\",\"link\":\"/guide/\"},{\"text\":\"Installation\",\"link\":\"/guide/installation\"},{\"text\":\"Quick Start\",\"link\":\"/guide/quick-start\"}]},{\"text\":\"Topics\",\"items\":[{\"text\":\"SQL File Endpoints\",\"link\":\"/guide/sql-files\"},{\"text\":\"Configuration Guide\",\"link\":\"/guide/configuration\"},{\"text\":\"Comment Annotations Guide\",\"link\":\"/guide/annotations\"},{\"text\":\"Authentication\",\"link\":\"/guide/authentication\"},{\"text\":\"Server-Sent Events\",\"link\":\"/guide/sse\"},{\"text\":\"HTTP Custom Types\",\"link\":\"/guide/http-types\"},{\"text\":\"Proxy Endpoints\",\"link\":\"/guide/proxy\"},{\"text\":\"Testing\",\"link\":\"/guide/testing\"},{\"text\":\"Logging\",\"link\":\"/guide/logging\"}]},{\"text\":\"Reference\",\"items\":[{\"text\":\"FAQ & Troubleshooting\",\"link\":\"/guide/faq\"},{\"text\":\"Changelog\",\"link\":\"/guide/changelog/\"},{\"text\":\"Examples\",\"link\":\"/examples/\"},{\"text\":\"Annotations Reference\",\"link\":\"/annotations/\"},{\"text\":\"Configuration Reference\",\"link\":\"/config/\"}]}],\"/annotations/\":[{\"text\":\"HTTP & Routing\",\"items\":[{\"text\":\"Overview\",\"link\":\"/annotations/\"},{\"text\":\"HTTP\",\"link\":\"/annotations/http\"},{\"text\":\"PATH\",\"link\":\"/annotations/path\"},{\"text\":\"PROXY\",\"link\":\"/annotations/proxy\"},{\"text\":\"PROXY_OUT\",\"link\":\"/annotations/proxy-out\"},{\"text\":\"INTERNAL\",\"link\":\"/annotations/internal\"},{\"text\":\"ENABLED\",\"link\":\"/annotations/enabled\"},{\"text\":\"DISABLED\",\"link\":\"/annotations/disabled\"},{\"text\":\"TAGS\",\"link\":\"/annotations/tags\"},{\"text\":\"OPENAPI\",\"link\":\"/annotations/openapi\"},{\"text\":\"MCP\",\"link\":\"/annotations/mcp\"},{\"text\":\"HTTP CUSTOM TYPES\",\"link\":\"/annotations/http-type\"}]},{\"text\":\"Authorization\",\"items\":[{\"text\":\"AUTHORIZE\",\"link\":\"/annotations/authorize\"},{\"text\":\"ALLOW_ANONYMOUS\",\"link\":\"/annotations/allow-anonymous\"},{\"text\":\"LOGIN\",\"link\":\"/annotations/login\"},{\"text\":\"LOGOUT\",\"link\":\"/annotations/logout\"},{\"text\":\"BASIC_AUTH\",\"link\":\"/annotations/basic-auth\"},{\"text\":\"BASIC_AUTH_REALM\",\"link\":\"/annotations/basic-auth-realm\"},{\"text\":\"BASIC_AUTH_COMMAND\",\"link\":\"/annotations/basic-auth-command\"}]},{\"text\":\"Request Configuration\",\"items\":[{\"text\":\"REQUEST_PARAM_TYPE\",\"link\":\"/annotations/request-param-type\"},{\"text\":\"REQUEST_HEADERS_MODE\",\"link\":\"/annotations/request-headers-mode\"},{\"text\":\"REQUEST_HEADERS_PARAMETER_NAME\",\"link\":\"/annotations/request-headers-parameter-name\"},{\"text\":\"BODY_PARAMETER_NAME\",\"link\":\"/annotations/body-parameter-name\"},{\"text\":\"QUERY_STRING_NULL_HANDLING\",\"link\":\"/annotations/query-string-null-handling\"},{\"text\":\"VALIDATE\",\"link\":\"/annotations/validate\"}]},{\"text\":\"Response Configuration\",\"items\":[{\"text\":\"Response Headers\",\"link\":\"/annotations/response-headers\"},{\"text\":\"RESPONSE_NULL_HANDLING\",\"link\":\"/annotations/response-null-handling\"},{\"text\":\"NESTED\",\"link\":\"/annotations/nested\"},{\"text\":\"SINGLE\",\"link\":\"/annotations/single\"},{\"text\":\"VOID\",\"link\":\"/annotations/void\"},{\"text\":\"RAW\",\"link\":\"/annotations/raw\"},{\"text\":\"SEPARATOR\",\"link\":\"/annotations/separator\"},{\"text\":\"NEW_LINE\",\"link\":\"/annotations/new-line\"},{\"text\":\"COLUMN_NAMES\",\"link\":\"/annotations/column-names\"}]},{\"text\":\"Caching & Performance\",\"items\":[{\"text\":\"CACHED\",\"link\":\"/annotations/cached\"},{\"text\":\"CACHE_EXPIRES_IN\",\"link\":\"/annotations/cache-expires-in\"},{\"text\":\"CACHE_PROFILE\",\"link\":\"/annotations/cache-profile\"},{\"text\":\"BUFFER_ROWS\",\"link\":\"/annotations/buffer-rows\"},{\"text\":\"COMMAND_TIMEOUT\",\"link\":\"/annotations/command-timeout\"},{\"text\":\"RETRY_STRATEGY\",\"link\":\"/annotations/retry-strategy\"},{\"text\":\"Interval Format\",\"link\":\"/annotations/interval-format\"}]},{\"text\":\"Server-Sent Events\",\"items\":[{\"text\":\"SSE\",\"link\":\"/annotations/sse\"},{\"text\":\"SSE_EVENTS_LEVEL\",\"link\":\"/annotations/sse-events-level\"},{\"text\":\"SSE_EVENTS_SCOPE\",\"link\":\"/annotations/sse-events-scope\"}]},{\"text\":\"Upload & Policies\",\"items\":[{\"text\":\"UPLOAD\",\"link\":\"/annotations/upload\"},{\"text\":\"ERROR_CODE_POLICY\",\"link\":\"/annotations/error-code-policy\"},{\"text\":\"RATE_LIMITER_POLICY\",\"link\":\"/annotations/rate-limiter-policy\"}]},{\"text\":\"Output & Code Generation\",\"items\":[{\"text\":\"TABLE_FORMAT\",\"link\":\"/annotations/table-format\"},{\"text\":\"TSCLIENT\",\"link\":\"/annotations/tsclient\"}]},{\"text\":\"Parameters\",\"items\":[{\"text\":\"PARAM\",\"link\":\"/annotations/param\"},{\"text\":\"PARAMETER_HASH\",\"link\":\"/annotations/parameter-hash\"},{\"text\":\"ENCRYPT / DECRYPT\",\"link\":\"/annotations/encrypt-decrypt\"},{\"text\":\"Parameter Value Substitution\",\"link\":\"/annotations/parameter-substitution\"},{\"text\":\"Resolved Parameters\",\"link\":\"/annotations/resolved-parameters\"}]},{\"text\":\"SQL File Annotations\",\"items\":[{\"text\":\"DEFINE_PARAM\",\"link\":\"/annotations/define-param\"},{\"text\":\"RESULT_NAME\",\"link\":\"/annotations/result-name\"},{\"text\":\"SKIP\",\"link\":\"/annotations/skip\"},{\"text\":\"RETURNS\",\"link\":\"/annotations/returns\"}]},{\"text\":\"Test Files (--test)\",\"items\":[{\"text\":\"TEST @setup\",\"link\":\"/annotations/test-setup\"},{\"text\":\"TEST @teardown\",\"link\":\"/annotations/test-teardown\"},{\"text\":\"TEST @connection\",\"link\":\"/annotations/test-connection\"},{\"text\":\"TEST @tag\",\"link\":\"/annotations/test-tag\"},{\"text\":\"TEST @claim\",\"link\":\"/annotations/test-claim\"},{\"text\":\"TEST @response\",\"link\":\"/annotations/test-response\"}]},{\"text\":\"Context & Security\",\"items\":[{\"text\":\"USER_CONTEXT\",\"link\":\"/annotations/user-context\"},{\"text\":\"USER_PARAMETERS\",\"link\":\"/annotations/user-parameters\"},{\"text\":\"CONNECTION\",\"link\":\"/annotations/connection\"},{\"text\":\"SECURITY_SENSITIVE\",\"link\":\"/annotations/security-sensitive\"},{\"text\":\"Custom Parameters\",\"link\":\"/annotations/custom-parameters\"}]},{\"text\":\"Other Links\",\"collapsed\":false,\"items\":[{\"text\":\"Guide\",\"link\":\"/guide/\"},{\"text\":\"Configuration Reference\",\"link\":\"/config/\"},{\"text\":\"Changelog\",\"link\":\"/guide/changelog/\"}]}],\"/config/\":[{\"text\":\"Core Settings\",\"items\":[{\"text\":\"Overview\",\"link\":\"/config/\"},{\"text\":\"Latest Default Configuration\",\"link\":\"/config/latest\"},{\"text\":\"Top-Level Settings\",\"link\":\"/config/top-level\"},{\"text\":\"Config Section\",\"link\":\"/config/config-section\"},{\"text\":\"NpgsqlRest Options\",\"link\":\"/config/npgsqlrest\"},{\"text\":\"Routine Options\",\"link\":\"/config/routine-options\"},{\"text\":\"Connection Settings\",\"link\":\"/config/connection\"},{\"text\":\"Server & SSL\",\"link\":\"/config/server\"}]},{\"text\":\"Security\",\"items\":[{\"text\":\"Authentication\",\"link\":\"/config/auth\"},{\"text\":\"External OAuth\",\"link\":\"/config/external-auth\"},{\"text\":\"Passkey Authentication\",\"link\":\"/config/passkey-auth\"},{\"text\":\"Authentication Options\",\"link\":\"/config/authentication-options\"},{\"text\":\"Claims Mapping\",\"link\":\"/config/claims-mapping\"},{\"text\":\"Basic Auth Config\",\"link\":\"/config/basic-auth-config\"},{\"text\":\"Validation\",\"link\":\"/config/validation\"},{\"text\":\"Antiforgery\",\"link\":\"/config/antiforgery\"},{\"text\":\"Data Protection\",\"link\":\"/config/data-protection\"},{\"text\":\"CORS\",\"link\":\"/config/cors\"},{\"text\":\"Security Headers\",\"link\":\"/config/security-headers\"},{\"text\":\"Forwarded Headers\",\"link\":\"/config/forwarded-headers\"}]},{\"text\":\"Features\",\"items\":[{\"text\":\"SQL File Source\",\"link\":\"/config/sql-file-source\"},{\"text\":\"Test Runner\",\"link\":\"/config/test-runner\"},{\"text\":\"Watch Mode\",\"link\":\"/config/watch\"},{\"text\":\"Proxy\",\"link\":\"/config/proxy\"},{\"text\":\"OpenAPI\",\"link\":\"/config/openapi\"},{\"text\":\"MCP\",\"link\":\"/config/mcp\"},{\"text\":\"HTTP Files\",\"link\":\"/config/http-files\"},{\"text\":\"Code Generation\",\"link\":\"/config/codegen\"},{\"text\":\"Upload Options\",\"link\":\"/config/uploads\"},{\"text\":\"Table Format\",\"link\":\"/config/table-format\"},{\"text\":\"HTTP Client\",\"link\":\"/config/http-client\"}]},{\"text\":\"Performance\",\"items\":[{\"text\":\"Response Compression\",\"link\":\"/config/response-compression\"},{\"text\":\"Cache Options\",\"link\":\"/config/cache-options\"},{\"text\":\"Rate Limiter\",\"link\":\"/config/rate-limiter\"},{\"text\":\"Command Retry\",\"link\":\"/config/command-retry\"},{\"text\":\"Thread Pool\",\"link\":\"/config/thread-pool\"}]},{\"text\":\"Infrastructure\",\"items\":[{\"text\":\"Logging\",\"link\":\"/config/logging\"},{\"text\":\"Static Files\",\"link\":\"/config/static-files\"},{\"text\":\"Error Handling\",\"link\":\"/config/error-handling\"},{\"text\":\"Health Checks\",\"link\":\"/config/health-checks\"},{\"text\":\"Stats\",\"link\":\"/config/stats\"}]},{\"text\":\"Other Links\",\"collapsed\":false,\"items\":[{\"text\":\"Guide\",\"link\":\"/guide/\"},{\"text\":\"Annotations Reference\",\"link\":\"/annotations/\"}]}]},\"socialLinks\":[{\"icon\":\"github\",\"link\":\"https://github.com/NpgsqlRest/NpgsqlRest\"},{\"icon\":{\"svg\":\"<svg xmlns=\\\"http://www.w3.org/2000/svg\\\" viewBox=\\\"0 0 24 24\\\" fill=\\\"currentColor\\\"><path d=\\\"M22.957 7.21c-.004-3.064-2.391-5.576-5.191-6.482-3.478-1.125-8.064-.962-11.384.604C2.357 3.231 1.093 7.391 1.046 11.54c-.039 3.411.302 7.463 3.097 9.746 2.602 2.124 6.19 2.078 9.402 1.836 1.883-.142 3.939-.358 5.477-1.528 1.564-1.19 1.873-3.298 1.972-5.151.134-2.51.187-5.016-.037-7.233zm-2.55 7.703c-.078 1.593-.383 3.473-1.795 4.334-1.351.824-3.151.973-4.686 1.083-2.891.207-6.063.276-8.514-1.399C3.106 17.39 2.924 14.34 2.95 11.67c.03-3.206.678-6.721 3.654-8.375 2.756-1.531 6.556-1.727 9.544-.872 2.339.669 4.351 2.678 4.355 5.217.007 2.583-.016 5.168-.095 7.273z\\\"/><path d=\\\"M16.088 5.19c-1.008-.542-2.607-.48-3.441.388-.568.591-.773 1.389-.8 2.172-.034.993.068 2.013.361 2.953.307.985 1.034 1.848 2.077 2.012.878.138 1.83-.088 2.458-.732.7-.718.88-1.81.877-2.779-.003-.989-.084-1.99-.423-2.91-.268-.726-.654-1.328-1.109-1.104zm-.294 5.482c-.182.462-.581.771-1.091.696-.532-.078-.859-.533-1.04-1.005-.248-.647-.327-1.375-.312-2.06.01-.46.095-.939.371-1.313.345-.467.993-.447 1.426-.115.41.315.607.835.74 1.32.18.66.211 1.675-.094 2.477zM8.4 10.394c.233 1.084.755 2.299 1.907 2.579.934.227 1.993-.165 2.434-1.048.455-.91.346-1.995.166-2.972-.165-.893-.46-1.885-1.207-2.445-.745-.559-1.832-.457-2.479.192-.84.843-.958 2.138-.955 3.277.001.147.119.364.134.417zm1.363-2.808c.302-.321.805-.336 1.14-.054.445.375.618 1.015.75 1.558.131.54.212 1.156.082 1.703-.1.423-.378.821-.84.882-.469.062-.886-.306-1.088-.704-.326-.644-.401-1.426-.381-2.148.013-.465.104-.993.337-1.237z\\\"/></svg>\"},\"link\":\"https://patreon.com/vbconsulting\",\"ariaLabel\":\"Patreon\"},{\"icon\":{\"svg\":\"<svg xmlns=\\\"http://www.w3.org/2000/svg\\\" viewBox=\\\"0 0 24 24\\\" fill=\\\"currentColor\\\"><path d=\\\"M20.216 6.415l-.132-.666c-.119-.598-.388-1.163-1.001-1.379-.197-.069-.42-.098-.57-.241-.152-.143-.196-.366-.231-.572-.065-.378-.125-.756-.192-1.133-.057-.325-.102-.69-.25-.987-.195-.4-.597-.634-.996-.788a5.723 5.723 0 00-.626-.194c-1-.263-2.05-.36-3.077-.416a25.834 25.834 0 00-3.7.062c-.915.083-1.88.184-2.75.5-.318.116-.646.256-.888.501-.297.302-.393.77-.177 1.146.154.267.415.456.692.58.36.162.737.284 1.123.366 1.075.238 2.189.331 3.287.37 1.218.05 2.437.01 3.65-.118.299-.033.598-.073.896-.119.352-.054.578-.513.474-.834-.124-.383-.457-.531-.834-.473-.466.074-.96.108-1.382.146-1.177.08-2.358.082-3.536.006a22.228 22.228 0 01-1.157-.107c-.086-.01-.18-.025-.258-.036-.243-.036-.484-.08-.724-.13-.111-.027-.111-.185 0-.212h.005c.277-.06.557-.108.838-.147h.002c.131-.009.263-.032.394-.048a25.076 25.076 0 013.426-.12c.674.019 1.347.067 2.017.144l.228.031c.267.04.533.088.798.145.392.085.895.113 1.07.542.055.137.08.288.111.431l.319 1.484a.237.237 0 01-.199.284h-.003c-.037.006-.075.01-.112.015a36.704 36.704 0 01-4.743.295 37.059 37.059 0 01-4.699-.304c-.14-.017-.293-.042-.417-.06-.326-.048-.649-.108-.973-.161-.393-.065-.768-.032-1.123.161-.29.16-.527.404-.675.701-.154.316-.199.66-.267 1-.069.34-.176.707-.135 1.056.087.753.613 1.365 1.37 1.502a39.69 39.69 0 0011.343.376.483.483 0 01.535.53l-.071.697-1.018 9.907c-.041.41-.047.832-.125 1.237-.122.637-.553 1.028-1.182 1.171-.577.131-1.165.2-1.756.205-.656.004-1.31-.025-1.966-.022-.699.004-1.556-.06-2.095-.58-.475-.458-.54-1.174-.605-1.793l-.731-7.013-.322-3.094c-.037-.351-.286-.695-.678-.678-.336.015-.718.3-.678.679l.228 2.185.949 9.112c.147 1.344 1.174 2.068 2.446 2.272.742.12 1.503.144 2.257.156.966.016 1.942.053 2.892-.122 1.408-.258 2.465-1.198 2.616-2.657.34-3.332.683-6.663 1.024-9.995l.215-2.087a.484.484 0 01.39-.426c.402-.078.787-.212 1.074-.518.455-.488.546-1.124.385-1.766zm-1.478.772c-.145.137-.363.201-.578.233-2.416.359-4.866.54-7.308.46-1.748-.06-3.477-.254-5.207-.498-.17-.024-.353-.055-.47-.18-.22-.236-.111-.71-.054-.995.052-.26.152-.609.463-.646.484-.057 1.046.148 1.526.22.577.088 1.156.159 1.737.212 2.48.226 5.002.19 7.472-.14.45-.06.899-.13 1.345-.21.399-.072.84-.206 1.08.206.166.281.188.657.162.974a.544.544 0 01-.169.364z\\\"/></svg>\"},\"link\":\"https://buymeacoffee.com/vbilopavu\",\"ariaLabel\":\"Buy Me a Coffee\"}],\"editLink\":{\"pattern\":\"https://github.com/NpgsqlRest/npgsqlrest-docs/edit/main/docs/:path\",\"text\":\"Edit this page on GitHub\"},\"search\":{\"provider\":\"local\"}},\"locales\":{},\"scrollOffset\":134,\"cleanUrls\":false}");</script>
</body>
</html>