Hacker News Reader

> code generation, instead of e.g. reflection

"Hey man, we noticed there's not enough compiler in your compiler, so we made a second compiler for your compiler."

"Your compiler compiles A, but you also want to compile B, so we added an extra compiler to help you compile it"

Makes sense, Go can't compile SQL and vice versa.

The one thing that kept annoying me with compilation type orms like this is the chicken and egg situation.

Your migrations will likely be run by your application, but your application won't compile until you've run your migrations.

Nah, I love SQL, but if I have to choose between SQL and ORM, I will go with ... something in between, like the core layer of SQLAlchemy.

Just take a look at that monstrosity of an UPDATE statement in the article. In my first job, I had to do something similar in SQL, but with a SELECT statement to allow users to search dynamically using any combination of the columns. I picked up SQLAlchemy in my second job, and have never looked back.

I get this, but personally I love using jOOQ because of how composable and dynamic query building is with a good query builder pattern. Adding optional filters, joins etc doesn't turn into a string-concatenation abomination; it's something. can do in native readable Java

(and then translating back to generated Java objects is also great compared the alternative of unpacking some generic result set, casting to the right type, etc)

Working with SQL in X (any language) usually has a poor developer experience that is why ORM or query builders are popular. Things like proper syntax highlight or type safety (I remain to be convinced that sqlc can really check the validity at compile, usually it only works in specific basic cases).

You just have to choose wisely your tools for sure, but most of the code you write needs to be rewritten anyway every X years.

> Working with SQL in X (any language) usually has a poor developer experience that is why ORM or query builders are popular.

At least when it comes to Postgres, I don't understand why more developers don't create their own user-defined functions with PL/pgSQL. It's very a robust and powerful procedural language. In my opinion, ORM's like SQLAlchemy add a completely unnecessary layer of abstraction. ORMs might be convenient for quick/simple queries, but when you're creating or troubleshooting a moderately complex query, it's far better to do it with native SQL than it is with a chained mess of ORM methods.

At my company, we employ UDFs for every SQL query we make and every UDF returns a declared type. The end result is far easier to debug and maintain when compared with the ad hoc ORM query alternatives. Plus it has the side benefit of separating out application code from database code which allows our DBAs to more effectively code review SQL code written by developers.

> You just have to choose wisely your tools for sure, but most of the code you write needs to be rewritten anyway every X years.

Not if you stick with plain SQL and/or Pl/pgSQL :-)

Two problems

1. How do you handle versioning? Like if you want to try a development branch on a non-branch/shared db. Creating different version of stored procedures creates a recursive problem. A calls B, now A’ has to call B’

2. Sometimes we still need to programmatically decide to include a table in the join or not or get creative on a filter. Pl/pgsql is less flexible in this regard. You get the benefit of syntax checking only when query is verbatim and not dynamically constructed.

> 1. How do you handle versioning? Like if you want to try a development branch on a non-branch/shared db. Creating different version of stored procedures creates a recursive problem. A calls B, now A’ has to call B’

Writing UDFs and using Pl/pgSQL has no impact on how you do versioning. At my company we follow standard Gitflow and use golang-migrate for schema migrations (or Phinx for our PHP code bases).

If you're working at a company where developers are all forced to use the same shared database, then you're going to have a lot of development challenges that are unrelated to UDFs and Pl/pgSQL. Multiple devs sharing the same database always requires some team coordination to ensure that each member isn't stepping on another's toes -- whether that be prefixing your UDFs with your initials during development or agreeing not to work on the same UDFs at the same time.

> 2. Sometimes we still need to programmatically decide to include a table in the join or not or get creative on a filter. Pl/pgsql is less flexible in this regard. You get the benefit of syntax checking only when query is verbatim and not dynamically constructed.

That's just untrue. Pl/pgSQL fully supports conditional logic, dynamic query string construction, multi-query transactions, storing intermediate result sets in a variable or temp table, etc. The use case you described is actually a great example of when you would decide to use Pl/pgSQL. The language is extremely robust.

I get that pgsql can construct dynamic queries, but I was assuming you were talking about the benefit of install time / compile time verification of query syntax. This is true for most regular stored procedures except when query is dynamic. Obviously the exact query isn’t known until runtime. I agree it is not a major downside.

dynamic query string construction

Is this the same as concatenating strings or is there some special PL/pgSQL support for this?

I did this on a recent project, and it worked really well.

I had each function definition in its own .sql file, with a preceding "drop function" call, and a Makefile clause to run them all. Which meant managing versions was easy (coupled with migration .sql files). I also got to find out if any of my SQL was broken right up front, and testing the SQL was simple - call the function and check the return.

I also defined views for return types, so mapping the return values to the structs in the Go code was easy (yes it's boilerplate, but it really is not as painful as the author makes out). Query functions always returned the relevant view type (or a set of them).

The author's approach seems to go to great lengths to avoid a relatively small amount of boilerplate.

> I had each function definition in its own .sql file, with a preceding "drop function" call, and a Makefile clause to run them all.

why drop instead of CREATE OR REPLACE ?

CREATE OR REPLACE requires that the new function has the same signature as the old one [0]. I understand why, but I needed to change the signature sometimes. It was easier to do a Drop and then a Create as standard. Though this did mean having to manage dependencies between files myself (I prefixed the .sql files with 00_, 01_,02_ etc to indicate order of dependencies). It sounds like a lot of work, but in practice it was easy - it broke very quickly and very loudly if I got it wrong at all ;)

[0] https://www.postgresql.org/docs/13/sql-createfunction.html

Could you please give 2 specific examples on how this would work? Are the functions only for UPDATE/INSERT, or also for reading data? I'm using views to simplify queries, but still via ORM.

Sure, why not.

Functions are for both reading and modifying data, because the actual SQL for the query might be complex (and therefore better managed as a function than as a string in Go code).

Example: User CRUD

    create view vw_user as select id, name, email from users;

    create function fn_get_user(p_user_id uuid) returns vw_user as $$ select * from vw_users where id = p_user_id $$;

    create function fn_change_user_name(p_user_id uuid, p_name text) returns vw_user as $$ update users set name = p_name where id = p_user_id; select * from vw_users where id = p_user_id; $$

    create function fn_create_user(p_name text, p_email text) returns vw_user as $$ insert into users (id, name, email) values (gen_random_uuid(), p_name, p_email); select * from vw_users where id = p_user_id; $$

The advantage here is that there is only one return type, so you only need one ScanUser function which returns a hydrated Go User struct. If you need to change the struct, then you change the vw_user view, and the ScanUser function, and you're done.

Each function maps 1:1 to a Go function that calls it, though it's also possible/easy to have more complex Go functions that call more than one db function. Or indeed, meta-functions that call other functions before returning a value to the Go code.

The problem with ORMS is always that eventually the mapping between struct and database breaks, and you end up having to do some funky stuff to make it work (and from then on in it gets increasingly complex and difficult). The structures in the database are optimised for storing the data. The structures in the Go code need to be optimised for the business processes (and the structures in the UI are optimised for that, so they will be different again). An ORM ignores all of this and assumes that everything maps 1:1. Maintaining those relationships manually (rather than in an ORM) does involve some boilerplate, but it allows you to keep the structures separate.

For example: User UI data:

    create view vw_ui_user as select users.id as user_id, users.name, users.email, sessions.id as session_id, max(sessions.when_created) as last_logged_in from users left join sessions on users.id = sessions.user_id group by 1,2,3,4;

    create function get_ui_user(p_email text) returns json as $$ select to_json(select * from vw_ui_user where email = p_email);$$

The json data generated from this function can be returned direct to the UI without the Go code needing to do anything to it. If the UI needs different data, the view can be changed without affecting anything else.

caveat I didn't bother checking this for syntax or typos. I have probably made several errors in both.

Thanks so much Marcus, it makes more sense now!

I’d love to hear how you handle code coverage, dynamic queries (custom filters in reports, etc.), live schema migrations (we aren’t brave and don’t do that at all - and don’t use functions either).

I’m coming from a sqlalchemy background if that helps to set the context.

All my test code runs against sqlite. I'll only need a postgres instance for one or two geospatial queries that use postgis and for now that's manual, so this speeds up my builds. SQL functions tie you in

Yep, close to 100% of my data manipulation is done in pl/pgsql. It’s awesome. At least 50% fewer LOC, and 10-100x faster than the equivalent code written in Java or Go, due to all the round trips.

Oh yeah, I completely forgot to mention that. The performance gains are immense when you use Pl/pgSQL to eliminate round-trips to the database. That's easily one of the most important reasons to use Pl/pgSQL.

The vast majority of data-heavy web apps today must have the database running on the same server or within the same datacenter -- they can't tolerate any kind of latency between the application and the database server because they failed to reduce round-trips. Ever tried deploying MediaWiki on an application server with >20ms latency to the database server? It just doesn't work -- each page takes several seconds to load.

If you minimize round-trips to the database, it gives you more flexibility on how you can deploy/host your database server. That's flexibility you want when you're designing failover/disaster recovery schemes.

Yep, I realised I needed to give plpgsql a shot when I started thinking, from first principles, about all the effort I was wasting. Not just machine cycles - the buffer copying, context switches, network switches, latency - but also, as I was working in Java at the time, there was the immense weight of the ridiculous JPA ORM sitting on top of it all, making it worse. When I took a step back and realised what we had done, the minimalist in me went into cardiac arrest.

With plpgsql you define your schema once, in SQL alone; you don't write a million duplicate "entity objects" in your language of choice, there is no friction or "impedance mismatch", no need to catch network errors for each DB call -- you just write SQL and return values like any other Go function. Because my functions are generally self-contained, I rarely even need to bother with transaction management, which eliminates even more round trips.

It's true that I needed to write some supporting code to manage schema upgrades (one day I hope to open source it), and I'm really intrigued to see if I can use sqlc to create Go stubs for my PG functions. But my SQL code sits next to my Go code in my IDE, it's syntax and correctness checked by the GoLand IDE, and life with an SQL database is super enjoyable!

I'm looking forward to integrating plpgsql_check into my build chain.

JetBrains IDEs help with this actually.

When I was working with Go, all SQL longer than one or two lines went into the package level sql.go file, each query being a multi-line string. If you preface it with a comment like `// language=PostgreSQL`, you get syntax highlighting for PGSQL. What's more, if you have databases configured in your IDE, it tries to cross-reference the tables, columns, etc, and validates the queries for you.

I never did manage to get the latter part working 100%, but I think it's because we used multiple top-level databases, but in the DB setup we had just one connection, that we then switched around to look at things.

With Go 1.16, you can embed SQL files into your Go app instead of using a multiline literal.

I actually am curious if a SQLC competitor should be written using embed + generics once generics drop in 1.18. I haven’t totally worked on what it would look like yet, but it’s an interesting idea.

I think generics will change a lot of the ORM space for go yeah. Will be interesting to see.

> Working with SQL in X (any language) usually has a poor developer experience that is why ORM or query builders are popular.

I believe that you're partly right. In my experience there's also a large number of developers who simply have no SQL training, and don't actually care to learn.

We've frequently have customers who complain about the performance of managed database (either managed by us, or a cloud provider). When we look at the queries it's clear that they use a ORM, without giving the schema it will generate much thought. It can be extremely hard to help make optimizations, because you need to figure out how to wrangle something like Hibernate into generating efficient queries and schemas, while not breaking the object model for the developers.

For my own Go projects I normally just stick to sqlx. I like that I can design the schema the way I need, and just create the queries that will map into my structs. Then it's just updates that are annoying.

A few years ago I had spent a year working with a Go project that made heavy use of one of the (then) popular Go ORMs. Learned my lesson, never again. Magic=Bad.

What ORM was that?

Likely Gorm, I'm using that at the moment and it's eeehhhh.

That would've been GORM v1, then. Nowadays we're at v2. It's no Hibernate, but it has been solid in my experience.

I've used similar tools (which I'm not going to recommend, as they have not aged well), and found it great to be able to rely on the compiler for checks. With ORMs or SQL I realized I would be better off working in Python, as without the compile time checks I got none of the benefits of Go and all the down sides. I haven't used sqlc, but do like that you just feed it queries. Other tools rely on using a templating language to generate the Go code from database schema introspection, and it is just awful to work with. Generics should do away with needing the templates, so maybe the database schema introspection approach will improve.

It does indeed look promising!

Can it help with migrations? Seeing it has the field definitions right there it should at least be possible.

Otherwise I can see how a system like this could become quite complex over time as the database structure changes.

Based on just this article, it looks like it needs a complete table definition to work with. Unless it queries the database for the definitive schema, but that would mean you need a database up and running at compile time.

I took for granted that sqlc could run all these CREATE TABLEs for you, perhaps that's not the case. It probably should, shouldn't it?

Hopefully they'll get SQLite working on it soon and I'll be all over it.

I'm certainly eager to see official SQLite support as well. In the mean time, the overlap between postgres and SQLite syntax is enough that a lot of my "postgres" definitions in sqlc work just fine in SQLite. The recent addition of "RETURNING" (https://www.sqlite.org/lang_returning.html) to SQLite was a big help since that was a standard part of sql used for postgres with sqlc if you wanted the last insert ID.

sqlc definitely uses reflection

But don't let that stop you, it looks like a nice solution and reflection isn't really all that bad anyway :)

Are you sure? Well, it uses "reflection" in a general sense of introspecting your SQL code, but not in the Go sense of using using type information at runtime via the "reflect" package. sqlc compiles your SQL at build time to statically typed, non-reflect-using functions, as shown here: https://docs.sqlc.dev/en/stable/howto/select.html

Technically, reflection is used by Go’s database Scanner interface, but it’s not what most people think of when they complain about reflection.

Good point. I had assumed Rows.Scan() would have just used type switches for efficiency -- it looks like it does for common cases (https://github.com/golang/go/blob/d62866ef793872779c9011161e...) but then falls back to reflect. I wonder why it doesn't just do all of that with type switches? Maybe there are just too many cases and it ends up slower than reflect for the rest of the cases.

Scanner.Scan() is actually just called via a type assertion, though I guess implementations of Scan() might use reflection.

It needs reflect in order to translate struct field names into column names at the very least

sqlc doesn't quite work that way (though "sqlx" and other packages do). sqlc generates code at build time that avoids reflect by using database/sql's Rows.Scan() with pointers to fields (see the link above):

  var i Author
  if err := rows.Scan(&i.ID, &i.Bio, &i.BirthYear); ...

This generated code is exactly what you'd write by hand if you were using database/sql directly.

As earthboundkid points out, database/sql itself may use reflection under the hood to convert the individual fields (though the common cases are done without reflect, using ordinary type switches: https://github.com/golang/go/blob/d62866ef793872779c9011161e...).

> Why would someone author an ORM, painstakingly creating Go functions that just map to existing SQL features?

The answer to this question lies in the assumption you make in this statement:

> the one which every engineer already knows: SQL.

Not every engineer knows, or wants to learn, SQL. I've met very competent engineers, SMEs over their particular system, who were flummoxed by SQL. And many more just want to work in their preferred language. I don't like ORMs either but, like, half the reason why they exist is so the programmer can talk to the RDBMS in Java, JavaScript, etc. and not touch SQL.

> Not every engineer knows, or wants to learn, SQL.

Which is bizarre cause you pretty much need some form of RDBs in most of the apps. And because of ANSI SQL, the syntax/concepts are relatively same on different databases too. No point in not making this investment.

Agree. This is the weird kid down the street who gets by on manyioise and saltines. To engage persistent storage is to engage sql for the first 75% of all work. Hey you gotta have some competence in the domain of work. I like Jordache (orm) but not Calvin Klein (sql) isn't wisdom; it's merely personal predilection.

If you are using an ORM and need to write a query that will run on a SQL server you *need SQL knowledge and ORM knowledge*. If you are missing one the two, you probably have just wrote something with big performance penalties. This has been seen over and over in the Rails community.

Exactly my experience too.

The amount of SQL hatred from rails learning resources is unjustifiable.

If you're dealing with a relational database with SQL as its primary interface, you'll end up learning SQL eventually because all abstractions leak!

Or just to save time and better yet, save you from massive security issues down the line.

I've seen raw SQL queries full of fatal SQL injection bugs like these in littered in codebases, very cringeworthy.

Most languages have a way to avoid SQL injection attacks, and linters that enforce usage of that.

For bad workplaces just using an ORM is a lot safer though, I agree. Performance can quickly become an issue when people stop thinking entirely about the DB level operations happening, and this comes up much quicker at workplaces where not enough people care.

No, the main benefits of ORM are not to avoid to learn SQL, they are:

• syntactic sugar and language/tooling integration for very common operations;

• a centralized data access API to build upon, that you would have to create anyway with raw SQL;

• a single introspectable source of truth you can use to generate web API, models, data validation/migration and so on.

Eventually, even using an ORM, you will need to learn SQL if you go beyond the toy project.

ORMs often introduce their own flavor of Domain-Specific Language, or their own language (Entities instead of e.g. rows). I'd posit that learning an ORM is just as much work as learning SQL, but with an extra layer of indirection.

It's like learning HTML via React.