Kysely first impressions - Typescript SQL query builder and migrations

keasley is not an Oram Keely does not have the concept of relations it is a query Builder which is just some magical typescript black magic that I've never seen before so it's entire documentation is here like in my intellisense absolutely super useful hey how's it going this is a quick First Impressions video on kiesley a typesafe SQL query builder for typescript I kind of think of it as like the spiritual successor to connect.js you can see that it's heavily inspired by that so in my last video I had this micro project where we just have an index.ts file and we connect to the database and then we do some test queries off of that database using at the time drizzle but this time we're going to update it to use keasly for dependencies you can see that I originally we had TSX here and that allows us to run our our index.ts file and then we have the watch mode in here so that every time we change the code it's going to rerun and then finally we have that EnV installed that way we can pull in the database credentials from a DOT EnV file now you can see that at a glance here it looks like they primarily support these three packages PG my SQL 2 and better SQL Lite but they do have support for third-party stuff in here that they're calling dialects and I already have postgres Js installed if you saw earlier so we're gonna try and use that alright so in the terminal type that in hit enter alright so I'm going to copy some of the usage here just to get started let's empty out this interface for now I'll fill that back in later and then and if this is set up correctly I think the postgres package here will just read from environment variables my credentials by the way from the last video the database that I have is uh postgres1 from neon which is a serverless postgres and we only have two tables in here one for user which has two records in it it just has an ID and a name and then we also have a to-do table which has ID title description and a time stamp and finally a user ID to determine who the owner of these tasks are so my goal right now is to just be able to represent these two tables using kisly and the interfaces connect to the database and then see if we can write some queries to pull this data back so it looks like what we're supposed to do is basically Define an interface for each table and then we provide those as items within this database interface that we're feeding into Keely over here so like I said we have a users table let's start with that so we'll create an interface user table and that has a generated ID so it says that generated basically is for anything that's generated by a database such as serial Auto incrementing IDs and I think it says that this is so that it automatically becomes optional for inserts and updates which I think is uh very smart next we need to add a name column here which is a string we also need to create an interface for our to do table which has a title description and user ID it seems like from this documentation you have to match how the naming is on a database like if you have underscores in there you have to write it the same way I wonder if there's a way to easily transform that as view query to be you know camera case like you would expect in JavaScript anyways in our database let's add user user table to do to do table next you create your reference to the DB which is the Keely object and then sounds like we can just start running queries off of that so let's do a DB dot select from and you can see that it automatically knows that I can only provide either user or to do makes sense and is that it let's go ahead and log our result Run npm start so it says that I need to call execute or execute take first sure so I see that it is returning an array with two objects but it's empty so I guess it actually wants us to specify what we want to select I guess personally I kind of assume that to like select everything if I didn't provide it any other extra Fields so looks like oh there is a method called select all let's try that and there you go there's our expected result of the two users that we have so I guess that's just a learning curve for me let's let's try let's try selecting oh just the name it's interesting that it gives me these four different options instead of just two but I think that's probably something to do with aliases that we can try later alright so that's pretty straightforward let's go back to select all now where I want to go from here is similar to what I did in the drizzle video where I want to also select the uh the tasks the to Do's for each user so I imagine we need to add a join here let's try a left join and it says I need to provide a table to do so the second parameter must be providing the the on Clause so if we did to do that user ID do I just do something like this aha all right so there's our result you can see that I returned a new object per result that we got back and it kind of merges together all of the fields all in one object so it's hard to tell what is what like if you see in my last video they actually separated the the user object from the to do object so in the one object it was already separated for you and you can tell what was what here it's a little bit exactly like what you'd expect with a SQL query result right if you're using like a database client like it'll just return you a row with all the different uh columns on it so it makes sense but then it's hard to differentiate like for example uh this ID what is that ID for is that just for the user is that for the to do clearly in this case that ID is further to do because we have and even here it's it's weird that uh you know we have a user that has no to do items and they just have a bunch of nulls here so I mean I guess that it makes sense because I did a left join so for example if we made an inner join here I think we'll just get back the three items one thing that's a little bit odd to me is that our interface never remember I left out the created at timestamp column and it's being pulled in here so it's not like directly using that as the source of truth of what the columns are it's still kind of pulling everything from the database so I guess this is really just as you'd expect it's just additional information it's not necessarily a source of truth it's not like in drizzle where you create a a schema like an object that defines what are the individual columns and you can see that you can easily map like the the name that you have in the database and you know map it to like a JavaScript camo cased variable which is nice anyways uh getting a little sidetracked here what's interesting to me is I I kind of wonder how to decide to pull the ID from foreign added the two table instead of the user table because we're doing a select all which in my mind would have included the the user ID right I'm not sure if there's just some magic where it knows that hey the the user ID here would be the same thing and it kind of just decided that for you um I guess that's an interesting take I do see in the documentation here where you can add like aliases right in the select here so I kind of want to see how that works so let's change it so let's select and I instead want to select user ID then their name and then notice that it doesn't know about the to do because I guess it's above the join so let's move that down let's try if now it does see the to do so from like a typescript perspective I kind of can make sense of that although it's a little bit backwards to me because if I'm thinking about the sequel that I'm trying to write usually all the select statements are going to be at the top right so it's odd that you kind of have to like reorganize things so let's do to do that ID and to do that description what did that do so it's still not showing me the user ID so that part is a little confusing to me I guess if there's a ambiguity between the IDS it kind of gets confused so let's let's try adding some aliases so maybe I can do user ID as user ID and there you go you can see that the user IDs are now in each of these records and I guess it kind of just knows that oh this ID has to be the the the task ID the to-do ID let's go ahead and add a task ID here there you go one interesting thing from the result here if you take a look at the type you can see that it has user ID and task ID which is our Alias so there's some kind of like typescript black magic happening here where we gave it a string here and it magically sort of I guess parses this out and automatically Maps it over and then determines what the the underlying type for that right so like that the number in this case so I think that's kind of impressive I don't think I've ever seen anything like that before it even knows that task ID can be null I guess because uh like in this example because we're doing a left join you'll get a record that has the user but no tasks so does that mean if I change this to an inner join that's not going to be nullable anymore there you go that is pretty cool I also really like that they have these methods like execute um take first if I was trying to do a where Clause what would that look like where let's say user that ID oh nice so it even has intellisense for the different operators that you can use for user ID is 13. oh I guess I was thinking of this like just selecting a single user so if we got rid of our join here again for a second and I select and let's just do select all so I want to select just one user there's where the execute first makes a ton of sense that's kind of a really nice method to have they even have take first or throw so like if I provide a random number here is it gonna throw for me and it does the result error really nice that's uh that's an API that I don't think I saw in drizzle so I guess it like in drizzle you would have to uh like query and then if it's empty throw an error yourself or something like that I'm not sure all right what else can we do here I do like that the intellisense is absolutely incredible you got your usuals like limit and offset uh having you can do combinations of word classes like if you were doing where or where so that's really nice that seems reasonably intuitive to me personally I kind of want to actually add that created ad and try this column type thing that they have here it says that you can specify a different type for each operation wrapper here we Define a column type that is selected as a date but can optionally be provided as a string and inserts and can never be updated so that's really cool so it's like a it's like a multi-type so if we added that here and then I think we had created at I guess I need to add it to my select here and there you go you can see that it turns it into a date object if I just delete this is it giving me back a string I assume created that string or undefined that makes a ton of sense to me because uh just like it was talking about in the documentation right if you were querying a a date in the application code you probably want to turn that into a date object in a lot of cases if you're gonna do operations against it but if you're doing you know like inserts or updates uh usually you would you would provide like the iso string for the date not like a date object so that ability to kind of fuse the types together is just very very clever I also saw in the docs briefly that all the documentation behind all these are in the code itself so like you can see when I hover over this it tells me what it is like ads I select to the table and I think if I like do command click here you can see like where that's coming from and they have this massive uh comment block here that has documentation on this is I think what we were seeing earlier what it is and then plus examples which I just now notice I can actually scroll here that entire documentation is here like in my intellisense absolutely super useful where I can just hover here and it gives me what it is examples of how to use it and then it also tells me what's the generated SQL so so far I'm I'm super impressed actually when I first looked at the Kingsley docks I I was thinking that hey there's not a lot of things in here like they had a few examples for how to select which is probably enough but yeah in my head I was thinking like they definitely need to write more docs for people to understand how to use it but if like the docs is really pretty much in the code and it's reasonably intuitive to find what these methods are uh I like that a lot personally one thing to note is that they don't have a relations API again because it's a it's a query Builder they're they're Drawing the Line there that they're saying they're not an orm so they're not going to provide apis for relations in fact if you look up relations on the documentation they have this like messaging Keely is not an orm Keely does not have the concept of relations it is a query Builder now what they are saying here is that even though they don't have a relations API you can still kind of get that nice nesting that you want like if like let's say I wanted to get an array of users and then within those user objects there's uh tasks array that has the individual to do items they're saying here that well in SQL you could sort of simulate that by like uh turning things into Json and that's how you can sort of like get things nested and then uh they talk about how hey you can use that idea to create these utilities like Jason array from and they even uh provide some of these already for you in the helpers package so let's go ahead and try this out and see if we can make sense of how it works so the example here on the right they have a person table that has you know a relationship with pets it seems like so it looks like what I'll have to do is basically uh instead of doing a join I actually have to do like almost like a nested select query so let's get rid of all this and looks like you can actually provide uh a specific table here for the select all which is nice and then and then we're gonna add do another select here to do our query for the tasks so EV here is an Expression Builder let's do Json array from and then basically we're just going to select tasks which I keep saying it's tasks but really it's to do is what I'm saying that a a user has one too many tasks which which are to these uh sorry if that's confusing so anyways uh we're gonna do a can I do a select all in here too to do that where and in the example here they're using ref or to do the user ID is equal to user.id and then I've got a lot of Works quickly here because I guess I need to provide an alias as tasks and that got rid of my problem I guess we could also order by uh maybe title why is it that their example on the right doesn't have execute but you're supposed to execute but anyways uh let's see our result and there you go now we have sort of what I was looking for if we did have like a relations API we got a user object that has tasks in it let's try to select execute and take First I just want to get that output of the one user with a task so you can see better there you go you got the user you got the nested tasks so again if there was a relations API that's probably like the output that I would expect and if you watch my previous result video you can see that they have support for that so that's I think what they're trying to claim is that it doesn't block you from doing that like there's ways around uh still getting sort of the same result although it is a little bit more work and this Json array from thing is just not intuitive API in my mind and I don't know that most people would even know that you can do this to Json thing but it is an interesting solution I'll give it that uh the one thing I was curious about uh what is this where ref thing I see so a wear ref treats both sides of the operator as references whereas the regular ref treats the right side as an actual value I see so basically they're saying that this side can be a dynamic value you know depending on what it is for that specific query right like it's not a hard-coded ID like this that's why there's also a type error there so that's pretty cool but anyways they do say if this is something that you do often you can do basically your own Utilities in the user land to pull this off and then you can just use them like this which is like the cleaner way to do it but to me like I understand that you're trying to be just a query Builder but at least add at least add a relations API everyone needs to do relations because it's a relational database uh but anyways that's just my take I do wonder if again because it seems like the the owner of this the person who started this is the same guy behind objection I wonder if he's planning to create an orm on top of quizly like I could totally see that happening by the way it's probably a good idea to kind of walk through how would you do crud using Keely so we did selects uh here's some examples for how to do an insert right pretty straightforward you do insert into and then values and then I'm guessing this can also take in an array yep for multi-records you can see that in more complex scenarios the values takes in actually a function and you can do all sorts of things in here like you can do a nested sub query you can even do raw SQL speaking of raw SQL they do have this uh SQL template thing as well if you go in recipes and do raw SQL there's a link here to the SQL template tag and this is very similar to like what we've seen in drizzle where you can just pass in variables in that template string and it'll automatically parameterize it and such so that's very useful to have you know if in the worst case you have to drop down to Raw SQL you have that update similar thing update table set and then your where clause and then I imagine delete this a lot like that as well I do want to take a moment to take a look at migration so you'll notice that in typical fashion you might have seen this from uh you know connects itself or other Solutions like type or I'm you're supposed to define a an up and down method where obviously the up is what runs when you run your migration and then down I imagine this anytime you you decide to do a rollback of that migration it does say that execution order is based on the file names alphabetical order uh it doesn't it hasn't talked about any way to generate those files for you that would be something nice to have if they they have it they have some examples here for how to write your migrations it's very much a JavaScript based syntax which I imagine has just as a good typescript uh compatibility and they show that they do have this migrator function to actually run your your migrations I want to see how this actually works so I am going to copy this in the code maybe let's create a new migrations folder and let's do a test.ts and we'll just maybe try it up let me perhaps copy this I just copied over the schema the they have for creating a new person table so let's maybe clean up our index.ts here and add our migrator thing what's interesting is our example here says I'm supposed to pass any path but this says expected zero argument so not sure what's going on there I guess the path is probably in the config so I guess let's provide DB in here and it says I need to Pro give a provider all right so here's how it's supposed to do the file path thing it looks like so I'm importing path and also fs and that's being provided here provider there we go and this needs to be an absolute path migrations give it a shot npl start I probably should have added some logs I'm assuming that ran already let's take a look at our database so now if we reload our database there is a new person table here that has those fields that we created and then also those two migration tables so this was probably keeping track of yep so it's got the file name and the timestamp so that's how it's going to figure out I imagine the next time I run the command it's gonna say something like no migrations if I were to run this again I imagine that it's smart enough to know that there's no migrations to run the only thing is I I wish there were logs here it should at least tell me some something that something happened that it succeeded or there's nothing to run again this is something that from my drizzle video you can see with the user experience was a little bit better alright so other example here basically just says that if you want some logging you gotta do it yourself you gotta you gotta check the status and stuff like that which is okay I guess I get I don't know in 2023 I kind of expect my migration tools to log these things out for me but I don't know if that's an unreasonable expectation anyways guys I think that wraps up my video here for Kingsley again this was a quick sort of first impression anyways here's to hear your take what do you think of what you saw in this video would you use geisley in your project uh let me know in the comments or if you have any other feedback let me know as well hopefully you enjoy the video and I'll see you in the next one

Channel: Marius Espejo

Views: 13,917

Keywords: kysely, kysely orm, drizzle orm, drizzle-orm, prisma, typeorm, typescript, node orm, typescript query builder, type-safe sql, typescript sql, sql query builder, knex.js, knex, nextjs, theo

Id: vnSnor_C2rA

Length: 24min 15sec (1455 seconds)

Published: Tue Jun 06 2023