Kysely first impressions - Typescript SQL query builder and migrations
ฝัง
- เผยแพร่เมื่อ 5 มิ.ย. 2023
- In video we take a quick look at a new Typescript SQL query builder which optimizes for type-safety and the best possible intellisense. We'll walk through how to use it to connect to a database and perform typical CRUD queries, we'll explore how to simulate relational queries, and finally we'll give their migration feature a try!
Regarding source of truth remarks, the database should be the source of truth, and we encourage our users to use type generation solutions (kysely-codegen OR prisma-kysely) in production with Kysely.
btw, PostgreSQL users can also use kanel-kysely now.
Why in procution tho? Isn't type generation for developer experience?
@@paulojose7568 to avoid drifts, where the db schema changes and the codebase doesn't, and vice versa.
I really have been enjoying watching this video and the drizzle one 👏👏👏 really interesting to watch your first impression and how you walk through some of the gotchas you come across.
Thank you! It’s sort of a “learn with me” format that I wasn’t sure if people would watch. Thanks for feedback!
I love Kysely, I'm currently using it in my project. I also love Prisma and still use it for schema definition and migrations, so I combined the two to kinda get the best of both worlds, with a package named "prisma-kysely", which is a generator for Prisma (just like the official one). Highly recommend this solution!
Interesting approach! I think most people are looking at query builders though more for performance reasons, so I wouldn’t expect most to be mixing those two specifically. You might want to check out my previous video on Drizzle which I think is the balance you’re looking for where it still takes care of schema definition + migrations but also provides query builder + relations api. Although to be fair it’s not anywhere near the maturity of prisma
Hey, @TheEdoRan I am using the 'prisma-kysely" package and I noticed that it generates the types from the schema.prisma file rather than generating the types based on the database schema. Isn't it best practice to generate the types from the databse as @Igal Klebanov mentioned. Thank you in advance and thanks for the package.
The problem is it’s mixing philosophies. If you’re using prisma for migrations it promotes making the schema itself the source of truth.
www.prisma.io/docs/concepts/components/prisma-migrate/mental-model
Whereas kysely on its own is meant to use the database as the source of truth
@@bonsayeb9620 I tried to reply but my comments don't show up, don't know why.
I use Kysely in all my projects, it's actually a feature that it translates directly to SQL behavior, like the Relations subject. SQL is transferrable knowledge.
Really good point! I do think after a few minutes of using it it seems pretty intuitive to use. I think the relations thing using the json utils is just a learning curve for me personally
@@mariusespejo it's good not having these things "blackboxed", for example, now you are 100% sure it's a single query getting everything and if one day you need to optimize creating additional indexes, you will know what is the bottleneck.
Hey, do you still use it?
@@user-lj4lo7cx7m i do
@@user-lj4lo7cx7m I do!
Regarding order of clauses, the flow of writing a query is similar to how you'd do with raw SQL, or how the engine might do it. You first handle data sources (from, joins), then you might filter rows (where), then you might group (group by), then you project (select), then you might filter groups (having) and then you might limit, offset, order by, distinct, etc.
Like I mentioned in the video I do understand the flow coming from JS/TS, like if we were thinking of these as an array it might look like
sourceArr.map(selectXYZ).filter(whereX).sort(orderByX)….
but I was calling out the fact that most people would expect to write a SQL query like this:
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;
Even if that’s not intuitive (I agree it makes a ton of sense to declare your sources first) this is how the majority of SQL documentation is written. I’m not claiming that either approach is right/wrong, just that it’s not what I personally would have expected
@@mariusespejo I understand! thanks for a great video. :)
My interest in query builders started after being "bitten" by ORM(s). Although the bar seems a bit high for me (need to practice SQL), but I'll be sure to use Kysely in some personal project :)
Thanks for the video.
♥from Kysely.
Hi marius , you have the best nestjs course on youtube , i wish if you can build projects with it , maybe using monorepos with react or nextjs , i really have problem with this , and talk more about best practises in nestjs in security performance and auth
I will be so happy thank you my freind you are the best
This would indeed be a nice natural follow up to the available material. Would take a lot of work to create, but it would be a unique resource on TH-cam.
What's your advice for quering a relational db in a nest project? Prisma is out of question, Typeorm works but is getting old and I had some compatibility problem in the past, Drizzle looks promising, Kisely too but undersupported. Your opinion?
Why is prisma out of the question? It’s still a great option. Typeorm is old but it’s probably the one that integrates best with Nest. Drizzle looks great but it’s young, lots of things are missing or broken. Kysely is a query builder only so you won’t get much help with relations
@@mariusespejo I wanted to switch to Prisma at some point, then discovered that is problematic in aws lambdas, and that for performance / efficiency is horrible. Considering that at the moment the biggest thing in the aws' bill is aurora's I/O i'm just scared to put it in any project. I've read that someone use it in tandem with Kysely, but adding another point of failure/incompatibility make me anxious. I would prefer something that just works, but it's not in the node's dna.
Yup so context is definitely important here, prisma actually just made some recent improvements so that cold starts are 9x faster. But if that’s still not enough, then yeah Kysely/drizzle is probably the lightest option. I think they both optimize towards sending a single SQL query. But yes both are young, but I mean yeah all the node orms have their own issues haha
undersupported? 🤷♂
Could you share that VS Code theme?
I like kysleys idea, but it is not ready for me at the moment.
I had a few problems with migrations and relations.
Yup same for drizzle, I’d give a little time to mature
Hey @escarlow 👋
What are you missing? have you tried our issues section OR discord server?
I use kysely with react native, works fine with some preparation
You use it to query a local sqlite db I assume?
@@mariusespejo yes
Nice video...do you have any idea on how to integrate this into Nestjs?
Thanks! Yep take a look at this: github.com/kazu728/nestjs-kysely
@@mariusespejo thank you
would you consider to testdrive Orchid ORM too?
Just being honest, ~300 stars on github tells me not very many people have used it and it’s not well known, which effectively to me means it’s not battle tested. What’s special about it?
@@mariusespejo Well, I see your point. What intrigued me is its focus on Postgres. It's still very young, albeit less young than Drizzle, but it looks very good already (despite of the userbase rather limited). I think that the Overview page and a skim to the docs can offer a good idea.
At quick glance I don’t see anything there that drizzle and others isn’t already doing. And it being a postgres-only solution is a massive limitation in my opinion. But if it works for you don’t let me stop you!
Can you compare this to the sqlc-gen-typescript?
We’re talking apples to oranges there. Sqlc from my understanding makes you write real sql code and generates types off of it, it requires a build step to generate. Kysely allows you to use a query builder that closely resembles sql and is type-safe, no codegen or build step required
i loved your drizzle video few months back & now watching keysely.
bdw, can you do a video on sql migrations? up & down migrations? plus rollbacks. and just raw sql query with sqlite.
orms are good but lately been burned by drizzle while trying to perform migrations on production vps using docker. so i was looking at kysely where migrations are a bit simpler (at least with docker) but i think in the end, i'd rather just use raw sql queries in .sql files.
i'd love if you can cover better-sqlite3 + sqlite in .sql files + migrations (up & down) + rollback in a video with raw sql queries. ik it doesn't give types but i believe it is the best production grade solution with no dependencies so dont need to worry about orm-specific errors & all.
no one really covers this but i saw signal desktop use it. i searched sourcegraph for "db.prepare AND better-sqlite3" & found it but no tutorials & examples using raw sql. at least the ones covering migrations & rollbacks. you seem to have good db knowledge (as seen in your videos) so would love it if you can take this as a video request.
What problems did you run into with drizzle migrations? Doesn’t it just generate raw sql that you can change as needed? Although I think drizzle doesn’t have the up/down pattern. Kysely does but it’s now raw sql. I’m not sure I’ve run into a solution that offers that up/down pattern without needing to write the migration in a JS api instead of raw sql
Can I have both orm like
Micro and macro in my project...
??
Thanks
Not sure what you’re asking here
@@mariusespejo like say prisma and microorm in one project
Or In c# entity framework and dapper in one project
Oh like combining two solutions? Yeah I don’t see why you couldn’t
@@mariusespejo yep....🎈🎈🎉🎉🥰🥰
Hey, do you have any interest on trying to make videos about Google OAuth with NestJS? But rather the obvious simple one, I'd glad if you'd also implement it "real-world" like! Maybe add something interesting stuff like saving things to DB, add authorization along, and probably combine it with some JWT or anything!
I’ve covered authentication + JWT before on the channel for Nestjs using Passport, you’d just need to swap the strategy to a google one. I also have several videos on saving things to the database using various ORMs, and also covered authorization using Casl + guards. Everything you’re looking for is already there you just need to mix all the ideas together. But I’ll think about a full project video or something soon
@@mariusespejo yea i know it! just an idea for you. maybe you'd interested on it! (because I just done something almost similar that's why my advice was kinda specific haha).
Bro why the nestjs show the old playground while using the graphql apollo server 😢 . I want to use modern apollo server playground to configure. i am tired
I suggest asking in the repo’s github
@@mariusespejo what should i show my repo or where should i ask the question ❓
github.com/nestjs/graphql
Are you just looking for the sandbox? docs.nestjs.com/graphql/quick-start#apollo-sandbox
Btw this video really has nothing to do with nest or graphql. If you can’t find what you’re looking for, please ask in stackoverflow
Regarding ambiguities, Kysely is just a query builder. Nothing more, nothing less. It compiles to SQL you want it to, and passes back what the underlying drivers return. No magic. You can use plugins to alter queries or results. If something's confusing you in the results, that's just SQL being confusing.
Yup, fair enough! Btw thanks for the inputs, it’s great to hear from someone actually working on the project!
I'm not sure if you've suggested that people should become really good at SQL before using ORM or SQL builders, but it's important.
I believe I did, I mean I think it doesn’t even make sense to use a query builder if you don’t understand the query you’re building :)
Other approaches like Prisma however try to function at a high enough abstraction that I think it sort of allows people to be not as good at sql. But the tradeoff is performance because prisma generates the underlying sql for you, and it’s not always what you’d expect if your wrote it yourself. Hence why these solutions like drizzle and kysely are getting a lot of attention lately
@Marius Espejo, I remember ActiveRecord from RoR receiving a lot of attention, but under the hood, you could see the SQL it generated, and most of the time, it was not optimized. Sometimes it created multiple SQL queries for certain tasks, which made me skeptical. I hope we can put our trust in these ORMs. The thing I love about these ORMs is the migration feature, something I've found missing in Mongoose :(
@@jamesdenmark1396 how many years you haven't used RoR ActiveRecord? I think the optimization part changed since
i dont know why but you always laughing something, and that makes me giggling.
sorry for my bed england fhkdgjzdegewa
😂
Ugh, another Async library...
I can be sure that whatever code base you're working on right now, there is more than twice as much "await asyncFunc" than "unAwaitedAsyncFunc".
Huh?