Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Sqlc: Compile SQL to type-safe code

sqlc.dev

Post reply on HN
Print view View original

41–50 of 109 posts

Re: Sqlc: Compile SQL to type-safe code

#41

Post by Cyph0n »

magicalhippo wrote: post #27
Cyph0n wrote: post #25

Earlier quoted context omitted.

Interesting. What happens if your schema is rolled back (e.g., to remove a new column), but your binary isn’t? Or is the idea to always deploy schema rollbacks alongside your binary? Edit: Also, curious about how this would work with a progressive schema rollout across environments - e.g., staging vs. prod DB. Do you need to “wait” for your new column to hit prod before you can use it in unit tests?

Or just don't do schema rollbacks. We allow customers to run old versions if our program against an upgraded database, and to do that we just don't do destructive schema changes.

I suppose that’s one option. But sometimes you want the ability to rollback on bad or potentially destructive schema changes.

Re: Sqlc: Compile SQL to type-safe code

#42

Post by [deleted] »

taberiand wrote: post #34
Cyph0n wrote: post #25

Earlier quoted context omitted.

Interesting. What happens if your schema is rolled back (e.g., to remove a new column), but your binary isn’t? Or is the idea to always deploy schema rollbacks alongside your binary? Edit: Also, curious about how this would work with a progressive schema rollout across environments - e.g., staging vs. prod DB. Do you need to “wait” for your new column to hit prod before you can use it in unit tests?

At a basic level - database migrations must be backwards compatible at least with the previous version, and they go out before the service update is deployed. Unit tests don't run direct against prod usually, but regardless they would be run after migrations to a database of (production schema+migrations). Each environment - dev, test, staging, prod has its own db. Even spinning up an ephemeral db per test is possibl…

[deleted]

Re: Sqlc: Compile SQL to type-safe code

#43

Post by koito17 »

taeric wrote: post #16

I remain baffled that standard SQL isn't more supported by some of the newer tools coming out. If you are targeting a standard SQL dialect, it is basically trivial to standup an local database to test against during every build. I remember using https://sqlfairy.sourceforge.net/ back in the day to help test locally against mysql/postgres, but deploy to oracle. There were hiccups, but it felt like the world would most…

> I remain baffled that standard SQL isn't more supported by some of the newer tools coming out.

Because "standard SQL", assuming it means ANSI SQL, is quite limited on its own. Every relational database has its own syntax for DDLs. It's not an exaggeration to say each mainstream relational database has its own SQL dialect (e.g. MySQL and SQL Server have ISNULL but ANSI SQL has COALESCE). There is no way to portably write a query that e.g. constructs a table with a foreign key constraint. The best libraries can do is target the dialects they care about or the dialects they expect everybody to use (e.g. MySQL and PostgreSQL).

Re: Sqlc: Compile SQL to type-safe code

#44

Post by seer »

There was a saying that before learning postgres in depth, the db is just a dumb store of data for devs, once you spend time to learn the tools it provides though, most applications just look like a very thin layer on top of the sql.

There is so much more to rdbms (especially pg) than just joins - common table expressions, window functions, various views, let alone all the extensibility - extensions, custom types, even enums.

All of that can enable writing performant, type safe and very compact applications.

I am yet to see libs that embrace the elegance of it all - I’ve attempted this once - https://github.com/ivank/potygen but didn’t get much traction. I’m now just waiting for someone more determined to pick up those ideas - a client lib that exposes the type safety and intellisence at compile time and allows you to easily compose those sql queries.

I think this project has some ways to go to reach that though, but thankfully it is a step in the right direction.

Re: Sqlc: Compile SQL to type-safe code

#45

Post by benhoyt »

pier25 wrote: post #3

I've been looking into sqlc lately for Go. Seems brilliant except for the lack of dynamic queries: https://github.com/sqlc-dev/sqlc/discussions/364

I missed this too. However, I've found you can work around it pretty easily with clauses like CASE WHEN @field != "" THEN column = @field ELSE true END.

Example from the sqlc creator (https://github.com/sqlc-dev/sqlc/discussions/364#discussionc...):

  -- name: FilterFoo :many
  SELECT * FROM foo
  WHERE fk = @fk
    AND (CASE WHEN @is_bar::bool THEN bar = @bar ELSE TRUE END)
    AND (CASE WHEN @lk_bar::bool THEN bar LIKE @bar ELSE TRUE END)
    AND (CASE WHEN @is_baz::bool THEN baz = @baz ELSE TRUE END)
    AND (CASE WHEN @lk_baz::bool THEN baz LIKE @baz ELSE TRUE END)
  ORDER BY
    CASE WHEN @bar_asc::bool THEN bar END asc,
    CASE WHEN @bar_desc::bool THEN bar END desc,
    CASE WHEN @baz_asc::bool THEN baz END asc,
    CASE WHEN @baz_desc::bool THEN baz END desc;

Re: Sqlc: Compile SQL to type-safe code

#47

Post by Nugget0 »

seer wrote: post #44

There was a saying that before learning postgres in depth, the db is just a dumb store of data for devs, once you spend time to learn the tools it provides though, most applications just look like a very thin layer on top of the sql. There is so much more to rdbms (especially pg) than just joins - common table expressions, window functions, various views, let alone all the extensibility - extensions, custom types, ev…

This is what jOOQ does. Best SQL library I've ever used. https://www.jooq.org/

Re: Sqlc: Compile SQL to type-safe code

#50

Post by mewpmewp2 »

seer wrote: post #44

There was a saying that before learning postgres in depth, the db is just a dumb store of data for devs, once you spend time to learn the tools it provides though, most applications just look like a very thin layer on top of the sql. There is so much more to rdbms (especially pg) than just joins - common table expressions, window functions, various views, let alone all the extensibility - extensions, custom types, ev…

It would be great to have some sort of sql string to type parsing as some sort of plugin to TypeScript.
Post reply on HN