[FEATURE]: Fully type-safe query builder

[septatrix]

Describe what you want

Drizzle already has decent typescript support but many pitfalls can currently not be checked due to the API. Using select first and then from means that the former cannot rely and type information from the latter. This makes autocompletion worse and makes it impossible to check bugs where the user selected a column from a table which is not included in a from/join statement.

Other query builders¹ use a syntax where one first writes the from (then join) and then the select. This has the advantage of knowing which tables will be available when writing the select statement. Typescripts powerful type system can utilize this (as some of the below query builders demonstrate) and fully check statement as well as provide better autocompletion hints to the editor.

Drizzle is now at the stage where it has catched up to the other players in the JS-SQL world and providing a properly type-safe query builder will likely help it rise further. This will especially help Drizzle in corporate environments where correctness is vital as can be seen be the adoption of Typescript. I am aware that this will be a breaking change but with proper communication to the community I expect that this change will be welcomed by the community due to the advantages it brings.

¹ E.g. kysely, TypeORM (to a certain degree), Knex, Lucid, even outside of the Typescript world with e.g. sqlkata, diesel.rs - admittedly I do not know the latter in depth but the db.from(table).select(cols) seems to be quite widespread in mature and popular query builders. I could have searched further and likely found more...

[ricardo-valero]

I fully agree with this proposal. Even though it deviates from the traditional SQL syntax, it is a more than appropriate change.

The design of SQL has a lexical, not a logical order.

Another example of this approach can be seen in Linq, which also emphasizes logical flow over traditional syntax order.

Additional Syntax Considerations:
The proposed db.from(table).select(cols) looks fine, but if maintaining verb-first API consistency is a priority, consider these alternatives:

• db.read(table).select(cols)
• db.read(table).pick(cols)
• db.select(table).pick(cols)

These alternatives would align well with existing API patterns:

• db.insert(table).values(values)
• db.update(table).set(values).where(condition)
• db.delete(table).where(condition)

[dankochetov]

We deliberately switched to our current API from the one proposed here, to reduce the cognitive load on the library users, because we decided it will provide better DX than huge type errors that are quite hard to understand for average TS developers. Another reason is, even if we were to switch to this API, it still won't support selecting with joins, as they would have to come before the selection as well for full type safety, deviating the API from SQL even more.

[dankochetov]

Also, I think this conversation should be moved to discussions rather than issues, so I'll close this one. Feel free to create a discussion on this topic, if there isn't one already.

[septatrix]

...we decided it will provide better DX than huge type errors that are quite hard to understand for average TS developers.

The current behavior in such a case would be huge runtime errors which provide no context whatsoever so this would still be an improvement over the status quo. Additionally, having this properly encoded in the type system would improve autocompletion such that developers wouldn't even be tempted to select columns which are not returned by the query. I fail to see how this would degrade DX.

Another reason is, even if we were to switch to this API, it still won't support selecting with joins, as they would have to come before the selection as well for full type safety, deviating the API from SQL even more.

Sure, I only gave a simplified example. Joins would of course also have to be moved further ahead. The linked reference about lexical vs logical order is the key point I'm getting at here. One pretty good example given by Ricardo is also Linq which uses the logical ordering. While I have not personally worked with it, I have only ever heard good about it and no complaints about it's unusual ordering compared to structured queries. So DX seems to be no concern here...

Also, I think this conversation should be moved to discussions rather than issues, so I'll close this one. Feel free to create a discussion on this topic, if there isn't one already.

As a contributor you should have the permissions to convert this to a discussion. Would you mind doing so such that this is correctly shown/tracked by GitHub?

[septatrix]

We deliberately switched to our current API from the one proposed here

I only ever know the current API. Can I find information about the previous version somewhere? Assuming there was a discussion about that change then it would be very insightful to read through the arguments at that time

[dankochetov]

Can I find information about the previous version somewhere?

There was no discussion on GitHub at the time, only internally between the team members, as it was on quite an early stage of the library.

Actually, I've realized that an issue can be converted to a discussion, so I'll do that instead of opening a new one.

[AlexBlokh]

We didn't come to a conclusion, we actually implemented a type safe API everybody is mentioning almost 20 months ago and when started using it across multiple commercial production projects it turned out to be practically worse, errors were completely indigestible for mostly any regular developer, so we rolled back to what we have now.

So as you can see it's not only the urge of SQL-likeness, it's a combination of everything. Practical application on a non-hello-world apps, the API itself, the ability to build on top(Astro DB), etc.

[septatrix]

We didn't come to a conclusion, we actually implemented a type safe API everybody is mentioning almost 20 months ago and when started using it across multiple commercial production projects it turned out to be practically worse, errors were completely indigestible for mostly any regular developer, so we rolled back to what we have now.

Just because it did not fit your use case does not mean that it is unsuitable for everyone. Also the error messages are not a result of the approach itself. I have used kysely in the past and have not experienced indigestible error messages. It is more likely that there were some shortcomings in the typescript implementation. (Typescript inference and error messages are also steadily improving.)
And again, any error messages via static analysis is still way better than runtime errors which may not be easily catched.

So as you can see it's not only the urge of SQL-likeness, it's a combination of everything. Practical application on a non-hello-world apps, the API itself, the ability to build on top(Astro DB), etc.

I do not believe any of that is an issue of logical ordering but more of the chosen API and specific implementation. As I listed above there are several popular (typescript) projects which have successfully implemented such an approach.

Regardless, I find it wrong that you directly closed this discussion :/

[septatrix]

So as you can see it's not only the urge of SQL-likeness, it's a combination of everything. Practical application on a non-hello-world apps, the API itself, the ability to build on top(Astro DB), etc.

I mean for most things the QRM-style Query API will be most convenient and the quickest to code in. However, when you really need to use the query builder for either more performance or very complicated queries the additional type safety of a query builder with logical order instead of lexical order would be beneficial. Especially for the latter case where one writes complex queries this is indispensable.

Currently we have to use Kyselify for those cases but that is buggy and pulls in an additional dependency and we would be grateful drizzle would handle this.

[danicruz0415]

Hi, I didnt know this discussion existed so I opened a new one here: #1849

I did not find it, perhaps because its closed. Could this be reopened? I think there is not definite conclusion here yet

Thanks :)