-
Notifications
You must be signed in to change notification settings - Fork 2
/
Copy pathslides.html
2522 lines (2464 loc) · 152 KB
/
slides.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>
<head>
<meta charset="utf-8">
<meta name="generator" content="pandoc">
<title>Diesel Workshop</title>
<meta name="apple-mobile-web-app-capable" content="yes">
<meta name="apple-mobile-web-app-status-bar-style" content="black-translucent">
<meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=no, minimal-ui">
<link rel="stylesheet" href="https://unpkg.com/reveal.js@^4//dist/reset.css">
<link rel="stylesheet" href="https://unpkg.com/reveal.js@^4//dist/reveal.css">
<style>
.reveal .sourceCode { /* see #7635 */
overflow: visible;
}
code{white-space: pre-wrap;}
span.smallcaps{font-variant: small-caps;}
div.columns{display: flex; gap: min(4vw, 1.5em);}
div.column{flex: auto; overflow-x: auto;}
div.hanging-indent{margin-left: 1.5em; text-indent: -1.5em;}
/* The extra [class] is a hack that increases specificity enough to
override a similar rule in reveal.js */
ul.task-list[class]{list-style: none;}
ul.task-list li input[type="checkbox"] {
font-size: inherit;
width: 0.8em;
margin: 0 0.8em 0.2em -1.6em;
vertical-align: middle;
}
.display.math{display: block; text-align: center; margin: 0.5rem auto;}
/* CSS for syntax highlighting */
pre > code.sourceCode { white-space: pre; position: relative; }
pre > code.sourceCode > span { display: inline-block; line-height: 1.25; }
pre > code.sourceCode > span:empty { height: 1.2em; }
.sourceCode { overflow: visible; }
code.sourceCode > span { color: inherit; text-decoration: inherit; }
div.sourceCode { margin: 1em 0; }
pre.sourceCode { margin: 0; }
@media screen {
div.sourceCode { overflow: auto; }
}
@media print {
pre > code.sourceCode { white-space: pre-wrap; }
pre > code.sourceCode > span { text-indent: -5em; padding-left: 5em; }
}
pre.numberSource code
{ counter-reset: source-line 0; }
pre.numberSource code > span
{ position: relative; left: -4em; counter-increment: source-line; }
pre.numberSource code > span > a:first-child::before
{ content: counter(source-line);
position: relative; left: -1em; text-align: right; vertical-align: baseline;
border: none; display: inline-block;
-webkit-touch-callout: none; -webkit-user-select: none;
-khtml-user-select: none; -moz-user-select: none;
-ms-user-select: none; user-select: none;
padding: 0 4px; width: 4em;
color: #aaaaaa;
}
pre.numberSource { margin-left: 3em; border-left: 1px solid #aaaaaa; padding-left: 4px; }
div.sourceCode
{ }
@media screen {
pre > code.sourceCode > span > a:first-child::before { text-decoration: underline; }
}
code span.al { color: #ff0000; font-weight: bold; } /* Alert */
code span.an { color: #60a0b0; font-weight: bold; font-style: italic; } /* Annotation */
code span.at { color: #7d9029; } /* Attribute */
code span.bn { color: #40a070; } /* BaseN */
code span.bu { color: #008000; } /* BuiltIn */
code span.cf { color: #007020; font-weight: bold; } /* ControlFlow */
code span.ch { color: #4070a0; } /* Char */
code span.cn { color: #880000; } /* Constant */
code span.co { color: #60a0b0; font-style: italic; } /* Comment */
code span.cv { color: #60a0b0; font-weight: bold; font-style: italic; } /* CommentVar */
code span.do { color: #ba2121; font-style: italic; } /* Documentation */
code span.dt { color: #902000; } /* DataType */
code span.dv { color: #40a070; } /* DecVal */
code span.er { color: #ff0000; font-weight: bold; } /* Error */
code span.ex { } /* Extension */
code span.fl { color: #40a070; } /* Float */
code span.fu { color: #06287e; } /* Function */
code span.im { color: #008000; font-weight: bold; } /* Import */
code span.in { color: #60a0b0; font-weight: bold; font-style: italic; } /* Information */
code span.kw { color: #007020; font-weight: bold; } /* Keyword */
code span.op { color: #666666; } /* Operator */
code span.ot { color: #007020; } /* Other */
code span.pp { color: #bc7a00; } /* Preprocessor */
code span.sc { color: #4070a0; } /* SpecialChar */
code span.ss { color: #bb6688; } /* SpecialString */
code span.st { color: #4070a0; } /* String */
code span.va { color: #19177c; } /* Variable */
code span.vs { color: #4070a0; } /* VerbatimString */
code span.wa { color: #60a0b0; font-weight: bold; font-style: italic; } /* Warning */
</style>
<link rel="stylesheet" href="https://unpkg.com/reveal.js@^4//dist/theme/serif.css" id="theme">
</head>
<body>
<div class="reveal">
<div class="slides">
<section id="title-slide">
<h1 class="title">Diesel Workshop</h1>
</section>
<section>
<section id="introduction" class="title-slide slide level2">
<h2>Introduction</h2>
</section>
<section id="who-i-am" class="slide level3">
<h3>Who I am</h3>
<ul>
<li>I’m Georg (weiznich on Github)</li>
<li>Diesel contributor since 8 years and one of the core team members of
diesel since 5 years</li>
<li>I’m writing Rust code for 10 years now, including contributions to
diesel, the rust compiler and authoring one RFC</li>
<li>Working at GiGa Infosystems GmbH on a database system for geologic
subsurface models</li>
</ul>
</section>
<section id="content-of-the-workshop" class="slide level3">
<h3>Content of the workshop</h3>
<ul>
<li>Diesel Basics</li>
<li>Testing with Diesel</li>
<li>Extending Diesel</li>
</ul>
<aside class="notes">
<ul>
<li>Each topic will consist of me talking about stuff</li>
<li>Afterwards there will be a practical exercise</li>
<li>Will share the workshop material + solutions to the exercises after
the workshop</li>
<li>If there are question: Just indicate that you have a question and
ask them right away (after I stopped speaking)</li>
</ul>
</aside>
</section>
<section id="timeline" class="slide level3">
<h3>Timeline</h3>
<p>Before Lunch: * Diesel Basics</p>
<p>After Lunch: * Testing with Diesel * Extending Diesel</p>
<aside class="notes">
<ul>
<li>we might take another short break between the afternoon blocks,
depending on your needs?</li>
</ul>
</aside>
</section>
<section id="about-you" class="slide level3">
<h3>About you</h3>
<ul>
<li>Rust experience?</li>
<li>Database experience?</li>
<li>Diesel experience?</li>
</ul>
<aside class="notes">
<ul>
<li>did not use it yet, beginner, knowledge, using x professionally</li>
<li>other ORM’s?</li>
<li>Postgres, Sqlite, Mysql</li>
</ul>
</aside>
</section></section>
<section>
<section id="diesel-basics" class="title-slide slide level2">
<h2>Diesel Basics</h2>
</section>
<section id="what-is-diesel" class="slide level3">
<h3>What is Diesel</h3>
<ul>
<li>Diesel is a query builder and ORM for Rust</li>
<li>Exists since 2015</li>
<li>Focus on performance and safety</li>
<li>Checks your SQL at compile time</li>
<li>Expressive and extensible</li>
</ul>
<aside class="notes">
<ul>
<li>More query builder with some optional ORM features</li>
<li>Checks even “dynamic” queries at compile time</li>
<li>Influenced development of various rust features:
<ul>
<li>derive macros</li>
<li>coherence rules (orphan rule)</li>
<li>recently diagnostic improvements</li>
</ul></li>
<li>If it compiles your queries almost always works</li>
<li>possible to extend almost everything in diesel, will see examples
for this later in the workshop</li>
</ul>
</aside>
</section>
<section id="what-is-diesel-1" class="slide level3">
<h3>What is Diesel</h3>
<ul>
<li>Diesel consists of different parts:
<ul>
<li>Core <code>diesel</code> crate</li>
<li>A CLI tool <code>diesel-cli</code></li>
<li>Migration crate <code>diesel-migration</code></li>
<li>(Several internal helper crates for derives, etc)</li>
</ul></li>
<li>Notable extension crates
<ul>
<li><code>diesel-full-text-search</code></li>
<li><code>diesel-dynamic-schema</code></li>
<li><code>diesel-async</code></li>
</ul></li>
</ul>
<aside class="notes">
<ul>
<li><code>diesel-dynamic-schema</code> -> for situations where you
don’t know the schema at compile time</li>
<li><code>diesel-full-text-search</code> -> example crate for showing
how to provide support for a postgres extension</li>
<li><code>diesel-async</code> -> Support for async database
connections</li>
</ul>
</aside>
</section>
<section id="what-is-provided-by-diesel" class="slide level3">
<h3>What is provided by Diesel</h3>
<ul>
<li>Database connection abstractions</li>
<li>Database connections for PostgreSQL, MySQL and SQLite</li>
<li>A domain specific language (DSL) to describe your database
schema</li>
<li>A DSL to write your queries</li>
<li>Various proc-macros to map data from/to queries</li>
<li>Basic ORM like functionality</li>
<li>Tools to execute migrations</li>
</ul>
<aside class="notes">
<ul>
<li>Will see examples soon</li>
<li>ORM like functionality is “optional”, you can use it but it’s not
required</li>
<li>Migrations allow to manage your database schema</li>
</ul>
</aside>
</section>
<section id="diesel-design-goals" class="slide level3">
<h3>Diesel Design Goals</h3>
<ul>
<li>Check whatever is possible to check at compile time</li>
<li>Do not hide database features/differences from the user</li>
<li>Be extensible wherever possible</li>
<li>Make type mapping flexible</li>
</ul>
<aside class="notes">
<ul>
<li>We want to be really sure that code compiles == code is correct</li>
<li>Hide differences only if user explicitly requests that -> Then
fall back to a common subset of features</li>
<li>We do not want to restrict which database features you can use
<ul>
<li>For example we support DSL for most of the “advanced” JSON/JSONB
operators for postgres</li>
</ul></li>
<li>Want to have your own types/backend/… -> Sure no problem, just
implement these traits</li>
<li>No 1:1 mapping between database types and rust types, more like n:m.
Example: Datetime in SQL, different time types in rust
(chrono/time/jiff/…), might be stored as <code>Text</code> for
sqlite</li>
</ul>
</aside>
</section>
<section id="diesel-limitations" class="slide level3">
<h3>Diesel Limitations</h3>
<ul>
<li>Limited built-in DSL</li>
<li>Strongly typed, restricts certain kinds of dynamic queries</li>
<li>Favours explicit query building over implicit model based
approach</li>
</ul>
<aside class="notes">
<ul>
<li>Talk about this later in detail, why this restrictions exists</li>
<li>Limited DSL -> can be extend, will be later shown</li>
<li>Strongly typed -> restricts dynamic queries
<ul>
<li>FROM clause needs to be known</li>
<li>SELECT clause needs to be known</li>
<li>GROUP BY clause needs to be known</li>
<li>-> Again workaround exist, will talk about them later</li>
<li>Has impact on compile times/tooling</li>
<li>Has impact on compile errors</li>
</ul></li>
</ul>
</aside>
</section>
<section id="show-me-some-code" class="slide level3">
<h3>Show me some code</h3>
<div class="sourceCode" id="cb1"><pre
class="sourceCode rust"><code class="sourceCode rust"><span id="cb1-1"><a href="#cb1-1" aria-hidden="true" tabindex="-1"></a><span class="pp">diesel::table!</span> <span class="op">{</span></span>
<span id="cb1-2"><a href="#cb1-2" aria-hidden="true" tabindex="-1"></a> users <span class="op">{</span></span>
<span id="cb1-3"><a href="#cb1-3" aria-hidden="true" tabindex="-1"></a> id <span class="op">-></span> Integer<span class="op">,</span></span>
<span id="cb1-4"><a href="#cb1-4" aria-hidden="true" tabindex="-1"></a> name <span class="op">-></span> Text<span class="op">,</span></span>
<span id="cb1-5"><a href="#cb1-5" aria-hidden="true" tabindex="-1"></a> <span class="op">}</span></span>
<span id="cb1-6"><a href="#cb1-6" aria-hidden="true" tabindex="-1"></a><span class="op">}</span></span>
<span id="cb1-7"><a href="#cb1-7" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb1-8"><a href="#cb1-8" aria-hidden="true" tabindex="-1"></a><span class="kw">fn</span> load_usernames(conn<span class="op">:</span> <span class="op">&</span><span class="kw">mut</span> PgConnection) <span class="op">-></span> QueryResult<span class="op"><</span><span class="dt">Vec</span><span class="op"><</span><span class="dt">String</span><span class="op">>></span> <span class="op">{</span></span>
<span id="cb1-9"><a href="#cb1-9" aria-hidden="true" tabindex="-1"></a> <span class="pp">users::</span>table<span class="op">.</span>select(<span class="pp">users::</span>name)<span class="op">.</span>load(conn)</span>
<span id="cb1-10"><a href="#cb1-10" aria-hidden="true" tabindex="-1"></a><span class="op">}</span></span></code></pre></div>
<aside class="notes">
<ul>
<li>Simple example</li>
<li>Two relevant parts:
<ul>
<li>Schema definition, via <code>table!</code> macro</li>
<li>functions using the schema to construct queries</li>
</ul></li>
<li>Diesel is mostly designed around queries, not models</li>
<li>We will dive into the details soon</li>
</ul>
</aside>
</section>
<section id="creating-a-diesel-application" class="slide level3">
<h3>Creating a Diesel Application</h3>
<ol start="0" type="1">
<li>Install <code>diesel_cli</code></li>
<li>Setup the database using SQL migrations</li>
<li>Create a Rust schema file from your database</li>
<li>Write code that interacts with the database</li>
</ol>
<aside class="notes">
<ul>
<li>Step 1: Can be generated from your <code>table!</code> macro</li>
<li>Step 1: Can be skipped for existing/externally managed
databases</li>
<li>Step 2: Generates the definition used by diesel compile time checks
-> Important to keep that up to date</li>
</ul>
</aside>
</section>
<section id="what-is-diesel-cli" class="slide level3">
<h3>What is Diesel CLI</h3>
<ul>
<li>Helper tool for working on Diesel projects</li>
<li>Allows:
<ul>
<li>To manage your database</li>
<li>To manage your migrations</li>
<li>To generate migrations based on your Rust schema and an existing
database</li>
<li>Generate Rust schema from databases</li>
<li>Verify that the generated schema matches your database</li>
</ul></li>
</ul>
<aside class="notes">
<ul>
<li>Manage database == create new database, drop database etc ->
usful for testing</li>
<li>Migrations -> Tool to update database schema to a new
version</li>
<li>Main motivation for SQL migrations:
<ul>
<li>It’s portable, you can easily move tools/languages/ if required</li>
<li>Allows you to leverage advanced SQL concepts for data migration</li>
</ul></li>
<li>Also allows to run/undo/… migrations</li>
<li>Generating migrations == essentially diff your database schema with
what the rust side expects and generate a migration to get there
<ul>
<li>Limited, ambiguous problem</li>
<li>We generate SQL, so that you can inspect and change the migration
before applying</li>
<li>Useful for “trivial” things like adding new table, etc</li>
<li>Supports mostly adding/removing tables/columns</li>
<li>Does not handle more complex things like indices, constraints
(beside foreign keys)</li>
<li>Expectation there: You know that better and can adjust the generated
SQL easily</li>
</ul></li>
</ul>
</aside>
</section>
<section id="the-table-macro" class="slide level3">
<h3>The <code>table!</code> Macro</h3>
<div class="sourceCode" id="cb2"><pre
class="sourceCode rust"><code class="sourceCode rust"><span id="cb2-1"><a href="#cb2-1" aria-hidden="true" tabindex="-1"></a><span class="pp">diesel::table!</span> <span class="op">{</span></span>
<span id="cb2-2"><a href="#cb2-2" aria-hidden="true" tabindex="-1"></a> users <span class="op">{</span></span>
<span id="cb2-3"><a href="#cb2-3" aria-hidden="true" tabindex="-1"></a> id <span class="op">-></span> Integer<span class="op">,</span></span>
<span id="cb2-4"><a href="#cb2-4" aria-hidden="true" tabindex="-1"></a> name <span class="op">-></span> Text<span class="op">,</span></span>
<span id="cb2-5"><a href="#cb2-5" aria-hidden="true" tabindex="-1"></a> <span class="op">}</span></span>
<span id="cb2-6"><a href="#cb2-6" aria-hidden="true" tabindex="-1"></a><span class="op">}</span></span></code></pre></div>
<ul>
<li>Defines a representation of the database in your Rust code</li>
<li>Generates a number of types</li>
<li>Used for type checking + query building</li>
</ul>
</section>
<section id="the-table-macro-1" class="slide level3">
<h3>The <code>table!</code> Macro</h3>
<div class="sourceCode" id="cb3"><pre
class="sourceCode rust"><code class="sourceCode rust"><span id="cb3-1"><a href="#cb3-1" aria-hidden="true" tabindex="-1"></a><span class="kw">mod</span> users <span class="op">{</span></span>
<span id="cb3-2"><a href="#cb3-2" aria-hidden="true" tabindex="-1"></a> <span class="kw">pub</span> <span class="kw">struct</span> table<span class="op">;</span></span>
<span id="cb3-3"><a href="#cb3-3" aria-hidden="true" tabindex="-1"></a> <span class="kw">mod</span> columns <span class="op">{</span></span>
<span id="cb3-4"><a href="#cb3-4" aria-hidden="true" tabindex="-1"></a> <span class="kw">pub</span> <span class="kw">struct</span> id<span class="op">;</span></span>
<span id="cb3-5"><a href="#cb3-5" aria-hidden="true" tabindex="-1"></a> <span class="kw">pub</span> <span class="kw">struct</span> name<span class="op">;</span></span>
<span id="cb3-6"><a href="#cb3-6" aria-hidden="true" tabindex="-1"></a> <span class="op">}</span></span>
<span id="cb3-7"><a href="#cb3-7" aria-hidden="true" tabindex="-1"></a> <span class="kw">pub</span> <span class="kw">use</span> <span class="pp">columns::</span><span class="op">*;</span></span>
<span id="cb3-8"><a href="#cb3-8" aria-hidden="true" tabindex="-1"></a> </span>
<span id="cb3-9"><a href="#cb3-9" aria-hidden="true" tabindex="-1"></a> <span class="kw">pub</span> <span class="kw">mod</span> dsl <span class="op">{</span></span>
<span id="cb3-10"><a href="#cb3-10" aria-hidden="true" tabindex="-1"></a> <span class="kw">pub</span> <span class="kw">use</span> <span class="kw">super</span><span class="pp">::</span>table <span class="kw">as</span> users<span class="op">;</span></span>
<span id="cb3-11"><a href="#cb3-11" aria-hidden="true" tabindex="-1"></a> <span class="kw">pub</span> <span class="kw">use</span> <span class="kw">super</span><span class="pp">::columns::</span><span class="op">*;</span></span>
<span id="cb3-12"><a href="#cb3-12" aria-hidden="true" tabindex="-1"></a> <span class="op">}</span></span>
<span id="cb3-13"><a href="#cb3-13" aria-hidden="true" tabindex="-1"></a><span class="op">}</span></span></code></pre></div>
<ul>
<li>Contains zero sized structs that represent your table + columns</li>
<li>Prefer qualified uses, e.g <code>users::id</code></li>
</ul>
<aside class="notes">
<ul>
<li>Simplified expansion of the generated code (skipped all traits, and
some other stuff)</li>
<li>These types are the entry point for query building</li>
<li>The <code>dsl</code> module provides a short hand for wildcard
imports -> Only use those in function scope if only a single table is
in place -> Do not globally import these types, as that leads to name
collisions</li>
</ul>
</aside>
</section>
<section id="select-statements" class="slide level3">
<h3>Select Statements</h3>
<ul>
<li>Diesel provides a query DSL to construct statically known
queries</li>
<li>DSL maps literally to the generated SQL,
e.g. <code>users::table.select(users::id)</code> maps to
<code>SELECT id FROM users</code></li>
<li>Documentation available via the <code>QueryDsl</code> trait</li>
<li>Offers support for most select statement constructs</li>
</ul>
<aside class="notes">
<ul>
<li>Starting point is the schema DSL shown in the previous slide</li>
<li>Query needs to be known statically, otherwise boxing is required
(via <code>QueryDsl::into_boxed()</code>)</li>
<li>Exceptions for literal mapping: Rust keywords like
<code>WHERE</code> or <code>IN</code> (later for expressions)</li>
</ul>
</aside>
</section>
<section id="select-statements-1" class="slide level3">
<h3>Select Statements</h3>
<div class="sourceCode" id="cb4"><pre
class="sourceCode rust"><code class="sourceCode rust"><span id="cb4-1"><a href="#cb4-1" aria-hidden="true" tabindex="-1"></a><span class="pp">users::</span>table</span>
<span id="cb4-2"><a href="#cb4-2" aria-hidden="true" tabindex="-1"></a> <span class="op">.</span>select((<span class="pp">users::</span>id<span class="op">,</span> <span class="pp">users::</span>name))</span>
<span id="cb4-3"><a href="#cb4-3" aria-hidden="true" tabindex="-1"></a> <span class="op">.</span>filter(<span class="pp">users::</span>name<span class="op">.</span>like(<span class="st">"%John%"</span>))</span>
<span id="cb4-4"><a href="#cb4-4" aria-hidden="true" tabindex="-1"></a> <span class="op">.</span>order_by(<span class="pp">users::</span>id)</span>
<span id="cb4-5"><a href="#cb4-5" aria-hidden="true" tabindex="-1"></a> <span class="op">.</span>limit(<span class="dv">42</span>)</span>
<span id="cb4-6"><a href="#cb4-6" aria-hidden="true" tabindex="-1"></a> <span class="op">.</span>offset(<span class="dv">24</span>)</span>
<span id="cb4-7"><a href="#cb4-7" aria-hidden="true" tabindex="-1"></a> <span class="op">.</span><span class="pp">load::</span><span class="op"><</span>RustTargetType<span class="op">></span>(<span class="op">&</span><span class="kw">mut</span> conn)<span class="op">?;</span></span></code></pre></div>
<aside class="notes">
<ul>
<li>Methods can be chained</li>
<li>Only need to use these calls that you want</li>
<li>Order of the methods is mostly irrelevant</li>
<li>Exceptions:
<ul>
<li>Group by + Select -> First group then select, required for better
error messages for invalid selects</li>
<li>Join + Select/Filter/Order -> First join then
select/filter/order</li>
</ul></li>
<li>Need to end with <code>load</code> (or similar method from
<code>RunQueryDsl</code>) to execute the query</li>
<li>Will look at result mapping first, talk about different parts of
selects later</li>
</ul>
</aside>
</section>
<section id="query-execution" class="slide level3">
<h3>Query Execution</h3>
<ul>
<li>Different ways to execute a query via <code>RunQueryDsl</code>:
<ul>
<li><code>load::<U></code>/<code>get_results::<U></code>:
Returns a list of <code>U</code></li>
<li><code>get_result::<U></code>: Returns the first <code>U</code>
ignores the rest</li>
<li><code>first::<U></code>: Returns the first <code>U</code>,
attaches a LIMIT 1 clause to the executed query</li>
<li><code>execute</code>: Returns the number of affected column</li>
</ul></li>
</ul>
<aside class="notes">
<ul>
<li>Prefer <code>load</code> and <code>first</code> for select
queries</li>
<li>Use <code>execute</code> for insert/update/delete</li>
<li>Use <code>get_result</code> for insert/update/delete with
returning</li>
</ul>
</aside>
</section>
<section id="select-statements-result-mapping" class="slide level3">
<h3>Select Statements (Result Mapping)</h3>
<ul>
<li>Diesel maps query results to Rust structs</li>
<li>Mapping is performed by field order, <strong>not by
name</strong></li>
<li>Compile time checks ensure that you only use compatible types on the
Rust side</li>
<li>Default implementation exists for tuples</li>
<li>Recommend to use structs that derive <code>Queryable</code></li>
</ul>
<aside class="notes">
<ul>
<li>query results != tables, might contain columns from zero, one or
more tables</li>
<li>Field in query results might not have a name</li>
<li>No mapping from Integer to String or similar allowed</li>
<li>Checks also verify all fields are used</li>
<li>Mismatch will cause compile time error</li>
<li>Exception to using structs: Queries which results are reprocessed
directly afterwards</li>
<li>Generally: Prefer using structs especially if they already exists
for other reasons (e.g. API’s …)</li>
</ul>
</aside>
</section>
<section id="result-mapping" class="slide level3">
<h3>Result Mapping</h3>
<div class="sourceCode" id="cb5"><pre
class="sourceCode rust"><code class="sourceCode rust"><span id="cb5-1"><a href="#cb5-1" aria-hidden="true" tabindex="-1"></a><span class="pp">diesel::table!</span> <span class="op">{</span></span>
<span id="cb5-2"><a href="#cb5-2" aria-hidden="true" tabindex="-1"></a> users <span class="op">{</span></span>
<span id="cb5-3"><a href="#cb5-3" aria-hidden="true" tabindex="-1"></a> id <span class="op">-></span> Integer<span class="op">,</span></span>
<span id="cb5-4"><a href="#cb5-4" aria-hidden="true" tabindex="-1"></a> name <span class="op">-></span> Text<span class="op">,</span></span>
<span id="cb5-5"><a href="#cb5-5" aria-hidden="true" tabindex="-1"></a> age <span class="op">-></span> Integer<span class="op">,</span></span>
<span id="cb5-6"><a href="#cb5-6" aria-hidden="true" tabindex="-1"></a> <span class="op">}</span></span>
<span id="cb5-7"><a href="#cb5-7" aria-hidden="true" tabindex="-1"></a><span class="op">}</span></span></code></pre></div>
<aside class="notes">
<ul>
<li>Table used in the next few examples</li>
<li>Important thing to keep in mind: 3 columns</li>
</ul>
</aside>
</section>
<section id="result-mapping-1" class="slide level3">
<h3>Result Mapping</h3>
<div class="sourceCode" id="cb6"><pre
class="sourceCode rust"><code class="sourceCode rust"><span id="cb6-1"><a href="#cb6-1" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb6-2"><a href="#cb6-2" aria-hidden="true" tabindex="-1"></a><span class="at">#[</span>derive<span class="at">(</span>Queryable<span class="at">)]</span></span>
<span id="cb6-3"><a href="#cb6-3" aria-hidden="true" tabindex="-1"></a><span class="kw">struct</span> User <span class="op">{</span></span>
<span id="cb6-4"><a href="#cb6-4" aria-hidden="true" tabindex="-1"></a> id<span class="op">:</span> <span class="dt">i32</span><span class="op">,</span></span>
<span id="cb6-5"><a href="#cb6-5" aria-hidden="true" tabindex="-1"></a> name<span class="op">:</span> <span class="dt">String</span><span class="op">,</span></span>
<span id="cb6-6"><a href="#cb6-6" aria-hidden="true" tabindex="-1"></a> age<span class="op">:</span> <span class="dt">i32</span><span class="op">,</span></span>
<span id="cb6-7"><a href="#cb6-7" aria-hidden="true" tabindex="-1"></a><span class="op">}</span></span>
<span id="cb6-8"><a href="#cb6-8" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb6-9"><a href="#cb6-9" aria-hidden="true" tabindex="-1"></a><span class="pp">users::</span>table<span class="op">.</span><span class="pp">load::</span><span class="op"><</span>User<span class="op">></span>(<span class="op">&</span><span class="kw">mut</span> conn)<span class="op">?;</span></span>
<span id="cb6-10"><a href="#cb6-10" aria-hidden="true" tabindex="-1"></a><span class="pp">users::</span>table<span class="op">.</span><span class="pp">load::</span><span class="op"><</span>(<span class="dt">i32</span><span class="op">,</span> <span class="dt">String</span><span class="op">,</span> <span class="dt">i32</span>)<span class="op">></span>(<span class="op">&</span><span class="kw">mut</span> conn)<span class="op">?;</span></span></code></pre></div>
<aside class="notes">
<ul>
<li><p>simplest possible query, loads all users from the
database</p></li>
<li><p>Load accepts a generic type to specify the target type for the
query result -> Should be used to make it obvious to the compiler
what’s requested</p></li>
<li><p>Order, count and type of the fields in your struct need to match
that in your select clause</p></li>
<li><p>Default select clause: All columns from your table, in the order
of definition in your table struct</p></li>
<li><p>Tuple variant loads the same data into a tuple</p></li>
<li><p>Using this variant might generate “bad” error messages if
something goes wrong</p></li>
</ul>
</aside>
</section>
<section id="result-mapping-2" class="slide level3">
<h3>Result Mapping</h3>
<div class="sourceCode" id="cb7"><pre
class="sourceCode rust"><code class="sourceCode rust"><span id="cb7-1"><a href="#cb7-1" aria-hidden="true" tabindex="-1"></a><span class="at">#[</span>derive<span class="at">(</span>Queryable<span class="at">)]</span></span>
<span id="cb7-2"><a href="#cb7-2" aria-hidden="true" tabindex="-1"></a><span class="kw">struct</span> UserWithName <span class="op">{</span></span>
<span id="cb7-3"><a href="#cb7-3" aria-hidden="true" tabindex="-1"></a> user_name<span class="op">:</span> <span class="dt">String</span><span class="op">,</span></span>
<span id="cb7-4"><a href="#cb7-4" aria-hidden="true" tabindex="-1"></a> id<span class="op">:</span> <span class="dt">i32</span><span class="op">,</span></span>
<span id="cb7-5"><a href="#cb7-5" aria-hidden="true" tabindex="-1"></a><span class="op">}</span></span>
<span id="cb7-6"><a href="#cb7-6" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb7-7"><a href="#cb7-7" aria-hidden="true" tabindex="-1"></a><span class="pp">users::</span>table<span class="op">.</span>select((<span class="pp">users::</span>name<span class="op">,</span> <span class="pp">users::</span>id))</span>
<span id="cb7-8"><a href="#cb7-8" aria-hidden="true" tabindex="-1"></a> <span class="op">.</span><span class="pp">load::</span><span class="op"><</span>UserWithName<span class="op">></span>(<span class="op">&</span><span class="kw">mut</span> conn)<span class="op">?;</span></span>
<span id="cb7-9"><a href="#cb7-9" aria-hidden="true" tabindex="-1"></a><span class="pp">users::</span>table<span class="op">.</span>select(<span class="pp">users::</span>id)<span class="op">.</span><span class="pp">load::</span><span class="op"><</span><span class="dt">i32</span><span class="op">></span>(<span class="op">&</span><span class="kw">mut</span> conn)<span class="op">?;</span></span></code></pre></div>
<aside class="notes">
<ul>
<li>Using an explicit select clause allows us to list which fields we
want</li>
<li>It also allows us to change the field order</li>
<li>The field name in your rust struct does not have any influence on
<code>Queryable</code> as it works with the field order</li>
</ul>
</aside>
</section>
<section id="result-mapping-3" class="slide level3">
<h3>Result Mapping</h3>
<pre><code>the trait bound `(diesel::sql_types::Integer, diesel::sql_types::Text, diesel::sql_types::Integer): load_dsl::private::CompatibleType<User, Sqlite>` is not satisfied
--> src/main.rs:19:31
|
19 | users::table.load::<User>(conn);
| ---- ^^^^ the trait `load_dsl::private::CompatibleType<User, Sqlite>` is not implemented for `(diesel::sql_types::Integer, diesel::sql_types::Text, diesel::sql_types::Integer)`, which is required by `table: LoadQuery<'_, _, User>`
| |
| required by a bound introduced by this call
|
= note: this is a mismatch between what your query returns and what your type expects the query to return
= note: the fields in your struct need to match the fields returned by your query in count, order and type
= note: consider using `#[derive(Selectable)]` or #[derive(QueryableByName)] + `#[diesel(check_for_backend(Sqlite))]`
on your struct `User` and in your query `.select(User::as_select())` to get a better error message</code></pre>
<aside class="notes">
<ul>
<li>Relatively hard to read, but if you sit down and go through the
parts you get what’s wrong</li>
<li>Now at least mentions how to do it better via
<code>note:</code></li>
</ul>
</aside>
</section>
<section id="result-mapping-4" class="slide level3">
<h3>Result Mapping</h3>
<ul>
<li>Potentially bad error messages due to field mismatches</li>
<li>Need to keep field order in struct and select clause in sync</li>
</ul>
<p>-> Diesel provides a <code>#[derive(Selectable)]</code> which
allows to generate a matching select clause from your struct</p>
</section>
<section id="result-mapping-5" class="slide level3">
<h3>Result Mapping</h3>
<div class="sourceCode" id="cb9"><pre
class="sourceCode rust"><code class="sourceCode rust"><span id="cb9-1"><a href="#cb9-1" aria-hidden="true" tabindex="-1"></a><span class="at">#[</span>derive<span class="at">(</span>Queryable<span class="op">,</span> Selectable<span class="at">)]</span></span>
<span id="cb9-2"><a href="#cb9-2" aria-hidden="true" tabindex="-1"></a><span class="at">#[</span>diesel<span class="at">(</span>table_name <span class="op">=</span> users<span class="at">)]</span></span>
<span id="cb9-3"><a href="#cb9-3" aria-hidden="true" tabindex="-1"></a><span class="at">#[</span>diesel<span class="at">(</span>check_for_backend<span class="at">(</span><span class="pp">diesel::pg::</span>Pg<span class="at">))]</span></span>
<span id="cb9-4"><a href="#cb9-4" aria-hidden="true" tabindex="-1"></a><span class="kw">struct</span> UserWithName <span class="op">{</span></span>
<span id="cb9-5"><a href="#cb9-5" aria-hidden="true" tabindex="-1"></a> <span class="at">#[</span>diesel<span class="at">(</span>column_name <span class="op">=</span> name<span class="at">)]</span></span>
<span id="cb9-6"><a href="#cb9-6" aria-hidden="true" tabindex="-1"></a> user_name<span class="op">:</span> <span class="dt">String</span><span class="op">,</span></span>
<span id="cb9-7"><a href="#cb9-7" aria-hidden="true" tabindex="-1"></a> id<span class="op">:</span> <span class="dt">i32</span><span class="op">,</span></span>
<span id="cb9-8"><a href="#cb9-8" aria-hidden="true" tabindex="-1"></a><span class="op">}</span></span>
<span id="cb9-9"><a href="#cb9-9" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb9-10"><a href="#cb9-10" aria-hidden="true" tabindex="-1"></a><span class="pp">users::</span>table<span class="op">.</span>select(<span class="pp">UserWithName::</span>as_select())<span class="op">.</span>load(<span class="op">&</span><span class="kw">mut</span> conn)<span class="op">?;</span></span></code></pre></div>
<aside class="notes">
<ul>
<li><code>Selectable</code> needs to know about the table + column names
<ul>
<li>By default infered via the struct name + field names</li>
<li>field name == column name</li>
<li>table name == struct name + ‘s’</li>
<li>Can be explicitly annotated if different</li>
</ul></li>
<li><code>check_for_backend</code> optional, but greatly improves error
messages
<ul>
<li>Error message point to struct field instead of query</li>
</ul></li>
<li>No equivalent tuple variant here, as it’s now coupled to the
struct</li>
<li><code>Selectable</code> also allows more complex select expressions
via attributes, check the documentation for more details</li>
</ul>
</aside>
</section>
<section id="result-mapping-6" class="slide level3">
<h3>Result Mapping</h3>
<pre><code>error[E0277]: cannot deserialize a value of the database type `diesel::sql_types::Integer` as `*const str`
--> src/main.rs:16:9
|
16 | id: String,
| ^^^^^^ the trait `FromSql<diesel::sql_types::Integer, Sqlite>` is not implemented for `*const str`, which is required by `String: FromSqlRow<diesel::sql_types::Integer, Sqlite>`
|
= note: double check your type mappings via the documentation of `diesel::sql_types::Integer`
= help: the trait `FromSql<diesel::sql_types::Text, Sqlite>` is implemented for `*const str`
= help: for that trait implementation, expected `diesel::sql_types::Text`, found `diesel::sql_types::Integer`
= note: required for `String` to implement `FromSql<diesel::sql_types::Integer, Sqlite>`
= note: required for `String` to implement `diesel::Queryable<diesel::sql_types::Integer, Sqlite>`
= note: required for `String` to implement `FromSqlRow<diesel::sql_types::Integer, Sqlite>`</code></pre>
<aside class="notes">
<ul>
<li><p>Much clearer now, points directly to the field</p></li>
<li><p>(Still not optimal, other features in the rust compiler in the
pipeline to improve it further)</p></li>
</ul>
</aside>
</section>
<section id="select-statements-select-clause" class="slide level3">
<h3>Select Statements (Select Clause)</h3>
<div class="sourceCode" id="cb11"><pre
class="sourceCode rust"><code class="sourceCode rust"><span id="cb11-1"><a href="#cb11-1" aria-hidden="true" tabindex="-1"></a><span class="pp">users::</span>table<span class="op">.</span>select((</span>
<span id="cb11-2"><a href="#cb11-2" aria-hidden="true" tabindex="-1"></a> <span class="pp">users::</span>id<span class="op">,</span> <span class="co">// select a column,</span></span>
<span id="cb11-3"><a href="#cb11-3" aria-hidden="true" tabindex="-1"></a> <span class="co">// select clause part based on selectable</span></span>
<span id="cb11-4"><a href="#cb11-4" aria-hidden="true" tabindex="-1"></a> <span class="pp">UserWithName::</span>as_select()<span class="op">,</span> </span>
<span id="cb11-5"><a href="#cb11-5" aria-hidden="true" tabindex="-1"></a> <span class="pp">users::</span>id <span class="op">+</span> <span class="pp">users::</span>id<span class="op">,</span> <span class="co">// arbitary expressions</span></span>
<span id="cb11-6"><a href="#cb11-6" aria-hidden="true" tabindex="-1"></a> <span class="pp">diesel::dsl::</span>date(<span class="pp">diesel::dsl::</span>now)<span class="op">,</span> <span class="co">// SQL function calls</span></span>
<span id="cb11-7"><a href="#cb11-7" aria-hidden="true" tabindex="-1"></a> <span class="st">"abc"</span><span class="op">.</span><span class="pp">into_sql::</span><span class="op"><</span>Text<span class="op">></span>(()<span class="op">,</span> <span class="co">// constants</span></span>
<span id="cb11-8"><a href="#cb11-8" aria-hidden="true" tabindex="-1"></a>))</span></code></pre></div>
<ul>
<li>If not provided a default select clause with all columns of the
table is generated</li>
<li>Calling it twice will replace the select clause</li>
</ul>
<aside class="notes">
<ul>
<li><code>QueryDsl::select</code> allows to customize the select
clause</li>
<li>Flexible in what it accepts
<ul>
<li>Columns via the DSL provided by the table</li>
<li>Select expressions based on selectable</li>
<li>arbitary expressions -> Generates “expected” SQL</li>
<li>SQL function calls</li>
<li>Constants</li>
<li>Mixes of all above</li>
</ul></li>
<li>You can also use all of this via <code>Selectable</code></li>
<li>Allows to write targeted queries matching your use case</li>
<li>Allows to skip loading unneeded data</li>
</ul>
</aside>
</section>
<section id="select-statements-where-clause" class="slide level3">
<h3>Select Statements (Where Clause)</h3>
<div class="sourceCode" id="cb12"><pre
class="sourceCode rust"><code class="sourceCode rust"><span id="cb12-1"><a href="#cb12-1" aria-hidden="true" tabindex="-1"></a><span class="pp">users::</span>table</span>
<span id="cb12-2"><a href="#cb12-2" aria-hidden="true" tabindex="-1"></a> <span class="op">.</span>filter(<span class="pp">users::</span>id<span class="op">.</span>eq(<span class="dv">42</span>))</span>
<span id="cb12-3"><a href="#cb12-3" aria-hidden="true" tabindex="-1"></a> <span class="op">.</span>filter(<span class="pp">users::</span>name<span class="op">.</span>like(<span class="st">"%John%"</span>))</span>
<span id="cb12-4"><a href="#cb12-4" aria-hidden="true" tabindex="-1"></a> <span class="op">.</span>or_filter(<span class="pp">users::</span>name<span class="op">.</span>is_null())</span></code></pre></div>
<ul>
<li>If not provided, no where clause is generated</li>
<li>Calling it twice will append the second call with an
<code>AND</code> expression</li>
<li>Filter conditions can be arbitrary expressions that evaluate to
booleans</li>
<li>See various <code>ExpressionMethods</code> for expressions</li>
</ul>
<aside class="notes">
<ul>
<li>Allows to construct where clauses step by step</li>
<li>Allows chaining</li>
<li>Will talk about expressions later in detail</li>
</ul>
</aside>
</section>
<section id="select-statements-order-clauses" class="slide level3">
<h3>Select Statements (Order Clauses)</h3>
<div class="sourceCode" id="cb13"><pre
class="sourceCode rust"><code class="sourceCode rust"><span id="cb13-1"><a href="#cb13-1" aria-hidden="true" tabindex="-1"></a><span class="pp">users::</span>table<span class="op">.</span>order_by(<span class="pp">users::</span>id)</span>
<span id="cb13-2"><a href="#cb13-2" aria-hidden="true" tabindex="-1"></a> <span class="op">.</span>then_order_by((<span class="pp">users::</span>age<span class="op">.</span>desc()<span class="op">,</span> <span class="pp">users::</span>name))</span></code></pre></div>
<ul>
<li>If not provided, no order clause is generated</li>
<li>Accepts a single or multiple expressions</li>
<li>Default order is <code>ASC</code></li>
</ul>
<aside class="notes">
<ul>
<li><code>then_order_by</code> allows chaining, <code>order_by</code>
replaces existing clause</li>
<li><code>asc()</code> also exists for those that want to be
explicit</li>
</ul>
</aside>
</section>
<section id="select-statements-other-methods" class="slide level3">
<h3>Select Statements (Other Methods)</h3>
<div class="sourceCode" id="cb14"><pre
class="sourceCode rust"><code class="sourceCode rust"><span id="cb14-1"><a href="#cb14-1" aria-hidden="true" tabindex="-1"></a><span class="pp">users::</span>table</span>
<span id="cb14-2"><a href="#cb14-2" aria-hidden="true" tabindex="-1"></a> <span class="op">.</span>group_by(<span class="pp">users::</span>id)</span>
<span id="cb14-3"><a href="#cb14-3" aria-hidden="true" tabindex="-1"></a> <span class="op">.</span>limit(<span class="dv">1</span>)</span>
<span id="cb14-4"><a href="#cb14-4" aria-hidden="true" tabindex="-1"></a> <span class="op">.</span>offset(<span class="dv">5</span>)</span></code></pre></div>
<ul>
<li>Many other <code>QueryDsl</code> methods for other clauses</li>
<li><code>GROUP BY</code> is special, as it adds additional
requirements</li>
</ul>
<aside class="notes">
<ul>
<li>Not shown here: Having clauses, lock clauses, or distinct</li>
<li>Behaviour similar to previous examples, use as required for your
use-cases</li>
<li><code>GROUP BY</code> checked at compile time, will disallow mixed
aggregate selects
<ul>
<li>E.g. group by name, won’t allow you to select id outside of
aggregate expressions (min/max/etc)</li>
</ul></li>
</ul>
</aside>
</section>
<section id="expressions" class="slide level3">
<h3>Expressions</h3>
<ul>
<li>Everything in Diesel is an expression</li>
<li>Expressions can be combined in all locations</li>
<li>Diesel provides a lot of functions/trait methods to construct
expressions</li>
<li>Diesel also overloads numeric operators (<code>+-*/</code>) so that
they can be used to combine expressions</li>
<li>All of them translate essentially literally to the underlying
SQL</li>
</ul>
</section>
<section id="expressions-1" class="slide level3">
<h3>Expressions</h3>
<div class="sourceCode" id="cb15"><pre
class="sourceCode rust"><code class="sourceCode rust"><span id="cb15-1"><a href="#cb15-1" aria-hidden="true" tabindex="-1"></a><span class="pp">users::</span>table</span>
<span id="cb15-2"><a href="#cb15-2" aria-hidden="true" tabindex="-1"></a> <span class="op">.</span>filter(<span class="pp">users::</span>my_boolean_column) </span>
<span id="cb15-3"><a href="#cb15-3" aria-hidden="true" tabindex="-1"></a> <span class="op">.</span>filter(<span class="pp">users::</span>id<span class="op">.</span>eq(<span class="dv">42</span>)) </span>
<span id="cb15-4"><a href="#cb15-4" aria-hidden="true" tabindex="-1"></a> <span class="op">.</span>filter(<span class="pp">users::</span>id<span class="op">.</span>(<span class="pp">users::</span>age <span class="op">+</span> <span class="pp">users::</span>age)) </span>
<span id="cb15-5"><a href="#cb15-5" aria-hidden="true" tabindex="-1"></a> <span class="op">.</span>filter(<span class="dv">42</span><span class="op">.</span><span class="pp">into_sql::</span><span class="op"><</span>Integer<span class="op">></span>()<span class="op">.</span>eq(<span class="pp">users::</span>id)) </span>
<span id="cb15-6"><a href="#cb15-6" aria-hidden="true" tabindex="-1"></a> <span class="op">.</span>filter(<span class="pp">users::</span>id<span class="op">.</span>eq(<span class="pp">users::</span>id <span class="op">+</span> <span class="dv">5</span>)<span class="op">.</span>eq(<span class="cn">false</span>)) </span>
<span id="cb15-7"><a href="#cb15-7" aria-hidden="true" tabindex="-1"></a> <span class="op">.</span>filter(<span class="pp">users::</span>id<span class="op">.</span>eq_any(</span>
<span id="cb15-8"><a href="#cb15-8" aria-hidden="true" tabindex="-1"></a> <span class="pp">posts::</span>table</span>
<span id="cb15-9"><a href="#cb15-9" aria-hidden="true" tabindex="-1"></a> <span class="op">.</span>filter(<span class="pp">posts::</span>name<span class="op">.</span>eq(<span class="st">"My fancy post"</span>))</span>
<span id="cb15-10"><a href="#cb15-10" aria-hidden="true" tabindex="-1"></a> <span class="op">.</span>select(<span class="pp">posts::</span>user_id)</span>
<span id="cb15-11"><a href="#cb15-11" aria-hidden="true" tabindex="-1"></a> )) </span></code></pre></div>
<aside class="notes">
<ul>
<li>Expressions are data type (SQL side) dependent</li>
<li>Not all data types have all kinds of expressions
<ul>
<li>No <code>like</code> for <code>Integer</code> expressions</li>
<li>No <code>-</code> for <code>Text</code> expressions</li>
</ul></li>
<li>Diesel always keeps track of the SQL side type of the expression and
only allows combinations that result in a valid type</li>
<li>Possible to mix and match expressions as required (as you would do
in SQL)</li>
<li>Rust values (like <code>42</code>) translate to bind values, so they
are safe from SQL injection issues</li>
</ul>
</aside>
</section>
<section id="insert-statements" class="slide level3">
<h3>Insert Statements</h3>
<div class="sourceCode" id="cb16"><pre
class="sourceCode rust"><code class="sourceCode rust"><span id="cb16-1"><a href="#cb16-1" aria-hidden="true" tabindex="-1"></a><span class="pp">diesel::</span>insert_into(<span class="pp">users::</span>table)</span>
<span id="cb16-2"><a href="#cb16-2" aria-hidden="true" tabindex="-1"></a> <span class="op">.</span>values((</span>
<span id="cb16-3"><a href="#cb16-3" aria-hidden="true" tabindex="-1"></a> <span class="pp">users::</span>name<span class="op">.</span>eq(<span class="st">"John"</span>)<span class="op">,</span></span>
<span id="cb16-4"><a href="#cb16-4" aria-hidden="true" tabindex="-1"></a> <span class="pp">users::</span>age<span class="op">.</span>eq(<span class="dv">42</span>)<span class="op">,</span></span>
<span id="cb16-5"><a href="#cb16-5" aria-hidden="true" tabindex="-1"></a> ))<span class="op">.</span>execute(<span class="op">&</span><span class="kw">mut</span> conn)<span class="op">?;</span></span></code></pre></div>
<ul>
<li>Creates and executes an <code>INSERT INTO table</code>
statement</li>
</ul>
<aside class="notes">
<ul>
<li>Allows to specify the insert values as tuple</li>
<li>Right hand side of the assignment can be arbitrary expression</li>
<li>Again: Diesel ensures that types match, so you cannot insert a
<code>i32</code> into a <code>Text</code> column</li>
<li>We do not yet check if you provide all non-null columns without
default</li>
<li>Also possible to perform INSERT FROM SELECT statements, check the
documentation for that</li>
</ul>
</aside>
</section>
<section id="insert-statements-1" class="slide level3">
<h3>Insert Statements</h3>
<div class="sourceCode" id="cb17"><pre
class="sourceCode rust"><code class="sourceCode rust"><span id="cb17-1"><a href="#cb17-1" aria-hidden="true" tabindex="-1"></a><span class="at">#[</span>derive<span class="at">(</span>Insertable<span class="at">)]</span></span>
<span id="cb17-2"><a href="#cb17-2" aria-hidden="true" tabindex="-1"></a><span class="at">#[</span>diesel<span class="at">(</span>table_name <span class="op">=</span> users<span class="at">)]</span></span>
<span id="cb17-3"><a href="#cb17-3" aria-hidden="true" tabindex="-1"></a><span class="kw">struct</span> NewUser <span class="op">{</span></span>
<span id="cb17-4"><a href="#cb17-4" aria-hidden="true" tabindex="-1"></a> name<span class="op">:</span> <span class="dt">String</span><span class="op">,</span></span>
<span id="cb17-5"><a href="#cb17-5" aria-hidden="true" tabindex="-1"></a> age<span class="op">:</span> <span class="dt">i32</span><span class="op">,</span></span>
<span id="cb17-6"><a href="#cb17-6" aria-hidden="true" tabindex="-1"></a><span class="op">}</span></span>
<span id="cb17-7"><a href="#cb17-7" aria-hidden="true" tabindex="-1"></a><span class="kw">let</span> values<span class="op">:</span> <span class="dt">Vec</span><span class="op"><</span>NewUser<span class="op">></span> <span class="op">=</span> <span class="co">/* … */</span><span class="op">;</span></span>
<span id="cb17-8"><a href="#cb17-8" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb17-9"><a href="#cb17-9" aria-hidden="true" tabindex="-1"></a><span class="pp">diesel::</span>insert_into(<span class="pp">users::</span>table)</span>
<span id="cb17-10"><a href="#cb17-10" aria-hidden="true" tabindex="-1"></a> <span class="op">.</span>values(values)</span>
<span id="cb17-11"><a href="#cb17-11" aria-hidden="true" tabindex="-1"></a> <span class="op">.</span>returning(<span class="pp">User::</span>as_returning())</span>
<span id="cb17-12"><a href="#cb17-12" aria-hidden="true" tabindex="-1"></a> <span class="op">.</span>get_results(<span class="op">&</span><span class="kw">mut</span> conn)<span class="op">?;</span></span></code></pre></div>
<ul>
<li>Insert via struct possible</li>
<li>Prefer this variant if the struct already exists</li>
</ul>
<aside class="notes">
<ul>
<li><p>We can also use a struct for inserting values</p></li>
<li><p>Prefer if the struct already exists, e.g. for json
deserialization</p></li>
<li><p>Loading and storing values is decoupled so you can use different
types for that</p></li>
<li><p>Types for input and output will differ over time in large
applications</p></li>
<li><p>Batch inserts possible, by passing vector of values</p></li>
<li><p>That also works with the tuple variant</p></li>
<li><p>Returning enables returning the inserted values (if supported by
the database system)</p></li>
<li><p>Same mapping rules as for select statements apply</p></li>
</ul>
</aside>
</section>
<section id="insert-statements-2" class="slide level3">
<h3>Insert Statements</h3>
<div class="sourceCode" id="cb18"><pre
class="sourceCode rust"><code class="sourceCode rust"><span id="cb18-1"><a href="#cb18-1" aria-hidden="true" tabindex="-1"></a><span class="at">#[</span>derive<span class="at">(</span>Insertable<span class="at">)]</span></span>
<span id="cb18-2"><a href="#cb18-2" aria-hidden="true" tabindex="-1"></a><span class="at">#[</span>diesel<span class="at">(</span>table_name <span class="op">=</span> users<span class="at">)]</span></span>
<span id="cb18-3"><a href="#cb18-3" aria-hidden="true" tabindex="-1"></a><span class="kw">struct</span> NewUser <span class="op">{</span></span>
<span id="cb18-4"><a href="#cb18-4" aria-hidden="true" tabindex="-1"></a> name<span class="op">:</span> <span class="dt">String</span><span class="op">,</span></span>
<span id="cb18-5"><a href="#cb18-5" aria-hidden="true" tabindex="-1"></a> age<span class="op">:</span> <span class="dt">i32</span><span class="op">,</span></span>
<span id="cb18-6"><a href="#cb18-6" aria-hidden="true" tabindex="-1"></a><span class="op">}</span></span>
<span id="cb18-7"><a href="#cb18-7" aria-hidden="true" tabindex="-1"></a><span class="kw">let</span> value <span class="op">=</span> NewUser <span class="op">{</span> <span class="co">/*…*/</span> <span class="op">};</span></span>
<span id="cb18-8"><a href="#cb18-8" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb18-9"><a href="#cb18-9" aria-hidden="true" tabindex="-1"></a><span class="pp">diesel::</span>insert_into(<span class="pp">users::</span>table)</span>
<span id="cb18-10"><a href="#cb18-10" aria-hidden="true" tabindex="-1"></a> <span class="op">.</span>values((value<span class="op">,</span> <span class="pp">users::</span>id<span class="op">.</span>eq(<span class="dv">42</span>)))</span>
<span id="cb18-11"><a href="#cb18-11" aria-hidden="true" tabindex="-1"></a> <span class="op">.</span>execute(<span class="op">&</span><span class="kw">mut</span> conn)<span class="op">;</span></span></code></pre></div>
<aside class="notes">
<ul>
<li>Also possible to mix struct + tuple values</li>
<li>Useful for adding values server side to a set of user provided
values, e.g. hash password before inserting</li>
</ul>
</aside>
</section>
<section id="update-statements" class="slide level3">
<h3>Update Statements</h3>
<div class="sourceCode" id="cb19"><pre
class="sourceCode rust"><code class="sourceCode rust"><span id="cb19-1"><a href="#cb19-1" aria-hidden="true" tabindex="-1"></a><span class="pp">diesel::</span>update(<span class="pp">users::</span>table<span class="op">.</span>find(<span class="dv">42</span>))</span>
<span id="cb19-2"><a href="#cb19-2" aria-hidden="true" tabindex="-1"></a> <span class="op">.</span>set((</span>
<span id="cb19-3"><a href="#cb19-3" aria-hidden="true" tabindex="-1"></a> <span class="pp">users::</span>name<span class="op">.</span>eq(<span class="st">"Jane"</span>)<span class="op">,</span></span>
<span id="cb19-4"><a href="#cb19-4" aria-hidden="true" tabindex="-1"></a> <span class="pp">users::</span>age<span class="op">.</span>eq(<span class="pp">users::</span>age <span class="op">+</span> <span class="dv">5</span><span class="op">.</span><span class="pp">into_sql::</span><span class="op"><</span>Integer<span class="op">></span>())</span>
<span id="cb19-5"><a href="#cb19-5" aria-hidden="true" tabindex="-1"></a> ))<span class="op">.</span>execute(<span class="op">&</span><span class="kw">mut</span> conn)<span class="op">?;</span></span></code></pre></div>
<ul>
<li>Creates a <code>UPDATE table</code> statement</li>
<li><code>find</code> is a shorthand for
<code>.filter(users::id.eq(42))</code></li>
</ul>
<aside class="notes">
<ul>
<li>Can also use different or no filter/find calls</li>
<li>Right hand side of the assignment can be arbitrary expression</li>
<li>Again: Diesel ensures that types match, so you cannot insert a
<code>i32</code> into a <code>Text</code> column</li>
</ul>
</aside>
</section>
<section id="update-statements-1" class="slide level3">
<h3>Update Statements</h3>
<div class="sourceCode" id="cb20"><pre
class="sourceCode rust"><code class="sourceCode rust"><span id="cb20-1"><a href="#cb20-1" aria-hidden="true" tabindex="-1"></a><span class="at">#[</span>derive<span class="at">(</span>AsChangeset<span class="op">,</span> Identifiable<span class="at">)]</span></span>
<span id="cb20-2"><a href="#cb20-2" aria-hidden="true" tabindex="-1"></a><span class="at">#[</span>diesel<span class="at">(</span>table_name <span class="op">=</span> users<span class="at">)]</span></span>
<span id="cb20-3"><a href="#cb20-3" aria-hidden="true" tabindex="-1"></a><span class="kw">struct</span> UserChangeset <span class="op">{</span></span>
<span id="cb20-4"><a href="#cb20-4" aria-hidden="true" tabindex="-1"></a> id<span class="op">:</span> <span class="dt">i32</span><span class="op">,</span></span>
<span id="cb20-5"><a href="#cb20-5" aria-hidden="true" tabindex="-1"></a> name<span class="op">:</span> <span class="dt">String</span><span class="op">,</span></span>
<span id="cb20-6"><a href="#cb20-6" aria-hidden="true" tabindex="-1"></a> age<span class="op">:</span> <span class="dt">i32</span><span class="op">,</span></span>
<span id="cb20-7"><a href="#cb20-7" aria-hidden="true" tabindex="-1"></a><span class="op">}</span></span>
<span id="cb20-8"><a href="#cb20-8" aria-hidden="true" tabindex="-1"></a><span class="kw">let</span> value <span class="op">=</span> UserChangeset <span class="op">{</span> <span class="co">/*…*/</span> <span class="op">};</span></span>
<span id="cb20-9"><a href="#cb20-9" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb20-10"><a href="#cb20-10" aria-hidden="true" tabindex="-1"></a><span class="pp">diesel::</span>update(<span class="op">&</span>value)</span>
<span id="cb20-11"><a href="#cb20-11" aria-hidden="true" tabindex="-1"></a> <span class="op">.</span>set(<span class="op">&</span>value)</span>
<span id="cb20-12"><a href="#cb20-12" aria-hidden="true" tabindex="-1"></a> <span class="op">.</span>returning(<span class="pp">users::</span>id)</span>
<span id="cb20-13"><a href="#cb20-13" aria-hidden="true" tabindex="-1"></a> <span class="op">.</span><span class="pp">get_result::</span><span class="op"><</span><span class="dt">i32</span><span class="op">></span>(<span class="op">&</span><span class="kw">mut</span> conn)<span class="op">?;</span></span></code></pre></div>
<ul>
<li>Again: A struct can be used as input</li>
<li>Prefer this variant if the struct already exists</li>
<li>Automatic filter by id via <code>Identifiable</code></li>
</ul>
<aside class="notes">
<ul>
<li><code>Identifiable</code> allows the <code>update</code> function to
automatically construct a <code>find(id)</code> filter</li>
<li><code>id</code> is not updated</li>
<li>Returning allows again to return values</li>
<li>Possible to mix struct + tuple variant similar to what is possible
for inserts</li>
<li></li>
</ul>
</aside>
</section>
<section id="delete-statements" class="slide level3">
<h3>Delete Statements</h3>
<div class="sourceCode" id="cb21"><pre
class="sourceCode rust"><code class="sourceCode rust"><span id="cb21-1"><a href="#cb21-1" aria-hidden="true" tabindex="-1"></a><span class="pp">diesel::</span>delete(<span class="pp">users::</span>table<span class="op">.</span>find(<span class="dv">42</span>))<span class="op">.</span>execute(<span class="op">&</span><span class="kw">mut</span> conn)<span class="op">?;</span></span></code></pre></div>
<ul>
<li>Constructs a <code>DELETE FROM table</code> statement</li>
<li>Filter is optional like for updates</li>
</ul>
<aside class="notes">
<ul>
<li>If you skip the filter you delete all entry in your table</li>
<li>Also accepts something that implements
<code>Identifiable</code></li>
</ul>
</aside>
</section>
<section id="raw-sql-queries" class="slide level3">
<h3>Raw SQL Queries</h3>
<div class="sourceCode" id="cb22"><pre
class="sourceCode rust"><code class="sourceCode rust"><span id="cb22-1"><a href="#cb22-1" aria-hidden="true" tabindex="-1"></a><span class="at">#[</span>derive<span class="at">(</span>QueryableByName<span class="at">)]</span></span>
<span id="cb22-2"><a href="#cb22-2" aria-hidden="true" tabindex="-1"></a><span class="at">#[</span>diesel<span class="at">(</span>table_name <span class="op">=</span> users<span class="at">)]</span></span>
<span id="cb22-3"><a href="#cb22-3" aria-hidden="true" tabindex="-1"></a><span class="kw">struct</span> User <span class="op">{</span></span>
<span id="cb22-4"><a href="#cb22-4" aria-hidden="true" tabindex="-1"></a> id<span class="op">:</span> <span class="dt">i32</span><span class="op">,</span></span>
<span id="cb22-5"><a href="#cb22-5" aria-hidden="true" tabindex="-1"></a> name<span class="op">:</span> <span class="dt">String</span><span class="op">,</span></span>
<span id="cb22-6"><a href="#cb22-6" aria-hidden="true" tabindex="-1"></a><span class="op">}</span></span>
<span id="cb22-7"><a href="#cb22-7" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb22-8"><a href="#cb22-8" aria-hidden="true" tabindex="-1"></a><span class="pp">diesel::</span>sql_query(<span class="st">"SELECT id, name FROM users WHERE name = $1"</span>)</span>
<span id="cb22-9"><a href="#cb22-9" aria-hidden="true" tabindex="-1"></a> <span class="op">.</span><span class="pp">bind::</span><span class="op"><</span>Text<span class="op">,</span> _<span class="op">></span>(<span class="st">"Jane"</span>)</span>
<span id="cb22-10"><a href="#cb22-10" aria-hidden="true" tabindex="-1"></a> <span class="op">.</span><span class="pp">load::</span><span class="op"><</span>User<span class="op">></span>(<span class="op">&</span><span class="kw">mut</span> conn)<span class="op">?;</span></span></code></pre></div>