Drizzle's Powerful SQL-like SELECT API Deserves More Attention

[tobychidi]

After reading through the discussions on relations API v2 #6, I remain thoroughly impressed by Drizzle's SELECT API.

Why I Prefer the SELECT API:

1. Simplified Relationship Definitions: Data structures and their relationships are defined in one place: PgTable

2. Beyond relationships, the SELECT API offers incredible flexibility, handling advanced SQL constructs with ease.

3. Reusable Components: The ability to reuse query portions dramatically reduces code duplication.

4. Crafting complex queries has become much more enjoyable, with joins now feeling intuitive rather than cumbersome.

Areas for Improvement:

1. Documentation and Examples: While the existing documentation is helpful, I believe more detailed guides and examples would be invaluable. For instance, I'd love to see a tutorial on how to reuse features, for example, with multiple queries that have deferred joins, pagination and repeated select columns. I still don't understand the difference between using db.$with and just joining with a .as("subquery"). 😅

2. Enhancing support for adding functions, triggers, and similar features would be highly beneficial. Currently, I use db.execute(sql``) to write these features as raw SQL while referencing my tables. I would love to then add these features to database with drizzle-kit push and migrations.

3. Utility Functions: Incorporating helpful utilities like coalesce and jsonAggBuildObject from @rphlmr's gists , into an official helpers package would boost the developer experience.

4. Typesafe Utilities Guide: Guidance on building typesafe utility functions would enable more robust, reusable code components.

I believe SQL-like deserves more attention and discussion within the community, as it has the potential to revolutionize database interactions in TS projects. What's your take?

[rphlmr]

👋 point 2, 3 and 4 are discussed internally 👀.
I'm advocating for point 2 and 3 🫡

[tobychidi]

That great news actually. Alot is being worked on. 🍹