WEBVTT 1 00:00:00.220 --> 00:00:01.680 Hi guys, and welcome back. 2 00:00:01.680 --> 00:00:03.060 In this video, we're going to learn 3 00:00:03.060 --> 00:00:06.050 about SQLite vs. PostgreSQL, 4 00:00:06.050 --> 00:00:07.453 and how they are different. 5 00:00:08.350 --> 00:00:10.450 The first difference is in database size 6 00:00:10.450 --> 00:00:12.130 and the setup time. 7 00:00:12.130 --> 00:00:15.350 SQLite is very easy to set up and use, 8 00:00:15.350 --> 00:00:17.780 the database is a small, single file 9 00:00:17.780 --> 00:00:18.613 as we've seen. 10 00:00:18.613 --> 00:00:21.780 For example, with a file called data.db. 11 00:00:21.780 --> 00:00:24.480 But, PostgreSQL is quite large in size, 12 00:00:24.480 --> 00:00:26.900 and it can require a lengthy setup. 13 00:00:26.900 --> 00:00:30.100 A PostgreSQL database has a server accepting 14 00:00:30.100 --> 00:00:32.650 incoming connections and responding to them, 15 00:00:32.650 --> 00:00:34.980 and, you know, it is quite large, 16 00:00:34.980 --> 00:00:36.800 and can be difficult to set up. 17 00:00:36.800 --> 00:00:38.680 SQLite is pretty easy to set up, 18 00:00:38.680 --> 00:00:40.410 and it's normally a small file. 19 00:00:40.410 --> 00:00:42.980 This directly affects the speed. 20 00:00:42.980 --> 00:00:45.910 Only one SQLite connection can write to 21 00:00:45.910 --> 00:00:47.580 the database at once. 22 00:00:47.580 --> 00:00:49.390 That is because it's in a single file, 23 00:00:49.390 --> 00:00:52.540 and you can only have one thing changing 24 00:00:52.540 --> 00:00:54.400 a file at a time. 25 00:00:54.400 --> 00:00:56.690 So therefore, when you're connecting to SQLite 26 00:00:56.690 --> 00:00:58.650 from say, multiple different applications 27 00:00:58.650 --> 00:01:00.960 to a single SQLite database, 28 00:01:00.960 --> 00:01:03.960 only one of those can change the file at a time. 29 00:01:03.960 --> 00:01:05.720 If two of them want to change the file, 30 00:01:05.720 --> 00:01:08.840 one of them has to wait until the other one's done. 31 00:01:08.840 --> 00:01:10.740 So, SQLite is great for reading 32 00:01:10.740 --> 00:01:13.410 because when you're reading you don't have this limitation, 33 00:01:13.410 --> 00:01:15.900 but not so great for writing. 34 00:01:15.900 --> 00:01:17.650 PostgreSQL doesn't have this limitation, 35 00:01:17.650 --> 00:01:19.860 so it's good for both. 36 00:01:19.860 --> 00:01:22.540 We've also got the issue of primary and foreign keys. 37 00:01:22.540 --> 00:01:23.690 We've already discussed this. 38 00:01:23.690 --> 00:01:27.760 PostgreSQL forces us to use foreign key constraints. 39 00:01:27.760 --> 00:01:30.590 So, SQLite has to have this enabled, 40 00:01:30.590 --> 00:01:32.453 and you can disable it. 41 00:01:33.330 --> 00:01:34.740 For example, with users and accounts. 42 00:01:34.740 --> 00:01:36.360 Let's say we've got an accounts table 43 00:01:36.360 --> 00:01:38.880 with a foreign key of user_id, 44 00:01:38.880 --> 00:01:41.200 and you've got a user with a primary key. 45 00:01:41.200 --> 00:01:42.500 As we've learned already, 46 00:01:42.500 --> 00:01:45.950 you can't delete a user that owns an account 47 00:01:45.950 --> 00:01:48.620 without first deleting the account. 48 00:01:48.620 --> 00:01:50.200 That is because if you delete the user 49 00:01:50.200 --> 00:01:51.430 and you leave the account, 50 00:01:51.430 --> 00:01:54.150 when you create a new user, if it has the same ID, 51 00:01:54.150 --> 00:01:56.560 it would inherit the account. 52 00:01:56.560 --> 00:02:00.680 In PostgreSQL though, we've got CASCADE constraint. 53 00:02:00.680 --> 00:02:03.270 With a CASCADE constraint, we can tell PostgreSQL 54 00:02:03.270 --> 00:02:05.650 what to do with an account when we delete 55 00:02:05.650 --> 00:02:07.900 the corresponding user. 56 00:02:07.900 --> 00:02:08.733 So, for example, 57 00:02:08.733 --> 00:02:10.930 here we've got some code that creates a table, 58 00:02:10.930 --> 00:02:13.670 and let's say that we create this accounts table, 59 00:02:13.670 --> 00:02:16.520 with a user ID and account number. 60 00:02:16.520 --> 00:02:18.310 It's got a foreign key of user ID 61 00:02:18.310 --> 00:02:20.630 that references the users table. 62 00:02:20.630 --> 00:02:23.750 And, we have ON DELETE CASCADE. 63 00:02:23.750 --> 00:02:27.090 So, let's take a look at what ON DELETE CASCADE does, 64 00:02:27.090 --> 00:02:29.980 as well as some other options that we've got available. 65 00:02:29.980 --> 00:02:31.550 So, we've got here ON DELETE CASCADE, 66 00:02:31.550 --> 00:02:33.090 and this diagram here represents 67 00:02:33.090 --> 00:02:36.300 the relationship between user and account. 68 00:02:36.300 --> 00:02:37.550 So, what we're saying here 69 00:02:37.550 --> 00:02:40.510 is that one user can have many accounts. 70 00:02:40.510 --> 00:02:42.930 The box on the left represents the users table, 71 00:02:42.930 --> 00:02:44.090 or user row, 72 00:02:44.090 --> 00:02:46.330 and the account on the right represents 73 00:02:46.330 --> 00:02:49.060 the accounts table, or multiple account rows. 74 00:02:49.060 --> 00:02:51.070 So, then we've got ON DELETE RESTRICT. 75 00:02:51.070 --> 00:02:52.260 This is a different option 76 00:02:52.260 --> 00:02:54.470 that we have available in PostgreSQL, 77 00:02:54.470 --> 00:02:55.930 and we also have the default, 78 00:02:55.930 --> 00:02:58.770 which is ON DELETE NO ACTION. 79 00:02:58.770 --> 00:03:00.680 So, when we create a foreign key 80 00:03:00.680 --> 00:03:02.640 with the ON DELETE CASCADE constraint, 81 00:03:02.640 --> 00:03:04.220 as we showed just a moment ago, 82 00:03:04.220 --> 00:03:07.950 what happens when we delete the user that has an account, 83 00:03:07.950 --> 00:03:09.640 is this. 84 00:03:09.640 --> 00:03:11.570 Essentially, it cascades. 85 00:03:11.570 --> 00:03:13.720 The deletion cascades through both tables, 86 00:03:13.720 --> 00:03:15.840 and you delete the account as well. 87 00:03:15.840 --> 00:03:17.800 If there's many accounts for that user, 88 00:03:17.800 --> 00:03:19.500 they all get deleted. 89 00:03:19.500 --> 00:03:20.660 With ON DELETE RESTRICT, 90 00:03:20.660 --> 00:03:22.870 if you try to delete the user, 91 00:03:22.870 --> 00:03:24.080 then it'll just come back. 92 00:03:24.080 --> 00:03:26.480 It won't let you delete it if it has an account. 93 00:03:26.480 --> 00:03:28.760 Notice if there are no accounts for the user, 94 00:03:28.760 --> 00:03:31.550 then the deletion would still work. 95 00:03:31.550 --> 00:03:32.440 Finally, the default, 96 00:03:32.440 --> 00:03:34.200 which is something that we've seen already, 97 00:03:34.200 --> 00:03:36.300 is that when you try to delete the user, 98 00:03:36.300 --> 00:03:37.440 you then get an error, 99 00:03:37.440 --> 00:03:38.437 if there are accounts for it, 100 00:03:38.437 --> 00:03:40.800 and it tells you, you can't delete this user 101 00:03:40.800 --> 00:03:42.650 because it has accounts related to it, 102 00:03:42.650 --> 00:03:44.980 or basically tells you that the foreign key constraint 103 00:03:44.980 --> 00:03:48.050 would be violated if you delete the user. 104 00:03:48.050 --> 00:03:49.480 So, these are the three things that we can do 105 00:03:49.480 --> 00:03:50.610 with on delete. 106 00:03:50.610 --> 00:03:52.340 And, just as a recap, 107 00:03:52.340 --> 00:03:55.260 you can delete the users account freely. 108 00:03:55.260 --> 00:03:57.310 So, if you have a user and one account, 109 00:03:57.310 --> 00:03:58.820 you can delete the account. 110 00:03:58.820 --> 00:03:59.660 And, that's totally fine, 111 00:03:59.660 --> 00:04:01.780 that doesn't violate any constraints. 112 00:04:01.780 --> 00:04:05.670 But, you can't delete the user that has an account, 113 00:04:05.670 --> 00:04:08.440 unless you delete the account first, of course, 114 00:04:08.440 --> 00:04:11.450 or you use ON DELETE CASCADE. 115 00:04:11.450 --> 00:04:13.300 If you use ON DELETE RESTRICT, 116 00:04:13.300 --> 00:04:16.290 you can't delete the user, it will just come back. 117 00:04:16.290 --> 00:04:17.950 And, if you use ON DELETE NO ACTION, 118 00:04:17.950 --> 00:04:18.980 which is the default, 119 00:04:18.980 --> 00:04:21.301 then that would raise an error. 120 00:04:21.301 --> 00:04:23.220 All right, thank you guys for watching this video. 121 00:04:23.220 --> 00:04:24.330 I hope you've learned something, 122 00:04:24.330 --> 00:04:25.980 and I'll see you in the next one.