Kysely first impressions - Typescript SQL query builder and migrations

Поділитися
Вставка
  • Опубліковано 5 жов 2024
  • 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!

КОМЕНТАРІ • 71

  • @igalklebanov921
    @igalklebanov921 Рік тому +28

    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.

    • @igalklebanov921
      @igalklebanov921 Рік тому +1

      btw, PostgreSQL users can also use kanel-kysely now.

    • @paulojose7568
      @paulojose7568 10 місяців тому +5

      Why in procution tho? Isn't type generation for developer experience?

    • @igalklebanov921
      @igalklebanov921 10 місяців тому

      @@paulojose7568 to avoid drifts, where the db schema changes and the codebase doesn't, and vice versa.

  • @TheEdoRan
    @TheEdoRan Рік тому +5

    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!

    • @mariusespejo
      @mariusespejo  Рік тому +1

      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

    • @bonsayeb9620
      @bonsayeb9620 Рік тому +1

      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.

    • @mariusespejo
      @mariusespejo  Рік тому +2

      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

    • @TheEdoRan
      @TheEdoRan Рік тому +1

      @@bonsayeb9620 I tried to reply but my comments don't show up, don't know why.

  • @Norfeldt
    @Norfeldt Рік тому +2

    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.

    • @mariusespejo
      @mariusespejo  Рік тому +2

      Thank you! It’s sort of a “learn with me” format that I wasn’t sure if people would watch. Thanks for feedback!

  • @thelinuxlich
    @thelinuxlich Рік тому +7

    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.

    • @mariusespejo
      @mariusespejo  Рік тому +3

      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

    • @thelinuxlich
      @thelinuxlich Рік тому +3

      @@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.

    • @user-lj4lo7cx7m
      @user-lj4lo7cx7m 5 місяців тому +1

      Hey, do you still use it?

    • @thelinuxlich
      @thelinuxlich 5 місяців тому

      @@user-lj4lo7cx7m i do

    • @thelinuxlich
      @thelinuxlich 5 місяців тому

      @@user-lj4lo7cx7m I do!

  • @igalklebanov921
    @igalklebanov921 Рік тому +1

    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.

    • @mariusespejo
      @mariusespejo  Рік тому +2

      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

    • @igalklebanov921
      @igalklebanov921 Рік тому +1

      @@mariusespejo I understand! thanks for a great video. :)

  • @mohammedgomaa8851
    @mohammedgomaa8851 5 місяців тому

    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.

  • @gamingwolf3385
    @gamingwolf3385 Рік тому +1

    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

    • @ragura
      @ragura Рік тому

      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 UA-cam.

  • @igalklebanov921
    @igalklebanov921 Рік тому +2

    ♥from Kysely.

  • @veritatas678
    @veritatas678 Рік тому +2

    I like kysleys idea, but it is not ready for me at the moment.
    I had a few problems with migrations and relations.

    • @mariusespejo
      @mariusespejo  Рік тому

      Yup same for drizzle, I’d give a little time to mature

    • @igalklebanov921
      @igalklebanov921 Рік тому

      Hey @escarlow 👋
      What are you missing? have you tried our issues section OR discord server?

  • @ГенаПетров-н5ы
    @ГенаПетров-н5ы Рік тому +1

    I use kysely with react native, works fine with some preparation

  • @rawlespringer3917
    @rawlespringer3917 4 місяці тому +1

    Nice video...do you have any idea on how to integrate this into Nestjs?

    • @mariusespejo
      @mariusespejo  4 місяці тому +1

      Thanks! Yep take a look at this: github.com/kazu728/nestjs-kysely

    • @rawlespringer3917
      @rawlespringer3917 4 місяці тому

      @@mariusespejo thank you

  • @StartupSpells
    @StartupSpells 7 місяців тому

    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.

    • @mariusespejo
      @mariusespejo  7 місяців тому

      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

  • @AlessioCollura
    @AlessioCollura Рік тому +1

    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?

    • @mariusespejo
      @mariusespejo  Рік тому +2

      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

    • @AlessioCollura
      @AlessioCollura Рік тому

      @@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.

    • @mariusespejo
      @mariusespejo  Рік тому +1

      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

    • @igalklebanov921
      @igalklebanov921 Рік тому +1

      undersupported? 🤷‍♂

  • @igalklebanov921
    @igalklebanov921 Рік тому +1

    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.

    • @mariusespejo
      @mariusespejo  Рік тому +1

      Yup, fair enough! Btw thanks for the inputs, it’s great to hear from someone actually working on the project!

  • @permanar_
    @permanar_ Рік тому

    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!

    • @mariusespejo
      @mariusespejo  Рік тому +1

      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

    • @permanar_
      @permanar_ Рік тому

      @@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).

  • @ThapakornTantirattanapong
    @ThapakornTantirattanapong 9 місяців тому

    Can you compare this to the sqlc-gen-typescript?

    • @mariusespejo
      @mariusespejo  9 місяців тому

      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

  • @jamesdenmark1396
    @jamesdenmark1396 Рік тому +2

    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.

    • @mariusespejo
      @mariusespejo  Рік тому +2

      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

    • @jamesdenmark1396
      @jamesdenmark1396 Рік тому +1

      @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 :(

    • @simomed5002
      @simomed5002 Рік тому

      @@jamesdenmark1396 how many years you haven't used RoR ActiveRecord? I think the optimization part changed since

  • @h3mb3
    @h3mb3 Рік тому

    Could you share that VS Code theme?

  • @paolo-e-basta
    @paolo-e-basta Рік тому

    would you consider to testdrive Orchid ORM too?

    • @mariusespejo
      @mariusespejo  Рік тому

      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?

    • @paolo-e-basta
      @paolo-e-basta Рік тому

      ​@@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.

    • @mariusespejo
      @mariusespejo  Рік тому

      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!

  • @saurabhtalele1537
    @saurabhtalele1537 Рік тому

    Can I have both orm like
    Micro and macro in my project...
    ??
    Thanks

    • @mariusespejo
      @mariusespejo  Рік тому

      Not sure what you’re asking here

    • @saurabhtalele1537
      @saurabhtalele1537 Рік тому

      @@mariusespejo like say prisma and microorm in one project

    • @saurabhtalele1537
      @saurabhtalele1537 Рік тому

      Or In c# entity framework and dapper in one project

    • @mariusespejo
      @mariusespejo  Рік тому +1

      Oh like combining two solutions? Yeah I don’t see why you couldn’t

    • @saurabhtalele1537
      @saurabhtalele1537 Рік тому

      @@mariusespejo yep....🎈🎈🎉🎉🥰🥰

  • @nabinsaud4688
    @nabinsaud4688 Рік тому

    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

    • @mariusespejo
      @mariusespejo  Рік тому

      I suggest asking in the repo’s github

    • @nabinsaud4688
      @nabinsaud4688 Рік тому

      @@mariusespejo what should i show my repo or where should i ask the question ❓

    • @mariusespejo
      @mariusespejo  Рік тому +1

      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

  • @twitchizle
    @twitchizle Рік тому

    i dont know why but you always laughing something, and that makes me giggling.

  • @anasouardini
    @anasouardini Рік тому

    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".