Sqlc: Compile SQL to type-safe code • Hacker News Board

Re: Sqlc: Compile SQL to type-safe code

#21

Post by renhanxue » Tue, Sep 10, 2024, 12:04 AM UTC

desiderantes wrote: post #6

geoka9 wrote: post #2

Have been enjoying go-jet that takes a different approach: analyzes the tables in your DB and generates a set of Golang structs that lets you write 100% Golang code that looks like 99% SQL. Genius! https://github.com/go-jet/jet

Oh, so it's jOOQ but for Go. Nice. Sadly it does not support most custom Postgres things.

I think the next release is gonna give you a lot of flexibility to write your own helpers that can generate the raw fragments you need. Not as good as having the features built in of course, but it'll do in a pinch.

jOOQ and jOOQ-like API's in other languages are the Right Thing in most cases, IMO. I've worked with very abstract ORM's, I've worked with raw SQL, and I've worked with a lot of things somewhere in between and that's my conclusion.

Re: Sqlc: Compile SQL to type-safe code

#22

Post by stanleydrew » Tue, Sep 10, 2024, 12:33 AM UTC

RamiAwar wrote: post #11

I've always said that the best ORM is one that allows for type safe query building. This kind of generates the type safe queries for you, which is the end goal. But then why don't developers use the query builder instead? Why have an unnecessary generation step? I feel like a good query builder ORM is more than enough and more straightforward than this. What am I missing?

One of the design principles of sqlc is that SQL queries should be static in application code so that you know exactly what SQL is running on your database. It turns out you can get pretty far operating under this constraint, although there are some annoyances.

Re: Sqlc: Compile SQL to type-safe code

#23

Post by valzam » Tue, Sep 10, 2024, 12:37 AM UTC

geoka9 wrote: post #2

Have been enjoying go-jet that takes a different approach: analyzes the tables in your DB and generates a set of Golang structs that lets you write 100% Golang code that looks like 99% SQL. Genius! https://github.com/go-jet/jet

SQLC has the same functionality, it does two things:

- Create structs for your custom queries - Create structs for your DB tables if you point it at a DB

Re: Sqlc: Compile SQL to type-safe code

#24

Post by [deleted] » Tue, Sep 10, 2024, 12:39 AM UTC

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…

[deleted]

Re: Sqlc: Compile SQL to type-safe code

#25

Post by Cyph0n » Tue, Sep 10, 2024, 12:41 AM UTC

geoka9 wrote: post #2

Have been enjoying go-jet that takes a different approach: analyzes the tables in your DB and generates a set of Golang structs that lets you write 100% Golang code that looks like 99% SQL. Genius! https://github.com/go-jet/jet

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?

Re: Sqlc: Compile SQL to type-safe code

#26

Post by bccdee » Tue, Sep 10, 2024, 12:49 AM UTC

RamiAwar wrote: post #11

I've always said that the best ORM is one that allows for type safe query building. This kind of generates the type safe queries for you, which is the end goal. But then why don't developers use the query builder instead? Why have an unnecessary generation step? I feel like a good query builder ORM is more than enough and more straightforward than this. What am I missing?

A query builder ORM is an abstraction I don't want and don't need. I know which SQL queries I want to run on my DB; please don't make me learn a whole DSL in order to run them. All I get in exchange for that is the extra work of translating my SQL into your DSL, plus the headache of circumventing the inevitable leaks in your abstraction.

I just want type-safe SQL templates that return native data structures. Sqlc provides that, and I've quite enjoyed working with it.

Re: Sqlc: Compile SQL to type-safe code

#27

Post by magicalhippo » Tue, Sep 10, 2024, 12:52 AM UTC

Cyph0n wrote: post #25

geoka9 wrote: post #2

Have been enjoying go-jet that takes a different approach: analyzes the tables in your DB and generates a set of Golang structs that lets you write 100% Golang code that looks like 99% SQL. Genius! https://github.com/go-jet/jet

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.

Re: Sqlc: Compile SQL to type-safe code

#28

Post by gregwebs » Tue, Sep 10, 2024, 1:16 AM UTC

valzam wrote: post #23

geoka9 wrote: post #2

Have been enjoying go-jet that takes a different approach: analyzes the tables in your DB and generates a set of Golang structs that lets you write 100% Golang code that looks like 99% SQL. Genius! https://github.com/go-jet/jet

SQLC has the same functionality, it does two things: - Create structs for your custom queries - Create structs for your DB tables if you point it at a DB

Jet was built for joins though- sqlc doesn’t seem able to do those.

Re: Sqlc: Compile SQL to type-safe code

#29

Post by mrastro » Tue, Sep 10, 2024, 1:21 AM UTC

gregwebs wrote: post #28

valzam wrote: post #23

Earlier quoted context omitted.

SQLC has the same functionality, it does two things: - Create structs for your custom queries - Create structs for your DB tables if you point it at a DB

Jet was built for joins though- sqlc doesn’t seem able to do those.

It can do joins. By default it combines the query into a single flatten struct but you have the option to write the query to embed entities in the join.

Not perfect and doesn't maintain the same type if you want a subset of columns but works for the majority of cases.

Re: Sqlc: Compile SQL to type-safe code

#30

Post by taeric » Tue, Sep 10, 2024, 1:29 AM UTC

paperplatter wrote: post #19

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…

Because RDBMSes are still an unsolved problem, and new features keep coming along that are actually very useful but not easy to standardize.

And if I'm doing advanced queries, I largely sympathize. Basic group by and such, though? I get why cube and roll-up aren't always there, maybe. The rest, though?

If I was demanding equivalent speed, it would be one thing. I am fine needing an integration environment for that. I only want a test environment to show queries return as expected. Is also good documentation for reporting focused queries.