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.
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 :-)
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.
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.
Is this the same as concatenating strings or is there some special PL/pgSQL support for this?
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.
why drop instead of CREATE OR REPLACE ?
[0] https://www.postgresql.org/docs/13/sql-createfunction.html
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; $$
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);$$
caveat I didn't bother checking this for syntax or typos. I have probably made several errors in both.
I’m coming from a sqlalchemy background if that helps to set the context.
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.
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.
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.
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 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.