SQL Builder Syntax

[anthonyalayo]

Hey there,

A year ago I browsed the internet looking for the closest thing to JOOQ / JET in TypeScript. After a year, I can definitely say that Kysely is almost there :-).

I started using it again, and the biggest DX issue I have is how filters (ie. where clauses) are designed. Even with my IDE auto-completing fields, there's significant friction to type out a comma separated list of fields.

Here is an example from Jet (in Go):

stmt := SELECT(
    Actor.ActorID, Actor.FirstName, Actor.LastName, Actor.LastUpdate,  // or just Actor.AllColumns
    Film.AllColumns,                                                  
    Language.AllColumns.Except(Language.LastUpdate),  // all language columns except last_update 
    Category.AllColumns,
).FROM(
    Actor.
        INNER_JOIN(FilmActor, Actor.ActorID.EQ(FilmActor.ActorID)).  
        INNER_JOIN(Film, Film.FilmID.EQ(FilmActor.FilmID)).          
        INNER_JOIN(Language, Language.LanguageID.EQ(Film.LanguageID)).
        INNER_JOIN(FilmCategory, FilmCategory.FilmID.EQ(Film.FilmID)).
        INNER_JOIN(Category, Category.CategoryID.EQ(FilmCategory.CategoryID)),
).WHERE(
    Language.Name.EQ(String("English")).             
        AND(Category.Name.NOT_EQ(String("Action"))).  
        AND(Film.Length.GT(Int(180))),               
).ORDER_BY(
    Actor.ActorID.ASC(),
    Film.FilmID.ASC(),
)

And an example from JOOQ (in Java):

create.select()
      .from(BOOK)
      .where(BOOK.AUTHOR_ID.eq(1))
      .and(BOOK.TITLE.eq("1984"))
      .fetch();

The benefits you get from the builder syntax is clear -- you flow through your IDE's autocomplete as you continue to add clauses until you are ready to end your statement.

Would Kysely be interested in an API like this?

[koskimas]

You should take a look at Drizzle. It's closer to what you're looking for.

Your suggestion has its own drawbacks and is a completely different design. You get better type safety and much better autocompletion with Kysely's API.

We obviously won't make a complete rewrite.

[igalklebanov]

you flow through your IDE's autocomplete as you continue to add clauses until you are ready to end your statement.

Your definition of flow is having to stop yourself to import something? 🙅

[anthonyalayo]

Your definition of flow is having to stop yourself to import something? 🙅

Not at all. Please don't misrepresent what I wrote and jump to a different conclusion. The fact that other popular libraries have this design is a good indicator of its value.

[anthonyalayo]

You should take a look at Drizzle. It's closer to what you're looking for.

Thanks for the pointer! I'll take a look.

[igalklebanov]

Your definition of flow is having to stop yourself to import something? 🙅

Not at all. Please don't misrepresent what I wrote and jump to a different conclusion. The fact that other popular libraries have this design is a good indicator of its value.

Other languages have their own historical constraints and flavors. That doesn't mean that's good or bad.

A good API optimizes for readability - something that we do a lot more often than writing. Your examples, especially the first one, are verbose, overly complex and distant from SQL. Class & fluent interface overkill.

[igalklebanov]

If anyone stumbles upon this issue, here's how you can write these in Kysely v0.24.2:

First example:

db
  .selectFrom('actor')
  .innerJoin('film_actor', 'actor.actor_id', 'film_actor.actor_id')
  .innerJoin('film', 'film.film_id', 'film_actor.film_id')
  .innerJoin('language', 'language.language_id', 'film.language_id')
  .innerJoin('film_category', 'film_category.film_id', 'film.film_id')
  .innerJoin(
    'category',
    'category.category_id',
    'film_category.category_id',
  )
  .where('language.name', '=', 'English')
  .where('category.name', '<>', 'Action')
  .where('film.length', '>', 180)
  .select([
    'actor.actor_id',
    'actor.first_name',
    'actor.last_name',
    'actor.last_update',
    'language.language_id',
    'language.name',
  ])
  .selectAll(['film', 'category'])
  .orderBy('actor.actor_id')
  .orderBy('film.film_id')

https://wirekang.github.io/kysely-playground/?p=f&i=-NSrEdU2eJOeCAcIqKow

Second example:

db
  .selectFrom('book')
  .where('book.author_id', '=', 1)
  .where('book.title', '=', '1984')
  .selectAll()

https://wirekang.github.io/kysely-playground/?p=f&i=-NSrF7UUu2OG4znrGwfh

In both of these, could also make a single .where invocation and use eb.and([...]) if wanted to be more explicit.