forked from jawj/zapatos
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathindex.html
4083 lines (4080 loc) · 778 KB
/
index.html
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
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
<!DOCTYPE html><html lang="en"><head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width,initial-scale=1">
<meta name="google-site-verification" content="tN1ANkxDkpFanVNXNfGs0pOFnDVAZH6tkBCRW2fkV8I">
<!-- tocbot -->
<script src="https://cdnjs.cloudflare.com/ajax/libs/tocbot/4.11.1/tocbot.min.js"></script>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/tocbot/4.11.1/tocbot.css">
<!-- highlighting -->
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/highlight.js/9.18.1/styles/xcode.min.css">
<!-- monaco editor -->
<script src="monaco/vs/loader.js"></script>
<script src="zapatos-bundle.js"></script>
<!-- fonts -->
<link rel="preconnect" href="https://fonts.googleapis.com">
<link rel="preconnect" href="https://fonts.gstatic.com" crossorigin="">
<link href="https://fonts.googleapis.com/css2?family=Source+Code+Pro:wght@400;700&family=Source+Serif+4:ital,opsz,wght@0,8..60,400;0,8..60,700;1,8..60,400;1,8..60,700&display=swap" rel="stylesheet">
<!-- octocat -->
<style>.github-corner:hover .octo-arm{animation:octocat-wave 560ms ease-in-out}@keyframes octocat-wave{0%,100%{transform:rotate(0)}20%,60%{transform:rotate(-25deg)}40%,80%{transform:rotate(10deg)}}@media (max-width:500px){.github-corner:hover .octo-arm{animation:none}.github-corner .octo-arm{animation:octocat-wave 560ms ease-in-out}}</style>
<!-- custom -->
<link rel="stylesheet" href="docs.css">
<title>Zapatos: Zero-Abstraction Postgres for TypeScript</title></head>
<body>
<!-- http://tholman.com/github-corners/ -->
<a href="https://github.com/jawj/zapatos" class="github-corner" aria-label="View source on GitHub"><svg width="80" height="80" viewBox="0 0 250 250" style="fill:#aaa; color:#fff; position: fixed; z-index: 150; top: 0; border: 0; right: 0;" aria-hidden="true"><path d="M0,0 L115,115 L130,115 L142,142 L250,250 L250,0 Z"></path><path d="M128.3,109.0 C113.8,99.7 119.0,89.6 119.0,89.6 C122.0,82.7 120.5,78.6 120.5,78.6 C119.2,72.0 123.4,76.3 123.4,76.3 C127.3,80.9 125.5,87.3 125.5,87.3 C122.9,97.6 130.6,101.9 134.4,103.2" fill="currentColor" style="transform-origin: 130px 106px;" class="octo-arm"></path><path d="M115.0,115.0 C114.9,115.1 118.7,116.5 119.8,115.4 L133.7,101.6 C136.9,99.2 139.9,98.4 142.2,98.6 C133.8,88.0 127.5,74.4 143.8,58.0 C148.5,53.4 154.0,51.2 159.7,51.0 C160.3,49.4 163.2,43.6 171.4,40.1 C171.4,40.1 176.1,42.5 178.8,56.2 C183.1,58.6 187.2,61.8 190.9,65.4 C194.5,69.0 197.7,73.2 200.1,77.6 C213.8,80.2 216.3,84.9 216.3,84.9 C212.7,93.1 206.9,96.0 205.4,96.6 C205.1,102.4 203.0,107.8 198.3,112.5 C181.9,128.9 168.3,122.5 157.7,114.1 C157.9,116.9 156.7,120.9 152.7,124.9 L141.0,136.5 C139.8,137.7 141.6,141.9 141.8,141.8 Z" fill="currentColor" class="octo-body"></path></svg></a>
<div id="outer-toc">
<div id="toc"></div>
<div style="padding: 15px 20px;"><a href="https://github.com/jawj/zapatos">GitHub »</a></div>
</div>
<div id="content"><div class="logos"><img class="pg-logo" src="pg.svg" width="43.2" height="44.5" alt="Postgres logo"><img class="ts-logo" src="ts.svg" width="50.5" height="39" alt="TypeScript logo"></div>
<h1 id="zapatos-zero-abstraction-postgres-for-typescript"><b>Zap<span class="extra-vowels a">a</span>t<span class="extra-vowels o">o</span>s:</b> <br><span style="font-weight: normal;">Zero-Abstraction Postgres for TypeScript</span></h1>
<p><a href="https://www.postgresql.org/">Postgres</a> and <a href="https://www.typescriptlang.org/">TypeScript</a> are each, individually, fabulous.</p>
<p>Zapatos aims to make them work beautifully together. No abstractions, no distractions: just your database, with type safety.</p>
<div class="testimonials-window"><div class="testimonials">
<div class="testimonial">
<div class="quote">
<p>Wow this is amazing. […] Exactly the kind of ‘use SQL in typescript code with type-safety’ non-ORM that I’ve always wanted.</p>
</div>
<div class="attribution">
<p><a href="https://news.ycombinator.com/item?id=24371212">ummonk, HN</a></p>
</div>
</div>
<div class="testimonial">
<div class="quote">
<p>There are a number of TypeScript SQL libraries out there, but I found that Zapatos hits the sweet spot.</p>
</div>
<div class="attribution">
<p><a href="https://risticnikola.com/tips-for-apis-typescript">Nikola Ristić</a></p>
</div>
</div>
<div class="testimonial">
<div class="quote">
<p>Zapatos is amazing. […] I think its design is wonderful.</p>
</div>
<div class="attribution">
<p><a href="https://news.ycombinator.com/item?id=24367867">skrebbel, HN</a></p>
</div>
</div>
<div class="testimonial">
<div class="quote">
<p>Probably the most underrated #TypeScript #PostgreSQL package right now.</p>
</div>
<div class="attribution">
<p><a href="https://twitter.com/andywritescode/status/1265196222782070784">@andywritescode, Twitter</a></p>
</div>
</div>
<div class="testimonial">
<div class="quote">
<p>OK just ran the sample on my own schema, whoa, this is fire.</p>
</div>
<div class="attribution">
<p><a href="https://github.com/jawj/zapatos/issues/19#issuecomment-642740212">mrjjwright, GitHub</a></p>
</div>
</div>
<div class="testimonial">
<div class="quote">
<p>[I’ve] tried every ORM there is in the JS/TypeScript land. And I’ve settled on Zapatos, which […] is a breath of fresh air and is a delight to use.</p>
</div>
<div class="attribution">
<p><a href="https://news.ycombinator.com/item?id=27556821">moltar, HN</a></p>
</div>
</div>
<div class="testimonial">
<div class="quote">
<p>Am I crazy for thinking this seems really good?</p>
</div>
<div class="attribution">
<p><a href="https://twitter.com/hughevans/status/1295914249420550144">@hughevans, Twitter</a></p>
</div>
</div>
<div class="testimonial">
<div class="quote">
<p>Zapatos is super nice</p>
</div>
<div class="attribution">
<p><a href="https://news.ycombinator.com/item?id=26889128">nikolasburk (Prisma employee), HN</a></p>
</div>
</div>
</div></div>
<h2 id="what-does-it-do">What does it do?</h2>
<p>To achieve this aim, Zapatos does these five things:</p>
<ul>
<li>
<p><strong>Typescript schema</strong> A command-line tool speaks to your Postgres database and writes up a detailed TypeScript schema for every table. This is just a means to an end: it enables the next three things in this list. <a href="#typescript-schema">Show me »</a></p>
</li>
<li>
<p><strong>Arbitrary SQL</strong> Simple building blocks help you write arbitrary SQL using <a href="https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Template_literals#Tagged_templates">tagged templates</a>, and manually apply the right types to what goes in and what comes back. <a href="#arbitrary-sql">Show me »</a></p>
</li>
<li>
<p><strong>Everyday CRUD</strong> Shortcut functions produce everyday <a href="https://en.wikipedia.org/wiki/Create,_read,_update_and_delete">CRUD</a> queries with no fuss and no surprises, fully and automatically typed. <a href="#everyday-crud">Show me »</a></p>
</li>
<li>
<p><strong>JOINs as nested JSON</strong> Nested shortcut calls generate <a href="https://www.postgresql.org/docs/12/queries-table-expressions.html#id-1.5.6.6.5.10.2"><code>LATERAL JOIN</code></a> queries, resulting in arbitrarily complex nested JSON structures, still fully and automatically typed. <a href="#joins-as-nested-json">Show me »</a></p>
</li>
<li>
<p><strong>Transactions</strong> Transaction helper functions assist in managing and retrying transactions. <a href="#transactions">Show me »</a></p>
</li>
</ul>
<h3 id="how-does-that-look">How does that look?</h3>
<h4 id="typescript-schema">Typescript schema</h4>
<p><strong>A command-line tool speaks to your Postgres database and writes up a detailed TypeScript schema for every table.</strong></p>
<p>Take this ultra-simple SQL schema for a single table, <code>authors</code>:</p>
<pre class="language-sql"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> "authors" </span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;">( "id" SERIAL <span class="hljs-keyword">PRIMARY</span> KEY</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;">, "name" TEXT <span class="hljs-keyword">NOT</span> <span class="hljs-keyword">NULL</span></span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;">, "isLiving" <span class="hljs-type">BOOLEAN</span> );</span></code></pre>
<p>We run <code>npx zapatos</code> to generate a file named <code>schema.d.ts</code>, including table definitions like this one:</p>
<pre class="language-typescript norun"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">export</span> <span class="hljs-keyword">namespace</span> authors {</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-keyword">export</span> <span class="hljs-keyword">type</span> <span class="hljs-title class_">Table</span> = <span class="hljs-string">'authors'</span>;</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-keyword">export</span> <span class="hljs-keyword">interface</span> <span class="hljs-title class_">Selectable</span> {</span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">id</span>: <span class="hljs-built_in">number</span>;</span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">name</span>: <span class="hljs-built_in">string</span>;</span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">isLiving</span>: <span class="hljs-built_in">boolean</span> | <span class="hljs-literal">null</span>;</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> }</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-keyword">export</span> <span class="hljs-keyword">interface</span> <span class="hljs-title class_">Whereable</span> {</span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> id?: <span class="hljs-built_in">number</span> | db.<span class="hljs-property">Parameter</span><<span class="hljs-built_in">number</span>> | db.<span class="hljs-property">SQLFragment</span> <span class="hljs-comment">/* | ... etc ... */</span>;</span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> name?: <span class="hljs-built_in">string</span> | db.<span class="hljs-property">Parameter</span><<span class="hljs-built_in">string</span>> | db.<span class="hljs-property">SQLFragment</span> <span class="hljs-comment">/* | ... etc ... */</span>;</span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> isLiving?: <span class="hljs-built_in">boolean</span> | db.<span class="hljs-property">Parameter</span><<span class="hljs-built_in">boolean</span>> | db.<span class="hljs-property">SQLFragment</span> <span class="hljs-comment">/* | ... etc ... */</span>;</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> }</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-keyword">export</span> <span class="hljs-keyword">interface</span> <span class="hljs-title class_">Insertable</span> {</span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> id?: <span class="hljs-built_in">number</span> | db.<span class="hljs-property">Parameter</span><<span class="hljs-built_in">number</span>> | db.<span class="hljs-property">DefaultType</span> | db.<span class="hljs-property">SQLFragment</span>;</span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">name</span>: <span class="hljs-built_in">string</span> | db.<span class="hljs-property">Parameter</span><<span class="hljs-built_in">string</span>> | db.<span class="hljs-property">SQLFragment</span>;</span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> isLiving?: <span class="hljs-built_in">boolean</span> | db.<span class="hljs-property">Parameter</span><<span class="hljs-built_in">boolean</span>> | <span class="hljs-literal">null</span> | db.<span class="hljs-property">DefaultType</span> | db.<span class="hljs-property">SQLFragment</span>;</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> }</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-keyword">export</span> <span class="hljs-keyword">interface</span> <span class="hljs-title class_">Updatable</span> {</span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> id?: <span class="hljs-built_in">number</span> | db.<span class="hljs-property">Parameter</span><<span class="hljs-built_in">number</span>> | db.<span class="hljs-property">DefaultType</span> | db.<span class="hljs-property">SQLFragment</span> <span class="hljs-comment">/* | ... etc ... */</span>;</span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> name?: <span class="hljs-built_in">string</span> | db.<span class="hljs-property">Parameter</span><<span class="hljs-built_in">string</span>> | db.<span class="hljs-property">SQLFragment</span> <span class="hljs-comment">/* | ... etc ... */</span>;</span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> isLiving?: <span class="hljs-built_in">boolean</span> | db.<span class="hljs-property">Parameter</span><<span class="hljs-built_in">boolean</span>> | <span class="hljs-literal">null</span> | db.<span class="hljs-property">DefaultType</span> | db.<span class="hljs-property">SQLFragment</span> <span class="hljs-comment">/* | ... etc ... */</span>;</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> }</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-comment">/* ... etc ... */</span></span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;">}</span></code></pre>
<p>The type names are, I hope, reasonably self-explanatory. <code>authors.Selectable</code> is what I’ll get back from a <code>SELECT</code> query on this table. <code>authors.Whereable</code> is what I can use in a <code>WHERE</code> condition: everything’s optional, and I can include arbitrary SQL. <code>authors.Insertable</code> is what I can <code>INSERT</code>: it’s similar to the <code>Selectable</code>, but any fields that are <code>NULL</code>able and/or have <code>DEFAULT</code> values are allowed to be missing, <code>NULL</code> or <code>DEFAULT</code>. <code>authors.Updatable</code> is what I can <code>UPDATE</code> the table with: like what I can <code>INSERT</code>, but all columns are optional: it’s (roughly) a <code>Partial<authors.Insertable></code>.</p>
<p><code>schema.d.ts</code> includes some other types that get used internally, including handy type mappings like this one:</p>
<pre class="language-typescript norun"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">export</span> <span class="hljs-keyword">type</span> <span class="hljs-title class_">SelectableForTable</span><T <span class="hljs-keyword">extends</span> <span class="hljs-title class_">Table</span>> = {</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-attr">authors</span>: authors.<span class="hljs-property">Selectable</span>;</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-attr">books</span>: books.<span class="hljs-property">Selectable</span>;</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-attr">tags</span>: tags.<span class="hljs-property">Selectable</span>;</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-comment">/* ... */</span></span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;">}[T];</span></code></pre>
<p>Zapatos supports tables, foreign tables, views and materialized views. It understands enumerated types: <code>CREATE TYPE "size" AS ENUM ('big', 'small');</code> comes to TypeScript as <code>'big' | 'small'</code>. And it lets you define the TypeScript treatment of <a href="https://www.postgresql.org/docs/current/domains.html">domain types</a> and user-defined types too.</p>
<p><a href="#how-do-i-get-it">Tell me more about the command line tool »</a></p>
<h4 id="arbitrary-sql">Arbitrary SQL</h4>
<p><strong>Simple building blocks help you write arbitrary SQL using <a href="https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Template_literals#Tagged_templates">tagged templates</a>, and manually apply the right types to what goes in and what comes back.</strong></p>
<p>Let’s insert something into that <code>authors</code> table for which we just generated the types. We’ll write the SQL query ourselves, to show how that works (though we’ll see an easier way <a href="#everyday-crud">in the next section</a>):</p>
<pre class="language-typescript runnable"><code class="imports"><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">import</span> * <span class="hljs-keyword">as</span> db <span class="hljs-keyword">from</span> <span class="hljs-string">'zapatos/db'</span>;</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">import type</span> * <span class="hljs-keyword">as</span> s <span class="hljs-keyword">from</span> <span class="hljs-string">'zapatos/schema'</span>;</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">import</span> pool <span class="hljs-keyword">from</span> <span class="hljs-string">'./pgPool'</span>;</span></code><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">const</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-attr">author</span>: s.<span class="hljs-property">authors</span>.<span class="hljs-property">Insertable</span> = {</span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">name</span>: <span class="hljs-string">'Gabriel Garcia Marquez'</span>,</span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">isLiving</span>: <span class="hljs-literal">false</span>,</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> },</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> [insertedAuthor] = <span class="hljs-keyword">await</span> db.<span class="hljs-property">sql</span><s.<span class="hljs-property">authors</span>.<span class="hljs-property">SQL</span>, s.<span class="hljs-property">authors</span>.<span class="hljs-property">Selectable</span>[]><span class="hljs-string">`</span></span>
<span class="indent-line" style="padding-left: 10ch; text-indent: -10ch;"><span class="hljs-string"> INSERT INTO <span class="hljs-subst">${<span class="hljs-string">"authors"</span>}</span> (<span class="hljs-subst">${db.cols(author)}</span>)</span></span>
<span class="indent-line" style="padding-left: 10ch; text-indent: -10ch;"><span class="hljs-string"> VALUES (<span class="hljs-subst">${db.vals(author)}</span>) RETURNING *`</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> .<span class="hljs-title function_">run</span>(pool);</span></code></pre><div class="sqlstuff">
<pre class="sqltext"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> "authors" ("isLiving", "name")</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-keyword">VALUES</span> ($<span class="hljs-number">1</span>, $<span class="hljs-number">2</span>)</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;">RETURNING <span class="hljs-operator">*</span></span></code></pre>
<pre class="sqlvalues"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-punctuation">[</span><span class="hljs-literal"><span class="hljs-keyword">false</span></span><span class="hljs-punctuation">,</span> <span class="hljs-string">"Gabriel Garcia Marquez"</span><span class="hljs-punctuation">]</span></span></code></pre>
<pre class="sqlresult"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-punctuation">[</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-punctuation">{</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"id"</span><span class="hljs-punctuation">:</span> <span class="hljs-number">1</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"name"</span><span class="hljs-punctuation">:</span> <span class="hljs-string">"Gabriel Garcia Marquez"</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"isLiving"</span><span class="hljs-punctuation">:</span> <span class="hljs-literal"><span class="hljs-keyword">false</span></span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-punctuation">}</span></span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-punctuation">]</span></span></code></pre>
</div>
<p>We apply the appropriate type to the object we’re trying to insert (<code>s.authors.Insertable</code>), giving us type-checking and autocompletion on that object. And we specify both which types are allowed as interpolated values in the template string (<code>s.authors.SQL</code>) and what type is going to be returned (<code>s.authors.Selectable[]</code>) when the query runs.</p>
<p>We also use the <a href="#cols-and-vals"><code>cols</code> and <code>vals</code> helper functions</a>. These compile, respectively, to the object’s keys (which are the column names) and query placeholders (<code>$1</code>, <code>$2</code>, …) for the corresponding values.</p>
<p><em>You can click ‘Explore types’ above to open the code in an embedded Monaco (VS Code) editor, so you can check those typings for yourself.</em></p>
<p><a href="#sql-tagged-template-strings">Tell me more about writing arbitrary SQL »</a></p>
<h4 id="everyday-crud">Everyday CRUD</h4>
<p><strong>Shortcut functions produce everyday <a href="https://en.wikipedia.org/wiki/Create,_read,_update_and_delete">CRUD</a> queries with no fuss and no surprises, fully and automatically typed.</strong></p>
<p>So — writing SQL with Zapatos is nicer than constructing a query and all its input and output types from scratch. But for a totally bog-standard CRUD query like the <code>INSERT</code> above, it still involves quite a lot of boilerplate.</p>
<p>To eliminate the boilerplate, Zapatos supplies some simple functions to generate these sorts of queries, fully and automatically typed.</p>
<p>Let’s use one of them — <code>insert</code> — to add two more authors:</p>
<pre class="language-typescript runnable"><code class="imports"><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">import</span> * <span class="hljs-keyword">as</span> db <span class="hljs-keyword">from</span> <span class="hljs-string">'zapatos/db'</span>;</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">import</span> pool <span class="hljs-keyword">from</span> <span class="hljs-string">'./pgPool'</span>;</span></code><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">const</span> [doug, janey] = <span class="hljs-keyword">await</span> db.<span class="hljs-title function_">insert</span>(<span class="hljs-string">'authors'</span>, [</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> { <span class="hljs-attr">name</span>: <span class="hljs-string">'Douglas Adams'</span>, <span class="hljs-attr">isLiving</span>: <span class="hljs-literal">false</span> },</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> { <span class="hljs-attr">name</span>: <span class="hljs-string">'Jane Austen'</span>, <span class="hljs-attr">isLiving</span>: <span class="hljs-literal">false</span> },</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;">]).<span class="hljs-title function_">run</span>(pool);</span></code></pre><div class="sqlstuff">
<pre class="sqltext"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> "authors" ("isLiving", "name")</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-keyword">VALUES</span> ($<span class="hljs-number">1</span>, $<span class="hljs-number">2</span>), ($<span class="hljs-number">3</span>, $<span class="hljs-number">4</span>)</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;">RETURNING to_jsonb ("authors".<span class="hljs-operator">*</span>) <span class="hljs-keyword">AS</span> <span class="hljs-keyword">result</span></span></code></pre>
<pre class="sqlvalues"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-punctuation">[</span><span class="hljs-literal"><span class="hljs-keyword">false</span></span><span class="hljs-punctuation">,</span> <span class="hljs-string">"Douglas Adams"</span><span class="hljs-punctuation">,</span> <span class="hljs-literal"><span class="hljs-keyword">false</span></span><span class="hljs-punctuation">,</span> <span class="hljs-string">"Jane Austen"</span><span class="hljs-punctuation">]</span></span></code></pre>
<pre class="sqlresult"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-punctuation">[</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-punctuation">{</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"id"</span><span class="hljs-punctuation">:</span> <span class="hljs-number">2</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"name"</span><span class="hljs-punctuation">:</span> <span class="hljs-string">"Douglas Adams"</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"isLiving"</span><span class="hljs-punctuation">:</span> <span class="hljs-literal"><span class="hljs-keyword">false</span></span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-punctuation">{</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"id"</span><span class="hljs-punctuation">:</span> <span class="hljs-number">3</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"name"</span><span class="hljs-punctuation">:</span> <span class="hljs-string">"Jane Austen"</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"isLiving"</span><span class="hljs-punctuation">:</span> <span class="hljs-literal"><span class="hljs-keyword">false</span></span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-punctuation">}</span></span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-punctuation">]</span></span></code></pre>
</div>
<p>The <code>insert</code> shortcut accepts a single <code>Insertable</code> or an <code>Insertable[]</code> array, and correspondingly returns a single <a href="#jsonselectable"><code>JSONSelectable</code></a> or a <code>JSONSelectable[]</code> array. Since we specified <code>'authors'</code> as the first argument here, and an array as the second, input and output will be checked and auto-completed as <code>authors.Insertable[]</code> and <code>authors.JSONSelectable[]</code> respectively.</p>
<p><em>Again, click ‘Explore types’ to play around and check those typings.</em></p>
<p>In addition to <code>insert</code>, there are shortcuts for <code>select</code> (plus <code>selectOne</code>, <code>selectExactlyOne</code>, and simple aggregates such as <code>count</code> and <code>sum</code>), and for <code>update</code>, <code>upsert</code>, <code>delete</code> and <code>truncate</code>.</p>
<p><a href="#shortcut-functions-and-lateral-joins">Tell me more about the shortcut functions »</a></p>
<h4 id="joins-as-nested-json">JOINs as nested JSON</h4>
<p><strong>Nested shortcut calls generate <a href="https://www.postgresql.org/docs/12/queries-table-expressions.html#id-1.5.6.6.5.10.2">LATERAL JOIN</a> queries, resulting in arbitrarily complex nested JSON structures, still fully and automatically typed.</strong></p>
<p>CRUD is our bread and butter, but the power of SQL is in the <code>JOIN</code>s. Postgres has powerful JSON features than can deliver sensibly-structured <code>JOIN</code> results with minimal post-processing: <code>json_agg</code>, <code>json_build_object</code>, and so on. Zapatos builds on these.</p>
<p>To demonstrate, let’s say that <code>authors</code> have <code>books</code> and <code>books</code> have <code>tags</code>, adding two new tables to our simple schema:</p>
<pre class="language-sql"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> "books" </span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;">( "id" SERIAL <span class="hljs-keyword">PRIMARY</span> KEY</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;">, "authorId" <span class="hljs-type">INTEGER</span> <span class="hljs-keyword">NOT</span> <span class="hljs-keyword">NULL</span> <span class="hljs-keyword">REFERENCES</span> "authors"("id")</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;">, "title" TEXT</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;">, "createdAt" TIMESTAMPTZ <span class="hljs-keyword">NOT</span> <span class="hljs-keyword">NULL</span> <span class="hljs-keyword">DEFAULT</span> now() );</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"></span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> "tags"</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;">( "tag" TEXT <span class="hljs-keyword">NOT</span> <span class="hljs-keyword">NULL</span></span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;">, "bookId" <span class="hljs-type">INTEGER</span> <span class="hljs-keyword">NOT</span> <span class="hljs-keyword">NULL</span> <span class="hljs-keyword">REFERENCES</span> "books"("id") <span class="hljs-keyword">ON</span> <span class="hljs-keyword">DELETE</span> CASCADE );</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"></span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">UNIQUE</span> INDEX "tagsUniqueIdx" <span class="hljs-keyword">ON</span> "tags"("tag", "bookId");</span></code></pre>
<p>And let’s say I want to show a list of books, each with its (one) author and (many) associated tags. We could knock up a manual query for this, of course, but <a href="#manual-joins-using-postgres-json-features">it gets quite hairy</a>. The <code>select</code> shortcut has an option called <code>lateral</code> that can nest other <code>select</code> queries and do it for us.</p>
<p>Let’s try it:</p>
<pre class="language-typescript runnable"><code class="imports"><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">import</span> * <span class="hljs-keyword">as</span> db <span class="hljs-keyword">from</span> <span class="hljs-string">'zapatos/db'</span>;</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">import</span> pool <span class="hljs-keyword">from</span> <span class="hljs-string">'./pgPool'</span>;</span></code><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">const</span> bookAuthorTags = <span class="hljs-keyword">await</span> db.<span class="hljs-title function_">select</span>(<span class="hljs-string">'books'</span>, db.<span class="hljs-property">all</span>, {</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-attr">lateral</span>: {</span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">author</span>: db.<span class="hljs-title function_">selectExactlyOne</span>(<span class="hljs-string">'authors'</span>, { <span class="hljs-attr">id</span>: db.<span class="hljs-title function_">parent</span>(<span class="hljs-string">'authorId'</span>) }),</span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">tags</span>: db.<span class="hljs-title function_">select</span>(<span class="hljs-string">'tags'</span>, { <span class="hljs-attr">bookId</span>: db.<span class="hljs-title function_">parent</span>(<span class="hljs-string">'id'</span>) }),</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> }</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;">}).<span class="hljs-title function_">run</span>(pool);</span></code></pre><div class="sqlstuff">
<pre class="sqltext"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">SELECT</span> <span class="hljs-built_in">coalesce</span>(jsonb_agg(<span class="hljs-keyword">result</span>), <span class="hljs-string">'[]'</span>) <span class="hljs-keyword">AS</span> <span class="hljs-keyword">result</span></span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">FROM</span> (</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-keyword">SELECT</span> to_jsonb ("books".<span class="hljs-operator">*</span>) <span class="hljs-operator">||</span> jsonb_build_object($<span class="hljs-number">1</span>::text, "lateral_author".<span class="hljs-keyword">result</span>, $<span class="hljs-number">2</span>::text, "lateral_tags".<span class="hljs-keyword">result</span>) <span class="hljs-keyword">AS</span> <span class="hljs-keyword">result</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-keyword">FROM</span> "books"</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-keyword">LEFT</span> <span class="hljs-keyword">JOIN</span> <span class="hljs-keyword">LATERAL</span> (</span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-keyword">SELECT</span> to_jsonb ("authors".<span class="hljs-operator">*</span>) <span class="hljs-keyword">AS</span> <span class="hljs-keyword">result</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-keyword">FROM</span> "authors"</span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-keyword">WHERE</span> ("id" <span class="hljs-operator">=</span> "books"."authorId")</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> LIMIT $<span class="hljs-number">3</span>) <span class="hljs-keyword">AS</span> "lateral_author" <span class="hljs-keyword">ON</span> <span class="hljs-literal">true</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-keyword">LEFT</span> <span class="hljs-keyword">JOIN</span> <span class="hljs-keyword">LATERAL</span> (</span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-keyword">SELECT</span> <span class="hljs-built_in">coalesce</span>(jsonb_agg(<span class="hljs-keyword">result</span>), <span class="hljs-string">'[]'</span>) <span class="hljs-keyword">AS</span> <span class="hljs-keyword">result</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-keyword">FROM</span> (</span>
<span class="indent-line" style="padding-left: 10ch; text-indent: -10ch;"> <span class="hljs-keyword">SELECT</span> to_jsonb ("tags".<span class="hljs-operator">*</span>) <span class="hljs-keyword">AS</span> <span class="hljs-keyword">result</span></span>
<span class="indent-line" style="padding-left: 10ch; text-indent: -10ch;"> <span class="hljs-keyword">FROM</span> "tags"</span>
<span class="indent-line" style="padding-left: 10ch; text-indent: -10ch;"> <span class="hljs-keyword">WHERE</span> ("bookId" <span class="hljs-operator">=</span> "books"."id")) <span class="hljs-keyword">AS</span> "sq_tags") <span class="hljs-keyword">AS</span> "lateral_tags" <span class="hljs-keyword">ON</span> <span class="hljs-literal">true</span>) <span class="hljs-keyword">AS</span> "sq_books"</span></code></pre>
<pre class="sqlvalues"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-punctuation">[</span><span class="hljs-string">"author"</span><span class="hljs-punctuation">,</span> <span class="hljs-string">"tags"</span><span class="hljs-punctuation">,</span> <span class="hljs-number">1</span><span class="hljs-punctuation">]</span></span></code></pre>
<pre class="sqlresult"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-punctuation">[</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-punctuation">{</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"id"</span><span class="hljs-punctuation">:</span> <span class="hljs-number">1000</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"tags"</span><span class="hljs-punctuation">:</span> <span class="hljs-punctuation">[</span></span>
<span class="indent-line" style="padding-left: 10ch; text-indent: -10ch;"> <span class="hljs-punctuation">{</span></span>
<span class="indent-line" style="padding-left: 12ch; text-indent: -12ch;"> <span class="hljs-attr">"tag"</span><span class="hljs-punctuation">:</span> <span class="hljs-string">"His Dark Materials"</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 12ch; text-indent: -12ch;"> <span class="hljs-attr">"bookId"</span><span class="hljs-punctuation">:</span> <span class="hljs-number">1000</span></span>
<span class="indent-line" style="padding-left: 10ch; text-indent: -10ch;"> <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 10ch; text-indent: -10ch;"> <span class="hljs-punctuation">{</span></span>
<span class="indent-line" style="padding-left: 12ch; text-indent: -12ch;"> <span class="hljs-attr">"tag"</span><span class="hljs-punctuation">:</span> <span class="hljs-string">"1/3"</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 12ch; text-indent: -12ch;"> <span class="hljs-attr">"bookId"</span><span class="hljs-punctuation">:</span> <span class="hljs-number">1000</span></span>
<span class="indent-line" style="padding-left: 10ch; text-indent: -10ch;"> <span class="hljs-punctuation">}</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-punctuation">]</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"title"</span><span class="hljs-punctuation">:</span> <span class="hljs-string">"Northern Lights"</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"author"</span><span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span></span>
<span class="indent-line" style="padding-left: 10ch; text-indent: -10ch;"> <span class="hljs-attr">"id"</span><span class="hljs-punctuation">:</span> <span class="hljs-number">1000</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 10ch; text-indent: -10ch;"> <span class="hljs-attr">"name"</span><span class="hljs-punctuation">:</span> <span class="hljs-string">"Philip Pullman"</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 10ch; text-indent: -10ch;"> <span class="hljs-attr">"isLiving"</span><span class="hljs-punctuation">:</span> <span class="hljs-literal"><span class="hljs-keyword">true</span></span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"authorId"</span><span class="hljs-punctuation">:</span> <span class="hljs-number">1000</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"createdAt"</span><span class="hljs-punctuation">:</span> <span class="hljs-string">"2024-01-08T11:50:21.508324+00:00"</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-punctuation">{</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"id"</span><span class="hljs-punctuation">:</span> <span class="hljs-number">1001</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"tags"</span><span class="hljs-punctuation">:</span> <span class="hljs-punctuation">[</span></span>
<span class="indent-line" style="padding-left: 10ch; text-indent: -10ch;"> <span class="hljs-punctuation">{</span></span>
<span class="indent-line" style="padding-left: 12ch; text-indent: -12ch;"> <span class="hljs-attr">"tag"</span><span class="hljs-punctuation">:</span> <span class="hljs-string">"His Dark Materials"</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 12ch; text-indent: -12ch;"> <span class="hljs-attr">"bookId"</span><span class="hljs-punctuation">:</span> <span class="hljs-number">1001</span></span>
<span class="indent-line" style="padding-left: 10ch; text-indent: -10ch;"> <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 10ch; text-indent: -10ch;"> <span class="hljs-punctuation">{</span></span>
<span class="indent-line" style="padding-left: 12ch; text-indent: -12ch;"> <span class="hljs-attr">"tag"</span><span class="hljs-punctuation">:</span> <span class="hljs-string">"2/3"</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 12ch; text-indent: -12ch;"> <span class="hljs-attr">"bookId"</span><span class="hljs-punctuation">:</span> <span class="hljs-number">1001</span></span>
<span class="indent-line" style="padding-left: 10ch; text-indent: -10ch;"> <span class="hljs-punctuation">}</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-punctuation">]</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"title"</span><span class="hljs-punctuation">:</span> <span class="hljs-string">"The Subtle Knife"</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"author"</span><span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span></span>
<span class="indent-line" style="padding-left: 10ch; text-indent: -10ch;"> <span class="hljs-attr">"id"</span><span class="hljs-punctuation">:</span> <span class="hljs-number">1000</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 10ch; text-indent: -10ch;"> <span class="hljs-attr">"name"</span><span class="hljs-punctuation">:</span> <span class="hljs-string">"Philip Pullman"</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 10ch; text-indent: -10ch;"> <span class="hljs-attr">"isLiving"</span><span class="hljs-punctuation">:</span> <span class="hljs-literal"><span class="hljs-keyword">true</span></span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"authorId"</span><span class="hljs-punctuation">:</span> <span class="hljs-number">1000</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"createdAt"</span><span class="hljs-punctuation">:</span> <span class="hljs-string">"2024-01-08T11:50:21.50977+00:00"</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-punctuation">{</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"id"</span><span class="hljs-punctuation">:</span> <span class="hljs-number">1002</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"tags"</span><span class="hljs-punctuation">:</span> <span class="hljs-punctuation">[</span></span>
<span class="indent-line" style="padding-left: 10ch; text-indent: -10ch;"> <span class="hljs-punctuation">{</span></span>
<span class="indent-line" style="padding-left: 12ch; text-indent: -12ch;"> <span class="hljs-attr">"tag"</span><span class="hljs-punctuation">:</span> <span class="hljs-string">"His Dark Materials"</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 12ch; text-indent: -12ch;"> <span class="hljs-attr">"bookId"</span><span class="hljs-punctuation">:</span> <span class="hljs-number">1002</span></span>
<span class="indent-line" style="padding-left: 10ch; text-indent: -10ch;"> <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 10ch; text-indent: -10ch;"> <span class="hljs-punctuation">{</span></span>
<span class="indent-line" style="padding-left: 12ch; text-indent: -12ch;"> <span class="hljs-attr">"tag"</span><span class="hljs-punctuation">:</span> <span class="hljs-string">"3/3"</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 12ch; text-indent: -12ch;"> <span class="hljs-attr">"bookId"</span><span class="hljs-punctuation">:</span> <span class="hljs-number">1002</span></span>
<span class="indent-line" style="padding-left: 10ch; text-indent: -10ch;"> <span class="hljs-punctuation">}</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-punctuation">]</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"title"</span><span class="hljs-punctuation">:</span> <span class="hljs-string">"The Amber Spyglass"</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"author"</span><span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span></span>
<span class="indent-line" style="padding-left: 10ch; text-indent: -10ch;"> <span class="hljs-attr">"id"</span><span class="hljs-punctuation">:</span> <span class="hljs-number">1000</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 10ch; text-indent: -10ch;"> <span class="hljs-attr">"name"</span><span class="hljs-punctuation">:</span> <span class="hljs-string">"Philip Pullman"</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 10ch; text-indent: -10ch;"> <span class="hljs-attr">"isLiving"</span><span class="hljs-punctuation">:</span> <span class="hljs-literal"><span class="hljs-keyword">true</span></span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"authorId"</span><span class="hljs-punctuation">:</span> <span class="hljs-number">1000</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"createdAt"</span><span class="hljs-punctuation">:</span> <span class="hljs-string">"2024-01-08T11:50:21.510292+00:00"</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-punctuation">{</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"id"</span><span class="hljs-punctuation">:</span> <span class="hljs-number">1003</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"tags"</span><span class="hljs-punctuation">:</span> <span class="hljs-punctuation">[</span></span>
<span class="indent-line" style="padding-left: 10ch; text-indent: -10ch;"> <span class="hljs-punctuation">{</span></span>
<span class="indent-line" style="padding-left: 12ch; text-indent: -12ch;"> <span class="hljs-attr">"tag"</span><span class="hljs-punctuation">:</span> <span class="hljs-string">"mystery"</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 12ch; text-indent: -12ch;"> <span class="hljs-attr">"bookId"</span><span class="hljs-punctuation">:</span> <span class="hljs-number">1003</span></span>
<span class="indent-line" style="padding-left: 10ch; text-indent: -10ch;"> <span class="hljs-punctuation">}</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-punctuation">]</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"title"</span><span class="hljs-punctuation">:</span> <span class="hljs-string">"The Curious Incident of the Dog in the Night-Time"</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"author"</span><span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span></span>
<span class="indent-line" style="padding-left: 10ch; text-indent: -10ch;"> <span class="hljs-attr">"id"</span><span class="hljs-punctuation">:</span> <span class="hljs-number">1001</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 10ch; text-indent: -10ch;"> <span class="hljs-attr">"name"</span><span class="hljs-punctuation">:</span> <span class="hljs-string">"Mark Haddon"</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 10ch; text-indent: -10ch;"> <span class="hljs-attr">"isLiving"</span><span class="hljs-punctuation">:</span> <span class="hljs-literal"><span class="hljs-keyword">true</span></span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"authorId"</span><span class="hljs-punctuation">:</span> <span class="hljs-number">1001</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"createdAt"</span><span class="hljs-punctuation">:</span> <span class="hljs-string">"2024-01-08T11:50:21.513693+00:00"</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-punctuation">{</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"id"</span><span class="hljs-punctuation">:</span> <span class="hljs-number">1004</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"tags"</span><span class="hljs-punctuation">:</span> <span class="hljs-punctuation">[</span></span>
<span class="indent-line" style="padding-left: 10ch; text-indent: -10ch;"> <span class="hljs-punctuation">{</span></span>
<span class="indent-line" style="padding-left: 12ch; text-indent: -12ch;"> <span class="hljs-attr">"tag"</span><span class="hljs-punctuation">:</span> <span class="hljs-string">"adventure"</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 12ch; text-indent: -12ch;"> <span class="hljs-attr">"bookId"</span><span class="hljs-punctuation">:</span> <span class="hljs-number">1004</span></span>
<span class="indent-line" style="padding-left: 10ch; text-indent: -10ch;"> <span class="hljs-punctuation">}</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-punctuation">]</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"title"</span><span class="hljs-punctuation">:</span> <span class="hljs-string">"Holes"</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"author"</span><span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span></span>
<span class="indent-line" style="padding-left: 10ch; text-indent: -10ch;"> <span class="hljs-attr">"id"</span><span class="hljs-punctuation">:</span> <span class="hljs-number">1002</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 10ch; text-indent: -10ch;"> <span class="hljs-attr">"name"</span><span class="hljs-punctuation">:</span> <span class="hljs-string">"Louis Sachar"</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 10ch; text-indent: -10ch;"> <span class="hljs-attr">"isLiving"</span><span class="hljs-punctuation">:</span> <span class="hljs-literal"><span class="hljs-keyword">true</span></span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"authorId"</span><span class="hljs-punctuation">:</span> <span class="hljs-number">1002</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"createdAt"</span><span class="hljs-punctuation">:</span> <span class="hljs-string">"2024-01-08T11:50:21.514519+00:00"</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-punctuation">}</span></span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-punctuation">]</span></span></code></pre>
</div>
<p>This generates an efficient three-table <code>LATERAL JOIN</code> that returns a nested JSON structure directly from the database. Every nested element is again fully and automatically typed.</p>
<p><em>Again, you can click ‘Explore types’ above to open the code in an embedded Monaco (VS Code) editor, so you can check those typings for yourself.</em></p>
<p>We can of course extend this to deeper nesting (e.g. query each author, with their books, with their tags); to self-joins (of a table with itself, e.g. employees to their managers in the same <code>employees</code> table); and to joins on relationships other than foreign keys (e.g. joining the nearest <em>N</em> somethings using the PostGIS <code><-></code> distance operator).</p>
<p><a href="#lateral-and-alias">Tell me more about nested <code>select</code> queries »</a></p>
<h4 id="transactions">Transactions</h4>
<p><strong>Transaction helper functions assist in managing and retrying transactions.</strong></p>
<p>Transactions are where I’ve found traditional ORMs like TypeORM and Sequelize most footgun-prone. Zapatos is always explicit about what client or pool is running your query — hence that <code>pool</code> argument in all our examples so far.</p>
<p>Zapatos also offers simple transaction helpers that handle issuing a SQL <code>ROLLBACK</code> on error, releasing the database client in a <code>finally</code> clause, and automatically retrying queries in case of serialization failures. There’s one for each isolation level (<code>SERIALIZABLE</code>, <code>REPEATABLE READ</code>, and so on), and they look like this:</p>
<pre class="language-typescript noresult runnable"><code class="imports"><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">import</span> * <span class="hljs-keyword">as</span> db <span class="hljs-keyword">from</span> <span class="hljs-string">'zapatos/db'</span>;</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">import</span> pool <span class="hljs-keyword">from</span> <span class="hljs-string">'./pgPool'</span>;</span></code><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">const</span> result = <span class="hljs-keyword">await</span> db.<span class="hljs-title function_">serializable</span>(pool, <span class="hljs-keyword">async</span> txnClient => {</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-comment">/* queries here use txnClient instead of pool */</span></span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;">});</span></code></pre>
<p>For instance, take this <code>bankAccounts</code> table:</p>
<pre class="language-sql"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> "bankAccounts" </span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;">( "id" SERIAL <span class="hljs-keyword">PRIMARY</span> KEY</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;">, "balance" <span class="hljs-type">INTEGER</span> <span class="hljs-keyword">NOT</span> <span class="hljs-keyword">NULL</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-number">0</span> <span class="hljs-keyword">CHECK</span> ("balance" <span class="hljs-operator">>=</span> <span class="hljs-number">0</span>) );</span></code></pre>
<p>We can use the transaction helpers like so:</p>
<pre class="language-typescript runnable"><code class="imports"><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">import</span> * <span class="hljs-keyword">as</span> db <span class="hljs-keyword">from</span> <span class="hljs-string">'zapatos/db'</span>;</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">import</span> pool <span class="hljs-keyword">from</span> <span class="hljs-string">'./pgPool'</span>;</span></code><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">const</span> [accountA, accountB] = <span class="hljs-keyword">await</span> db.<span class="hljs-title function_">insert</span>(<span class="hljs-string">'bankAccounts'</span>, </span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> [{ <span class="hljs-attr">balance</span>: <span class="hljs-number">50</span> }, { <span class="hljs-attr">balance</span>: <span class="hljs-number">50</span> }]).<span class="hljs-title function_">run</span>(pool);</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"></span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">const</span> <span class="hljs-title function_">transferMoney</span> = (<span class="hljs-params">sendingAccountId: <span class="hljs-built_in">number</span>, receivingAccountId: <span class="hljs-built_in">number</span>, amount: <span class="hljs-built_in">number</span></span>) =></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> db.<span class="hljs-title function_">serializable</span>(pool, <span class="hljs-function"><span class="hljs-params">txnClient</span> =></span> <span class="hljs-title class_">Promise</span>.<span class="hljs-title function_">all</span>([</span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> db.<span class="hljs-title function_">update</span>(<span class="hljs-string">'bankAccounts'</span>,</span>
<span class="indent-line" style="padding-left: 10ch; text-indent: -10ch;"> { <span class="hljs-attr">balance</span>: db.<span class="hljs-property">sql</span><span class="hljs-string">`<span class="hljs-subst">${db.self}</span> - <span class="hljs-subst">${db.param(amount)}</span>`</span> },</span>
<span class="indent-line" style="padding-left: 10ch; text-indent: -10ch;"> { <span class="hljs-attr">id</span>: sendingAccountId }).<span class="hljs-title function_">run</span>(txnClient),</span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> db.<span class="hljs-title function_">update</span>(<span class="hljs-string">'bankAccounts'</span>,</span>
<span class="indent-line" style="padding-left: 10ch; text-indent: -10ch;"> { <span class="hljs-attr">balance</span>: db.<span class="hljs-property">sql</span><span class="hljs-string">`<span class="hljs-subst">${db.self}</span> + <span class="hljs-subst">${db.param(amount)}</span>`</span> },</span>
<span class="indent-line" style="padding-left: 10ch; text-indent: -10ch;"> { <span class="hljs-attr">id</span>: receivingAccountId }).<span class="hljs-title function_">run</span>(txnClient),</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> ]));</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"></span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">try</span> {</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-keyword">const</span> [[updatedAccountA], [updatedAccountB]] = <span class="hljs-keyword">await</span> <span class="hljs-title function_">transferMoney</span>(accountA.<span class="hljs-property">id</span>, accountB.<span class="hljs-property">id</span>, <span class="hljs-number">60</span>);</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;">} <span class="hljs-keyword">catch</span>(<span class="hljs-attr">err</span>: <span class="hljs-built_in">any</span>) {</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-variable language_">console</span>.<span class="hljs-title function_">log</span>(err.<span class="hljs-property">message</span>, <span class="hljs-string">'/'</span>, err.<span class="hljs-property">detail</span>);</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;">}</span></code></pre><div class="sqlstuff">
<pre class="sqltext"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> "bankAccounts" ("balance")</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-keyword">VALUES</span> ($<span class="hljs-number">1</span>), ($<span class="hljs-number">2</span>)</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;">RETURNING to_jsonb ("bankAccounts".<span class="hljs-operator">*</span>) <span class="hljs-keyword">AS</span> <span class="hljs-keyword">result</span></span></code></pre>
<pre class="sqlvalues"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-punctuation">[</span><span class="hljs-number">50</span><span class="hljs-punctuation">,</span> <span class="hljs-number">50</span><span class="hljs-punctuation">]</span></span></code></pre>
<pre class="sqlresult"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-punctuation">[</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-punctuation">{</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"id"</span><span class="hljs-punctuation">:</span> <span class="hljs-number">1</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"balance"</span><span class="hljs-punctuation">:</span> <span class="hljs-number">50</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-punctuation">{</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"id"</span><span class="hljs-punctuation">:</span> <span class="hljs-number">2</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"balance"</span><span class="hljs-punctuation">:</span> <span class="hljs-number">50</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-punctuation">}</span></span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-punctuation">]</span></span></code></pre>
<pre class="transactionid"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;">Transaction 0</span></code></pre>
<pre class="sqltext"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">START</span> TRANSACTION ISOLATION LEVEL SERIALIZABLE</span></code></pre>
<pre class="transactionid"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;">Transaction 0</span></code></pre>
<pre class="sqltext"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">UPDATE</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> "bankAccounts"</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">SET</span> ("balance") <span class="hljs-operator">=</span> <span class="hljs-type">ROW</span> ("balance" <span class="hljs-operator">-</span> $<span class="hljs-number">1</span>)</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">WHERE</span> ("id" <span class="hljs-operator">=</span> $<span class="hljs-number">2</span>)</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;">RETURNING to_jsonb ("bankAccounts".<span class="hljs-operator">*</span>) <span class="hljs-keyword">AS</span> <span class="hljs-keyword">result</span></span></code></pre>
<pre class="sqlvalues"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-punctuation">[</span><span class="hljs-number">60</span><span class="hljs-punctuation">,</span> <span class="hljs-number">1</span><span class="hljs-punctuation">]</span></span></code></pre>
<pre class="transactionid"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;">Transaction 0</span></code></pre>
<pre class="sqltext"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">UPDATE</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> "bankAccounts"</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">SET</span> ("balance") <span class="hljs-operator">=</span> <span class="hljs-type">ROW</span> ("balance" <span class="hljs-operator">+</span> $<span class="hljs-number">1</span>)</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">WHERE</span> ("id" <span class="hljs-operator">=</span> $<span class="hljs-number">2</span>)</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;">RETURNING to_jsonb ("bankAccounts".<span class="hljs-operator">*</span>) <span class="hljs-keyword">AS</span> <span class="hljs-keyword">result</span></span></code></pre>
<pre class="sqlvalues"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-punctuation">[</span><span class="hljs-number">60</span><span class="hljs-punctuation">,</span> <span class="hljs-number">2</span><span class="hljs-punctuation">]</span></span></code></pre>
<pre class="transactionid"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;">Transaction 0</span></code></pre>
<pre class="sqltext"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">ROLLBACK</span></span></code></pre>
<pre class="console"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;">new row for relation "bankAccounts" violates check constraint "bankAccounts_balance_check" / Failing row contains (1, -10).</span></code></pre>
</div>
<p>Finally, Zapatos provides a set of hierarchical isolation types so that, for example, if you type a <code>txnClient</code> argument to a function as <code>TxnClientForRepeatableRead</code>, you can call it with <code>IsolationLevel.Serializable</code> or <code>IsolationLevel.RepeatableRead</code> but not <code>IsolationLevel.ReadCommitted</code>.</p>
<p><a href="#transaction">Tell me more about the transaction functions »</a></p>
<h3 id="why-does-it-do-those-things">Why does it do those things?</h3>
<p>It is a truth universally acknowledged that <a href="https://en.wikipedia.org/wiki/Object-relational_impedance_mismatch">ORMs aren’t very good</a>. JavaScript and TypeScript ORMs are perhaps even worse than the average. One Zapatos user <a href="https://news.ycombinator.com/item?id=27556821">described a popular TypeScript ORM</a> as “full of broken magic under the hood”, which nicely captures what originally motivated me to write this library.</p>
<p>I like SQL, and Postgres especially. In my experience, abstractions that obscure the underlying SQL, or that prioritise ease of switching to another database tomorrow over effective use of <em>this</em> database <em>today</em>, are a source of misery.</p>
<p>I’ve also come to love strongly typed languages, and TypeScript in particular. VS Code’s type checking and autocomplete speed development, prevent bugs, and simplify refactoring. Especially when they <em>just happen</em>, they bring joy. But, traditionally, talking to the database is a place where they really don’t <em>just happen</em>.</p>
<p>Zapatos aims to fix that.</p>
<p>If it interests you, there’s a whole other <a href="https://github.com/jawj/mostly-ormless">repository about how Zapatos came about</a>.</p>
<h3 id="what-doesnt-it-do">What doesn’t it do?</h3>
<p>Zapatos doesn’t handle schema migrations. Other tools can help you with this: check out <a href="https://github.com/amacneil/dbmate">dbmate</a>, for instance.</p>
<p>It also doesn’t manage the connection pool for you, as some ORMs do — mainly because the <code>pg</code> module makes this so easy. For example, my <code>pgPool.ts</code> looks something like this:</p>
<pre class="language-typescript norun"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">import</span> pg <span class="hljs-keyword">from</span> <span class="hljs-string">'pg'</span>;</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"></span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">const</span> pool = <span class="hljs-keyword">new</span> pg.<span class="hljs-title class_">Pool</span>({ <span class="hljs-attr">connectionString</span>: process.<span class="hljs-property">env</span>.<span class="hljs-property">DATABASE_URL</span> });</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;">pool.<span class="hljs-title function_">on</span>(<span class="hljs-string">'error'</span>, <span class="hljs-function"><span class="hljs-params">err</span> =></span> <span class="hljs-variable language_">console</span>.<span class="hljs-title function_">error</span>(err)); <span class="hljs-comment">// don't let a pg restart kill your app</span></span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"></span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">export</span> <span class="hljs-keyword">default</span> pool;</span></code></pre>
<p>Finally, it won’t tell you how to structure your code: Zapatos doesn’t deal in the ‘model’ classes beloved of traditional ORMs, just (fully-typed) <a href="https://twitter.com/_ericelliott/status/831965087749533698?lang=en">POJOs</a>.</p>
<h2 id="how-do-i-get-it">How do I get it?</h2>
<h3 id="install-it">Install it</h3>
<p>First: check your <code>tsconfig.json</code>. You need <code>"strictNullChecks": true</code> or <code>"strict": true</code> (which implies <code>"strictNullChecks": true</code>). Without <code>strictNullChecks</code>, some things just won’t work — namely, the <code>lateral</code>, <code>extras</code>, <code>returning</code> and <code>columns</code> options to the shortcut functions.</p>
<p>Since TypeScript 4.4, it’s <a href="https://github.com/jawj/zapatos/issues/97">also a good idea to set <code>"exactOptionalPropertyTypes": true</code></a>.</p>
<p>Then install Zapatos with <code>npm</code>:</p>
<pre class="language-bash"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;">npm install --save zapatos</span></code></pre>
<h3 id="configure-it">Configure it</h3>
<p>Add a top-level file <code>zapatosconfig.json</code> to your project. Here’s an example:</p>
<pre class="language-json"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-punctuation">{</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-attr">"db"</span><span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"connectionString"</span><span class="hljs-punctuation">:</span> <span class="hljs-string">"postgresql://localhost/example_db"</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-attr">"outDir"</span><span class="hljs-punctuation">:</span> <span class="hljs-string">"./src"</span></span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-punctuation">}</span></span></code></pre>
<p>These are available top-level keys, all of which are optional:</p>
<ul>
<li>
<p><code>"db"</code> gives Postgres connection details. You can provide <a href="https://node-postgres.com/features/connecting/#Programmatic">anything that you’d pass</a> to <code>new pg.Pool(/* ... */)</code> here.</p>
</li>
<li>
<p><code>"outDir"</code> defines where your <code>zapatos</code> folder will be created, relative to the project root. If not specified, it defaults to the project root, i.e. <code>"."</code>.</p>
</li>
<li>
<p><code>"outExt"</code> defines the file extension for all generated type files. It defaults to <code>".d.ts"</code>, but <a href="https://github.com/jawj/zapatos/issues/53">for certain use cases you may wish to set it to <code>".ts"</code></a>.</p>
</li>
<li>
<p><code>"progressListener"</code> is a boolean that determines how chatty the tool is. If <code>true</code>, it enumerates its progress in generating the schema. It defaults to <code>false</code>. If you <a href="#programmatic-generation">generate your schema programmatically</a>, you can alternatively provide your own listener function.</p>
</li>
<li>
<p><code>"warningListener"</code> is a boolean that determines whether or not the tool logs a warning when a new user-defined type or domain is encountered and given its own type file in <code>zapatos/custom</code>. If <code>true</code>, which is the default, it does. Again, if you <a href="#programmatic-generation">generate your schema programmatically</a>, you can alternatively provide your own listener function.</p>
</li>
<li>
<p><code>"customTypesTransform"</code> is a string that determines how user-defined Postgres type names are mapped to TypeScript type names. Your options are <code>"my_type"</code>, <code>"PgMyType"</code> or <code>"PgMy_type"</code>, each representing how a Postgres type named <code>my_type</code> will be transformed. The default (for reasons of backward-compatibility rather than superiority) is <code>"PgMy_type"</code>. If you <a href="#programmatic-generation">generate your schema programmatically</a>, you can alternatively define your own transformation function.</p>
</li>
<li>
<p><code>"schemas"</code> is an object that lets you define the schemas, and the tables and views within schemas, for which types will be generated. Each key is a schema name, and each value is an object with keys <code>"include"</code> and <code>"exclude"</code>. Those keys can take the value <code>"*"</code> (for all tables in the schema) or an array of table names. The <code>"exclude"</code> list takes precedence over the <code>"include"</code> list. Thanks to generous sponsorship by <a href="https://www.seam.co/">Seam</a>, schemas are <a href="https://github.com/jawj/zapatos/issues/3#issuecomment-1126933350">properly supported</a> (via namespacing of types) as of version 6.</p>
</li>
</ul>
<p>If not specified, the default value for <code>"schemas"</code> includes all tables in the <code>public</code> schema, i.e.:</p>
<pre class="language-json"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-attr">"schemas"</span><span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-attr">"public"</span><span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"include"</span><span class="hljs-punctuation">:</span> <span class="hljs-string">"*"</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"exclude"</span><span class="hljs-punctuation">:</span> <span class="hljs-punctuation">[</span><span class="hljs-punctuation">]</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-punctuation">}</span></span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-punctuation">}</span></span></code></pre>
<p>If you use PostGIS, you’ll likely want to exclude its system tables:</p>
<pre class="language-json"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-attr">"schemas"</span><span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-attr">"public"</span><span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"include"</span><span class="hljs-punctuation">:</span> <span class="hljs-string">"*"</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"exclude"</span><span class="hljs-punctuation">:</span> <span class="hljs-punctuation">[</span></span>
<span class="indent-line" style="padding-left: 10ch; text-indent: -10ch;"> <span class="hljs-string">"geography_columns"</span><span class="hljs-punctuation">,</span> </span>
<span class="indent-line" style="padding-left: 10ch; text-indent: -10ch;"> <span class="hljs-string">"geometry_columns"</span><span class="hljs-punctuation">,</span> </span>
<span class="indent-line" style="padding-left: 10ch; text-indent: -10ch;"> <span class="hljs-string">"raster_columns"</span><span class="hljs-punctuation">,</span> </span>
<span class="indent-line" style="padding-left: 10ch; text-indent: -10ch;"> <span class="hljs-string">"raster_overviews"</span><span class="hljs-punctuation">,</span> </span>
<span class="indent-line" style="padding-left: 10ch; text-indent: -10ch;"> <span class="hljs-string">"spatial_ref_sys"</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-punctuation">]</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-punctuation">}</span></span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-punctuation">}</span></span></code></pre>
<ul>
<li>
<p><code>"unprefixedSchema"</code> determines which schema’s objects don’t need to be prefixed with their schema name (so that you can specify table <code>myTable</code> rather than <code>public.myTable</code>, for example). It should be set to the first schema listed in your Postgres <code>search_path</code> that actually exists in the database. Usually, that’s <code>"public"</code>, which is the option’s default value. <code>"unprefixedSchema"</code> can also be set to <code>null</code>, in which case all objects will be prefixed. That’s necessary if any schema shares its name with any table in the <code>public</code> schema.</p>
</li>
<li>
<p><code>"columnOptions"</code> is an object mapping options to named columns of named (or all) tables. Currently, you can use it to manually exclude column keys from the <code>Insertable</code> and <code>Updatable</code> types, using the options <code>"insert": "excluded"</code> and <code>"update": "excluded"</code>, or to force column keys to be optional in <code>Insertable</code> types, using the option <code>"insert": "optional"</code>. This supports use cases where columns are set using triggers.</p>
</li>
</ul>
<p>For example, say you have a <code>BEFORE INSERT</code> trigger on your <code>customers</code> table that can guess a default value for the <code>gender</code> column based on the value of the <code>title</code> column (though note: <a href="https://design-system.service.gov.uk/patterns/gender-or-sex/">don’t do that</a>). In this case, the <code>gender</code> column is actually optional on insert, even if it’s <code>NOT NULL</code> with no default, because the trigger provides a default value. You can tell Zapatos about that like so:</p>
<pre class="language-json"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-attr">"columnOptions"</span><span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-attr">"customers"</span><span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"gender"</span><span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span></span>
<span class="indent-line" style="padding-left: 10ch; text-indent: -10ch;"> <span class="hljs-attr">"insert"</span><span class="hljs-punctuation">:</span> <span class="hljs-string">"optional"</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-punctuation">}</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-punctuation">}</span></span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-punctuation">}</span></span></code></pre>
<p>Note that tables outside the <code>public</code> schema (or whichever schema you set for <code>"unprefixedSchema"</code>) should be schema-prefixed here, as usual — e.g. <code>"columnOptions": { "someSchema.someTable": /* ... */ } }</code>.</p>
<p>You can also use <code>"*"</code> as a wildcard to match all tables in all schemas. For example, perhaps you’ve set up the appropriate triggers to keep <code>updatedAt</code> columns up to date throughout your database. Then you might choose to exclude all your <code>updatedAt</code> columns from the <code>Insertable</code> and <code>Updatable</code> types for all tables as follows:</p>
<pre class="language-json"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-attr">"columnOptions"</span><span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-attr">"*"</span><span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"updatedAt"</span><span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span></span>
<span class="indent-line" style="padding-left: 10ch; text-indent: -10ch;"> <span class="hljs-attr">"insert"</span><span class="hljs-punctuation">:</span> <span class="hljs-string">"excluded"</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 10ch; text-indent: -10ch;"> <span class="hljs-attr">"update"</span><span class="hljs-punctuation">:</span> <span class="hljs-string">"excluded"</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-punctuation">}</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-punctuation">}</span></span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-punctuation">}</span></span></code></pre>
<p>Wildcard table options have lower precedence than named table options. The default values, should you want to restore them for named tables, are <code>"insert": "auto"</code> and <code>"update": "auto"</code>. Note that <code>"*"</code> is only supported as the whole key — you can’t use a <code>*</code> to match parts of schema or table names — and isn’t supported for column names.</p>
<ul>
<li>
<p><code>"schemaJSDoc"</code> is a boolean that turns JSDoc comments for each column in the generated schema on (the default) or off. JSDoc comments enable per-column VS Code pop-ups giving details of Postgres data type, default value and so on. They also make the schema file longer and less readable.</p>
</li>
<li>
<p><code>"customJSONParsingForLargeNumbers"</code> is a boolean that changes the types for <code>bigint</code>/<code>int8</code> and <code>numeric</code>/<code>decimal</code> values to reflect the use of <a href="#custom-json-parsing-for-bigint-and-numeric">custom JSON parsing to maintain precision</a>.</p>
</li>
</ul>
<p>In summary, the expected structure is defined like so:</p>
<pre class="language-typescript norun"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">export</span> <span class="hljs-keyword">interface</span> <span class="hljs-title class_">OptionalConfig</span> {</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-attr">db</span>: pg.<span class="hljs-property">ClientConfig</span>;</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-attr">outDir</span>: <span class="hljs-built_in">string</span>;</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-attr">outExt</span>: <span class="hljs-built_in">string</span>;</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-attr">schemas</span>: <span class="hljs-title class_">SchemaRules</span>;</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-attr">unprefixedSchema</span>: <span class="hljs-built_in">string</span> | <span class="hljs-literal">null</span>;</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-attr">progressListener</span>: <span class="hljs-built_in">boolean</span> | (<span class="hljs-function">(<span class="hljs-params">s: <span class="hljs-built_in">string</span></span>) =></span> <span class="hljs-built_in">void</span>);</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-attr">warningListener</span>: <span class="hljs-built_in">boolean</span> | (<span class="hljs-function">(<span class="hljs-params">s: <span class="hljs-built_in">string</span></span>) =></span> <span class="hljs-built_in">void</span>);</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-attr">customTypesTransform</span>: <span class="hljs-string">'PgMy_type'</span> | <span class="hljs-string">'my_type'</span> | <span class="hljs-string">'PgMyType'</span> | (<span class="hljs-function">(<span class="hljs-params">s: <span class="hljs-built_in">string</span></span>) =></span> <span class="hljs-built_in">string</span>);</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-attr">columnOptions</span>: <span class="hljs-title class_">ColumnOptions</span>;</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-attr">schemaJSDoc</span>: <span class="hljs-built_in">boolean</span>;</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-attr">customJSONParsingForLargeNumbers</span>: <span class="hljs-built_in">boolean</span>;</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;">}</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"></span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">interface</span> <span class="hljs-title class_">SchemaRules</span> {</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> [<span class="hljs-attr">schema</span>: <span class="hljs-built_in">string</span>]: {</span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">include</span>: <span class="hljs-string">'*'</span> | <span class="hljs-built_in">string</span>[];</span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">exclude</span>: <span class="hljs-string">'*'</span> | <span class="hljs-built_in">string</span>[];</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> };</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;">}</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"></span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">interface</span> <span class="hljs-title class_">ColumnOptions</span> {</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> [<span class="hljs-attr">k</span>: <span class="hljs-built_in">string</span>]: { <span class="hljs-comment">// table name or '*'</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> [<span class="hljs-attr">k</span>: <span class="hljs-built_in">string</span>]: { <span class="hljs-comment">// column name</span></span>
<span class="indent-line" style="padding-left: 10ch; text-indent: -10ch;"> insert?: <span class="hljs-string">'auto'</span> | <span class="hljs-string">'excluded'</span> | <span class="hljs-string">'optional'</span>;</span>
<span class="indent-line" style="padding-left: 10ch; text-indent: -10ch;"> update?: <span class="hljs-string">'auto'</span> | <span class="hljs-string">'excluded'</span>;</span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> };</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> };</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;">}</span></code></pre>
<h4 id="environment-variables">Environment variables</h4>
<p>All values in <code>zapatosconfig.json</code> can have environment variables (Node’s <code>process.env.SOMETHING</code>) interpolated via <a href="https://handlebarsjs.com/">handlebars</a>-style doubly-curly-brackets <code>{{variables}}</code>.</p>
<p>This is likely most useful for the database connection details. For example, on Heroku you might configure your database as:</p>
<pre class="language-json"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-attr">"db"</span><span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-attr">"connectionString"</span><span class="hljs-punctuation">:</span> <span class="hljs-string">"{{DATABASE_URL}}"</span></span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-punctuation">}</span></span></code></pre>
<h4 id="eslint--tslint">ESLint / tslint</h4>
<p>A general configuration suggestion: set up <a href="https://typescript-eslint.io/getting-started/">ESLint</a> with the rules <a href="https://github.com/typescript-eslint/typescript-eslint/blob/master/packages/eslint-plugin/docs/rules/await-thenable.md"><code>@typescript-eslint/await-thenable</code></a> and <a href="https://github.com/typescript-eslint/typescript-eslint/blob/master/packages/eslint-plugin/docs/rules/no-floating-promises.md"><code>@typescript-eslint/no-floating-promises</code></a> (or the now-deprecated <a href="https://palantir.github.io/tslint/">tslint</a> with <a href="https://palantir.github.io/tslint/rules/no-floating-promises/"><code>no-floating-promises</code></a> and <a href="https://palantir.github.io/tslint/rules/await-promise/"><code>await-promise</code></a>) to avoid various <code>Promise</code>-related pitfalls.</p>
<h3 id="generate-your-schema">Generate your schema</h3>
<p>Zapatos provides a command line tool. With everything configured, run it like so:</p>
<pre><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;">npx zapatos</span></code></pre>
<p>This generates the TypeScript schema for your database as <code>zapatos/schema.d.ts</code> inside your configured <code>outDir</code>. Any user-defined or domain types encountered get defined within <code>zapatos/custom</code> in their own <code>.d.ts</code> files, which you can subsequently customise.</p>
<p>These files must be included in your TypeScript compilation. That may happen for you automatically, but you may need to check the <code>"include"</code> or <code>"files"</code> keys in <code>tsconfig.json</code>. If you use <code>ts-node</code> or <code>node -r ts-node/register</code>, you may need to change it to <code>ts-node --files</code> or set <code>TS_NODE_FILES=true</code>.</p>
<h4 id="programmatic-generation">Programmatic generation</h4>
<p>As an alternative to the command line tool, it’s also possible to generate the schema programmatically by importing from <code>zapatos/generate</code>. For example:</p>
<pre class="language-typescript norun"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">import</span> * <span class="hljs-keyword">as</span> zg <span class="hljs-keyword">from</span> <span class="hljs-string">'zapatos/generate'</span>;</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"></span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">const</span> <span class="hljs-attr">zapCfg</span>: zg.<span class="hljs-property">Config</span> = { <span class="hljs-attr">db</span>: { <span class="hljs-attr">connectionString</span>: <span class="hljs-string">'postgres://localhost/mydb'</span> } };</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">await</span> zg.<span class="hljs-title function_">generate</span>(zapCfg);</span></code></pre>
<p>Call the <code>generate</code> method with an object structured exactly the same as <code>zapatosconfig.json</code>, documented above, with the following two exceptions:</p>
<ul>
<li>
<p>The <code>"progressListener"</code> and <code>"warningListener"</code> keys can each take <code>true</code> or <code>false</code> (as in the JSON case), or alternatively a function with the signature <code>(s: string) => void</code>, which you can use to implement your own logging.</p>
</li>
<li>
<p>The <code>"customTypesTransform"</code> key can take any of the string values allowed in the JSON case, or otherwise a function with the signature <code>(s: string) => string</code>, with which you can define your own type name transformation.</p>
</li>
</ul>
<h4 id="custom-types-and-domains">Custom types and domains</h4>
<p>As mentioned previously, any user-defined or domain types encountered during schema generation get defined in their own <code>.d.ts</code> files under <code>zapatos/custom</code>, which you can subsequently customise.</p>
<p>You can use domain types in order to specify custom types on the TypeScript side for certain Postgres columns. Say, for example, that you have a Postgres <code>jsonb</code> column on which you want to impose a particular structure. You could do the following:</p>
<pre class="language-sql"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">CREATE</span> DOMAIN "mySpecialJsonb" <span class="hljs-keyword">AS</span> "jsonb";</span></code></pre>
<p>Since you’ve done nothing else with this domain, it’s effectively just a simple alias to <code>jsonb</code> on the Postgres side. Now you can use that in place of <code>jsonb</code> in your table definition:</p>
<pre class="language-sql"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">ALTER</span> <span class="hljs-keyword">TABLE</span> "myTable" <span class="hljs-keyword">ALTER</span> <span class="hljs-keyword">COLUMN</span> "myExistingJsonbColumn" TYPE "mySpecialJsonb";</span></code></pre>
<p>When you next regenerate the TypeScript schema, you’ll find a custom type for <code>PgMySpecialJsonb</code> in <code>zapatos/custom/PgMySpecialJsonb.d.ts</code>, defined like so:</p>
<pre class="language-typescript norun"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">export</span> <span class="hljs-keyword">type</span> <span class="hljs-title class_">PgMySpecialJsonb</span> = db.<span class="hljs-property">JSONValue</span>;</span></code></pre>
<p>You can of course replace this definition with whatever TypeScript type or interface you choose. The file will not be overwritten on future schema generations. For example, perhaps this column holds blog article data:</p>
<pre class="language-typescript norun"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">export</span> <span class="hljs-keyword">interface</span> <span class="hljs-title class_">PgMySpecialJsonb</span> {</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-attr">title</span>: <span class="hljs-built_in">string</span>;</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-attr">text</span>: <span class="hljs-built_in">string</span>;</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-attr">tags</span>: <span class="hljs-built_in">string</span>[];</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-attr">version</span>: <span class="hljs-built_in">number</span>;</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;">};</span></code></pre>
<h3 id="import-it">Import it</h3>
<p>In your code, get the core library like so:</p>
<pre class="language-typescript norun"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">import</span> * <span class="hljs-keyword">as</span> db <span class="hljs-keyword">from</span> <span class="hljs-string">'zapatos/db'</span>;</span></code></pre>
<p>ESM wrappers are provided, so the import should work the same whether your project is set to use the CommonJS or ESM module specs.</p>
<p>To import your ordinary schema types (<code>myTable.Selectable</code>, <code>myOtherTable.Insertable</code>, etc.):</p>
<pre class="language-typescript norun"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">import</span> <span class="hljs-keyword">type</span> * <span class="hljs-keyword">as</span> s <span class="hljs-keyword">from</span> <span class="hljs-string">'zapatos/schema'</span>;</span></code></pre>
<p>Be sure to <code>import type</code> for this, not plain <code>import</code>, or you’ll upset <code>ts-jest</code> and maybe others.</p>
<p>To import any user-defined or domain types:</p>
<pre class="language-typescript norun"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">import</span> <span class="hljs-keyword">type</span> * <span class="hljs-keyword">as</span> c <span class="hljs-keyword">from</span> <span class="hljs-string">'zapatos/custom'</span>;</span></code></pre>
<p>The paths <code>zapatos/db</code> and <code>zapatos/generate</code> point to real folders in <code>node_modules</code>. Although they look like file paths, <code>zapatos/schema</code> and <code>zapatos/custom</code> are actually the names of <a href="https://www.typescriptlang.org/docs/handbook/modules.html#ambient-modules">ambient modules</a> declared in the generated files in your source tree: <code>zapatos/schema.d.ts</code> and <code>zapatos/custom/*.d.ts</code>.</p>
<h2 id="user-guide">User guide</h2>
<div style="height: 1px; clear: both;"></div><div class="src-link"><a href="https://github.com/jawj/zapatos/blob/master/src/db/core.ts#L210">Source code »</a></div>
<h3 id="sql-tagged-template-strings"><code>sql</code> tagged template strings</h3>
<p>Arbitrary queries are written using the tagged template function <code>sql</code>, which returns <a href="#sqlfragment"><code>SQLFragment</code></a> class instances.</p>
<p>The <code>sql</code> function is <a href="https://www.typescriptlang.org/docs/handbook/generics.html">generic</a>, having two type variables. For example:</p>
<pre class="language-typescript runnable"><code class="imports"><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">import</span> * <span class="hljs-keyword">as</span> db <span class="hljs-keyword">from</span> <span class="hljs-string">'zapatos/db'</span>;</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">import type</span> * <span class="hljs-keyword">as</span> s <span class="hljs-keyword">from</span> <span class="hljs-string">'zapatos/schema'</span>;</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">import</span> pool <span class="hljs-keyword">from</span> <span class="hljs-string">'./pgPool'</span>;</span></code><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">const</span> authors = <span class="hljs-keyword">await</span> db.<span class="hljs-property">sql</span><s.<span class="hljs-property">authors</span>.<span class="hljs-property">SQL</span>, s.<span class="hljs-property">authors</span>.<span class="hljs-property">Selectable</span>[]><span class="hljs-string">`</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"><span class="hljs-string"> SELECT * FROM <span class="hljs-subst">${<span class="hljs-string">"authors"</span>}</span>`</span>.<span class="hljs-title function_">run</span>(pool);</span></code></pre><div class="sqlstuff">
<pre class="sqltext"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">SELECT</span> <span class="hljs-operator">*</span></span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">FROM</span> "authors"</span></code></pre>
<pre class="sqlresult"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-punctuation">[</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-punctuation">{</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"id"</span><span class="hljs-punctuation">:</span> <span class="hljs-number">1000</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"name"</span><span class="hljs-punctuation">:</span> <span class="hljs-string">"Philip Pullman"</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"isLiving"</span><span class="hljs-punctuation">:</span> <span class="hljs-literal"><span class="hljs-keyword">true</span></span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-punctuation">{</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"id"</span><span class="hljs-punctuation">:</span> <span class="hljs-number">1001</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"name"</span><span class="hljs-punctuation">:</span> <span class="hljs-string">"Mark Haddon"</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"isLiving"</span><span class="hljs-punctuation">:</span> <span class="hljs-literal"><span class="hljs-keyword">true</span></span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-punctuation">{</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"id"</span><span class="hljs-punctuation">:</span> <span class="hljs-number">1002</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"name"</span><span class="hljs-punctuation">:</span> <span class="hljs-string">"Louis Sachar"</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"isLiving"</span><span class="hljs-punctuation">:</span> <span class="hljs-literal"><span class="hljs-keyword">true</span></span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-punctuation">{</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"id"</span><span class="hljs-punctuation">:</span> <span class="hljs-number">1</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"name"</span><span class="hljs-punctuation">:</span> <span class="hljs-string">"Gabriel Garcia Marquez"</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"isLiving"</span><span class="hljs-punctuation">:</span> <span class="hljs-literal"><span class="hljs-keyword">false</span></span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-punctuation">{</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"id"</span><span class="hljs-punctuation">:</span> <span class="hljs-number">2</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"name"</span><span class="hljs-punctuation">:</span> <span class="hljs-string">"Douglas Adams"</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"isLiving"</span><span class="hljs-punctuation">:</span> <span class="hljs-literal"><span class="hljs-keyword">false</span></span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-punctuation">{</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"id"</span><span class="hljs-punctuation">:</span> <span class="hljs-number">3</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"name"</span><span class="hljs-punctuation">:</span> <span class="hljs-string">"Jane Austen"</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"isLiving"</span><span class="hljs-punctuation">:</span> <span class="hljs-literal"><span class="hljs-keyword">false</span></span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-punctuation">}</span></span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-punctuation">]</span></span></code></pre>
</div>
<p>The first type variable, <code>Interpolations</code> (above: <code>s.authors.SQL</code>), defines allowable interpolation values. If not specified, it defaults to <code>db.SQL</code>: this is the union of all the per-table <code>SQL</code> types, and thus allows all table and column names present in the database as string interpolations (some of which would throw runtime errors in this case).</p>
<p>As another example, imagine we were joining the <code>authors</code> and <code>books</code> tables. Then we could specify <code>s.authors.SQL | s.books.SQL</code> for <code>Interpolations</code> here.</p>
<p>The second type variable, <code>RunResult</code> (above: <code>s.authors.Selectable[]</code>), describes what will be returned if we call <code>run()</code> on the query (after any transformations performed in <a href="#runresulttransform-qr-pgqueryresult--any"><code>runResultTransform()</code></a>), or if we embed it within the <a href="#extras"><code>extras</code></a> or <a href="#lateral-and-alias"><code>lateral</code></a> query options. Its default value if not specified is <code>any[]</code>.</p>
<p>Take another example of these type variables:</p>
<pre class="language-typescript runnable"><code class="imports"><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">import</span> * <span class="hljs-keyword">as</span> db <span class="hljs-keyword">from</span> <span class="hljs-string">'zapatos/db'</span>;</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">import</span> pool <span class="hljs-keyword">from</span> <span class="hljs-string">'./pgPool'</span>;</span></code><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">const</span> [{ random }] = <span class="hljs-keyword">await</span> db.<span class="hljs-property">sql</span><<span class="hljs-built_in">never</span>, [{ <span class="hljs-attr">random</span>: <span class="hljs-built_in">number</span> }]><span class="hljs-string">`</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"><span class="hljs-string"> SELECT random()`</span>.<span class="hljs-title function_">run</span>(pool);</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"></span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-variable language_">console</span>.<span class="hljs-title function_">log</span>(random);</span></code></pre><div class="sqlstuff">
<pre class="sqltext"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">SELECT</span> random()</span></code></pre>
<pre class="sqlresult"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-punctuation">[</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-punctuation">{</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"random"</span><span class="hljs-punctuation">:</span> <span class="hljs-number">0.4620805530200762</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-punctuation">}</span></span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-punctuation">]</span></span></code></pre>
<pre class="console"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;">0.4620805530200762</span></code></pre>
</div>
<p><code>Interpolations</code> is <code>never</code> because nothing needs to be interpolated in this query, and the <code>RunResult</code> type says that the query will return one row comprising one numeric column, named <code>random</code>. The <code>random</code> TypeScript variable we initialize will of course be typed as a <code>number</code>.</p>
<p>If you’re happy to have your types tied down a little less tightly, it also works to wholly omit the type variables in this particular query, falling back on their defaults:</p>
<pre class="language-typescript noresult runnable"><code class="imports"><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">import</span> * <span class="hljs-keyword">as</span> db <span class="hljs-keyword">from</span> <span class="hljs-string">'zapatos/db'</span>;</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">import</span> pool <span class="hljs-keyword">from</span> <span class="hljs-string">'./pgPool'</span>;</span></code><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">const</span> [{ random }] = <span class="hljs-keyword">await</span> db.<span class="hljs-property">sql</span><span class="hljs-string">`SELECT random()`</span>.<span class="hljs-title function_">run</span>(pool);</span></code></pre>
<p>In this case, the <code>random</code> variable is of course still a <code>number</code>, but it is typed as <code>any</code>.</p>
<h3 id="sql-template-interpolation-types"><code>sql</code> template interpolation types</h3>
<h4 id="strings">Strings</h4>
<p>The strings that can be directly interpolated into a <code>sql</code> template string are defined by its <code>Interpolations</code> type variable, <a href="#sql-tagged-template-strings">as noted above</a>. Typically, this will limit them to the names of tables and columns.</p>
<p>Interpolated strings are passed through to the raw SQL query double-quoted, to preserve capitalisation and neutralise SQL keywords. For example, <code>myTable</code> becomes <code>"myTable"</code>, and <code>mySchema.myTable</code> becomes <code>"mySchema"."myTable"</code>.</p>
<p>It’s highly preferable to use interpolated string literals for table and column names rather than just writing those values in the query itself, in order to benefit from auto-completion and (ongoing) type-checking.</p>
<p>So, for example, do write:</p>
<pre class="language-typescript noresult runnable"><code class="imports"><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">import</span> * <span class="hljs-keyword">as</span> db <span class="hljs-keyword">from</span> <span class="hljs-string">'zapatos/db'</span>;</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">import</span> pool <span class="hljs-keyword">from</span> <span class="hljs-string">'./pgPool'</span>;</span></code><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">const</span> title = <span class="hljs-keyword">await</span> db.<span class="hljs-property">sql</span><span class="hljs-string">`</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"><span class="hljs-string"> SELECT <span class="hljs-subst">${<span class="hljs-string">"title"</span>}</span> FROM <span class="hljs-subst">${<span class="hljs-string">"books"</span>}</span> LIMIT 1`</span>.<span class="hljs-title function_">run</span>(pool);</span></code></pre>
<p>But <strong>don’t</strong> write</p>
<pre class="language-typescript noresult runnable"><code class="imports"><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">import</span> * <span class="hljs-keyword">as</span> db <span class="hljs-keyword">from</span> <span class="hljs-string">'zapatos/db'</span>;</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">import</span> pool <span class="hljs-keyword">from</span> <span class="hljs-string">'./pgPool'</span>;</span></code><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">const</span> title = <span class="hljs-keyword">await</span> db.<span class="hljs-property">sql</span><span class="hljs-string">`</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"><span class="hljs-string"> SELECT "title" FROM "books" LIMIT 1`</span>.<span class="hljs-title function_">run</span>(pool); <span class="hljs-comment">// no, don't do this</span></span></code></pre>
<p>— even if the two produce the same result right now.</p>
<p>More critically, <strong>never never never</strong> explicitly override type-checking so as to write:</p>
<pre class="language-typescript runnable"><code class="imports"><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">import</span> * <span class="hljs-keyword">as</span> db <span class="hljs-keyword">from</span> <span class="hljs-string">'zapatos/db'</span>;</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">import</span> pool <span class="hljs-keyword">from</span> <span class="hljs-string">'./pgPool'</span>;</span></code><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">const</span> </span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> nameSubmittedByUser = <span class="hljs-string">'books"; DROP TABLE "authors"; --'</span>,</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> title = <span class="hljs-keyword">await</span> db.<span class="hljs-property">sql</span><<span class="hljs-built_in">any</span>><span class="hljs-string">`</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"><span class="hljs-string"> SELECT * FROM <span class="hljs-subst">${nameSubmittedByUser}</span> LIMIT 1`</span>.<span class="hljs-title function_">run</span>(pool); <span class="hljs-comment">// NEVER do this!</span></span></code></pre><div class="sqlstuff">
<pre class="sqltext"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">SELECT</span> <span class="hljs-operator">*</span></span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">FROM</span> "books";</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"></span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">DROP</span> <span class="hljs-keyword">TABLE</span> "authors";</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"></span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-comment">--" LIMIT 1</span></span></code></pre>
<pre class="console"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;">error: cannot drop table authors because other objects depend on it</span></code></pre>
</div>
<p>If you override type-checking to pass untrusted data to Zapatos in unexpected places, such as the above use of <code>any</code>, you can expect successful SQL injection attacks.</p>
<p>(It <em>is</em> safe to pass untrusted data as values in <code>Whereable</code>, <code>Insertable</code>, and <code>Updatable</code> objects, manually by using <a href="#paramvalue-any-cast-boolean--string-parameter"><code>param</code></a>, and in certain other places. If you’re in any doubt, double-check that the generated SQL is using <code>$1</code>, <code>$2</code>, … parameters for all potentially untrusted data).</p>
<h4 id="cols-and-vals"><code>cols()</code> and <code>vals()</code></h4>
<p>The <code>cols</code> and <code>vals</code> wrapper functions (which return <code>ColumnNames</code> and <code>ColumnValues</code> class instances respectively) are intended to help with certain <code>INSERT</code> and <code>SELECT</code> queries.</p>
<p>In the <code>INSERT</code> context, pass them each the same <code>Insertable</code> object: <code>cols</code> is compiled to a comma-separated list of the object’s keys, which are the column names, and <code>vals</code> is compiled to a comma-separated list of SQL placeholders (<code>$1</code>, <code>$2</code>, …) associated with the corresponding values, in matching order. To return to (approximately) an earlier example:</p>
<pre class="language-typescript runnable"><code class="imports"><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">import</span> * <span class="hljs-keyword">as</span> db <span class="hljs-keyword">from</span> <span class="hljs-string">'zapatos/db'</span>;</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">import type</span> * <span class="hljs-keyword">as</span> s <span class="hljs-keyword">from</span> <span class="hljs-string">'zapatos/schema'</span>;</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">import</span> pool <span class="hljs-keyword">from</span> <span class="hljs-string">'./pgPool'</span>;</span></code><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">const</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-attr">author</span>: s.<span class="hljs-property">authors</span>.<span class="hljs-property">Insertable</span> = {</span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">name</span>: <span class="hljs-string">'Joseph Conrad'</span>,</span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">isLiving</span>: <span class="hljs-literal">false</span>,</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> },</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> [insertedAuthor] = <span class="hljs-keyword">await</span> db.<span class="hljs-property">sql</span><s.<span class="hljs-property">authors</span>.<span class="hljs-property">SQL</span>, s.<span class="hljs-property">authors</span>.<span class="hljs-property">Selectable</span>[]><span class="hljs-string">`</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"><span class="hljs-string"> INSERT INTO <span class="hljs-subst">${<span class="hljs-string">"authors"</span>}</span> (<span class="hljs-subst">${db.cols(author)}</span>)</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"><span class="hljs-string"> VALUES (<span class="hljs-subst">${db.vals(author)}</span>) RETURNING *`</span>.<span class="hljs-title function_">run</span>(pool);</span></code></pre><div class="sqlstuff">
<pre class="sqltext"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> "authors" ("isLiving", "name")</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-keyword">VALUES</span> ($<span class="hljs-number">1</span>, $<span class="hljs-number">2</span>)</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;">RETURNING <span class="hljs-operator">*</span></span></code></pre>
<pre class="sqlvalues"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-punctuation">[</span><span class="hljs-literal"><span class="hljs-keyword">false</span></span><span class="hljs-punctuation">,</span> <span class="hljs-string">"Joseph Conrad"</span><span class="hljs-punctuation">]</span></span></code></pre>
<pre class="sqlresult"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-punctuation">[</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-punctuation">{</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"id"</span><span class="hljs-punctuation">:</span> <span class="hljs-number">4</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"name"</span><span class="hljs-punctuation">:</span> <span class="hljs-string">"Joseph Conrad"</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"isLiving"</span><span class="hljs-punctuation">:</span> <span class="hljs-literal"><span class="hljs-keyword">false</span></span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-punctuation">}</span></span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-punctuation">]</span></span></code></pre>
</div>
<p>The <code>cols</code> and <code>vals</code> wrappers can also each take an array instead of an object.</p>
<p>For the <code>cols</code> function, this can help us select only a subset of columns, in conjunction with the <code>OnlyCols</code> type. Pass an array of column names to <code>cols</code> to have them compiled appropriately, as seen in this example:</p>
<pre class="language-typescript runnable"><code class="imports"><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">import</span> * <span class="hljs-keyword">as</span> db <span class="hljs-keyword">from</span> <span class="hljs-string">'zapatos/db'</span>;</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">import type</span> * <span class="hljs-keyword">as</span> s <span class="hljs-keyword">from</span> <span class="hljs-string">'zapatos/schema'</span>;</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">import</span> pool <span class="hljs-keyword">from</span> <span class="hljs-string">'./pgPool'</span>;</span></code><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-comment">// the <const> prevents generalization to string[]</span></span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">const</span> bookCols = <<span class="hljs-keyword">const</span>>[<span class="hljs-string">'id'</span>, <span class="hljs-string">'title'</span>];</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">type</span> <span class="hljs-title class_">BookDatum</span> = s.<span class="hljs-property">books</span>.<span class="hljs-property">OnlyCols</span><<span class="hljs-keyword">typeof</span> bookCols>;</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"></span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">const</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> bookData = <span class="hljs-keyword">await</span> db.<span class="hljs-property">sql</span><s.<span class="hljs-property">books</span>.<span class="hljs-property">SQL</span>, <span class="hljs-title class_">BookDatum</span>[]><span class="hljs-string">`</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"><span class="hljs-string"> SELECT <span class="hljs-subst">${db.cols(bookCols)}</span> FROM <span class="hljs-subst">${<span class="hljs-string">"books"</span>}</span>`</span>.<span class="hljs-title function_">run</span>(pool);</span></code></pre><div class="sqlstuff">
<pre class="sqltext"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">SELECT</span> "id", "title"</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">FROM</span> "books"</span></code></pre>
<pre class="sqlresult"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-punctuation">[</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-punctuation">{</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"id"</span><span class="hljs-punctuation">:</span> <span class="hljs-number">1000</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"title"</span><span class="hljs-punctuation">:</span> <span class="hljs-string">"Northern Lights"</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-punctuation">{</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"id"</span><span class="hljs-punctuation">:</span> <span class="hljs-number">1001</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"title"</span><span class="hljs-punctuation">:</span> <span class="hljs-string">"The Subtle Knife"</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-punctuation">{</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"id"</span><span class="hljs-punctuation">:</span> <span class="hljs-number">1002</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"title"</span><span class="hljs-punctuation">:</span> <span class="hljs-string">"The Amber Spyglass"</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-punctuation">{</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"id"</span><span class="hljs-punctuation">:</span> <span class="hljs-number">1003</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"title"</span><span class="hljs-punctuation">:</span> <span class="hljs-string">"The Curious Incident of the Dog in the Night-Time"</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-punctuation">{</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"id"</span><span class="hljs-punctuation">:</span> <span class="hljs-number">1004</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"title"</span><span class="hljs-punctuation">:</span> <span class="hljs-string">"Holes"</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-punctuation">}</span></span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-punctuation">]</span></span></code></pre>
</div>
<p>For the <code>vals</code> function, this can help with <code>IN (...)</code> queries, such as the following:</p>
<pre class="language-typescript runnable"><code class="imports"><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">import</span> * <span class="hljs-keyword">as</span> db <span class="hljs-keyword">from</span> <span class="hljs-string">'zapatos/db'</span>;</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">import type</span> * <span class="hljs-keyword">as</span> s <span class="hljs-keyword">from</span> <span class="hljs-string">'zapatos/schema'</span>;</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">import</span> pool <span class="hljs-keyword">from</span> <span class="hljs-string">'./pgPool'</span>;</span></code><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">const</span> </span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> authorIds = [<span class="hljs-number">1</span>, <span class="hljs-number">2</span>, <span class="hljs-number">123</span>],</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> authors = <span class="hljs-keyword">await</span> db.<span class="hljs-property">sql</span><s.<span class="hljs-property">authors</span>.<span class="hljs-property">SQL</span>, s.<span class="hljs-property">authors</span>.<span class="hljs-property">Selectable</span>[]><span class="hljs-string">` </span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"><span class="hljs-string"> SELECT * FROM <span class="hljs-subst">${<span class="hljs-string">"authors"</span>}</span> WHERE <span class="hljs-subst">${<span class="hljs-string">"id"</span>}</span> IN (<span class="hljs-subst">${db.vals(authorIds)}</span>)`</span>.<span class="hljs-title function_">run</span>(pool);</span></code></pre><div class="sqlstuff">
<pre class="sqltext"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">SELECT</span> <span class="hljs-operator">*</span></span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">FROM</span> "authors"</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">WHERE</span> "id" <span class="hljs-keyword">IN</span> ($<span class="hljs-number">1</span>, $<span class="hljs-number">2</span>, $<span class="hljs-number">3</span>)</span></code></pre>
<pre class="sqlvalues"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-punctuation">[</span><span class="hljs-number">1</span><span class="hljs-punctuation">,</span> <span class="hljs-number">2</span><span class="hljs-punctuation">,</span> <span class="hljs-number">123</span><span class="hljs-punctuation">]</span></span></code></pre>
<pre class="sqlresult"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-punctuation">[</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-punctuation">{</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"id"</span><span class="hljs-punctuation">:</span> <span class="hljs-number">1</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"name"</span><span class="hljs-punctuation">:</span> <span class="hljs-string">"Gabriel Garcia Marquez"</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"isLiving"</span><span class="hljs-punctuation">:</span> <span class="hljs-literal"><span class="hljs-keyword">false</span></span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-punctuation">{</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"id"</span><span class="hljs-punctuation">:</span> <span class="hljs-number">2</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"name"</span><span class="hljs-punctuation">:</span> <span class="hljs-string">"Douglas Adams"</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"isLiving"</span><span class="hljs-punctuation">:</span> <span class="hljs-literal"><span class="hljs-keyword">false</span></span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-punctuation">}</span></span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-punctuation">]</span></span></code></pre>
</div>
<h4 id="whereable"><code>Whereable</code></h4>
<p>Any plain JavaScript object interpolated into a <code>sql</code> template string is type-checked as a <code>Whereable</code>, and compiled into one or more conditions joined with <code>AND</code> (but, for flexibility, no <code>WHERE</code>). The object’s keys represent column names, and the corresponding values are automatically compiled as (injection-safe) <a href="#paramvalue-any-cast-boolean--string-parameter"><code>Parameter</code></a> instances.</p>
<p>For example:</p>
<pre class="language-typescript runnable"><code class="imports"><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">import</span> * <span class="hljs-keyword">as</span> db <span class="hljs-keyword">from</span> <span class="hljs-string">'zapatos/db'</span>;</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">import type</span> * <span class="hljs-keyword">as</span> s <span class="hljs-keyword">from</span> <span class="hljs-string">'zapatos/schema'</span>;</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">import</span> pool <span class="hljs-keyword">from</span> <span class="hljs-string">'./pgPool'</span>;</span></code><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">const</span> </span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> title = <span class="hljs-string">'Northern Lights'</span>,</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> books = <span class="hljs-keyword">await</span> db.<span class="hljs-property">sql</span><s.<span class="hljs-property">books</span>.<span class="hljs-property">SQL</span>, s.<span class="hljs-property">books</span>.<span class="hljs-property">Selectable</span>[]><span class="hljs-string">`</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"><span class="hljs-string"> SELECT * FROM <span class="hljs-subst">${<span class="hljs-string">"books"</span>}</span> WHERE <span class="hljs-subst">${{ title }}</span>`</span>.<span class="hljs-title function_">run</span>(pool);</span></code></pre><div class="sqlstuff">
<pre class="sqltext"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">SELECT</span> <span class="hljs-operator">*</span></span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">FROM</span> "books"</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">WHERE</span> ("title" <span class="hljs-operator">=</span> $<span class="hljs-number">1</span>)</span></code></pre>
<pre class="sqlvalues"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-punctuation">[</span><span class="hljs-string">"Northern Lights"</span><span class="hljs-punctuation">]</span></span></code></pre>
<pre class="sqlresult"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-punctuation">[</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-punctuation">{</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"id"</span><span class="hljs-punctuation">:</span> <span class="hljs-number">1000</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"authorId"</span><span class="hljs-punctuation">:</span> <span class="hljs-number">1000</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"title"</span><span class="hljs-punctuation">:</span> <span class="hljs-string">"Northern Lights"</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"createdAt"</span><span class="hljs-punctuation">:</span> <span class="hljs-string">"2024-01-08T11:50:21.508Z"</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-punctuation">}</span></span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-punctuation">]</span></span></code></pre>
</div>
<p>(If you need to specify a <code>CAST</code> of a parameter to a specific SQL type, you can also manually wrap <code>Whereable</code> values using <a href="#paramvalue-any-cast-boolean--string-parameter"><code>param</code></a> — this is useful primarily when using <a href="#shortcut-functions-and-lateral-joins">the shortcut functions</a>).</p>
<p>A <code>Whereable</code>’s values can alternatively be <code>SQLFragments</code>, and this makes them extremely flexible. In a <code>SQLFragment</code> inside a <code>Whereable</code>, the special symbol <code>self</code> can be used to refer to the column name. This arrangement enables us to use any operator or function we want — not just <code>=</code>.</p>
<p>For example:</p>
<pre class="language-typescript runnable"><code class="imports"><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">import</span> * <span class="hljs-keyword">as</span> db <span class="hljs-keyword">from</span> <span class="hljs-string">'zapatos/db'</span>;</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">import type</span> * <span class="hljs-keyword">as</span> s <span class="hljs-keyword">from</span> <span class="hljs-string">'zapatos/schema'</span>;</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">import</span> pool <span class="hljs-keyword">from</span> <span class="hljs-string">'./pgPool'</span>;</span></code><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">const</span> </span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> titleLike = <span class="hljs-string">'Northern%'</span>,</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> books = <span class="hljs-keyword">await</span> db.<span class="hljs-property">sql</span><s.<span class="hljs-property">books</span>.<span class="hljs-property">SQL</span>, s.<span class="hljs-property">books</span>.<span class="hljs-property">Selectable</span>[]><span class="hljs-string">`</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"><span class="hljs-string"> SELECT * FROM <span class="hljs-subst">${<span class="hljs-string">"books"</span>}</span> WHERE <span class="hljs-subst">${{ </span></span></span>
<span class="indent-line" style="padding-left: 10ch; text-indent: -10ch;"><span class="hljs-string"><span class="hljs-subst"> title: db.sql<span class="hljs-string">`<span class="hljs-subst">${db.self}</span> LIKE <span class="hljs-subst">${db.param(titleLike)}</span>`</span>,</span></span></span>
<span class="indent-line" style="padding-left: 10ch; text-indent: -10ch;"><span class="hljs-string"><span class="hljs-subst"> createdAt: db.sql<span class="hljs-string">`<span class="hljs-subst">${db.self}</span> > now() - INTERVAL '7 days'`</span>,</span></span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"><span class="hljs-string"><span class="hljs-subst"> }}</span>`</span>.<span class="hljs-title function_">run</span>(pool);</span></code></pre><div class="sqlstuff">
<pre class="sqltext"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">SELECT</span> <span class="hljs-operator">*</span></span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">FROM</span> "books"</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">WHERE</span> (("createdAt" <span class="hljs-operator">></span> now() <span class="hljs-operator">-</span> <span class="hljs-type">INTERVAL</span> <span class="hljs-string">'7 days'</span>)</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-keyword">AND</span> ("title" <span class="hljs-keyword">LIKE</span> $<span class="hljs-number">1</span>))</span></code></pre>
<pre class="sqlvalues"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-punctuation">[</span><span class="hljs-string">"Northern%"</span><span class="hljs-punctuation">]</span></span></code></pre>
<pre class="sqlresult"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-punctuation">[</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-punctuation">{</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"id"</span><span class="hljs-punctuation">:</span> <span class="hljs-number">1000</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"authorId"</span><span class="hljs-punctuation">:</span> <span class="hljs-number">1000</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"title"</span><span class="hljs-punctuation">:</span> <span class="hljs-string">"Northern Lights"</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"createdAt"</span><span class="hljs-punctuation">:</span> <span class="hljs-string">"2024-01-08T11:50:21.508Z"</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-punctuation">}</span></span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-punctuation">]</span></span></code></pre>
</div>
<p>Finally, there’s a set of helper functions you can use to create appropriate <code>SQLFragment</code>s like these for use as <code>Whereable</code> values. The advantages are: (1) there’s slighly less to type, and (2) you get type-checking on their arguments (so you’re not tempted to compare incomparable things).</p>
<p>They’re exported under <code>conditions</code> on the main object, and the full set can be seen in <a href="https://github.com/jawj/zapatos/blob/master/src/db/conditions.ts">conditions.ts</a>. Using some of these, we could rewrite the above example as:</p>
<pre class="language-typescript runnable"><code class="imports"><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">import</span> * <span class="hljs-keyword">as</span> db <span class="hljs-keyword">from</span> <span class="hljs-string">'zapatos/db'</span>;</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">import</span> { conditions <span class="hljs-keyword">as</span> dc } <span class="hljs-keyword">from</span> <span class="hljs-string">'zapatos/db'</span>;</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">import type</span> * <span class="hljs-keyword">as</span> s <span class="hljs-keyword">from</span> <span class="hljs-string">'zapatos/schema'</span>;</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">import</span> pool <span class="hljs-keyword">from</span> <span class="hljs-string">'./pgPool'</span>;</span></code><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">const</span> </span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> titleLike = <span class="hljs-string">'Northern%'</span>,</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> books = <span class="hljs-keyword">await</span> db.<span class="hljs-property">sql</span><s.<span class="hljs-property">books</span>.<span class="hljs-property">SQL</span>, s.<span class="hljs-property">books</span>.<span class="hljs-property">Selectable</span>[]><span class="hljs-string">`</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"><span class="hljs-string"> SELECT * FROM <span class="hljs-subst">${<span class="hljs-string">"books"</span>}</span> WHERE <span class="hljs-subst">${{ </span></span></span>
<span class="indent-line" style="padding-left: 10ch; text-indent: -10ch;"><span class="hljs-string"><span class="hljs-subst"> title: dc.like(titleLike),</span></span></span>
<span class="indent-line" style="padding-left: 10ch; text-indent: -10ch;"><span class="hljs-string"><span class="hljs-subst"> createdAt: dc.after(dc.fromNow(-<span class="hljs-number">7</span>, <span class="hljs-string">'days'</span>)),</span></span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"><span class="hljs-string"><span class="hljs-subst"> }}</span>`</span>.<span class="hljs-title function_">run</span>(pool);</span></code></pre><div class="sqlstuff">
<pre class="sqltext"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">SELECT</span> <span class="hljs-operator">*</span></span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">FROM</span> "books"</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">WHERE</span> (("createdAt" <span class="hljs-operator">></span> now() <span class="hljs-operator">+</span> $<span class="hljs-number">1</span>)</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-keyword">AND</span> ("title" <span class="hljs-keyword">LIKE</span> $<span class="hljs-number">2</span>))</span></code></pre>
<pre class="sqlvalues"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-punctuation">[</span><span class="hljs-string">"-7 days"</span><span class="hljs-punctuation">,</span> <span class="hljs-string">"Northern%"</span><span class="hljs-punctuation">]</span></span></code></pre>
<pre class="sqlresult"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-punctuation">[</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-punctuation">{</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"id"</span><span class="hljs-punctuation">:</span> <span class="hljs-number">1000</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"authorId"</span><span class="hljs-punctuation">:</span> <span class="hljs-number">1000</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"title"</span><span class="hljs-punctuation">:</span> <span class="hljs-string">"Northern Lights"</span><span class="hljs-punctuation">,</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-attr">"createdAt"</span><span class="hljs-punctuation">:</span> <span class="hljs-string">"2024-01-08T11:50:21.508Z"</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-punctuation">}</span></span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-punctuation">]</span></span></code></pre>
</div>
<h4 id="self"><code>self</code></h4>
<p>The use of the <code>self</code> symbol is explained in <a href="#whereable">the section on <code>Whereable</code>s</a>.</p>
<h4 id="paramvalue-any-cast-boolean--string-parameter"><code>param(value: any, cast?: boolean | string): Parameter</code></h4>
<p>In general, Zapatos’ type-checking won’t let us <a href="https://xkcd.com/327/">pass user-supplied data unsafely into a query</a> by accident. The <code>param</code> wrapper function exists to enable the safe passing of user-supplied data into a query using numbered query parameters (<code>$1</code>, <code>$2</code>, …).</p>
<p>For example:</p>
<pre class="language-typescript runnable"><code class="imports"><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">import</span> * <span class="hljs-keyword">as</span> db <span class="hljs-keyword">from</span> <span class="hljs-string">'zapatos/db'</span>;</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">import type</span> * <span class="hljs-keyword">as</span> s <span class="hljs-keyword">from</span> <span class="hljs-string">'zapatos/schema'</span>;</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">import</span> pool <span class="hljs-keyword">from</span> <span class="hljs-string">'./pgPool'</span>;</span></code><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">const</span> </span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> title = <span class="hljs-string">'Pride and Prejudice'</span>,</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> books = <span class="hljs-keyword">await</span> db.<span class="hljs-property">sql</span><s.<span class="hljs-property">books</span>.<span class="hljs-property">SQL</span>, s.<span class="hljs-property">books</span>.<span class="hljs-property">Selectable</span>[]><span class="hljs-string">`</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"><span class="hljs-string"> SELECT * FROM <span class="hljs-subst">${<span class="hljs-string">"books"</span>}</span> WHERE <span class="hljs-subst">${<span class="hljs-string">"title"</span>}</span> = <span class="hljs-subst">${db.param(title)}</span>`</span>.<span class="hljs-title function_">run</span>(pool);</span></code></pre><div class="sqlstuff">
<pre class="sqltext"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">SELECT</span> <span class="hljs-operator">*</span></span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">FROM</span> "books"</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">WHERE</span> "title" <span class="hljs-operator">=</span> $<span class="hljs-number">1</span></span></code></pre>
<pre class="sqlvalues"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-punctuation">[</span><span class="hljs-string">"Pride and Prejudice"</span><span class="hljs-punctuation">]</span></span></code></pre>
</div>
<p>This same mechanism is applied automatically when we use <a href="#whereable">a <code>Whereable</code> object</a> (and in this example, using a <code>Whereable</code> would be more readable and more concise). It’s also applied when we use <a href="#cols-and-vals">the <code>vals</code> function</a> to create a <code>ColumnValues</code> wrapper object.</p>
<p>The optional second argument to <code>param</code>, <code>cast</code>, allows us to specify a SQL <code>CAST</code> type for the wrapped value. If <code>cast</code> is a string, it’s interpreted as a Postgres type, so <code>param(someValue, 'text')</code> comes out in the compiled query as as <code>CAST($1 TO "text")</code>. If <code>cast</code> is <code>true</code>, the parameter value will be JSON stringified and cast to <code>json</code>, and if <code>cast</code> is <code>false</code>, the parameter will <strong>not</strong> be JSON stringified or cast to <code>json</code> (regardless, in both cases, of <a href="#casting-parameters-to-json">the <code>castArrayParamsToJson</code> and <code>castObjectParamsToJson</code> configuration options</a>).</p>
<h4 id="default"><code>Default</code></h4>
<p>The <code>Default</code> symbol simply compiles to the SQL <code>DEFAULT</code> keyword. This may be useful in <code>INSERT</code> and <code>UPDATE</code> queries where no value is supplied for one or more of the affected columns.</p>
<h4 id="sql-template-strings"><code>sql</code> template strings</h4>
<p><code>sql</code> template strings (resulting in <code>SQLFragment</code>s) can be interpolated within other <code>sql</code> template strings (<code>SQLFragment</code>s). This provides flexibility in building queries programmatically.</p>
<p>For example, the <a href="#select-selectone-and-selectexactlyone"><code>select</code> shortcut</a> makes extensive use of nested <code>sql</code> templates to build its queries:</p>
<pre class="language-typescript norun"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">const</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> rowsQuery = sql<<span class="hljs-variable constant_">SQL</span>, <span class="hljs-built_in">any</span>><span class="hljs-string">`</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"><span class="hljs-string"> SELECT <span class="hljs-subst">${allColsSQL}</span> AS result </span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"><span class="hljs-string"> FROM <span class="hljs-subst">${table}</span><span class="hljs-subst">${tableAliasSQL}</span></span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"><span class="hljs-string"> <span class="hljs-subst">${lateralSQL}</span><span class="hljs-subst">${whereSQL}</span><span class="hljs-subst">${orderSQL}</span><span class="hljs-subst">${limitSQL}</span><span class="hljs-subst">${offsetSQL}</span>`</span>,</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-comment">// we need the aggregate function, if one's needed, to sit in an outer </span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-comment">// query, to keep ORDER and LIMIT working normally in the main query</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> query = mode !== <span class="hljs-title class_">SelectResultMode</span>.<span class="hljs-property">Many</span> ? rowsQuery :</span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> sql<<span class="hljs-variable constant_">SQL</span>, <span class="hljs-built_in">any</span>><span class="hljs-string">`</span></span>
<span class="indent-line" style="padding-left: 10ch; text-indent: -10ch;"><span class="hljs-string"> SELECT coalesce(jsonb_agg(result), '[]') AS result </span></span>
<span class="indent-line" style="padding-left: 10ch; text-indent: -10ch;"><span class="hljs-string"> FROM (<span class="hljs-subst">${rowsQuery}</span>) AS <span class="hljs-subst">${raw(<span class="hljs-string">`"sq_<span class="hljs-subst">${aliasedTable}</span>"`</span>)}</span>`</span>;</span></code></pre>
<h4 id="arrays">Arrays</h4>
<p>Items in an interpolated array are treated just the same as if they had been interpolated directly. This, again, can be useful for building queries programmatically.</p>
<p>To take the <a href="#select-selectone-and-selectexactlyone"><code>select</code> shortcut</a> as our example again, an interpolated array is used to generate <code>LATERAL JOIN</code> query elements from the <code>lateral</code> option, like so:</p>
<pre class="language-typescript norun"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">const</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> lateralOpt = allOptions.<span class="hljs-property">lateral</span>,</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> lateralSQL = lateralOpt === <span class="hljs-literal">undefined</span> ? [] :</span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> <span class="hljs-title class_">Object</span>.<span class="hljs-title function_">keys</span>(lateralOpt).<span class="hljs-title function_">map</span>(<span class="hljs-function"><span class="hljs-params">k</span> =></span> {</span>
<span class="indent-line" style="padding-left: 10ch; text-indent: -10ch;"> <span class="hljs-keyword">const</span> subQ = lateralOpt[k];</span>
<span class="indent-line" style="padding-left: 10ch; text-indent: -10ch;"> subQ.<span class="hljs-property">parentTable</span> = aliasedTable; <span class="hljs-comment">// enables `parent()` in subquery's Whereables</span></span>
<span class="indent-line" style="padding-left: 10ch; text-indent: -10ch;"> <span class="hljs-keyword">return</span> sql<<span class="hljs-variable constant_">SQL</span>><span class="hljs-string">` LEFT JOIN LATERAL (<span class="hljs-subst">${subQ}</span>) AS <span class="hljs-subst">${raw(<span class="hljs-string">`"cj_<span class="hljs-subst">${k}</span>"`</span>)}</span> ON true`</span>;</span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"> });</span></code></pre>
<p>The <code>lateralSQL</code> variable — a <code>SQLFragment[]</code> — is subsequently interpolated into the final query (some additional SQL using <code>jsonb_build_object()</code> is interpolated earlier in that query, to return the result of the lateral subquery alongside the main query columns).</p>
<p>Note that a useful idiom also seen here is the use of the empty array (<code>[]</code>) to conditionally interpolate nothing at all.</p>
<h4 id="rawvalue-string-dangerousrawstring"><code>raw(value: string): DangerousRawString</code></h4>
<p>The <code>raw</code> function returns <code>DangerousRawString</code> wrapper instances. This represents an escape hatch, enabling us to interpolate arbitrary strings into queries in contexts where the <code>param</code> wrapper is unsuitable (such as when we’re interpolating basic SQL syntax elements). <strong>If you pass user-controlled data to this function you will open yourself up to SQL injection attacks.</strong></p>
<h4 id="parentcolumnname-string-parentcolumn"><code>parent(columnName?: string): ParentColumn</code></h4>
<p>Within queries passed as subqueries to the <code>lateral</code> option of <code>select</code> and related queries, the <code>parent()</code> wrapper can be used to refer to a column of the table that’s the subject of the immediately containing query (the ‘parent’ table).</p>
<p>To refer to a column of the parent table by name, pass a <code>string</code> argument. If the column of the parent table has the same name as the column with which it’s being joined, no argument is required.</p>
<p>For usage details, see the <a href="#lateral-and-alias">documentation for the <code>lateral</code> option</a>.</p>
<h3 id="sqlfragment"><code>SQLFragment</code></h3>
<p><code>SQLFragment<RunResult></code> class instances are what is returned by the <code>sql</code> tagged template function — you’re unlikely ever to contruct them directly with <code>new</code>. They take on the <code>RunResult</code> type variable from the <code>sql</code> template function that constructs them.</p>
<p>You can <a href="#sql-template-strings">interpolate them</a> into other <code>sql</code> tagged template strings, or call/access the following properties on them:</p>
<div style="height: 1px; clear: both;"></div><div class="src-link"><a href="https://github.com/jawj/zapatos/blob/master/src/db/core.ts#L253">Source code »</a></div>
<h4 id="preparedname-string-this"><code>prepared(name: string): this</code></h4>
<p>The <code>prepared</code> function causes a <code>name</code> property to be added to the compiled SQL query object that’s passed to <code>pg</code>, and this <a href="https://node-postgres.com/features/queries#prepared-statements">instructs Postgres to treat it as a prepared statement</a>. You can specify a prepared statement name as the function’s argument, or let it default to <code>"_zapatos_prepared_N"</code> (where N is a sequence number). This name appears in the Postgres logs.</p>
<div style="height: 1px; clear: both;"></div><div class="src-link"><a href="https://github.com/jawj/zapatos/blob/master/src/db/core.ts#L264">Source code »</a></div>
<h4 id="async-runqueryable-queryable-force--false-promiserunresult"><code>async run(queryable: Queryable, force = false): Promise<RunResult></code></h4>
<p>The <code>run</code> function compiles, executes, and returns the transformed result of the query represented by this <code>SQLFragment</code>. The <code>awaited</code> return value is typed according to the <code>SQLFragment</code>’s <code>RunResult</code> type variable.</p>
<p>Taking that one step at a time:</p>
<ol>
<li>
<p>First, <a href="#compile-sqlquery">the <code>compile</code> function</a> is called, recursively compiling this <code>SQLFragment</code> and its interpolated values into a <code>{ text: '', values: [] }</code> query that can be passed straight to the <code>pg</code> module. If a <code>queryListener</code> function <a href="#run-time-configuration">has been configured</a>, it is called with the query as its argument now.</p>
</li>
<li>
<p>Next, the compiled SQL query is executed against the supplied <code>Queryable</code>, which is defined as a <code>pg.Pool</code> or <code>pg.ClientBase</code> (this definition also covers the <code>TxnClient</code> provided by the <a href="#transaction"><code>transaction</code> helper function</a>).</p>
</li>
<li>
<p>Finally, the result returned from <code>pg</code> is fed through this <code>SQLFragment</code>’s <a href="#runresulttransform-qr-pgqueryresult--any"><code>runResultTransform()</code></a> function, whose default implementation simply returns the <code>rows</code> property of the result. If a <code>resultListener</code> function <a href="#run-time-configuration">has been configured</a>, it is called with the transformed result as its argument now.</p>
</li>
</ol>
<p>Examples of the <code>run</code> function are scattered throughout this documentation.</p>
<p>The <code>force</code> parameter is relevant only if this <code>SQLFragment</code> has been marked as a <a href="https://en.wiktionary.org/wiki/no-op#Etymology_2">no-op</a>: at present, Zapatos does this automatically if you pass an empty array to <code>insert</code> or <code>upsert</code>. By default, the database will not be disturbed in such cases, but you can force a no-op query to actually be run against the database — perhaps for logging or triggering reasons — by setting <code>force</code> to <code>true</code>.</p>
<div style="height: 1px; clear: both;"></div><div class="src-link"><a href="https://github.com/jawj/zapatos/blob/master/src/db/core.ts#L292">Source code »</a></div>
<h4 id="compile-sqlquery"><code>compile(): SQLQuery</code></h4>
<p>The <code>compile</code> function recursively transforms this <code>SQLFragment</code> and its interpolated values into a <code>SQLQuery</code> object (<code>{ text: string; values: any[]; }</code>) that can be passed straight to the <code>pg</code> module. It is called without arguments (the arguments it can take are for internal use).</p>
<p>For example:</p>
<pre class="language-typescript runnable"><code class="imports"><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">import</span> * <span class="hljs-keyword">as</span> db <span class="hljs-keyword">from</span> <span class="hljs-string">'zapatos/db'</span>;</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">import type</span> * <span class="hljs-keyword">as</span> s <span class="hljs-keyword">from</span> <span class="hljs-string">'zapatos/schema'</span>;</span></code><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">const</span> </span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> authorId = <span class="hljs-number">12</span>, <span class="hljs-comment">// from some untrusted source</span></span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> query = db.<span class="hljs-property">sql</span><s.<span class="hljs-property">books</span>.<span class="hljs-property">SQL</span>, s.<span class="hljs-property">books</span>.<span class="hljs-property">Selectable</span>[]><span class="hljs-string">`</span></span>
<span class="indent-line" style="padding-left: 8ch; text-indent: -8ch;"><span class="hljs-string"> SELECT * FROM <span class="hljs-subst">${<span class="hljs-string">"books"</span>}</span> WHERE <span class="hljs-subst">${{authorId}}</span>`</span>,</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> compiled = query.<span class="hljs-title function_">compile</span>();</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"></span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-variable language_">console</span>.<span class="hljs-title function_">log</span>(compiled);</span></code></pre><div class="sqlstuff">
<pre class="console"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;">{</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> text: '\n SELECT * FROM "books" WHERE ("authorId" = $1)',</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> values: [ 12 ]</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;">}</span></code></pre>
</div>
<p>You may never need this function. Use it if and when you want to see the SQL that would be executed by the <code>run</code> function, without in fact executing it.</p>
<div style="height: 1px; clear: both;"></div><div class="src-link"><a href="https://github.com/jawj/zapatos/blob/master/src/db/core.ts#L237">Source code »</a></div>
<h4 id="runresulttransform-qr-pgqueryresult--any"><code>runResultTransform: (qr: pg.QueryResult) => any</code></h4>
<p>When you call <code>run</code>, the function stored in this property is applied to the <code>QueryResult</code> object returned by <code>pg</code>, in order to produce the result that the <code>run</code> function ultimately returns.</p>
<p>By default, the <code>QueryResult</code>’s <code>rows</code> property (which is an array) is returned: that is, the default implementation is just <code>qr => qr.rows</code>. However, the <a href="#shortcut-functions-and-lateral-joins">shortcut functions</a> supply their own <code>runResultTransform</code> implementations in order to match their declared <code>RunResult</code> types.</p>
<p>Generally you will not need to call this function directly, but there may be cases where you want to assign a new function to replace the default implementation.</p>
<p>For example, imagine we wanted to create a function returning a query that, when run, returns the current database timestamp directly as a <code>Date</code>. We could do so like this:</p>
<pre class="language-typescript runnable"><code class="imports"><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">import</span> * <span class="hljs-keyword">as</span> db <span class="hljs-keyword">from</span> <span class="hljs-string">'zapatos/db'</span>;</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">import</span> pool <span class="hljs-keyword">from</span> <span class="hljs-string">'./pgPool'</span>;</span></code><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">function</span> <span class="hljs-title function_">dbNowQuery</span>(<span class="hljs-params"></span>) {</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-keyword">const</span> query = db.<span class="hljs-property">sql</span><<span class="hljs-built_in">never</span>, <span class="hljs-title class_">Date</span>><span class="hljs-string">`SELECT now()`</span>;</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> query.<span class="hljs-property">runResultTransform</span> = <span class="hljs-function"><span class="hljs-params">qr</span> =></span> qr.<span class="hljs-property">rows</span>[<span class="hljs-number">0</span>].<span class="hljs-property">now</span>;</span>
<span class="indent-line" style="padding-left: 6ch; text-indent: -6ch;"> <span class="hljs-keyword">return</span> query;</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;">}</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"></span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">const</span> dbNow = <span class="hljs-keyword">await</span> <span class="hljs-title function_">dbNowQuery</span>().<span class="hljs-title function_">run</span>(pool);</span>
<span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-comment">// dbNow is a Date: the result you can toggle below has come via JSON.stringify</span></span></code></pre><div class="sqlstuff">
<pre class="sqltext"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">SELECT</span> now()</span></code></pre>
<pre class="sqlresult"><code><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-string">"2024-01-08T11:51:14.463Z"</span></span></code></pre>
</div>
<p>Note that the <code>RunResult</code> type variable on the <code>sql</code> template function (in this case, <code>Date</code>) must reflect the type of the <em>transformed</em> result, not what comes straight back from <code>pg</code> (which in this case is roughly <code>{ rows: [{ now: Date }] }</code>).</p>
<p>If a <code>SQLFragment</code> does not have <code>run</code> called on it directly — for example, if it is instead interpolated into another <code>SQLFragment</code>, or given as the value of the <code>lateral</code> option to the <code>select</code> shortcut — then the <code>runResultTransform</code> function is never applied.</p>
<h3 id="manual-joins-using-postgres-json-features">Manual joins using Postgres’ JSON features</h3>
<p>We can make use of Postgres’ excellent JSON support to achieve a variety of <code>JOIN</code> queries. That’s not unique to Zapatos, of course, but it may be helpful to consider a few example queries in this context.</p>
<p>Take this example, retrieving each book with its (single) author:</p>
<pre class="language-typescript runnable"><code class="imports"><span class="indent-line" style="padding-left: 4ch; text-indent: -4ch;"><span class="hljs-keyword">import</span> * <span class="hljs-keyword">as</span> db <span class="hljs-keyword">from</span> <span class="hljs-string">'zapatos/db'</span>;</span>