-
Notifications
You must be signed in to change notification settings - Fork 1
Expand file tree
/
Copy path2021-03-13_Everything-You-Need-To-Know-About-Relational-Databases--SQL--PostgreSQL-and-Sequelize-To-Build--8acb68284a98.html
More file actions
1244 lines (1241 loc) · 115 KB
/
2021-03-13_Everything-You-Need-To-Know-About-Relational-Databases--SQL--PostgreSQL-and-Sequelize-To-Build--8acb68284a98.html
File metadata and controls
1244 lines (1241 loc) · 115 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
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 http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Everything You Need To Know About Relational Databases, SQL, PostgreSQL and Sequelize To Build…</title>
<link rel="stylesheet" href="./style.css">
</head>
<body>
<article class="h-entry">
<header>
<h1 class="p-name">Everything You Need To Know About Relational Databases, SQL, PostgreSQL and Sequelize To
Build…</h1>
</header>
<section data-field="subtitle" class="p-summary">
For Front end developers who like myself struggle with making the jump to fullstack.
</section>
<section data-field="body" class="e-content">
<section name="1929" class="section section--body section--first">
<div class="section-divider">
<hr class="section-divider">
</div>
<div class="section-content">
<div class="section-inner sectionLayout--insetColumn">
<h4 name="3e7c" id="3e7c" class="graf graf--h4 graf--leading graf--kicker"><a
href="http://medium.com/codex" data-href="http://medium.com/codex"
class="markup--anchor markup--h4-anchor">CODEX</a></h4>
<h3 name="bb85" id="bb85" class="graf graf--h3 graf-after--h4 graf--title"><strong
class="markup--strong markup--h3-strong">Everything You Need To Know About Relational Databases, SQL,
PostgreSQL and Sequelize To Build Your Backend!</strong></h3>
<p name="9e16" id="9e16" class="graf graf--p graf-after--h3"><strong
class="markup--strong markup--p-strong">For Front end developers who like myself struggle with making
the jump to fullstack.</strong></p>
<p name="9b77" id="9b77" class="graf graf--p graf-after--p">You can access and query the data using the
findByPk, findOne, and findAll methods.</p>
<p name="de32" id="de32" class="graf graf--p graf-after--p"><strong
class="markup--strong markup--p-strong">Terminology:</strong></p>
<ul class="postList">
<li name="c63a" id="c63a" class="graf graf--li graf-after--p"><a href="https://nodejs.org/en/"
data-href="https://nodejs.org/en/" class="markup--anchor markup--li-anchor" rel="noopener"
target="_blank">NodeJS</a> We re going to use this to run JavaScript code on the server. I ve
decided to use the latest version of Node, v6.3.0 at the time of writing, so that we ll have access to
most of the new features introduced in ES6.</li>
<li name="11a6" id="11a6" class="graf graf--li graf-after--li"><a href="https://expressjs.com/"
data-href="https://expressjs.com/" class="markup--anchor markup--li-anchor" rel="noopener"
target="_blank">Express</a> As per their website, Express is a Fast, unopinionated, minimalist web
framework for Node.js , that we re going to be building our Todo list application on.</li>
<li name="5808" id="5808" class="graf graf--li graf-after--li"><a
href="https://www.postgresql.org/docs/9.5/static/index.html"
data-href="https://www.postgresql.org/docs/9.5/static/index.html"
class="markup--anchor markup--li-anchor" rel="noopener" target="_blank">PostgreSQL</a> This is a
powerful open-source database that we re going to use. I ve attached an article I published on the
setup below!</li>
</ul>
<p name="fce6" id="fce6" class="graf graf--p graf-after--li"><a
href="https://bryanguner.medium.com/postgresql-setup-for-windows-wsl-ubuntu-801672ab7089"
data-href="https://bryanguner.medium.com/postgresql-setup-for-windows-wsl-ubuntu-801672ab7089"
class="markup--anchor markup--p-anchor"
title="https://bryanguner.medium.com/postgresql-setup-for-windows-wsl-ubuntu-801672ab7089"
rel="noopener" target="_blank"><strong class="markup--strong markup--p-strong">PostgreSQL Setup For
Windows & WSL/Ubuntu</strong><br><em class="markup--em markup--p-em">If you follow this guide to
a tee you will install PostgreSQL itself on your Windows installation. Then, you will
</em>bryanguner.medium.com</a></p>
<ul class="postList">
<li name="bb49" id="bb49" class="graf graf--li graf-after--p">However, if you face issues while
installing PostgreSQL, or you don t want to dive into installing it, you can opt for a version of
PostgreSQL hosted online. I recommend <a href="https://www.elephantsql.com/"
data-href="https://www.elephantsql.com/" class="markup--anchor markup--li-anchor" rel="noopener"
target="_blank">ElephantSQL</a>. I found it s pretty easy to get started with. However, the free
version will only give you a 20MB allowance.</li>
<li name="539a" id="539a" class="graf graf--li graf-after--li"><a
href="http://docs.sequelizejs.com/en/latest/" data-href="http://docs.sequelizejs.com/en/latest/"
class="markup--anchor markup--li-anchor" rel="noopener" target="_blank">Sequelize</a> In addition,
we re going to use Sequelize, which is a database <a
href="https://en.wikipedia.org/wiki/Object-relational_mapping"
data-href="https://en.wikipedia.org/wiki/Object-relational_mapping"
class="markup--anchor markup--li-anchor" rel="noopener" target="_blank">ORM</a> that will interface
with the Postgres database for us.</li>
</ul>
<p name="9e96" id="9e96" class="graf graf--p graf-after--li"><strong
class="markup--strong markup--p-strong">RDBMS and Database Entities</strong></p>
<p name="821c" id="821c" class="graf graf--p graf-after--p"><strong
class="markup--strong markup--p-strong">Define what a relational database management system
is</strong></p>
<ul class="postList">
<li name="6529" id="6529" class="graf graf--li graf-after--p">RDBMS stands for Relational Database
Management System</li>
<li name="6d7e" id="6d7e" class="graf graf--li graf-after--li">A software application that you run that
your programs can connect to so that they can store, modify, and retrieve data.</li>
<li name="79d9" id="79d9" class="graf graf--li graf-after--li">An RDBMS can track many databases. We
will use PostgreSQL, or postgres , primarily for our RDBMS and it will be able to create individual
databases for each of our projects.</li>
</ul>
<p name="c849" id="c849" class="graf graf--p graf-after--li"><strong
class="markup--strong markup--p-strong">Describe what relational data is</strong></p>
<ul class="postList">
<li name="d8fd" id="d8fd" class="graf graf--li graf-after--p">In general, relational data is information
that is connected to other pieces of information.</li>
<li name="2fa8" id="2fa8" class="graf graf--li graf-after--li">When working with relational databases,
we can connect two entries together utilizing foreign keys (explained below).</li>
<li name="e199" id="e199" class="graf graf--li graf-after--li">In a pets database, we could be keeping
track of dogs and cats as well as the toys that each of them own. That ownership of a cat to a toy is
the relational aspect of relational data. Two pieces of information that can be connected together to
show some sort of meaning.</li>
</ul>
<p name="bbc1" id="bbc1" class="graf graf--p graf-after--li"><strong
class="markup--strong markup--p-strong">Define what a database is</strong></p>
<ul class="postList">
<li name="06fe" id="06fe" class="graf graf--li graf-after--p">The actual location that data is stored.
</li>
<li name="78c5" id="78c5" class="graf graf--li graf-after--li">A database can be made up of many tables
that each store specific kinds of information.</li>
<li name="f752" id="f752" class="graf graf--li graf-after--li">We could have a pets database that stores
information about many different types of animals. Each animal type could potentially be represented
by a different table.</li>
</ul>
<p name="6a60" id="6a60" class="graf graf--p graf-after--li"><strong
class="markup--strong markup--p-strong">Define what a database table is</strong></p>
<ul class="postList">
<li name="04ae" id="04ae" class="graf graf--li graf-after--p">Within a database, a table stores one
specific kind of information.</li>
<li name="3878" id="3878" class="graf graf--li graf-after--li">The records (entries) on these tables can
be connected to records on other tables through the use of foreign keys</li>
<li name="d6f9" id="d6f9" class="graf graf--li graf-after--li">In our pets database, we could have a
dogs table, with individual records</li>
</ul>
<p name="35d3" id="35d3" class="graf graf--p graf-after--li"><strong
class="markup--strong markup--p-strong">Describe the purpose of a primary key</strong></p>
<ul class="postList">
<li name="4d0e" id="4d0e" class="graf graf--li graf-after--p">A primary key is used in the database as a
unique identifier for the table.</li>
<li name="8fb0" id="8fb0" class="graf graf--li graf-after--li">We often use an id field that simply
increments with each entry. The incrementing ensures that each record has a unique identifier, even if
their are other fields of the record that are repeated (two people with the same name would still need
to have a unique identifier, for example).</li>
<li name="8876" id="8876" class="graf graf--li graf-after--li">With a unique identifier, we can easily
connect records within the table to records from other tables.</li>
</ul>
<p name="8341" id="8341" class="graf graf--p graf-after--li"><strong
class="markup--strong markup--p-strong">Describe the purpose of a foreign key</strong></p>
<ul class="postList">
<li name="cf54" id="cf54" class="graf graf--li graf-after--p">A foreign key is used as the connector
from this record to the primary key of another table s record.</li>
<li name="f397" id="f397" class="graf graf--li graf-after--li">In our pets example, we can imagine two
tables to demonstrate: a table to represent cats and a table to represent toys. Each of these tables
has a primary key of id that is used as the unique identifier. In order to make a connection between a
toy and a cat, we can add another field to the cat table called owner_id , indicating that it is a
foreign key for the cat table. By setting a toy s owner_id to the same value as a particular cat s
id , we can indicate that the cat is the owner of that toy.</li>
</ul>
<p name="004a" id="004a" class="graf graf--p graf-after--li"><strong
class="markup--strong markup--p-strong">Describe how to properly name things in PostgreSQL</strong>
</p>
<ul class="postList">
<li name="d6e6" id="d6e6" class="graf graf--li graf-after--p">Names within postgres should generally
consist of only lowercase letters, numbers, and underscores.</li>
<li name="0fb3" id="0fb3" class="graf graf--li graf-after--li">Tables within a database are plural by
convention, so a table for cats would typically be cats and office locations would be office_locations
(all lowercase, underscores to replace spaces, plural)</li>
</ul>
<p name="63da" id="63da" class="graf graf--p graf-after--li"><strong
class="markup--strong markup--p-strong">Connect to an instance of PostgreSQL with the command line
tool psql</strong></p>
<ul class="postList">
<li name="1b7f" id="1b7f" class="graf graf--li graf-after--p">The psql command by default will try to
connect to a database and username that matches your system s username</li>
<li name="1454" id="1454" class="graf graf--li graf-after--li">We connect to a different database by
providing an argument to the psql command</li>
<li name="3439" id="3439" class="graf graf--li graf-after--li">psql pets</li>
<li name="fb37" id="fb37" class="graf graf--li graf-after--li">To connect with a different username we
can use the -U flag followed by the username we would like to use. To connect to the pets database as
pets_user</li>
<li name="d357" id="d357" class="graf graf--li graf-after--li">psql -U pets_user pets</li>
<li name="e139" id="e139" class="graf graf--li graf-after--li">If there is a password for the user, we
can tell psql that we would like a prompt for the password to show up by using the -W flag.</li>
<li name="8709" id="8709" class="graf graf--li graf-after--li">psql -U pets_user -W pets (the order of
our flags doesn t matter, as long as any arguments associated with them are together, such as
pets_user directly following -U in this example)</li>
</ul>
<p name="2ca3" id="2ca3" class="graf graf--p graf-after--li"><strong
class="markup--strong markup--p-strong">Identify whether a user is a normal user or a superuser by the
prompt in the psql shell</strong></p>
<ul class="postList">
<li name="8ff5" id="8ff5" class="graf graf--li graf-after--p">You can tell if you are logged in as a
superuser or normal user by the prompt in the terminal.</li>
<li name="de97" id="de97" class="graf graf--li graf-after--li">If the prompt shows =>, the user is a
normal user</li>
<li name="7ce3" id="7ce3" class="graf graf--li graf-after--li">If the prompt show =#, the user is a
superuser</li>
</ul>
<p name="7d10" id="7d10" class="graf graf--p graf-after--li"><strong
class="markup--strong markup--p-strong">Create a user for the relational database management
system</strong></p>
<ul class="postList">
<li name="f4dc" id="f4dc" class="graf graf--li graf-after--p">Within psql, we can create a user with the
CREATE USER {username} {WITH options} command.</li>
<li name="7eda" id="7eda" class="graf graf--li graf-after--li">The most common options we ll want to use
are WITH PASSWORD ‘mypassword’ to provide a password for the user we are creating, CREATEDB to allow
the user to create new databases, or SUPERUSER to create a user with all elevated permissions.</li>
</ul>
<p name="10dc" id="10dc" class="graf graf--p graf-after--li"><strong
class="markup--strong markup--p-strong">Create a database in the database management system</strong>
</p>
<ul class="postList">
<li name="3fff" id="3fff" class="graf graf--li graf-after--p">We can use the command CREATE DATABASE
{database name} {options} inside psql to create a new database.</li>
<li name="7d2f" id="7d2f" class="graf graf--li graf-after--li">A popular option we may utilize is WITH
OWNER {owner name} to set another user as the owner of the database we are making.</li>
</ul>
<p name="aa78" id="aa78" class="graf graf--p graf-after--li"><strong
class="markup--strong markup--p-strong">Configure a database so that only the owner (and superusers)
can connect to it</strong></p>
<ul class="postList">
<li name="1a73" id="1a73" class="graf graf--li graf-after--p">We can GRANT and REVOKE privileges from a
database to users or categories of users.</li>
<li name="8795" id="8795" class="graf graf--li graf-after--li">In order to remove connection privileges
to a database from the public we can use REVOKE CONNECT ON DATABASE {db_name} FROM PUBLIC;, removing
all public connection access.</li>
<li name="ee98" id="ee98" class="graf graf--li graf-after--li">If we wanted to grant it back, or to a
specific user, we could similarly do GRANT CONNECT ON DATABASE {db_name} FROM {specific user, PUBLIC,
etc.};</li>
</ul>
<p name="c655" id="c655" class="graf graf--p graf-after--li"><strong
class="markup--strong markup--p-strong">View a list of databases in an installation of
PostgreSQL</strong></p>
<ul class="postList">
<li name="2111" id="2111" class="graf graf--li graf-after--p">To list all databases we can use the \l or
\list command in psql.</li>
</ul>
<p name="42b3" id="42b3" class="graf graf--p graf-after--li"><strong
class="markup--strong markup--p-strong">Create tables in a database</strong></p>
<pre name="7f71" id="7f71"
class="graf graf--pre graf-after--p">CREATE TABLE {table name} (<br>{columnA} {typeA},<br>{columnB} {typeB},<br>etc…<br>);</pre>
<ul class="postList">
<li name="a2fc" id="a2fc" class="graf graf--li graf-after--pre">The whitespace does not matter. Creating
the SQL statements on multiple lines is easier to read, but just like JavaScript, they can be
presented differently.</li>
<li name="a203" id="a203" class="graf graf--li graf-after--li">One common issue is that SQL does not
like trailing commas, so the last column cannot have a comma after its type in this example.</li>
</ul>
<p name="5b12" id="5b12" class="graf graf--p graf-after--li"><strong
class="markup--strong markup--p-strong">View a list of tables in a database</strong></p>
<ul class="postList">
<li name="964a" id="964a" class="graf graf--li graf-after--p">To list all database tables, use the \dt
command.</li>
</ul>
<p name="97cc" id="97cc" class="graf graf--p graf-after--li"><strong
class="markup--strong markup--p-strong">Identify and describe the common data types used in
PostgreSQL</strong></p>
<ul class="postList">
<li name="a2fd" id="a2fd" class="graf graf--li graf-after--p">There are many different data types that
we can use in our tables, here are some common examples:</li>
<li name="1d53" id="1d53" class="graf graf--li graf-after--li">SERIAL: autoincrementing, very useful for
IDs</li>
<li name="78ce" id="78ce" class="graf graf--li graf-after--li">VARCHAR(n): a string with a character
limit of n</li>
<li name="2e02" id="2e02" class="graf graf--li graf-after--li">TEXT: doesn t have character limit, but
less performant</li>
<li name="c20e" id="c20e" class="graf graf--li graf-after--li">BOOLEAN: true/false</li>
<li name="d525" id="d525" class="graf graf--li graf-after--li">SMALLINT: signed two-byte integer (-32768
to 32767)</li>
<li name="6b96" id="6b96" class="graf graf--li graf-after--li">INTEGER: signed four-byte integer
(standard)</li>
<li name="fa38" id="fa38" class="graf graf--li graf-after--li">BIGINT: signed eight-byte integer (very
large numbers)</li>
<li name="aa0c" id="aa0c" class="graf graf--li graf-after--li">NUMERIC: or DECIMAL, can store exact
decimal values</li>
<li name="1fb6" id="1fb6" class="graf graf--li graf-after--li">TIMESTAMP: date and time</li>
</ul>
<p name="ddde" id="ddde" class="graf graf--p graf-after--li"><strong
class="markup--strong markup--p-strong">Describe the purpose of the UNIQUE and NOT NULL constraints,
and create columns in database tables that have them</strong></p>
<ul class="postList">
<li name="377b" id="377b" class="graf graf--li graf-after--p">In addition to the data type, we can
provide flags for constraints to place on our column data.</li>
<li name="bda2" id="bda2" class="graf graf--li graf-after--li">The UNIQUE flag indicates that the data
for the column must not be repeated.</li>
<li name="e14c" id="e14c" class="graf graf--li graf-after--li">By default we can create entries in our
tables that are missing data from columns. When creating a pet, maybe we don t provide an age because
we don t know it, for example. If we want to require that the data be present in order to create a new
record, we can indicate that column must be NOT NULL.</li>
<li name="6745" id="6745" class="graf graf--li graf-after--li">In the example below, we are requiring
our pets to have unique names and for them to be present (both UNIQUE and NOT NULL). We have no such
constraints on the age column, allowing repetition of ages or their complete absence.</li>
</ul>
<pre name="598c" id="598c"
class="graf graf--pre graf-after--li">CREATE TABLE pets (<br>id SERIAL PRIMARY KEY,<br>name VARCHAR(255) UNIQUE NOT NULL,<br>age SMALLINT<br>);</pre>
<p name="3971" id="3971" class="graf graf--p graf-after--pre"><strong
class="markup--strong markup--p-strong">Create a primary key for a table</strong></p>
<ul class="postList">
<li name="da50" id="da50" class="graf graf--li graf-after--p">When creating a table we can indicate the
primary key by passing in the column name to parentheses like so:</li>
</ul>
<pre name="408c" id="408c"
class="graf graf--pre graf-after--li">CREATE TABLE people (<br>id SERIAL,<br>first_name VARCHAR(50),<br>last_name VARCHAR(50),<br>PRIMARY KEY (id)<br>);</pre>
<ul class="postList">
<li name="4871" id="4871" class="graf graf--li graf-after--pre">We could have also used the PRIMARY KEY
flag on the column definition itself:</li>
</ul>
<pre name="522c" id="522c"
class="graf graf--pre graf-after--li">CREATE TABLE people (<br>id SERIAL PRIMARY KEY,<br>first_name VARCHAR(50),<br>last_name VARCHAR(50)<br>);</pre>
<p name="c523" id="c523" class="graf graf--p graf-after--pre"><strong
class="markup--strong markup--p-strong">Create foreign key constraints to relate tables</strong></p>
<ul class="postList">
<li name="e576" id="e576" class="graf graf--li graf-after--p">In our table definition, we can use the
line FOREIGN KEY (foreign_key_stored_in_this_table) REFERENCE {other table} ({other_tables_key_name})
to connect two tables.</li>
<li name="4c08" id="4c08" class="graf graf--li graf-after--li">This is probably easier to see in an
example:</li>
</ul>
<pre name="5089" id="5089"
class="graf graf--pre graf-after--li">CREATE TABLE people (<br>id SERIAL PRIMARY KEY,<br>first_name VARCHAR(50),<br>last_name VARCHAR(50)<br>);</pre>
<pre name="deb9" id="deb9"
class="graf graf--pre graf-after--pre">CREATE TABLE pets (<br>id SERIAL PRIMARY KEY,<br>name VARCHAR(255),<br>age SMALLINT,<br>person_id INTEGER,<br>FOREIGN KEY (person_id) REFERENCES people (id)<br>);</pre>
<p name="bbb3" id="bbb3" class="graf graf--p graf-after--pre"><strong
class="markup--strong markup--p-strong">SQL is not case sensitive for its keywords but is for its
entity names</strong></p>
<ul class="postList">
<li name="7749" id="7749" class="graf graf--li graf-after--p">Exactly as the LO states, CREATE TABLE and
create table are interpreted the same way. Using capitalization is a good convention in order to
distinguish your keywords.</li>
<li name="7d1b" id="7d1b" class="graf graf--li graf-after--li">The entity names that we use ARE
case-sensitive, however. So a table named pets is unique from a table named Pets. In general, we
prefer to use all lowercase for our entities to avoid any of this confusion.</li>
</ul>
<p name="5290" id="5290" class="graf graf--p graf-after--li"><strong
class="markup--strong markup--p-strong">SQL</strong></p>
<p name="8948" id="8948" class="graf graf--p graf-after--p">1. How to use the SELECT … FROM … statement to
select data from a single table</p>
<ul class="postList">
<li name="9202" id="9202" class="graf graf--li graf-after--p">Supply the column names in the SELECT
clause. If we want all columns, we can also use *</li>
<li name="9fdc" id="9fdc" class="graf graf--li graf-after--li">Supply the table names in the FROM clause
</li>
</ul>
<p name="00f2" id="00f2" class="graf graf--p graf-after--li">— Selects all columns from the friends table
</p>
<pre name="178f" id="178f" class="graf graf--pre graf-after--p">SELECT<br>*<br>FROM<br>friends;</pre>
<p name="274c" id="274c" class="graf graf--p graf-after--pre">— Selects the first_name column from the
friends table (remember whitespace is ignored)<br>SELECT name<br>FROM friends;</p>
<ul class="postList">
<li name="ef22" id="ef22" class="graf graf--li graf-after--p">Sometimes we may need to specify what
table we are selecting a column from, particulurly if we had joined multiple tables together.</li>
</ul>
<p name="d615" id="d615" class="graf graf--p graf-after--li">— Notice here we are indicating that we want
the “name” field from the “friends” table as well as the “name” field from the “puppies” table. We
indicate the table name by table.column<br> — We are also aliasing these fields with the AS keyword so
that our returned results have friend_name and puppy_name as field headers</p>
<pre name="774f" id="774f"
class="graf graf--pre graf-after--p">SELECT<br>friends.name AS friend_name , puppies.name AS puppy_name<br>FROM<br>friends<br>JOIN<br>puppies ON friends.puppy_id = puppies.id</pre>
<p name="c0bc" id="c0bc" class="graf graf--p graf-after--pre"><strong
class="markup--strong markup--p-strong">How to use the WHERE clause on SELECT, UPDATE, and DELETE
statements to narrow the scope of the command</strong></p>
<ul class="postList">
<li name="6a59" id="6a59" class="graf graf--li graf-after--p">The WHERE clause allows us to select or
apply actions to records that match specific criteria instead of to a whole table.</li>
<li name="7ec8" id="7ec8" class="graf graf--li graf-after--li">We can use WHERE with a couple of
different operators when making our comparison</li>
<li name="70eb" id="70eb" class="graf graf--li graf-after--li">WHERE {column} = {value} provides an
exact comparison</li>
<li name="79bf" id="79bf" class="graf graf--li graf-after--li">WHERE {column} IN ({value1}, {value2},
{value3}, etc.) matches any provided value in the IN statement. We can make this more complex by
having a subquery inside of the parentheses, having our column match any values within the returned
results.</li>
<li name="150e" id="150e" class="graf graf--li graf-after--li">WHERE {column} BETWEEN {value1} AND
{value2} can check for matches between two values (numeric ranges)</li>
<li name="9077" id="9077" class="graf graf--li graf-after--li">WHERE {column} LIKE {pattern} can check
for matches to a string. This is most useful when we use the wildcard %, such as WHERE breed LIKE
‘%Shepherd’, which will match any breed that ends in Shepherd</li>
<li name="4a87" id="4a87" class="graf graf--li graf-after--li">The NOT operator can also be used for
negation in the checks.</li>
<li name="db35" id="db35" class="graf graf--li graf-after--li">Mathematical operators can be used when
performing calculations or comparisons within a query as well, such as</li>
</ul>
<pre name="b6c2" id="b6c2"
class="graf graf--pre graf-after--li">SELECT name, breed, weight_lbs FROM puppies WHERE weight_lbs > 50; — OR SELECT name, breed, age_yrs FROM puppies WHERE age_yrs * 10 = 5;</pre>
<p name="6aa5" id="6aa5" class="graf graf--p graf-after--pre"><strong
class="markup--strong markup--p-strong">How to use the JOIN keyword to join two (or more) tables
together into a single virtual table</strong></p>
<ul class="postList">
<li name="a391" id="a391" class="graf graf--li graf-after--p">When we want to get information from a
related table or do querying based on related table values, we can join the connected table by
comparing the foreign key to where it lines up on the other table:</li>
</ul>
<p name="0957" id="0957" class="graf graf--p graf-after--li">— Here we are joining the puppies table on to
the friends table. We are specifying that the comparison we should make is the foreign key puppy_id on
the friends table should line up with the primary key id on the puppies table.</p>
<pre name="c2da" id="c2da"
class="graf graf--pre graf-after--p">SELECT<br>*<br>FROM<br>friends<br>JOIN<br>puppies ON friends.puppy_id = puppies.id</pre>
<p name="e101" id="e101" class="graf graf--p graf-after--pre"><strong
class="markup--strong markup--p-strong">How to use the INSERT statement to insert data into a
table</strong></p>
<ul class="postList">
<li name="b3e7" id="b3e7" class="graf graf--li graf-after--p">When a table is already created we can
then insert records into it using the INSERT INTO keywords.</li>
<li name="d4d3" id="d4d3" class="graf graf--li graf-after--li">We provide the name of the table that we
would like to add records to, followed by the VALUES keyword and each record we are adding. Here s an
example:</li>
</ul>
<p name="7276" id="7276" class="graf graf--p graf-after--li">— We are providing the table name, then
multiple records to insert<br> — The values are listed in the order that they are defined on the table
</p>
<pre name="f963" id="f963"
class="graf graf--pre graf-after--p">INSERT INTO table_name<br>VALUES<br>(column1_value, colum2_value, column3_value),<br>(column1_value, colum2_value, column3_value),<br>(column1_value, colum2_value, column3_value);</pre>
<ul class="postList">
<li name="123c" id="123c" class="graf graf--li graf-after--pre">We can also specify columns when we are
inserting data. This makes it clear which fields we are providing data for and allows us to provide
them out of order, skip null or default values, etc.</li>
</ul>
<p name="da1d" id="da1d" class="graf graf--p graf-after--li">— In this example, we want to use the default
value for id since it is autoincremented, so we provide DEFAULT for this field</p>
<pre name="db9b" id="db9b"
class="graf graf--pre graf-after--p">INSERT INTO friends (id, first_name, last_name)<br>VALUES<br>(DEFAULT, ‘Amy’, ‘Pond’);</pre>
<p name="b50b" id="b50b" class="graf graf--p graf-after--pre">— Alternatively, we can leave it out
completely, since the default value will be used if none is provided</p>
<pre name="a343" id="a343"
class="graf graf--pre graf-after--p">INSERT INTO friends (first_name, last_name)<br>VALUES<br>(‘Rose’, ‘Tyler’),<br>(‘Martha’, ‘Jones’),<br>(‘Donna’, ‘Noble’),<br>(‘River’, ‘Song’);</pre>
<p name="f87b" id="f87b" class="graf graf--p graf-after--pre"><strong
class="markup--strong markup--p-strong">How to use an UPDATE statement to update data in a
table</strong></p>
<ul class="postList">
<li name="8e80" id="8e80" class="graf graf--li graf-after--p">The UPDATE keyword can be used to find
records and change their values in our database.</li>
<li name="91b5" id="91b5" class="graf graf--li graf-after--li">We generally follow the pattern of UPDATE
{table} SET {column} = {new value} WHERE {match condition};.</li>
<li name="5fff" id="5fff" class="graf graf--li graf-after--li">Without a condition to narrow our records
down, we will update every record in the table, so this is an important thing to double check!</li>
<li name="cdee" id="cdee" class="graf graf--li graf-after--li">We can update multiple fields as well by
specifying each column in parentheses and their associated new values: UPDATE {table} SET ({column1},
{column2}) = ({value1}, {value2}) WHERE {match condition};</li>
</ul>
<p name="a330" id="a330" class="graf graf--p graf-after--li">— Updates the pet with id of 4 to change
their name and breed</p>
<pre name="2a61" id="2a61"
class="graf graf--pre graf-after--p">UPDATE<br>pets<br>SET<br>(name, breed) = (‘Floofy’, ‘Fluffy Dog Breed’) WHERE id = 4;</pre>
<p name="b7ca" id="b7ca" class="graf graf--p graf-after--pre"><strong
class="markup--strong markup--p-strong">How to use a DELETE statement to remove data from a
table</strong></p>
<ul class="postList">
<li name="1c5e" id="1c5e" class="graf graf--li graf-after--p">Similar to selecting records, we can
delete records from a table by specifying what table we are deleting from and what criteria we would
like to match in order to delete.</li>
<li name="1b25" id="1b25" class="graf graf--li graf-after--li">We follow the general structure DELETE
FROM {table} WHERE {condition};</li>
<li name="0e74" id="0e74" class="graf graf--li graf-after--li">The condition here is also very
important! Without a condition, all records match and will be deleted.</li>
</ul>
<p name="05aa" id="05aa" class="graf graf--p graf-after--li">— Deletes from the pets table any record that
either has a name Floofy, a name Doggo, or an id of 3.</p>
<pre name="bd4b" id="bd4b"
class="graf graf--pre graf-after--p">DELETE FROM<br>pets<br>WHERE<br>name IN (‘Floofy’, ‘Doggo’) OR id = 3;</pre>
<p name="d846" id="d846" class="graf graf--p graf-after--pre"><strong
class="markup--strong markup--p-strong">How to use a seed file to populate data in a database</strong>
</p>
<ul class="postList">
<li name="687c" id="687c" class="graf graf--li graf-after--p">Seed files are a great way for us to
create records that we want to start our database out with.</li>
<li name="80f6" id="80f6" class="graf graf--li graf-after--li">Instead of having to individually add
records to our tables or manually entering them in psql or postbird, we can create a file that has all
of these records and then just pass this file to psql to run.</li>
<li name="28da" id="28da" class="graf graf--li graf-after--li">Seed files are also great if we ever need
to reset our database. We can clear out any records that we have by dropping all of our tables, then
just run our seed files to get it into a predetermined starting point. This is great for our personal
projects, testing environments, starting values for new tables we create, etc.</li>
<li name="22c5" id="22c5" class="graf graf--li graf-after--li">There are two main ways we can use a seed
file with psql, the < and the | operators. They perform the same function for us, just in slightly
different orders, taking the content of a .sql file and executing in within the psql environment:</li>
<li name="19c8" id="19c8" class="graf graf--li graf-after--li">psql -d {database} < {sql filepath}
</li>
<li name="7ec0" id="7ec0" class="graf graf--li graf-after--li">cat {sql filepath} | psql -d {database}
</li>
</ul>
<p name="8416" id="8416" class="graf graf--p graf-after--li"><strong
class="markup--strong markup--p-strong">SQL (continued)</strong></p>
<p name="b81e" id="b81e" class="graf graf--p graf-after--p"><strong
class="markup--strong markup--p-strong">How to perform relational database design</strong></p>
<ul class="postList">
<li name="8204" id="8204" class="graf graf--li graf-after--p">Steps to Designing the Database:</li>
<li name="0276" id="0276" class="graf graf--li graf-after--li">Define the entities. What data are are
you storing, what are the fields for each entity?</li>
<li name="cd73" id="cd73" class="graf graf--li graf-after--li">You can think of this in similar ways to
OOP (object oriented programming).</li>
<li name="0cf7" id="0cf7" class="graf graf--li graf-after--li">If you wanted to model this information
using classes, what classes would you make? Those are generally going to be the tables that are
created in your database.</li>
<li name="57f4" id="57f4" class="graf graf--li graf-after--li">The attributes of your classes are
generally going to be the fields/columns that we need for each table.</li>
<li name="0681" id="0681" class="graf graf--li graf-after--li">Identify primary keys. Most of the time
these will be ids that you can generate as a serial field, incrementing with each addition to the
database.</li>
<li name="99da" id="99da" class="graf graf--li graf-after--li">Establish table relationships. Connect
related data together with foreign keys. Know how we store these keys in a one-to-one, one-to-many, or
many-to-many relationship.</li>
<li name="c8c4" id="c8c4" class="graf graf--li graf-after--li">With a one-to-one or one-to-many
relationship, we are able to use a foreign key on the table to indicate the other specific record that
it is connected to.</li>
<li name="2e56" id="2e56" class="graf graf--li graf-after--li">With a many-to-many relationship, each
record could be connected to multiple records, so we have to create a join table to connect these
entities. A record on this join table connects a record from one table to a record from another table.
</li>
</ul>
<p name="f9d6" id="f9d6" class="graf graf--p graf-after--li"><strong
class="markup--strong markup--p-strong">How to use transactions to group multiple SQL commands into
one succeed or fail operation</strong></p>
<ul class="postList">
<li name="6bfa" id="6bfa" class="graf graf--li graf-after--p">We can define an explicit transaction
using BEGIN and ending with either COMMIT or ROLLBACK.</li>
<li name="0c34" id="0c34" class="graf graf--li graf-after--li">If any command inside the block fails,
everything will be rolled back. We can also specify that we want to roll back at the end of the block
instead of committing. We saw that this can be useful when analyzing operations that would manipulate
our database.</li>
</ul>
<pre name="de92" id="de92"
class="graf graf--pre graf-after--li">BEGIN;<br>UPDATE accounts SET balance = balance — 100.00<br>WHERE name = ‘Alice’;<br>UPDATE branches SET balance = balance — 100.00<br>WHERE name = (SELECT branch_name FROM accounts WHERE name = ‘Alice’);<br>UPDATE accounts SET balance = balance + 100.00<br>WHERE name = ‘Bob’;<br>UPDATE branches SET balance = balance + 100.00<br>WHERE name = (SELECT branch_name FROM accounts WHERE name = ‘Bob’);<br>COMMIT;</pre>
<pre name="d801" id="d801"
class="graf graf--pre graf-after--pre">BEGIN;<br>EXPLAIN ANALYZE<br>UPDATE cities<br>SET city = ‘New York City’<br>WHERE city = ‘New York’;<br>ROLLBACK;</pre>
<p name="fdfe" id="fdfe" class="graf graf--p graf-after--pre"><strong
class="markup--strong markup--p-strong">How to apply indexes to tables to improve performance</strong>
</p>
<ul class="postList">
<li name="704c" id="704c" class="graf graf--li graf-after--p">An index can help optimize queries that we
have to run regularly. If we are constantly looking up records in a table by a particular field (such
as username or phone number), we can add an index in order to speed up this process.</li>
<li name="142a" id="142a" class="graf graf--li graf-after--li">An index maintains a sorted version of
the field with a reference to the record that it points to in the table (via primary key). If we want
to find a record based on a field that we have an index for, we can look through this index in a more
efficient manner than having to scan through the entire table (generally O(log n) since the index is
sorted, instead of O(n) for a sequential scan).</li>
<li name="2c8f" id="2c8f" class="graf graf--li graf-after--li">To add an index to a field we can use the
following syntax:</li>
</ul>
<pre name="0d60" id="0d60"
class="graf graf--pre graf-after--li">CREATE INDEX index_name ON table_name (column_name);</pre>
<ul class="postList">
<li name="a6d3" id="a6d3" class="graf graf--li graf-after--pre">To drop an index we can do the
following:</li>
</ul>
<pre name="f3ce" id="f3ce" class="graf graf--pre graf-after--li">DROP INDEX index_name</pre>
<ul class="postList">
<li name="12fa" id="12fa" class="graf graf--li graf-after--pre">Making an index is not always the best
approach. Indices allow for faster lookup, but slow down record insertion and the updating of
associated fields, since we not only have to add the information to the table, but also manipulate the
index.</li>
<li name="0ef6" id="0ef6" class="graf graf--li graf-after--li">We generally wouldn t care about adding
an index if:</li>
<li name="8833" id="8833" class="graf graf--li graf-after--li">The tables are small</li>
<li name="b3b0" id="b3b0" class="graf graf--li graf-after--li">We are updating the table frequently,
especially the associated columns</li>
<li name="5ebd" id="5ebd" class="graf graf--li graf-after--li">The column has many NULL values</li>
</ul>
<p name="0f2b" id="0f2b" class="graf graf--p graf-after--li"><strong
class="markup--strong markup--p-strong">Explain what the EXPLAIN command is used for:</strong></p>
<ul class="postList">
<li name="36da" id="36da" class="graf graf--li graf-after--p">EXPLAIN gives us information about how a
query will run (the query plan)</li>
<li name="482c" id="482c" class="graf graf--li graf-after--li">It gives us an idea of how our database
will search for data as well as a qualitative comparitor for how expensive that operation will be.
Comparing the cost of two queries will tell us which one is more efficient (lower cost).</li>
<li name="c7c7" id="c7c7" class="graf graf--li graf-after--li">We can also use the ANALYZE command with
EXPLAIN, which will actually run the specified query. Doing so gives us more detailed information,
such as the milliseconds it took our query to execute as well as specifics like the exact number of
rows filtered and returned.</li>
</ul>
<p name="c49a" id="c49a" class="graf graf--p graf-after--li">1. Demonstrate how to install and use the
node-postgres library and its Pool class to query a PostgreSQL-managed database</p>
<ul class="postList">
<li name="8f73" id="8f73" class="graf graf--li graf-after--p">We can add the node-postgres library to
our application with npm install pg. From there we will typically use the Pool class associated with
this library. That way we can run many SQL queries with one database connection (as opposed to Client,
which closes the connection after a query).</li>
</ul>
<pre name="a04e" id="a04e" class="graf graf--pre graf-after--li">const { Pool } = require(‘pg’);</pre>
<p name="be64" id="be64" class="graf graf--p graf-after--pre">// If we need to specify a username,
password, or database, we can do so when we create a Pool instance, otherwise the default values for
logging in to psql are used:</p>
<pre name="2cb8" id="2cb8"
class="graf graf--pre graf-after--p">const pool = new Pool({ username: ‘<<username>>’, password: ‘<<password>>’, database: ‘<<database>>’})</pre>
<ul class="postList">
<li name="a267" id="a267" class="graf graf--li graf-after--pre">The query method on the Pool instance
will allow us to execute a SQL query on our database. We can pass in a string that represents the
query we want to run</li>
</ul>
<pre name="64af" id="64af"
class="graf graf--pre graf-after--li">const allAirportsSql = `<br>SELECT id, city_id, faa_id, name<br>FROM airports;<br>`;</pre>
<pre name="5b49" id="5b49"
class="graf graf--pre graf-after--pre">async function selectAllAirports() {<br>const results = await pool.query(allAirportsSql);<br>console.log(results.rows);<br>pool.end(); // invoking end() will close our connection to the database<br>}</pre>
<p name="2899" id="2899" class="graf graf--p graf-after--pre">selectAllAirports();</p>
<ul class="postList">
<li name="edf8" id="edf8" class="graf graf--li graf-after--p">The return value of this asynchronous
function is an object with a rows key that points to an array of objects, each object representing a
record with field names as keys.</li>
</ul>
<p name="1b7b" id="1b7b" class="graf graf--p graf-after--li"><strong
class="markup--strong markup--p-strong">Explain how to write prepared statements with placeholders for
parameters of the form $1 , $2 , and so on</strong></p>
<ul class="postList">
<li name="ee72" id="ee72" class="graf graf--li graf-after--p">The prepared statement (SQL string that we
wrote) can also be made more dynamic by allowing for parameters to be passed in.</li>
<li name="3b64" id="3b64" class="graf graf--li graf-after--li">The Pool instance s query function allows
us to pass a second argument, an array of parameters to be used in the query string. The location of
the parameter substitutions are designated with $1, $2, etc., to signify the first, second, etc.,
arguments.</li>
</ul>
<pre name="03f2" id="03f2"
class="graf graf--pre graf-after--li">const airportsByNameSql = `<br>SELECT name, faa_id<br>FROM airports<br>WHERE UPPER(name) LIKE UPPER($1)<br>`;</pre>
<p name="fff4" id="fff4" class="graf graf--p graf-after--pre">async function selectAirportsByName(name)
{<br>const results = await pool.query(airportsByNameSql, [ `%${name}%`
]);<br>console.log(results.rows);<br>pool.end(); // invoking end() will close our connection to the
database<br>}</p>
<pre name="20be" id="20be"
class="graf graf--pre graf-after--p">// Get the airport name from the command line and store it<br>// in the variable “name”. Pass that value to the<br>// selectAirportsByName function.<br>const name = process.argv[2];<br>// console.log(name);<br>selectAirportsByName(name);</pre>
<p name="2cac" id="2cac" class="graf graf--p graf-after--pre"><strong
class="markup--strong markup--p-strong">ORM</strong></p>
<p name="e058" id="e058" class="graf graf--p graf-after--p">1. How to install, configure, and use
Sequelize, an ORM for JavaScript</p>
<ul class="postList">
<li name="0e86" id="0e86" class="graf graf--li graf-after--p">To start a new project we use our standard
npm initialize statement</li>
<li name="7bf0" id="7bf0" class="graf graf--li graf-after--li">npm init -y</li>
<li name="564b" id="564b" class="graf graf--li graf-after--li">Add in the packages we will need
(sequelize, sequelize-cli, and pg)</li>
<li name="9c9b" id="9c9b" class="graf graf--li graf-after--li">npm install sequelize@⁵.0.0
sequelize-cli@⁵.0.0 pg@⁸.0.0</li>
<li name="0b1c" id="0b1c" class="graf graf--li graf-after--li">Initialize sequelize in our project</li>
<li name="8052" id="8052" class="graf graf--li graf-after--li">npx sequelize-cli init</li>
<li name="d03e" id="d03e" class="graf graf--li graf-after--li">Create a database user with credentials
we will use for the project</li>
<li name="bbc8" id="bbc8" class="graf graf--li graf-after--li">psql</li>
<li name="caca" id="caca" class="graf graf--li graf-after--li">CREATE USER example_user WITH PASSWORD
‘badpassword’</li>
<li name="72ab" id="72ab" class="graf graf--li graf-after--li">Here we can also create databases since
we are already in postgres</li>
</ul>
<pre name="4bb4" id="4bb4"
class="graf graf--pre graf-after--li">CREATE DATABASE example_app_development WITH OWNER example_user</pre>
<pre name="9003" id="9003"
class="graf graf--pre graf-after--pre">CREATE DATABASE example_app_test WITH OWNER example_user</pre>
<pre name="756d" id="756d"
class="graf graf--pre graf-after--pre">CREATE DATABASE example_app_production WITH OWNER example_user</pre>
<ul class="postList">
<li name="ae27" id="ae27" class="graf graf--li graf-after--pre">If we don t create these databases now,
we could also create them after we make our changes to our config file. If we take this approach, we
need to make sure our user that we created has the CREATEDB option when we make them, since sequelize
will attempt to make the databases with this user. This other approach would look like:</li>
<li name="f6a3" id="f6a3" class="graf graf--li graf-after--li">In psql: CREATE USER example_user WITH
PASSWORD ‘badpassword’ CREATEDB</li>
<li name="45ac" id="45ac" class="graf graf--li graf-after--li">In terminal: npx sequelize-cli db:create
</li>
<li name="cff2" id="cff2" class="graf graf--li graf-after--li">Double check that our configuration file
matches our username, password, database, dialect, and seederStorage (these will be filled out for you
in an assessment scenario):</li>
</ul>
<pre name="3bab" id="3bab"
class="graf graf--pre graf-after--li">{<br>“development”: {<br>“username”: “sequelize_recipe_box_app”,<br>“password”: “HfKfK79k”,<br>“database”: “recipe_box_development”,<br>“host”: “127.0.0.1”,<br>“dialect”: “postgres”,<br>“seederStorage”: “sequelize”<br>},<br>“test”: {<br>“username”: “sequelize_recipe_box_app”,<br>“password”: “HfKfK79k”,<br>“database”: “recipe_box_test”,<br>“host”: “127.0.0.1”,<br>“dialect”: “postgres”,<br>“seederStorage”: “sequelize”<br>},<br>“production”: {<br>“username”: “sequelize_recipe_box_app”,<br>“password”: “HfKfK79k”,<br>“database”: “recipe_box_production”,<br>“host”: “127.0.0.1”,<br>“dialect”: “postgres”,<br>“seederStorage”: “sequelize”<br>}<br>}</pre>
<p name="0240" id="0240" class="graf graf--p graf-after--pre">1. How to use database migrations to make
your database grow with your application in a source-control enabled way</p>
<p name="96d4" id="96d4" class="graf graf--p graf-after--p"><strong
class="markup--strong markup--p-strong">Migrations</strong></p>
<ul class="postList">
<li name="3710" id="3710" class="graf graf--li graf-after--p">In order to make new database tables and
sequelize models that reflect them, we want to generate a migration file and model file using
model:generate</li>
</ul>
<pre name="7ce9" id="7ce9"
class="graf graf--pre graf-after--li">npx sequelize-cli model:generate — name Cat — attributes “firstName:string,specialSkill:string”</pre>
<ul class="postList">
<li name="bc91" id="bc91" class="graf graf--li graf-after--pre">Here we are creating a migration file
and a model file for a Cat. We are specifying that we want this table to have fields for firstName and
specialSkill. Sequelize will automatically make fields for an id, createdAt, and updatedAt, as well,
so we do not need to specify these.</li>
<li name="4d04" id="4d04" class="graf graf--li graf-after--li">Once our migration file is created, we
can go in and edit any details that we need to. Most often we will want to add in database constraints
such as allowNull: false, adding a uniqueness constraint with unique: true, adding in character limits
to fields such as type: Sequelize.STRING(100), or specifying a foreign key with references to another
table references: { model: ‘Categories’ }.</li>
<li name="ca79" id="ca79" class="graf graf--li graf-after--li">After we make any necessary changes to
our migration file, we need to perform the migration, which will run the SQL commands to actually
create the table.</li>
</ul>
<pre name="409e" id="409e" class="graf graf--pre graf-after--li">npx sequelize-cli db:migrate</pre>
<ul class="postList">
<li name="78a8" id="78a8" class="graf graf--li graf-after--pre">This command runs any migration files
that have not been previously run, in the order that they were created (this is why the timestamp in
the file name is important)</li>
<li name="0790" id="0790" class="graf graf--li graf-after--li">If we realize that we made a mistake
after migrating, we can undo our previous migration, or all of our migrations. After undoing them, we
can make any changes necessary to our migration files (They won t be deleted from the undo, so we don
t need to generate anything! Just make the necessary changes to the files that already exist and save
the files.). Running the migrations again will make the tables with the updates reflected.</li>
</ul>
<pre name="1c17" id="1c17" class="graf graf--pre graf-after--li">npx sequelize-cli db:migrate:undo</pre>
<pre name="76a3" id="76a3"
class="graf graf--pre graf-after--pre">npx sequelize-cli db:migrate:undo:all</pre>
<p name="5528" id="5528" class="graf graf--p graf-after--pre"><strong
class="markup--strong markup--p-strong">Models Validations and Associations</strong></p>
<ul class="postList">
<li name="a4dd" id="a4dd" class="graf graf--li graf-after--p">In addition to the migration files, our
model:generate command also created a model file for us. This file is what allows sequelize to
transform the results of its SQL queries into useful JavaScript objects for us.</li>
<li name="5c70" id="5c70" class="graf graf--li graf-after--li">The model is where we can specify a
validation that we want to perform before trying to run a SQL query. If the validation fails, we can
respond with a message instead of running the query, which can be an expensive operation that we know
won t work.</li>
</ul>
<pre name="2c1f" id="2c1f"
class="graf graf--pre graf-after--li">// Before we make changes, sequelize generates the type that this field represents specification:<br>DataTypes.TEXT<br>// We can replace the generated format with an object to specify not only the type, but the validations that we want to implement. The validations can also take in messages the respond with on failure and arguments.<br>specification: {<br> type: DataTypes.TEXT,<br> validate: {<br> notEmpty: {<br> msg: 'The specification cannot be empty'<br> },<br> len: {<br> args: [10, 100]<br> msg: 'The specifcation must be between 10 and 100 characters'<br> }<br> }<br>}</pre>
<ul class="postList">
<li name="94ef" id="94ef" class="graf graf--li graf-after--pre">Another key part of the model file is
setting up our associations. We can use the belongsTo, hasMany, and belongsToMany methods to set up
model-level associations. Doing so is what creates the helpful functionality like addOwner that we saw
in the pets example, a function that automatically generates the SQL necessary to create a petOwner
record and supplies the appropriate petId and ownerId.</li>
<li name="e5fc" id="e5fc" class="graf graf--li graf-after--li">In a one-to-many association, we need to
have a belongsTo association on the many side, and a hasMany association on the one side:</li>
<li name="21c2" id="21c2" class="graf graf--li graf-after--li">Instruction.belongsTo(models.Recipe, {
foreignKey: ‘recipeId’ });</li>
<li name="6e6b" id="6e6b" class="graf graf--li graf-after--li">Recipe.hasMany(models.Instruction, {
foreignKey: ‘recipeId’ });</li>
<li name="7d6a" id="7d6a" class="graf graf--li graf-after--li">In a many-to-many association, we need to
have a belongsToMany on each side of the association. We generally specify a columnMapping object to
show the association more clearly:</li>
</ul>
<pre name="7983" id="7983" class="graf graf--pre graf-after--li">// In our Owner model</pre>
<pre name="d349" id="d349"
class="graf graf--pre graf-after--pre">// To connect this Owner to a Pet through the PetOwner</pre>
<pre name="9424" id="9424" class="graf graf--pre graf-after--pre">const columnMapping = {</pre>
<pre name="efeb" id="efeb" class="graf graf--pre graf-after--pre">through: ‘PetOwner’,</pre>
<pre name="7edd" id="7edd" class="graf graf--pre graf-after--pre">// joins table</pre>
<pre name="5397" id="5397" class="graf graf--pre graf-after--pre">otherKey: ‘petId’,</pre>
<pre name="433e" id="433e"
class="graf graf--pre graf-after--pre">// key that connects to other table we have a many association with foreignKey: ‘ownerId’</pre>
<pre name="6730" id="6730"
class="graf graf--pre graf-after--pre">// our foreign key in the joins table</pre>
<pre name="ed2d" id="ed2d" class="graf graf--pre graf-after--pre">}</pre>
<pre name="cb1c" id="cb1c"
class="graf graf--pre graf-after--pre">Owner.belongsToMany( models.Pet, columnMapping );</pre>
<pre name="b587" id="b587" class="graf graf--pre graf-after--pre">// In our Pet model</pre>
<pre name="d7ac" id="d7ac"
class="graf graf--pre graf-after--pre">// To connect this Pet to an Owner through the PetOwner</pre>
<pre name="4e21" id="4e21"
class="graf graf--pre graf-after--pre">const columnMapping = { through: ‘PetOwner’,</pre>
<pre name="5576" id="5576" class="graf graf--pre graf-after--pre">// joins table</pre>
<pre name="dfb9" id="dfb9" class="graf graf--pre graf-after--pre">otherKey: ‘ownerId’,</pre>
<pre name="dc3a" id="dc3a"
class="graf graf--pre graf-after--pre">// key that connects to other table we have a many association with</pre>
<pre name="2493" id="2493" class="graf graf--pre graf-after--pre">foreignKey: ‘petId’</pre>
<pre name="150c" id="150c"
class="graf graf--pre graf-after--pre">// our foreign key in the joins table</pre>
<pre name="65b1" id="65b1" class="graf graf--pre graf-after--pre">}</pre>
<pre name="f05d" id="f05d"
class="graf graf--pre graf-after--pre">Pet.belongsToMany( models.Owner, columnMapping );</pre>
<h3 name="5d1e" id="5d1e" class="graf graf--h3 graf-after--pre"><strong
class="markup--strong markup--h3-strong">How to perform CRUD operations with Sequelize</strong></h3>
<ul class="postList">
<li name="fc1a" id="fc1a" class="graf graf--li graf-after--h3">Seed Files</li>
<li name="ad49" id="ad49" class="graf graf--li graf-after--li">Seed files can be used to populate our
database with starter data.</li>
<li name="9058" id="9058" class="graf graf--li graf-after--li">npx sequelize-cli seed:generate — name
add-cats</li>
<li name="d2cf" id="d2cf" class="graf graf--li graf-after--li">up indicates what to create when we seed
our database, down indicates what to delete if we want to unseed the database.</li>
<li name="d605" id="d605" class="graf graf--li graf-after--li">For our up, we use the
queryInterface.bulkInsert() method, which takes in the name of the table to seed and an array of
objects representing the records we want to create:</li>
</ul>
<pre name="2040" id="2040"
class="graf graf--pre graf-after--li">up: (queryInterface, Sequelize) => {<br> return queryInterface.bulkInsert('<<TableName>>', [{<br> field1: value1a,<br> field2: value2a<br> }, {<br> field1: value1b,<br> field2: value2b<br> }, {<br> field1: value1c,<br> field2: value2c<br> }]);<br>}</pre>
<ul class="postList">
<li name="0cb9" id="0cb9" class="graf graf--li graf-after--pre">For our down, we use the
queryInterface.bulkDelete() method, which takes in the name of the table and an object representing
our WHERE clause. Unseeding will delete all records from the specified table that match the WHERE
clause.</li>
</ul>
<pre name="d6ae" id="d6ae"
class="graf graf--pre graf-after--li">// If we want to specify what to remove:<br>down: (queryInterface, Sequelize) => {<br>return queryInterface.bulkDelete(‘<<TableName>>’, {<br>field1: [value1a, value1b, value1c], //…etc.<br>});<br>};<br>// If we want to remove everything from the table:<br>down: (queryInterface, Sequelize) => {<br>return queryInterface.bulkDelete(‘<<TableName>>’, null, {});<br>};</pre>
<ul class="postList">
<li name="c9e3" id="c9e3" class="graf graf--li graf-after--pre">Running npx sequelize-cli db:seed:all
will run all of our seeder files.</li>
<li name="8778" id="8778" class="graf graf--li graf-after--li">npx sequelize-cli db:seed:undo:all will
undo all of our seeding.</li>
<li name="d655" id="d655" class="graf graf--li graf-after--li">If we omit the :all we can run specific
seed files</li>
<li name="4788" id="4788" class="graf graf--li graf-after--li">Inserting with Build and Create</li>
<li name="fc2d" id="fc2d" class="graf graf--li graf-after--li">In addition to seed files, which we
generally use for starter data, we can create new records in our database by using build and save, or
the combined create</li>
<li name="3807" id="3807" class="graf graf--li graf-after--li">Use the .build method of the Cat model to
create a new Cat instance in index.js</li>
</ul>
<pre name="648e" id="648e"
class="graf graf--pre graf-after--li">// Constructs an instance of the JavaScript `Cat` class. **Does not<br>// save anything to the database yet**. Attributes are passed in as a<br>// POJO.<br>const newCat = Cat.build({<br> firstName: 'Markov',<br> specialSkill: 'sleeping',<br> age: 5<br>});<br>// This actually creates a new `Cats` record in the database. We must<br>// wait for this asynchronous operation to succeed.<br>await newCat.save();<br>// This builds and saves all in one step. If we don't need to perform any operations on the instance before saving it, this can optimize our code.<br>const newerCat = await Cat.create({<br> firstName: 'Whiskers',<br> specialSkill: 'sleeping',<br> age: 2<br>})</pre>
<p name="222d" id="222d" class="graf graf--p graf-after--pre"><strong
class="markup--strong markup--p-strong">Updating Records</strong></p>
<ul class="postList">
<li name="4ecc" id="4ecc" class="graf graf--li graf-after--p">When we have a reference to an instance of
a model (i.e. after we have queried for it or created it), we can update values by simply reassigning
those fields and using the save method</li>
</ul>
<p name="1828" id="1828" class="graf graf--p graf-after--li"><strong
class="markup--strong markup--p-strong">Deleting Records</strong></p>
<ul class="postList">
<li name="606a" id="606a" class="graf graf--li graf-after--p">When we have a reference to an instance of
a model, we can delete that record by using destroy</li>
<li name="be30" id="be30" class="graf graf--li graf-after--li">const cat = await Cat.findByPk(1); //
Remove the Markov record. await cat.destroy();</li>
<li name="0b7f" id="0b7f" class="graf graf--li graf-after--li">We can also call destroy on the model
itself. By passing in an object that specifies a where clause, we can destroy all records that match
that query</li>
<li name="5f9f" id="5f9f" class="graf graf--li graf-after--li">await Cat.destroy({ where: {
specialSkill: ‘jumping’ } });</li>
</ul>
<p name="18bd" id="18bd" class="graf graf--p graf-after--li"><strong
class="markup--strong markup--p-strong">How to query using Sequelize</strong></p>
<p name="1b82" id="1b82" class="graf graf--p graf-after--p"><strong
class="markup--strong markup--p-strong">findAll</strong></p>
<pre name="89ec" id="89ec"
class="graf graf--pre graf-after--p">const cats = await Cat.findAll();<br>// Log the fetched cats.<br>// The extra arguments to stringify are a replacer and a space respectively<br>// Here we're specifying a space of 2 in order to print more legibly<br>// We don't want a replacer, so we pass null just so that we can pass a 3rd argument<br>console.log(JSON.stringify(cats, null, 2));</pre>
<p name="7819" id="7819" class="graf graf--p graf-after--pre"><strong
class="markup--strong markup--p-strong">WHERE clause</strong></p>
<ul class="postList">
<li name="e68d" id="e68d" class="graf graf--li graf-after--p">Passing an object to findAll can add on
clauses to our query</li>
<li name="ec33" id="ec33" class="graf graf--li graf-after--li">The where key takes an object as a value
to indicate what we are filtering by</li>
<li name="aa61" id="aa61" class="graf graf--li graf-after--li">{ where: { field: value } } => WHERE
field = value</li>
</ul>
<pre name="c50b" id="c50b"
class="graf graf--pre graf-after--li">const cats = await Cat.findAll({ where: { firstName: “Markov” } }); console.log(JSON.stringify(cats, null, 2));</pre>
<p name="3bf9" id="3bf9" class="graf graf--p graf-after--pre"><strong
class="markup--strong markup--p-strong">OR in the WHERE clause</strong></p>
<ul class="postList">
<li name="755e" id="755e" class="graf graf--li graf-after--p">Using an array for the value tells
sequelize we want to match any of these values</li>
</ul>
<p name="90db" id="90db" class="graf graf--p graf-after--li">{ where: { field: [value1, value2] } =>
WHERE field IN (value1, value2)</p>
<pre name="aa84" id="aa84"
class="graf graf--pre graf-after--p">const cats = await Cat.findAll({ where: { firstName: [“Markov”, “Curie”] } });const cats = await Cat.findAll({<br> where: {<br> firstName: "Markov",<br> age: 4<br> }<br>});<br>console.log(JSON.stringify(cats, null, 2));</pre>
<pre name="2363" id="2363"
class="graf graf--pre graf-after--pre">console.log(JSON.stringify(cats, null, 2));</pre>
<p name="942b" id="942b" class="graf graf--p graf-after--pre"><strong
class="markup--strong markup--p-strong">AND in the WHERE clause</strong></p>
<ul class="postList">
<li name="afbb" id="afbb" class="graf graf--li graf-after--p">Providing additional key/value pairs to
the where object indicates all filters must match</li>
<li name="45a1" id="45a1" class="graf graf--li graf-after--li">{ where: { field1: value1, field2: value2
} } => WHERE field1 = value1 AND field2 = value2</li>
</ul>
<h3 name="9cb5" id="9cb5" class="graf graf--h3 graf-after--li">Sequelize Op operator</h3>
<ul class="postList">
<li name="dd6a" id="dd6a" class="graf graf--li graf-after--h3">By requiring Op from the sequelize
library we can provide more advanced comparison operators</li>
<li name="0a01" id="0a01" class="graf graf--li graf-after--li">const { Op } = require(“sequelize”);</li>
<li name="8936" id="8936" class="graf graf--li graf-after--li">Op.ne: Not equal operator</li>
</ul>
<pre name="0fdd" id="0fdd"
class="graf graf--pre graf-after--li">const cats = await Cat.findAll({<br> where: {<br> firstName: {<br> // All cats where the name is not equal to "Markov"<br> // We use brackets in order to evaluate Op.ne and use the value as the key<br> [Op.ne]: "Markov"<br> },<br> },<br>});<br>console.log(JSON.stringify(cats, null, 2));</pre>
<h3 name="16ad" id="16ad" class="graf graf--h3 graf-after--pre">Op.and: and operator</h3>
<pre name="9686" id="9686"
class="graf graf--pre graf-after--h3">const cats = await Cat.findAll({<br> where: {<br> // The array that Op.and points to must all be true <br> // Here, we find cats where the name is not "Markov" and the age is 4<br> [Op.and]: [{<br> firstName: {<br> [Op.ne]: "Markov"<br> }<br> }, {<br> age: 4<br> }, ],<br> },<br>});<br>console.log(JSON.stringify(cats, null, 2));</pre>
<h3 name="66d6" id="66d6" class="graf graf--h3 graf-after--pre">Op.or: or operator</h3>
<pre name="77d1" id="77d1"
class="graf graf--pre graf-after--h3">const cats = await Cat.findAll({<br> where: {<br> // One condition in the array that Op.or points to must be true <br> // Here, we find cats where the name is "Markov" or where the age is 4<br> [Op.or]: [{<br> firstName: "Markov"<br> }, {<br> age: 4<br> }, ],<br> },<br>});<br>console.log(JSON.stringify(cats, null, 2));</pre>
<p name="10b1" id="10b1" class="graf graf--p graf-after--pre">Op.gt and Op.lt: greater than and less than
operators</p>
<p name="2af8" id="2af8" class="graf graf--p graf-after--p">const cats = await Cat.findAll({ where: { //
Find all cats where the age is greater than 4 age: { [Op.gt]: 4 }, } }, });
console.log(JSON.stringify(cats, null, 2));</p>
<h4 name="d61a" id="d61a" class="graf graf--h4 graf-after--p">Ordering results</h4>
<ul class="postList">
<li name="4ee4" id="4ee4" class="graf graf--li graf-after--h4">Just like the where clause, we can pass
an order key to specify we want our results ordered</li>
<li name="46a6" id="46a6" class="graf graf--li graf-after--li">The key order points to an array with the
fields that we want to order by</li>
<li name="ba7f" id="ba7f" class="graf graf--li graf-after--li">By default, the order is ascending, just
like standard SQL. If we want to specify descending, we can instead use a nested array with the field
name as the first element and DESC as the second element. (We could also specify ASC as a second
element in a nested array, but it is unnecessary as it is default)</li>
<li name="f9f9" id="f9f9" class="graf graf--li graf-after--li">const cats = await Cat.findAll({ // Order
by age descending, then by firstName ascending if cats have the same age order: [[“age”, “DESC”],
“firstName”], }); console.log(JSON.stringify(cats, null, 2));</li>
</ul>
<pre name="d42e" id="d42e"
class="graf graf--pre graf-after--li">// Get a reference to the cat record that we want to update (here just the cat with primary key of 1)<br>const cat = await Cat.findByPk(1);<br>// Change cat's attributes.<br>cat.firstName = "Curie";<br>cat.specialSkill = "jumping";<br>cat.age = 123;<br>// Save the new name to the database.<br>await cat.save();</pre>
<ul class="postList">
<li name="c46e" id="c46e" class="graf graf--li graf-after--pre">Limiting results</li>
<li name="8e6f" id="8e6f" class="graf graf--li graf-after--li">We can provide a limit key in order to
limit our results to a specified number</li>
</ul>
<pre name="8273" id="8273"
class="graf graf--pre graf-after--li">const cats = await Cat.findAll({<br> order: [<br> ["age", "DESC"]<br> ],<br> // Here we are limiting our results to one record. It will still return an array, just with one object inside. We could have said any number here, the result is always an array. <br> limit: 1,<br>});<br>console.log(JSON.stringify(cats, null, 2));</pre>
<h3 name="f60d" id="f60d" class="graf graf--h3 graf-after--pre">findOne</h3>
<ul class="postList">
<li name="0151" id="0151" class="graf graf--li graf-after--h3">If we only want one record to be returned
we can use findOne instead of findAll</li>
<li name="2a32" id="2a32" class="graf graf--li graf-after--li">If multiple records would have matched
our findOne query, it will return the first record</li>
<li name="4c25" id="4c25" class="graf graf--li graf-after--li">Unlike findAll, findOne will return the
object directly instead of an array. If no records matched the query it will return null.</li>
</ul>
<pre name="f26a" id="f26a"
class="graf graf--pre graf-after--li">// finds the oldest cat const cat = await Cat.findOne({ order: [[“age”, “DESC”]], }); console.log(JSON.stringify(cat, null, 2));</pre>
<ul class="postList">
<li name="3bc7" id="3bc7" class="graf graf--li graf-after--pre"><strong
class="markup--strong markup--li-strong">Querying with Associations</strong></li>
</ul>
<p name="3d93" id="3d93" class="graf graf--p graf-after--li">We can include associated data by adding an
include key to our options object</p>
<pre name="c81c" id="c81c"
class="graf graf--pre graf-after--p">const pet = Pet.findByPk(1, {<br> include: [PetType, Owner]<br>});<br>console.log(pet.id, pet.name, pet.age, pet.petTypeId, pet.PetType.type, pet.Owners</pre>
<p name="093d" id="093d" class="graf graf--p graf-after--pre">We can get nested associations by having
include point to an object that specifies which model we have an association with, then chaining an
association on with another include</p>
<p name="b85e" id="b85e" class="graf graf--p graf-after--p"><strong
class="markup--strong markup--p-strong">How to perform data validations with Sequelize</strong></p>
<ul class="postList">
<li name="e654" id="e654" class="graf graf--li graf-after--p">See the database migrations section above.
</li>
<li name="9f26" id="9f26" class="graf graf--li graf-after--li">In general, we add in a validate key to
each field that we want validations for. This key points to an object that specifies all of the
validations we want to make on that field, such as notEmpty, notNull, len, isIn, etc.</li>
</ul>
<pre name="cd37" id="cd37"
class="graf graf--pre graf-after--li">specification: {<br> type: DataTypes.TEXT,<br> validate: {<br> notEmpty: {<br> msg: 'The specification cannot be empty'<br> },<br> len: {<br> args: [10, 100]<br> msg: 'The specifcation must be between 10 and 100 characters'<br> }<br> }<br>}</pre>
<p name="744b" id="744b" class="graf graf--p graf-after--pre"><strong
class="markup--strong markup--p-strong">How to use transactions with Sequelize</strong></p>
<ul class="postList">
<li name="1059" id="1059" class="graf graf--li graf-after--p">We can create a transaction block in order
to make sure either all operations are performed or none of them are</li>
<li name="a632" id="a632" class="graf graf--li graf-after--li">We use the .transaction method in order
to create our block. The method takes in a callback with an argument to track our transaction id
(typically just a simple tx variable).</li>
<li name="79f9" id="79f9" class="graf graf--li graf-after--li">All of our sequelize operations can be
passed a transaction key on their options argument which points to our transaction id. This indicates
that this operation is part of the transaction block and should only be executed in the database when
the whole block executes without error.</li>
</ul>
<pre name="139d" id="139d"
class="graf graf--pre graf-after--li graf--trailing">async function main() {<br> try {<br> // Do all database access within the transaction.<br> await sequelize.transaction(async (tx) => {<br> // Fetch Markov and Curie's accounts.<br> const markovAccount = await BankAccount.findByPk(<br> 1, {<br> transaction: tx<br> },<br> );<br> const curieAccount = await BankAccount.findByPk(<br> 2, {<br> transaction: tx<br> }<br> );<br> // No one can mess with Markov or Curie's accounts until the<br> // transaction completes! The account data has been locked!<br> // Increment Curie's balance by $5,000.<br> curieAccount.balance += 5000;<br> await curieAccount.save({<br> transaction: tx<br> });<br> // Decrement Markov's balance by $5,000.<br> markovAccount.balance -= 5000;<br> await markovAccount.save({<br> transaction: tx<br> });<br> });<br> } catch (err) {<br> // Report if anything goes wrong.<br> console.log("Error!");<br> for (const e of err.errors) {<br> console.log(<br> `${e.instance.clientName}: ${e.message}`<br> );<br> }<br> }<br> await sequelize.close();<br>}<br>main();</pre>
</div>
</div>
</section>
<section name="323f" class="section section--body section--last">
<div class="section-divider">
<hr class="section-divider">
</div>
<div class="section-content">
<div class="section-inner sectionLayout--insetColumn">
<h3 name="0ad4" id="0ad4" class="graf graf--h3 graf--leading">Sequelize Cheatsheet</h3>
<h4 name="52ef" id="52ef" class="graf graf--h4 graf-after--h3">Command Line</h4>
<p name="e96e" id="e96e" class="graf graf--p graf-after--h4">Sequelize provides utilities for generating
migrations, models, and seed files. They are exposed through the <code
class="markup--code markup--p-code">sequelize-cli</code> command.</p>
<h4 name="7d79" id="7d79" class="graf graf--h4 graf-after--p">Init Project</h4>
<pre name="96e0" id="96e0" class="graf graf--pre graf-after--h4">$ npx sequelize-cli init</pre>
<p name="64fd" id="64fd" class="graf graf--p graf-after--pre">You must create a database user, and update
the <code class="markup--code markup--p-code">config/config.json</code> file to match your database
settings to complete the initialization process.</p>
<h4 name="133c" id="133c" class="graf graf--h4 graf-after--p">Create Database</h4>
<pre name="366a" id="366a" class="graf graf--pre graf-after--h4">npx sequelize-cli db:create</pre>
<h4 name="d695" id="d695" class="graf graf--h4 graf-after--pre">Generate a model and its migration</h4>
<pre name="bd86" id="bd86"
class="graf graf--pre graf-after--h4">npx sequelize-cli model:generate --name <ModelName> --attributes <column1>:<type>,<column2>:<type>,...</pre>
<h4 name="d6c1" id="d6c1" class="graf graf--h4 graf-after--pre">Run pending migrations</h4>
<pre name="955a" id="955a" class="graf graf--pre graf-after--h4">npx sequelize-cli db:migrate</pre>
<h4 name="016b" id="016b" class="graf graf--h4 graf-after--pre">Rollback one migration</h4>
<pre name="e851" id="e851" class="graf graf--pre graf-after--h4">npx sequelize-cli db:migrate:undo</pre>
<h4 name="d9e4" id="d9e4" class="graf graf--h4 graf-after--pre">Rollback all migrations</h4>
<pre name="51b8" id="51b8"
class="graf graf--pre graf-after--h4">npx sequelize-cli db:migrate:undo:all</pre>
<h4 name="d44b" id="d44b" class="graf graf--h4 graf-after--pre">Generate a new seed file</h4>
<pre name="8b7a" id="8b7a"
class="graf graf--pre graf-after--h4">npx sequelize-cli seed:generate --name <descriptiveName></pre>
<h4 name="97d8" id="97d8" class="graf graf--h4 graf-after--pre">Run all pending seeds</h4>
<pre name="b1ee" id="b1ee" class="graf graf--pre graf-after--h4">npx sequelize-cli db:seed:all</pre>
<h4 name="3ea4" id="3ea4" class="graf graf--h4 graf-after--pre">Rollback one seed</h4>
<pre name="05d4" id="05d4" class="graf graf--pre graf-after--h4">npx sequelize-cli db:seed:undo</pre>
<h4 name="f964" id="f964" class="graf graf--h4 graf-after--pre">Rollback all seeds</h4>
<pre name="4f0d" id="4f0d" class="graf graf--pre graf-after--h4">npx sequelize-cli db:seed:undo:all</pre>
<h4 name="fc2b" id="fc2b" class="graf graf--h4 graf-after--pre">Migrations</h4>
<h3 name="f3b5" id="f3b5" class="graf graf--h3 graf-after--h4">Create Table (usually used in the
up() method)</h3>
<pre name="ab1a" id="ab1a"
class="graf graf--pre graf-after--h3">// This uses the short form for references<br>return queryInterface.createTable(<TableName>, {<br> <columnName>: {<br> type: Sequelize.<type>,<br> allowNull: <true|false>,<br> unique: <true|false>,<br> references: { model: <TableName> }, // This is the plural table name<br> // that the column references.<br> }<br>});<br>// This the longer form for references that is less confusing<br>return queryInterface.createTable(<TableName>, {<br> <columnName>: {<br> type: Sequelize.<type>,<br> allowNull: <true|false>,<br> unique: <true|false>,<br> references: {<br> model: {<br> tableName: <TableName> // This is the plural table name<br> }<br> }<br> }<br>});</pre>
<h3 name="9e63" id="9e63" class="graf graf--h3 graf-after--pre">Delete Table (usually used in the down()
function)</h3>
<pre name="f040" id="f040"
class="graf graf--pre graf-after--h3">return queryInterface.dropTable(<TableName>);</pre>
<h3 name="b8c5" id="b8c5" class="graf graf--h3 graf-after--pre">Adding a column</h3>
<pre name="e875" id="e875"
class="graf graf--pre graf-after--h3">return queryInteface.addColumn(<TableName>, <columnName>: {<br> type: Sequelize.<type>,<br> allowNull: <true|false>,<br> unique: <true|false>,<br> references: { model: <TableName> }, // This is the plural table name<br> // that the column references.<br>});</pre>
<h3 name="edd5" id="edd5" class="graf graf--h3 graf-after--pre">Removing a column</h3>
<pre name="1a06" id="1a06"
class="graf graf--pre graf-after--h3">return queryInterface.removeColumn(<TableName>, <columnName>);</pre>
<h3 name="cfca" id="cfca" class="graf graf--h3 graf-after--pre">Model Associations</h3>
<h3 name="d10a" id="d10a" class="graf graf--h3 graf-after--h3">One to One between Student and Scholarship
</h3>
<p name="fceb" id="fceb" class="graf graf--p graf-after--h3"><code