The Provider Table

[00:00 - 00:04] Let's start doing some work on our database. But before we do that, I want to introduce SchemaLint.

[00:05 - 00:16] SchemaLint is a tool I like to use in conjunction with Kannell. I've already spoken about how I like Linter rules and how I think they have the potential to be much more than just code formatting rules.

[00:17 - 00:26] I like to refer to them as architecture assertions. So there are little rules that define this is the way we write code in this particular project.

[00:27 - 00:43] And when we're letting the database be our source of truth like we're doing when we use Kannell and the Postgres is sort of driving the architecture, there are certain rules that have to be enforced at the Schema level if we want to do so at all. And that is what SchemaLint allows us to do.

[00:44 - 01:04] We'll start by installing it as a developer dependency in the Schema package. And I will just copy this configuration file and we can try and run it and see where it comes out.

[01:05 - 01:14] So, SchemaLint RC.js, this is again a JavaScript configuration file. Let's try and run it.

[01:15 - 01:27] There. So we get four error messages and a SQL string for a suggested fix here.

[01:28 - 01:34] Let's quickly take a look at what our actual migration looked like. So this is the users table that we have.

[01:35 - 01:48] It has an ID which is a serial primary key, an email which is a 255 character var char and the same goes for password. And let's take a look at the configuration file here.

[01:49 - 01:56] So the rules that we have set up, these are all built in rules in SchemaLint. So the real power comes from when you start to develop your own rules.

[01:57 - 02:03] But it does have some useful built in ones and these are the ones that we're using here. The first one is name casing.

[02:04 - 02:26] So we're specifying that we want to use snake casing which is sort of a Post gres standard. So that means, words are separated by an underscore and Postgres suggests that you do that because if you use uppercase characters in your names, which is possible, then you have to enclose all of your names in double quotes whenever you write a SQL statement.

[02:27 - 02:34] And that can be just a little bit tedious. So it's easy to forget and you can get confusing error messages.

[02:35 - 02:50] On the other hand, you may consider wanting to do so because it will make the naming consistent with what you might have in your TypeScript types. So I don't think I have a very strong opinion on it, but for this, we're using the default which is snake case.

[02:51 - 02:59] So we're saying if anything is named with anything other than snake casing, it should report an error. However, our users table is just called users.

[03:00 - 03:03] So there's just one word. So actually we can't know whether or not this is snake case or not.

[03:04 - 03:10] But it's a perfectly valid snake case name. The second rule we're using here is name inflection.

[03:11 - 03:17] And so we're saying we want singular names. And that is a matter of a bit of controversy in database world.

[03:18 - 03:28] If you want to name your tables singular or plural, that is there are pros and cons to both. I tend to like using singular.

[03:29 - 03:43] That means that my database names are consistent with my type names because I want to refer to a user in my code, not to a users, which you may recall is what Canal actually generated for us. So we have this interface called users here.

[03:44 - 03:53] And that is misleading in my opinion, because this is reflecting one instance of this. So we could make it so that this was actually changed.

[03:54 - 03:58] Obviously, the casing here has already been changed. So you could argue that it's perfectly fine.

[03:59 - 04:07] I believe that this is what they do in Ruby on Rails, for instance. But I think personally that it's nicer to have my database tables named singular.

[04:08 - 04:15] So that just matches what I have. And I've set this rule up to try and enforce this here.

[04:16 - 04:22] One thing to keep in mind is that inflection is difficult. It's much more difficult in many other languages.

[04:23 - 04:26] But even in English, it's not. There's a lot of irregular words.

[04:27 - 04:36] So trying to figure out if a table should be called person or people. It's not always just a matter of whether or not it ends with an S.

[04:37 - 04:49] So this uses a little dictionary that was available online somewhere. But you may find that if you're using some very unusual words, it won't catch whatever you're doing.

[04:50 - 04:57] So that's a little bit to keep in mind. But for most words, it should work at least if you're using English.

[04:58 - 05:04] The third rule here is something that doesn't apply to anything we have just yet. But I always think it's nice to put in place.

[05:05 - 05:16] So it says that if any column is of the JSON type, it should probably be JSON B instead. So the JSON type was introduced in an older version of Postgres.

[05:17 - 05:24] And JSON B is superior in just about every way. So it stores the JSON in a binary format instead of just the raw string.

[05:25 - 05:35] And that allows some better indexing, some better queries, and some better performance overall, better storage. But there may be a few situations where you want to use the regular JSON.

[05:36 - 05:44] But as a general rule, this is what you should be sticking to. So we'll set up that rule just to have it in place already.

[05:45 - 06:01] The fourth rule here is what we're actually seeing two eras of as well. So email and password are both of the Varchar type, which is something if you 've been coming from a SQL server or MySQL, that may seem intuitive to you.

[06:02 - 06:14] But in Postgres, they actually recommend officially that you always just use the text type, which is of variable length. And it doesn't have any implications in terms of performance or storage space.

[06:15 - 06:26] So creating this artificial limitation to how many characters your string can have is actually not getting you anything. So that is why the official recommendation is to always use the text.

[06:27 - 06:39] And that is what we're being told down here that we should do for both the email and password columns. And you can see down here that it suggests how we might change it if we want to create a migration that does that.

[06:40 - 06:58] The final one is that we should prefer identity to serial. So we're using this serial type here, which is a sort of macro that Postgres has that creates an index and turns this into a numerical type.

[06:59 - 07:12] The identity is more of a SQL standard and the good folks at Postgres are now suggesting that we should use that instead. The schema length documentation has links to explanations for these rules if you want to look further into it.

[07:13 - 07:17] You may want to make exceptions to it. But for now, we are going to use this rule as well.

[07:18 - 07:23] So there's a little suggesting how we can alter our table here. I think we'll just change our migration.

[07:24 - 07:38] But if we wanted to make a new migration, we could use pretty much this SQL that is suggested here verbatim. Just to finish up what the configuration states here, it says that the schema we're going to be looking at is the one called public.

[07:39 - 07:41] That is the only one we have right now. So that makes perfect sense.

[07:42 - 07:56] We might install some plugin or something that creates an additional schema. And if that runs its own database stuff, maybe we don't want to run any lending rules on that because it's a third party thing.

[07:57 - 08:13] And finally, we want to ignore the two next migration tables because those are also not there out of our scope. So if they were named, say with a different naming convention that we would prefer, or if any other rule might break on those two tables, we want to not care about it .

[08:14 - 08:30] So we're specifying that these two tables should ignore any rule that is failing on them. Since our migration hasn't been run on any production database, there is no need to create an additional migration to roll back these changes.

[08:31 - 08:51] Let's just reset it instead and we can work on the migration file that we already have. So sorry that this is there.

[08:52 - 09:06] It should now run this down migration that drops the table users and we are free to reset everything. So schemalin suggested that we rename our users table user.

[09:07 - 09:12] But coincidentally, that's a reserved word in Postgres. So you want to be careful doing that.

[09:13 - 09:25] It is possible if you use quotes everywhere, just like you would have to do if you used uppercase letters. But I typically get around this by calling my table member or something instead .

[09:26 - 09:41] In this case, I think it makes sense for us to actually split things up into two, because we have providers and customers and I think they're quite distinct. So the providers are probably going to be logging in and looking at their schedule and so on, but customers are not going to be logging in anywhere.

[09:42 - 10:00] So they might be registered by an email, but they don't actually really have an account. So rather than having just one table for both of them and then setting up a complex roll system, let's just create two tables instead that differentiate between providers and customers.

[10:01 - 10:11] Both of these tables are going to have an email address column. And while schemalin suggests that we should be using text rather than varchar for this column, we can actually do a little bit better.

[10:12 - 10:30] So Postgres has something called domains, which is a way to add additional constraints to a type. And there is a built-in extension called CI text, which is short for case insensitive text that will create a type that is always case insensitive, which is nice for email addresses.

[10:31 - 10:45] And we can create our own domain that expands on this further by adding a regular expression check to make sure that a string is a valid email address before we allowed inserted into a column. So let's try and add those two things.

[10:46 - 10:50] So I will paste in this code. Let me just close this down here.

[10:51 - 11:00] So we start by creating or adding the extension CI text. And then we expand on that domain by creating a new domain called email.

[11:01 - 11:10] And that involves this check, which is a regular expression check. And one thing to be vigilant of here is that this is a string within a string.

[11:11 - 11:15] So we have this string out here. And in here we're inserting a string as well.

[11:16 - 11:26] So because of that, there's sort of two levels of escaping going on, which is a little bit confusing when you're writing regular expression. So that's just something to keep in mind.

[11:27 - 11:41] We will update our down migration to drop these two things so that we're back to square one when we reach this point. So with that in place, we can now create our provider table.

[11:42 - 11:52] I will copy this create statement here. Let me just fix this indenting actually there.

[11:53 - 12:01] So we're now creating a table called provider, which is a singular word. It's snake case in theory because there's just one word.

[12:02 - 12:09] So it's at least a valid name for all our rules. And we're now using the identity instead of the serial for creating our ID.

[12:10 - 12:15] So this is an int. And this long expression here turns it into an identity.

[12:16 - 12:24] So this is a standard SQL, which is why it's a little bit better than using the serial. And you can look up the details on that if you were interested.

[12:25 - 12:40] We'll still add the name as a regular text, but we can now add the email column as the email type that we created up here. And we will specify that it needs to be unique so that no two providers can have the same email address.

[12:41 - 13:00] We should add a drop statement for this as well in our down migration. And one thing to note here is that this obviously needs to happen in the opposite order so that we don't first drop things that depend on other things.

[13:01 - 13:23] Let's try and run our new migration and then run schema lint and kennel again and see what happens. I'll migrate to latest and then try and run schema lint.

[13:24 - 13:30] No issues detected. All right, let's try and run kennel now.

[13:31 - 13:37] So we can see that it has created in addition to the index file. We now have two other files which is provider and email.

[13:38 - 13:49] So the domain that we created is actually also turned into a type. On the other hand, we should be able to see here in our public folder that the user's file that we had before is gone.

[13:50 - 13:57] And that is because we have the pre delete models folder set to true. So every time we generate models, it will delete everything in that folder.

[13:58 - 14:04] And that was the user's file which is now gone. So we just have the email and the provider table.

[14:05 - 14:10] Let's take a look at the provider. We have this which has the email address set to email.

[14:11 - 14:25] So email refers to the email type that is generated here. However, kennel doesn't know what the email domain should be because it has this constraint and it refers to CI text.

[14:26 - 14:35] And you can see here that it tells us that it doesn't know what CI text is. So there are a couple of things we can do here.

[14:36 - 14:46] We could add a comment directly in the database on the domain and specify the type there. There's a specific tag pattern that you can use for doing so with kennel.

[14:47 - 15:01] Alternatively, we could specify in the kennel configuration what email should refer to and create either a specific type for it or just use string. What we're going to do now is just tell kennel that public.ci text should be a string.

[15:02 - 15:15] And you can argue that this isn't completely correct because CI text obviously is a case insensitive string. So we're going to have something that's a little bit wider in TypeScript land than in our actual database.

[15:16 - 15:25] But I think we can live with that for now. Let's change our kennel configuration to add a custom type map.

[15:26 - 15:39] We'll add that at the bottom here. So this specifies that anytime something is of the type public.ci text, it should use this type in TypeScript land.

[15:40 - 15:50] Let's try and run kennel again and see if that helps. We now don't get any warning message and let's take a look at the emails hype here.

[15:51 - 16:02] Email is now type string. So the provider table here that has an email, even though this is called email, this is basically just a string and there's no branding or anything going on here.

[16:03 - 16:08] So this is just a macro, if you will, to a string type. That still fixes our problem, at least for now.