All of these choices seem reasonable except letting an ORM be a sticking point.
Is there an institutional issue with SQL knowledge or is it exceedingly complex database?
As someone who finds ORMs more trouble than they're worth I really struggle with that point, particularly if it's SQLite.
And while Go's testing framework is really nice, I've never had much issue doing unit testing in Rust. I'm assuming the problem was more with the testing + the tauri framework than Rust itself.
IMO the best value of the ORM is not in the object relational mapping part, but in type synchronization between the DB and a client. Thus there's a wave of recent enthusiasm for ORM-lite solutions.
Very much agree with this. I really like how sqlx (on rust) does it. You just write normal SQL queries, and it will check and validate the types against your db. Best of both worlds.
I guess it's a good example of how powerful Rust is. The "magic" that sqlx does at compile time gets really difficult if not impossible to do without macros.
Of course you could create a separate parser/builder, and codegen works as well, but definitely not as convenient and flexible.
I've found a happy medium, where I use sql-migrate to build the database, and go-jet to generate the table/model structures for use in go, and go-jet's syntax is reasonably close to SQL that it's easy enough for expression.
caveat: I'm an ex-DBA so I'm comfortable with building complex tables, relationships and queries. Less familiar folks might struggle building the tables, relationships, and doing joins, ctes etc.
The big draw of ORMs is (was?) the idea of database independence which is a fools errand unless you're a vendor pushing some kind of application which needs some kind of relational database.
If working in-house or building something bespoke, an ORM provides negative value. The only possible upside I can think of is that it offers an opportunity to capture databases access events or... no, that's it. In exchange for this dubious capability, ORMs end up sacrificing the full generality of SQL for simple CRUD statements. On top of that, if ORM migrations are in play then database maintenance becomes bifurcated because, like on the CRUD side, ORMs can only provide crippled imitations of DML.
No, the big draw of an ORM is you can map objects to your database relations. That's why it's an object-relation-mapping. When working with Postgres I think of database rows as instances of a class in my app's native language. I think of queries for the table as static methods, queries for rows as instance methods. Going with raw SQL queries means you not only lose good intellisense on query methods as well as composability, but for a non-trivial app you'll end up implementing much of what an ORM will give to you anyway.
It's the power of an ACID RDBMS with the level of integration of DAOs written in whatever language you want.
Maybe we're coming at this from different ends? I've always adopted the position that the database is the source of truth, not the ORM data model. I've always maintained that mirroring the database schema in an ORM data model is an anti pattern. And I don't like exposing tables to client code under any circumstances -- a view for queries is an absolute minimum and for updates I prefer to implement those through stored procs.
There's nothing stopping anyone from writing their own DAO layer with bespoke SQL statements and allowing intellisense to index the API over the top of that.
> There's nothing stopping anyone from writing their own DAO layer with bespoke SQL statements and allowing intellisense to index the API over the top of that.
That sounds like a homebrew ORM. It could end up being better than any out-of-the-box solution. Or you could make a spaghetti code mess. But I’d rather spend my time elsewhere.
In my opinion the bespoke app code is the center of everything. The database is on the periphery and should conform to my needs as a software engineer building a user facing product. I need to live in reality, though, and understand the database accessed through an ORM is a leaky abstraction. But for many common use cases (find by ID, update a field for a row, etc) I can forget that it’s Postgres under the hood.
I know SQL well enough, understand how to craft a decent schema, how to keep the database happy as tables grow in size. But the main job is writing web app code, not operating as a DBA.
I operate on the principle that the center of the app can be determined by finding where most of the bullshit is. For an app where you’ve got some tables and indexes, the database is too idyllic to be the center of bullshit. Your app code probably has 1000x more bugs and spaghetti to it.
ORMs are a tool that can be abused, but eventually you need something that loads data from your database and places it into an in-memory object. Given how repetitive the SQL and mapping code is, why wouldn't you use a tool to automate that, even if you're an SQL expert?
The SQL and mapping code is very simple. Getting an ORM to generate queries as efficient as hand written SQL requires substantial ORM study, experimentation, and ongoing tweaks. I've seen too many junior programmers write simplistic code that creates a massive number of database queries because they didn't understand how the ORM works. That rarely happens with handcoded SQL because how the code interacts with the database is transparent.
You can use both though. Keep the ORM for the easy stuff to save yourself the boilerplate, and drop down to raw SQL when needed or when it's easier than figuring out how to do the same with the ORM.
But then you're still having to fully understand the ORM so you're not saving yourself any work.
I'm always amazed at how much I can do in a single SQL query and how much extra code is required to accomplish the same when using an ORM. But then I once wrote a SQL compiler and runtime system so I'm quite adept at SQL.
Specifically in Golang, an issue I encountered writing SQL queries is when you expect the response will contain null fields. Specifically in Go, at least with the stdlib database/sql client, there's a lot of boilerplate code you need to add to make raw queries without an ORM to map to a sql.NullT object (where T is the Golang equivalent type for the column), then check for each row if the sql.NullT object is "valid" and then copy the column values for each row over to a new struct after doing each sequential null check. An ORM might have saved me about 50 lines of code for just this one query that I'm looking at, just in handling null types alone.
I think there are a lot of database libraries across a lot of languages that let you jam data directly into an object.
The only thing I think (some) ORMs do really well is migration management. But you can also devise your own patterns for this easily enough that it's not worth it.
Back in my Java days a long time ago, I too preferred just writing prepared statements and then copying the data to my POJOs. Until I thought about it and decided to use reflection to do the heavy lifting after modifying the code every sprint to deal with new requirements. Then I added the ability for my POJOs to be annotated so I could define the table and columns it belonged to. It didn't hit me until near the end that I had reinvented Hibernate without all of the rest of its features.
> It didn't hit me until near the end that I had reinvented Hibernate without all of the rest of its features.
This is pretty much the life cycle of the “ORMs suck” crowd. The hangups are similar to “XML sucks” — conflating a poor ORM framework or XML-based format with the concept of ORMs or XML in general.
ORMs are a very useful abstraction, and even if you think you aren’t using an ORM, you probably are — just a bespoke one.
I wasn't in the "ORMs suck" mindset, but rather I just didn't want to take the time to integrate it since it was "just a small little project for a few people". But as word got around, more people got interested in it and wanted their own fields and data added. It was a case where premature optimizing would have been a good idea.
I think it’s a false dichotomy that people against ORMs put out that the user of an ORM doesn’t know SQL well enough or sql is better than an ORM.
An ORMs super power is in easily composable queries. Something no easily don’t with SQL strings because of name conflicts and other issues. ORMs provide incredible benefits when it comes to sub queries and IN statements for instance.
ORMs are not a replacement for SQL strings or knowing SQL. Knowing SQL is sometimes a prerequisite for using some advanced features of an ORM to begin with.
Maybe it comes from people using crappy libs or perhaps some people just like something to feel superior about, but I’ll keep using my humble ORM. So many incredible benefits of ORMs and zero restrictions on just using raw SQL with them anyways.
The property of composable queries is not unique to (or dare I say even characteristic of) ORMs, which are primarily concerned with mapping between tables or relations (in the relational algebra sense), and in-memory objects.
It's the modelling of SQL as an AST that gives you that composability, and this does not require an ORM; see for instance arel (even though this was later absorbed into Rails' ActiveRecord ORM as an implementation detail...). [0]
I feel like the majority of the people who complain about orms, haven't taken the time to learn the orm. Often times, there is a DSL you need to learn, but for simple database relations and joins not using an ORM is a waste of time and resources.
orms are often opinionated and tables that are setup with an ORM may not be compatible with other ORMS. This is the only real downside. If you have a large legacy application that needs to be migrated to a new framework that uses different conventions for index names, table names, ect, then you are in for a bad time.
Django's orm is probably one of the best out there. Django also has orm manager classes you can extend with abstractions around orm functions or raw queries.
For complex queries, like with CTE's, complex joins, recrusive queries, index optimizations, there is no way around custom queries but any good ORM has an api around these raw queries that can hopefully also map to objects in your applications.
I feel similarly. I think people just tend to be put off by SQL and the programmer answer of 'just another abstraction' takes over.
It's not good, but it's not bad enough to justify ORM complexity. Then again I've worked in places where no one knew what an index was so maybe they make sense in such environments.
The impedance mismatch comes as a result of needing workarounds for database implementations that exhibit high query latency in order to see the execution finish in a reasonable amount of time. It is not fundamental. You can avoid the mismatch by using a DBMS that provides low-latency queries, like SQLite.
To be clear, and I assume you know this, network connectivity is probably a small part of typical query latency. Large joins, sorts, etc, would tend to dominate. Maybe for huge result sets bandwidth might factor in a again but so would the sorts.
ORMs are not an either/or decision. You can use an ORM, save yourself time with the usual repetitive "CRUD", result set mapping, etc. and then drop down to SQL if you require something complex (like reporting.)
On the other hand, I've worked at companies where they built their own barely functional ORM. It was total garbage. The original developer did not understand prepared statements, so you can imagine how well that worked out. We'd regularly hit SQL errors in production because a quoted string came through.
Is there an institutional issue with SQL knowledge or is it exceedingly complex database?
As someone who finds ORMs more trouble than they're worth I really struggle with that point, particularly if it's SQLite.
And while Go's testing framework is really nice, I've never had much issue doing unit testing in Rust. I'm assuming the problem was more with the testing + the tauri framework than Rust itself.