I think the opposite is True: SQL is backward to a programming language. Creating statements by concatenating strings? An ORM makes real objects and types you can actually instantiate and use, or even type check.
You don't create statements by concatenating strings. Use prepared queries with placeholders and pass values. Databases will compile the query once, then can run it many times.
Sometimes you have to concatenate, e.g. to specify order by clauses. This is something database vendors need to fix. There need to be ways to tell the database to modify a prepared statement without changing it's SQL. You might be able to do it with a crazy IIF, but then it's likely not to use indexes well.
Prepared statements on anything but Oracle are only asking for trouble, if the distribution of your parameters does not match that of the query the statement was compiled with you can easily end up with bad query plans. Furthermore these issues are hard to analyze (no parameters in logs) and even harder to solve.
I have hit this problem in just about any system using prepared statements. The performance overhead of query planning is minimal compared to the headaches it prevents.
> I think the opposite is True: SQL is backward to a programming language. Creating statements by concatenating strings? An ORM makes real objects and types you can actually instantiate and use, or even type check.
That you view the situation as ORM or concatenation terrifies me. No wonder SQL injection remains so prevalent.
Prepared statements are what you use here. You provide a query with placeholders for the values you want to fill in, and then you call that prepared statement with the arguments you need. These are typically type-checked and hardened against SQL injection, but of course you should verify with your particular language/library combination.
The problem is prepared statements aren't composable. So saying i want this query but also with this extra where in there and a limit and an offset means you have to write a whole new prepared statement. Or concatenate strings!
With an ORM you can construct the prepared statements programmatically.
It sounds to me like there's a reasonable middle ground here, where you treat your SQL query more like an AST than a string. So you can do something like myquery = select("col1").where("col2 < {}", my_value), then later myquery.limit(10).offset(20).
You're still working with SQL as a language, but you're manipulating it directly instead of trying to go via string formatting.
// In your handlers, construct queries and ask your models for the data
// Find a simple model by id
page, err := pages.Find(1)
// Start querying the database using chained finders
q := page.Query().Where("id IN (?,?)",4,5).Order("id desc").Limit(30)
// Build up chains depending on other app logic, still no db requests
if shouldRestrict {
q.Where("id > ?",3).OrWhere("keywords ~* ?","Page")
}
SQLAlchemy has this. It sits alongside and works in tandem with the ORM, and I've used both in a projects (and also raw SQL), each where appropriate to the use case.
> The problem is prepared statements aren't composable. So saying i want this query but also with this extra where in there and a limit and an offset means you have to write a whole new prepared statement. Or concatenate strings!
Absolutely, prepared statements would not be appropriate for that.
> With an ORM you can construct the prepared statements programmatically.
That's not limited to ORMs though. You can also just use a query builder. In fact I would argue that an ORM that provides that functionality is a query builder with additional ORM features on top.
You can use functions to create basic "getters" for application data structures. Layering these through further functions for limits/offsets/etc is simple (and you can write those layers in a generic way).
You just have to get used to the mental mindshift that all your data manipulation code lives in the database, and your application simply consumes data from single function calls.
> That you view the situation as ORM or concatenation terrifies me. No wonder SQL injection remains so prevalent.
I don't think others mean concatenation of values where placeholders should be, but the fact that the query itself is a big string. Unless some sort of query builder is used to literally build this big string for you.
SQL is not compatible with programming languages because in programming language you are describing how to do something, while in SQL you are describing what the result supposed to be.
What would be great to have first class support in the language (or perhaps IDE) that would understand database schema and database types so you could have error highlighting and autocompletion.
Something like JOOQ for Java seems to do that. Seems like a good idea at the surface, though I don't currently program in Java and don't see anything similar in other languages, so I'm wondering what's wrong with it
You'll have to pry ActiveRecord from my cold, dead hands. I'll take it any day over the half baked quasi-ORM that your company has inevitably made over the years because an early engineer was anti-ORM and it turns out doing raw SQL all the time is tedious.
What’s nice about ActiveRecord is that while it obviously does hook into the Rails object-oriented style, it’s actually more relational and functional than oo in how it works underneath. It’s a fairly thin layer of abstraction over SQL. I think of it more as a way to build and compose SQL statements than a true ORM—at least when it’s used well.
At my last position we simply did sprocs and dapper. When you take as true that the database is the source of truth, it only makes sense to put the queries in the database. The only exception is dynamic queries or modifications.
With the MS stack it's really easy. You can design the schema and procs, views, etc. in Visual Studio, then have it compare against each environment to create the deployment script.
How do you avoid leaking business logic into database stored procedures? Sounds like you would now have two sources of truth, application logic, and database stored procedures.
I've seen "business logic" refer to both A) how a discount promotion works and B) how to arrange images of the product for a particular set of customers. Obviously it's fine to have A in the database...
thanks for bringing that up. everyone on a team needs to have the same working definitions, and yeah... "business logic" is a term that's used a lot, but I very often see people not really understanding that it's usually meant in opposition to "display logic", not just as an interchangeable term for "any code".
I'd like to see someone have a decent go at reexamining the idea that business logic (BL) doesn't belong in the DB and tease apart exactly what that should mean, if it's actually still true, or even if it's ever actually been true. Maybe something like that's been posted here before, but I haven't seen it...
I bought into the idea myself for a while, but when I interrogate my belief there it was just something I picked up at uni as part of the general 3-tier approach, and I'm not sure how much of it is really practically grounded. I see the same ideas held dogmatically by newer employees, and when I ask them about it in detail, I see the same fuzziness.
After all, if you want to really be pedantic, then you could claim anything beyond having a single table with two columns, "key" and "value" is pushing BL into the DB.
I.e, what practically does the separation gain you? You may want to swap out the DB in future? Almost never happens, not without some significant other refactoring going on (e.g breaking out into separate DBs because the product's grown to the point of needing distributing across services, etc). If you want to really design with that in mind, you're almost certainly giving up a lot of functionality that the particular DB is going to give you. It's on the level of holding onto the possibility that you'll want to change the language you're writing in at some stage (and that event would favour pushing more into the DB anyway).
In reality, what I've found is when there's a reliable, transactional, relational datastore at the bottom (i.e we're not talking about a larger distributed system), then for your own sanity you want to be pushing as much as possible down the stack as possible, to reduce the surface area that someone could reach through to change data in the wrong way. Data consistency issues are some of the worst when it comes to eroding customer trust, and if your data access/mutation paths are many and varied then you can do your head in or burn through a lot of dev morale trying to diagnose them.
The strongest advocates otherwise I've found are those in office cultures where there's little trust between devs and DBAs and getting things through DB review are a rigamorale that end up driving devs towards doing as much in code as possible. I've always suspected that beyond Google envy, these sort of dynamics are what drove a lot of the initial NoSQL movement...
Thanks, the Derek Sivers piece rings a bell now (and maybe was going into my thinking on this), and the other two will go into my notes to avoid having to repeat myself the next time this one inevitably comes up on the office Slack...
I'm working on a project that is almost literally your example of reducing a relational database to a key value store, despite client applications having complex problem domains to model.
The consequence has been catastrophic data consistency problems and the whole programme rapidly grinding to a halt.
I think the problem is the overly broad definition of 'business logic' that encompasses everything from data integrity to presentation logic.
I worked on one system that did intentionally put business logic almost entirely in the DB. The VP was a former database guy and the primary justification was auditing. It worked well enough in practice.
There’s a quote from Gavin King somewhere exhorting developers that ORMs and raw sql are meant to be used concurrently, ORM for CRUD ops,sql for most everything else.
The problem I have with ORMs is lack of paging on collections; there’s always that one user that pulls in half the database.
As a rule, relational database systems have two strong areas: parallel processing and data consistency enforcement. I've always understood "don't do business logic in the database" as meaning not to use the database engine for sequential processing.
If you have a single-user database system (e.g. webapp with database backend), there is relatively little to gain implementing data validity checking in the database, other than the declarative statements versus imperative rules discussion (which can already be a huge benefit, depending on the team).
But once you have a central database with multiple frontends (common in enterprise ERP solutions), enforcing data consistency in the backend becomes pretty much unavoidable -- otherwise a single bug or new feature in one frontend could disable entire production lines.
when there's a reliable, transactional, relational datastore at the bottom [..] you want to be pushing as much as possible down the stack, to reduce the surface area that someone could [..] change data in the wrong way
In my experience, keeping BL out of the DB is a practical concern, not a dogmatic one.
Usually there are two things:
1. Performance. Business logic is usually horizontally scalable, databases are usually not. You want to pull as much BL out of the database as possible, and put it into stateless microservices so they can be scaled.
2. Version control and deploying changes to business logic. Business logic changes frequently. Do you want to have to be making that frequent of changes to your database? Do you have practices around safe deployment and code review for stored procedures?
I've worked at a place where there was lots of business logic (millions of LOC) stored "in the database". In this case the database was MUMPS. It can be done, and it can be pleasant. The catch is that vertical scaling is the only option, and you have to spend years building your own tooling from scratch.
I think the line between database and code is destined to become even more blurred, but not by bringing the code down into the database, but by lifting more and more database concepts up into "application space" as distributed computing becomes more normalized.
Yeah the performance point is interesting, and I think it has merit in a well-defined scope. The idea of bringing DB concepts up is something I've seen done, but it wasn't a pleasant experience overall and I think everyone involved is regretful that we went down that path. It's a good story though. Ramble starts:
The system was an enterprise-focused offering with weak product management, so in the early growth-oriented days of the company ended up saying "yes" to a lot of feature requests that we probably shouldn't have. Where this particularly impacted was the permissions system, which evolved over time from an ACL with odd warts to also include a bolted on hierarchy of users and their objects, and then a role based system, and then all sorts of enterprise-level oddities like cross-links in the hierarchy, shared subhierarchies, roles that could appear at multiple hierarchy points, virtual ACLs that behaved slightly differently etc. So potentially a large number of different access paths between a given user and resource.
Years ago, when this mess was starting to expand it was decided it was too hard to model this in the DB (and really that should've been a giant red flag), so the new approach was to load each customer's dataset up into an application server that would handle the BL, crucially including the permission logic. It very much wasn't stateless, as you mention, but I'm not sure how it could've been really, given you needed so much loaded up from the DB in order to make these permission decisions. Would've avoided a lot of headache if it had...
The consequence though of using this write-through in-memory cache was it became the source of truth.
The chief problem this led to was that shift into application land was a bell that couldn't be unrung. Everything others in this thread have complained about seeing BL spread across all sorts of SPs in a DB happened here, just in application code (which again hints I guess that the chief problem is architectural and lack of governance, not a wrong-layer problem). Nobody could properly describe the permissions system in response to support requests without a lot of code scouring, let alone hold it in their heads.
Even worse, as the application grew in size and needs, we found we still needed things we had left behind in the DB. A couple of smart devs working on the core service, because they were smart and trusted, convinced themselves that what we needed was an in-memory transaction system for managing this in-server cache (by now the core was being called the Object Cache, a name so generic that it also should've been a red flag). So a couple of years went into implementing a technically impressive reimplementation of a transaction system.
Meanwhile the system as a whole was well past the point of being needed to split up into multiple services, so a grand goal was set of moving the Object Cache into a standalone service: the Object Service. Slap an OData API on it, and then leave that API open for all internal teams to hit. By this point the core team who owned this was starting to become well aware they had fallen into a bad pit of reimplementing the Postgres DB everything still sat on: transactions, generic query API, configurable in-memory indexes for each type of object, partition loading logic for the timeseries sets, user permissions etc. Worse, the generic query API (and this is what's ultimately turned me off OData/GraphQL etc for cross-team interfaces) ran into all the same problems as an SQL interface - people in other teams would always be coming up with brand new queries your indexes hadn't anticipated, forcing new work on the keepers of the cache to support.
The way forward probably would've been to leave the permissions structure in place and pull as much as possible out of the service/cache into separate stanadlone services, i.e leave the objects in the object cache little more than just IDs you could look up elsewhere for actual attribut...
Have done stuff like this where the queries were quite complex and essentially BI style.. Wrote a system to store and manage the procs in VCS and manage them through the ORM migration system(ORMs are quite a la cart).
Your either generating and submitting the queries through prepared statements or just storing them in the database. It's not "leaking" IMHO to do the later and in fact can come with benefits; you can call the procs while connected to the database through various other clients be it command line or a BI system. So in effect you are encapsulating the logic in the place with the most potential for re-use.
Business logic being in stored procedures is the ideal place for it.
The problem with it is technical: the tooling for migrating and managing schemas and stored procedures is hot garbage, and there aren't good ways to enforce consistency between the invariants of the applications and the database.
Really, it should be possible, on both application deploy and when stored procedures are updated, to import library functions that the stored procedures use from the database and run unit tests against them to ensure that all clients are compatible.
I like ActiveRecord. I like it so much I've implemented the Active Record pattern myself and compared my results with ActiveRecord.
That said, I've come around to the Repository pattern and quite like the Ecto way of doing things. It's a lot more flexible at the expense of some simplicity. It makes some really hard things easier than AR can.
As a fellow AR lover the only things that consistently bug me are when it does things like:
- .count on a collection calls SQL every time, but there is also a .count method for arrays - which means you need to remember to use .length if you just want to count the objects loaded into memory
- ‘build’ methods for an association usually only initialize a record in memory, but with has_one associations it deletes any record that already exists
So basically when it’s not clear about the fact that it’s doing database things.
There's a .size that works for pretty much everything: if the relation isn't loaded then it will do select count(*), but for arrays and loaded associations it does length of an array
This but Hibernate + Spring Repositories. Writing SQL is fine but the 95% CRUD cases are much better served with a decently managed ORM. We have an older project here using iBATIS, MSSQL and lots of stored procedures/SQL-first approach. See this tutorial for a preview of the horror to come: https://www.tutorialspoint.com/ibatis/ibatis_create_operatio...
'Employee.xml'
Now imagine this but with stored procedures and inserts with tens of parameters.
I've worked on a system where almost all of the business logic was handled as stored procedures (apart from things that belonged at the UI level, of course). Worked fine, was easy to debug , and performed great. Sure, you had to know how to actually write good SQL, which threw off kids who think programming equals using JavaScript framework du jour, but I don't see it as a shortcoming. No XML anywhere either.
At some point they rewrote it as a web app with NHibernate and all that. Took them literally many hundreds of man-years, and it still runs like molasses. And profiling whatever crap NHibernate emits is rather joyless enterprise.
So many people in this thread seem to think that your options are ORM or string concatenation. It explains a lot about why both ORMs and SQL injection are still so prevalent.
Does no one teach prepared statements in schools/online classes/textbooks? Does no one go digging underneath their abstractions to learn what they're providing of value and what is easily available without that abstraction?
> ORM protections against SQL injection attacks are worth the clunky syntax. Change my mind.
You can create what's called a prepared statement in most databases. This is a statement that contains placeholders for parts of the query that you want to change at runtime. You then query against this prepared statement, providing arguments for each placeholder. These arguments are typically typechecked and protected against SQL injection.
(I only say "typically" because I'm sure someone out there can provide a database/driver combo that does the wrong thing, but I'm equally sure you can find a misbehaving ORM that does the wrong thing as well.)
It looks like the author is generalizing shortcoming in SQLAlchemy as faults with the concept of ORMs. ActiveRecord for example, treats SQL as “the source of truth”, per his definition.
Also, there is a whole section called "ORMs take over connection management and migration" and not a single point as to why connection management handled by the ORM is bad, only comments on migrations. Moreover, migrations are not an unsolved problem at all.
In Ruby, ActiveRecord solves all scenarios I have encountered gracefully, and trust me, I have dealt with tons of edge case projects.
They mention the case where the ORM shadows the db schema. The argument isn't terribly salient, it's not like you can't build type checking in, it just means more work if that's what you want to do. Essentially according to the author the only way to do it right if you have an existing db is to write all queries from scratch.
My preferred solution for existing DBs is Ruby's Sequel or ROM if you want to go nuts. But I'm a diehard Rails lover and will choose ActiveRecord over all other solutions if I can. To me there's only one decent ORM and everything else is either good for special purposes or junk.
ActiveRecord does not treat SQL as "the source of truth". ActiveRecord objects maintain their own state, and still try to be a "proxy" for rows in the database. When you call "save", it's tracking the identity of the object using the primary key, and generating SQL from the runtime state of the object. This is backwards.
The biggest problem for me is this "proxy" behaviour. When you start treating objects as proxies for database rows, your database logic inevitably leaks into your business logic. Someone returns an ActiveRecord `User` object from a database method and now random other parts of your application are intricately linked to your database implementation.
On top of that you suffer terrible performance problems. Even if you're smart enough to avoid the whole N+1 query issue, you lose control (and more importantly visibility) over where database updates are coming from, and your ORM is either not smart enough to efficiently apply updates, or so smart that building the query takes longer than actually running it! (I have this problem with SQLAlchemy right now, where it's taking several seconds per request CPU bound in the ORM). Undoing this is literally impossible without a near total rewrite.
And it all serves no purpose! The application-specific interface to its database is usually quite simple. You can just have it be a literal interface/trait/whatever and implement each method by executing a bit of SQL, or calling a stored procedure, or however you like. Arguments and return values should be plain-old-data (no magic proxy objects) and each method should correspond roughly to a transaction so that the rest of the application doesn't have to worry about that database-specific stuff.
There are a ton of other benefits to doing stuff this way, too many to list here, but it really helps with complex migrations (like if you need to migrate from one database to another), maintainability, testing, etc.
While you're not technically wrong at the SQL level, I believe there's a higher level of business problem that ORMs like ActiveRecord solve for that's being overlooked. (aka ActiveRecord got Airbnb and Shopify a long way in scaling both developers and product).
CPU time building a query is rarely the business bottleneck until you're at a huge scale - scaling up the engineering team uniformly and having business logic at abstraction level closer to the rapidly evolving Product specs is the bottleneck that ActiveRecord solves for, and does pretty well.
If you'd like something that avoids object/hidden state - check out how Elixir/Phoenix's Ecto[1] was designed - it avoids many of the shortcomings of ActiveRecord and SLQAlchemy:
If "SQL is the source of truth" means you have no in-memory state, I'm not sure that's feasible, with or without an ORM.
Once you fetch something from the db, you have it in variables in memory. With or without an ORM. That could no longer be sync'd with the db a ms later. That you will probably re-use for more than one access (if you need it more than once), because issuing the same SQL again the second time you need to reference any data returned, in a given routine, would be insane.
I think what they’re proposing is something like “if I want to read and then update a value, incrementing it by one, the proper way to do that is to tell the DB to increment whatever value it has stored; not to increment my local representation of the value and then tell the DB to overwrite the value with it.”
The number of times I've had to increment a value by one are minimal. Or similar things where you want to update one value based on the other values, and do it in an atomic way.
But if I had to increment specifically, and were using ActiveRecord... I'd use the ActiveRecord increment! method. Which has the proper db-atomicity semantics you want, it does execute `UPDATE x SET n = n+1 WHERE pk = y ...`
This is not obvious unless you think about it - I've seen issues like this because the code looks correct if you ignore the update semantics, especially when it wont fail on your local dev env because in memory state will be the same as DB state since you're the only one hitting it...
Having to think about these things because the abstraction is leaky instead of just using the SQL where the transformation is explicit is not worth the hassle.
Using Clojure and yesql was a real pleasure for this - Clojure is immutable so you're working with values all over the place - and working with SQL query is just passing values in it and getting values out - no magic mapping - it's just values - if you want the latest value - query again, if you want to store a new value - send the new value. No mutation, no magic, just data.
> Once you fetch something from the db, you have it in variables in memory. With or without an ORM. That could no longer be sync'd with the db a ms later.
Nope. The DBMS is making the same transaction isolation guarantees to an ORM as to any other client.
I think the larger "source of truth" issue is how the schema is represented.
All of these systems that provide their set of schema objects that are then pushed to the database. This works fine for LAMP stacks, but as soon as you grow it becomes apparent that you've put the horse before the cart.
> (I have this problem with SQLAlchemy right now, where it's taking several seconds per request CPU bound in the ORM). Undoing this is literally impossible without a near total rewrite.
for the write? writes are not usually much of a performance issue except when people are inserting thousands of rows. You can replace flush with direct INSERT/UPDATE or use the bulk API. Pre-setting primary key values will also improve performance by an order of magnitude. Share some of your code and profile results on the mailing list and we can look into speeding it up.
SQLAlchemy always provides many techniques to optimize areas that have performance problems, not to mention the primary direction for most major releases is that of new performance features and improvements year after year, despite having to deal with the dog-slow cPython interpreter. No "total rewrite" of your application should be needed.
The SQLAlchemy ORM is a very complex beast. It provides plenty of methods to optimise the queries it generates. It provides no way to "magically speed up the python code".
This is not just for writes, but also for loading complex relationships from the database: we've gone to great lengths to ensure we're using relationships optimally so as to cache query results and not fall into the N+1 query problem, but our application spends all its time inside SQLAlchemy.
The only way to get the performance improvements we needed was to stop using it as an ORM altogether. However, the ORM was too intricately tied with our business logic to do that without a near total rewrite.
What we've actually ended up doing to do this rewrite more incrementally is to create a Rust service which implements just the "GET" end-points: this gives us an order of magnitude performance improvement despite it using the exact same database and fetching the exact same data. Most of the business logic did not need to be duplicated as it is not used from "GET" requests, but users of our application will get much snappier loading times, and it will generally feel much better.
> It provides no way to "magically speed up the python code".
it most certainly does provide many techniques to reduce in-Python computation. Have you read the performance section in the FAQ and worked through the examples given ? Have you looked into baked queries , querying for columns and not objects ? These are often quick wins that make a huge difference. The baked queries concept is going to be much more cleanly integrated in an upcoming release.
> This is not just for writes, but also for loading complex relationships from the database: we've gone to great lengths to ensure we're using relationships optimally so as to cache query results and not fall into the N+1 query problem, but our application spends all its time inside SQLAlchemy.
that's typical because a CRUD application is all about its object model and SQLAlchemy is doing all of that work. if you wrote your own object layer using Core then you'd see all the time spent in your own objects<->core layer. This is just the reality of Python.
Here's a runsnakerun I make some years ago of the PyMYSQL driver loading data over a network-connected database: https://techspot.zzzeek.org/files/2015/pymysql_runsnake_netw... Notice how just the Python driver spends TWO THIRDS of the time an the actual waiting for the database 1/3rd ? that's Python. If the database is on localhost, that pink box shrinks to nothing and all of the time is spent in Python, just reading strings and building tuples. It's a very slow language.
Rust language however is blazingly fast and is one of the fastest languages you can use short of straight C code. This is not the fault of an ORM, this is just the reality of Python. You certainly wouldn't think that if you used an ORM that runs under Rust, it would be as slow as your Python application again. It would continue to be extremely fast. The Hibernate ORM is an enormous beast but runs immensely faster than SQLAlchemy because it's running on the JVM. These are platform comparisons. SQLAlchemy is really fast for the level of automation it provides under pure Python. You made the right choice rewriting in a fast compiled language for your performance critical features but that has little to do with whether or not you use an ORM. As long as your database code was in your Python application, you'd end up writing an ORM of your own in any case, it would just be vastly more code to maintain.
> it most certainly does provide many techniques to reduce in-Python computation. Have you read the performance section in the FAQ and worked through the examples given ? Have you looked into baked queries , querying for columns and not objects ? These are often quick wins that make a huge difference. The baked queries concept is going to be much more cleanly integrated in an upcoming release.
Most of those techniques boil down to "use SQLAlchemy as a query builder rather than an ORM" - something I wholeheartedly agree with and would love to do, but it is next to impossible because these ORM objects have leaked into the business logic of the application.
> that's typical because a CRUD application is all about its object model and SQLAlchemy is doing all of that work. if you wrote your own object layer using Core then you'd see all the time spent in your own objects<->core layer. This is just the reality of Python.
I'm not trying to say SQLAlchemy is a bad implementation - it's a great implementation with tons of powerful features. I just believe that using anything as an ORM is a bad idea and directly leads to these performance and maintainability issues. Whether that's ActiveRecord, SQLAlchemy or even if someone implemented a similar ORM in Rust.
Some of the performance issues are due to python, but that's not the whole story: if I use SQLAlchemy as a query builder, and only return plain-old-data, I do not see nearly the same performance issues. Not because SQLAlchemy ORM is badly written, just because it has to do more work, create more objects, with more "magic properties", maintain more entries in the session, etc. etc.
>Someone returns an ActiveRecord `User` object from a database method and now random other parts of your application are intricately linked to your database implementation.
Indeed. Its a rookie mistake but hey, you work with rookies all the time! Orms can be useful but they certainly allow for some dangerous patterns that are hard to notice if you're a young developer.
> ActiveRecord for example, treats SQL as “the source of truth”, per his definition.
SQLAlchemy author here. I can't imagine how someone would not consider SQLAlchemy to treat "SQL" as the "source of truth" as well. All of SQLAlchemy's constructs map directly to SQL syntactically. Our most grumpy users are the ones who don't know SQL. This blog post would certainly benefit from some actual examples. "ORM-light tools that coerce responses into native structs and allow for type-checking are less offensive to me." - that is...an ORM?
What the blog probably means by "SQL as a source of truth" is that you first design your database and then generate the domain classes from it and not vice versa. This is for example what myBatis and JOOQ do.
I don't think there is enough information from the database schema to feed an ORM reliably. E.g., take a "boolean" in MySQL: MySQL doesn't have proper booleans, it just represents them as a `tinyint`. But you'd really like your ORM to convert those to your language's `bool`!
There is certainly a lot of information in a schema, and you definitely want your ORM's idea of the database and the database's idea of it to be in close alignment, but I feel like any serious attempt would find all sorts of holes like the above when it actually came to doing it.
Wouldn’t you just offer user-modifiable translators for such cases (presumably with some sane default)? It seems more preferable than writing the domain model in code first, resulting in N copies of the schema definition for N applications utilizing the db..
> N copies of the schema definition for N applications utilizing the db..
Is multiple applications talking to a shared DB schema a common practice, especially nowadays?
In my mind, each app/service should have a DB schema which only it talks to, and other apps/services needing data in that DB go through services exposed by that app/service (REST, RPC, GraphQL, whatever) rather than talking to its DB directly. That means you can rearchitect the DB schema, change which DB you use completely, etc., and only the app/service which owns that DB needs to be modified.
I've used SQLAlchemy a bunch and found that it has excellent support for complex (for me anyways) query options. For example, avoiding N+1 selects with eager joins has been a breeze.
Why use an ORM when I have to know SQL and its a direct syntactical mapping?
>"ORM-light tools that coerce responses into native structs and allow for type-checking are less offensive to me." - that is...an ORM?
Yeah...its a light ORM that focuses on turning a result set into an object but not the syntax remapping of queries. Object mapping is almost universally liked but ORMs usually include query syntax mappings and not the addition of a transaction lifecycle into your data objects.
because an ORM has nothing to do with writing your SQL for you. You can use textual SQL with an ORM and an ORM like SQLAlchemy has a query language that mirrors the structure of SQL in any case. nobody is taking your SQL away. Additionally, you most certainly do want a tool that will write most of your DML for you, there is no deep wisdom or joy in writing the same INSERT / UPDATE statement over and over again.
So you know one guy that quit over having and ORM instead of using SQL... do you have any idea how many quit due having to deal with thousands of lines of SQL string concatenations because some genius that also hates ORMs decided to do a giant project that way?
Then you call your alternative anti-orm and it generates... orm specs, and then you say "migration commands based on the git history"... what does that even mean? What does git history has to do with the database?
I tried to think about it from a gitflow release cycle perspective to be generous. This would mean constant churn of migration code for each branch of the repo.
If you have complex migrations which go beyond simply renaming columns, it would require you to write special migration code on a release branch which would then need to be merged back. yuck!
No, I agree with the post I replied to in that migrations should be human versioned as opposed to auto-versioned via a VCS. Again, I was being generous to the author trying to imagine a scenario where this would not create a dumpster fire.
This isn't quite the same as being solely git-based, but there are numerous examples in the industry of successful use of auto-versioned declarative schema management. By this I mean, you have a repo of CREATE statements, and a schema change request = making a new commit that adds, modifies, or removes those CREATE statements. No one actually writes a "migration" in this approach.
Facebook, who operate one of the largest relational database fleets in the world, have used declarative schema management company-wide for nearly a decade. If engineered well, you end up with a system that allows developers to change schemas easily without needing any DBA intervention.
This is interesting but I am curious about some of the implications. I am under the impression that migrations encompass more than schema changes. It may require data transformations, index rebuilds, or updates to related tables. I would agree that these things are preferably avoided, but in practice it seems that they happen frequently.
I viewed the author as targeting smaller dev shops who don't have a dedicated DBA/expert. Groups who might use an ORM's migration framework for example. In which case, it would appear that there is significant loss in migration flexibility if you remove application code from the architecture.
When operating at scale, it becomes necessary to have separate processes/systems for schema changes (DDL) vs row data migrations (DML) anyway.
For example, say you need to populate a new column based on data in other columns, across an entire table. If the table is a billion rows, you can't safely run this as a single DML query. It will take too long to run and be disruptive to applications, given the locking impact; it will cause excessive journaling due to the huge transaction; there can be MVCC old-row-version-history pileups as well.
Typically companies build custom systems that handle this by splitting up DML into many smaller queries. It's then no longer atomic, which means such a system needs knowledge about application-level consistency assumptions.
In what way is manually tracking the needed DB changes per feature branch even possibly better? Even if you are not running your migrations automatically on deploy, I would still check those migrations into version control.
Having auto-runnable migrations checked in alongside the code that depends on them allows you to review and test those migrations as part of your normal code review and CI processes.
I think there must be some misunderstanding here because I don't understand why you say either of these things:
> This would mean constant churn of migration code for each branch of the repo.
> it would require you to write special migration code on a release branch
There should only a be at most a few migrations per feature branch and merging is only an issue when you have two feature branches with inconsistent schema definitions. You should only have to add custom migrations to a release branch to fix bugs and those should be merged back into your develop branch as soon as possible to minimize effort spend merging later.
> So you know one guy that quit over having and ORM instead of using SQL... do you have any idea how many quit due having to deal with thousands of lines of SQL string concatenations because some genius that also hates ORMs decided to do a giant project that way?
Those aren't the only alternatives. Query builders exist.
A lot of languages have pleasant multi-line string literals. Also if you have a 1000 line hand-crafted SQL query, it's probably not going to be pleasant in your ORM either.
So I want to respond to this because there is a genuine question behind your rhetoric. And that question is "How do I cleanly do complex things in SQL without an ORM?"
And there is an answer to that. The idealistic answer is good DB design from the start. The realistic answer is by using views (sometimes materialized views) to create a clean representation out of odd tables.
The monster query can be decomposed into simple and clean operations against these views (and if necessary, views upon views).
The only caveat is you need a SQL expert to understand the performance if you want to do this at scale.
> The idealistic answer is good DB design from the start.
The problem with this statement is that DB schemas like code can evolve badly over time, even if they're maintained by a DBA sometimes (since they're human afterall too).
And then the DBA starts to suggest that you use things like triggers in your DB, which may or may not be good, but often surprise developers. "I just wrote 0 into the DB, why did it turn into NULL? What line of code did that? Ohhhh... there's a trigger."
I wrote SQLAlchemy after a five year job where all we did was work with enormous views in an Oracle database. They performed nightmarishly bad. The slightest attempt to query from one of these views using something as heretical as a join would kill the database. The source code for the views was locked up tight by DBAs who had no idea how our application worked, didn't care, and they could never be bothered to help us with their horrendously inefficient views or to give us new ones that our application sorely needed in order to get data from the database in an efficient manner.
SQLAlchemy exists exactly because of the pain the "just use views" approach causes. The entire point is that you can structure a query of arbitrary complexity in a succinct manner using Python structures, while not losing any of the relational capabilities. Of course you can write queries that are too complex, if you're then lucky enough to be able to have materialized views at your disposal, you can turn the SQL you wrote into one. But that's an optimization you can use if you need it, or not.
I've had similar issues, but I got the added benefit of being "DBA-splained" on why what we were doing was terrible.
I feel like if a DBA keeps thinking of the database as the solution to all of the problems (by implementing views, triggers, stored procedures, etc) then maybe that person should be willing to support them, otherwise they really shouldn't complain that the developers moved to an ORM.
Okay, obviously I've never worked in an enterprise enough company. I've always assumed a DBA was just a member of a backend team who likes thinking about SQLy stuff. Or a sysadmin who upgrades the computer the db runs on. Basically, a hat someone wears.
Do I seriously make the correct assumption that the people who are writing the backend of many apps are actually separated from the people who decide how the data those apps process the data, and you may have no direct say about the database schema? that effectively just a client - perhaps the only client, but nothing more than a client.
Obviously you've described the disadvantage of this approach; what advantage does it have?
It makes some sense when the data is extremely valuable, like in a bank. As a developer you can only access the db through stored procedures created by the database team. Which limits the damage you can do. I haven't worked in a big enterprise in years though so I don't know how common this still is.
On the one hand the article itself is offering a anecdote about being unhappy with ORMs. On the other hand you have an experience in which you were unhappy with views.
That would leave us at an impasse (he said / she said), unless we get more nuanced. So let's.
It sounds like some of your frustration is at some DBAs you knew. Firstly I've never worked with a DBA, I'm just a senior software engineer / DevOps and to me that includes an intimate knowledge of SQL perfomance, so maybe this is why I don't mind an in-database approach.
I'm fairly sure that whatever SQL Alchemy could do with a monstrously complex data-relationship views can do with better performance (the further the abstraction gets from the engine the less performance optimization it can do).
Now maybe you agree with that, and see ORMs as a tool for the kind of job where the dev is locked out of the database. If so then I can't really disagree as I haven't worked at such places much at all.
> I'm fairly sure that whatever SQL Alchemy could do with a monstrously complex data-relationship views can do with better performance (the further the abstraction gets from the engine the less performance optimization it can do).
SQL Alchemy will spit out SQL query. Whatever the DB engine can do with queries hand-written on top of views, it can also do it with the query generated by SQL Alchemy.
> Whatever the DB engine can do with queries hand-written on top of views, it can also do it with the query generated by SQL Alchemy.
No, on two levels. Firstly, there are many features in SQL that an ORM will not support (e.g. no substitute for a materialized view).
Secondly, once you get really good at SQL, you learn that very tiny seeming things can make the difference between a query running for an hour or a second (e.g. case-insensitive search against an indexed column). Part of being expert in DB technologies is knowing how/why some ways of writing a query are very fast, and others are very slow. The idea of an ORM is to hide that complexity, which is fine for a toy todo-mvc app. But once you start querying tables with 100k rows you need to understand that complexity and master it if you want to write a fast query.
You have shifted the discussion from ability of DB engine to optimize queries to the ability of a developer to write fast queries.
But in any case, we are not discussing some abstract ORM whose "idea is to hide complexity", but SQL Alchemy whose idea (one of them, at least) is to allow you to write composable queries yet still retain full power of SQL.
You are correct about needing to understand the underlying dB to be able to optimise it. I consider myself lucky to have come from a raw sql background and to have a deep understanding about how the query planners work.
You are incorrect in your other assertion. Sqlalchemy allows for optimising your queries at runtime in a way that just isn’t available to sql views. The beauty of sqlalchemy is that allows for arbitrary composition of blocks of sql. I too have had to fight with dbas in a previous life because they didn’t want me to construct a query in code (non sqla), but I eventually won because I was magnitudes faster because I had runtime knowledge they couldn’t use.
> Secondly, once you get really good at SQL, you learn that very tiny seeming things can make the difference between a query running for an hour or a second (e.g. case-insensitive search against an indexed column).
SQLAlchemy supports case-insensitive searches, per-column / per-expression collation settings, index and other SQL hint formats for all databases that support them, e.g. SQLAlchemy's query language supports most optimizing SQL syntaxes, with the possible exception of very esoteric / outdated Oracle things like which table you list first in the FROM clause (use index hints instead). We are adding esoteric performance features all the time to support not just SQL-level tricks but driver level tricks too which are usually much more consequential, such as the typing information applied to bound parameters matching up for indexes as well as special driver-level APIs to improve the speed of bulk operations.
SQLAlchemy has been around for thirteen years, plenty of SQL experts have come along and requested these features and we implement them all. Feel free to come up with examples of SQL-expert level performance optimizations that SQLAlchemy doesn't support and we'll look into them.
Yeah, don't get me wrong, I haven't used SQL alchemy and I'm not trying to besmirch it. I'm not even sure we disagree.
What I'm trying to challenge is the philosophy that an ORM is an adequate facade in place of learning how databases work. My point about views is that in my experience, the answer to disgusting queries is cleaning up the DB design (and views can be the tool to accomplish this). My point about case-insensitive searching (you can create a case-insensitive index if you want) is that a lot of db-performance stuff just can't be solved on the query side alone anyways.
It sounds like SqlAlchemy is designed with a lot of flexibility around how queries are run, so maybe you agree that understanding what's going on beneath the hood is important to handle these complicated cases.
> What I'm trying to challenge is the philosophy that an ORM is an adequate facade in place of learning how databases work.
Thank you for this response and I agree, we are likely on the same page. I don't know that anyone actually espouses that philosophy. This is the anxiety that ORM skeptics have, and certainly you get beginners who rush into using ORMs not knowing what they are doing, but if someone wants to be a DB expert, I'm pretty sure they go to read about databases :) These ORM beginners will fail either with the ORM or without out. I guess the idea is you'd prefer they "fail fast", e.g. the ORM covers for them while they proceed to screw up more deeply? This is arguable; if you've seen much of the non-ORM raw DB code I've seen, it too fails pretty hard. But even with this argument, if ORMs produce the problem of incompetents who are not found out fast enough, why hoist the denial of useful tools to those developers who do know what they're doing.
> I'm fairly sure that whatever SQL Alchemy could do with a monstrously complex data-relationship views can do with better performance (the further the abstraction gets from the engine the less performance optimization it can do).
if you have the option to use materialized views, that can be helpful when appropriate, however materialized views have their own issues, not the least of which is that they need to be refreshed in order to have current data, they can take up a vast amount of storage, not to mention a lot of databases don't support them at all. It should not be necessary to emit CREATE MATERIALIZED VIEW with super-user privileges and to build the infrastructure to store them and keep them up-to-date for every query one needs to write or modify that happens to have some degree of composed complexity.
you don't need an ORM for these queries either, just a composable query builder (like SQLAlchemy Core) that allows the composition task to be more easily organizable in application code.
none of this means you can't use views but it's unnecessary to dictate that they should be the only tool available for dealing with large composed queries.
Many of us also know stories about how ORMs can be slow if developers don't pay sufficient attention. In some cases told by database experts who see the query code locked up tight by the app team, etc :)
Having a separate "DBA" from the developers or having an adverserial relationship with said person is not really a technical problem I think.
> "migration commands based on the git history"... what does that even mean?
It actually seems like a really cool idea. E.g.
* Commit A: create a schema.sql file "create table t (a INT);"
* Commit B: update the schema.sql to "create table t (a INT, b INT);"
* The tool can generate a migration from A..B "alter table t add column b INT;"
It seems like it probably would work really well for creating new tables and columns in simple projects. In more complex projects I've worked on, we usually had more complicated migrations to update the data to fit the new schema, and we often had to be careful to interleave schema, data and code updates (especially if you wanted to be able to rollback).
Automatic migrations using only source-code snapshots don’t work for things like column renames or splitting/combining columns. Every tool I’ve used requires some migration metadata.
Any SQL wrapper worth its salt provides some mechanism to specify positional (or even named) parameters within that string (without resorting to string interpolation or concatenation) and pass them in when executing the query. Usually it'll be something like
foos = execute_sql("SELECT * FROM foo WHERE bar = ? AND baz = ?", [bar, baz])
If you're resorting to concatenating strings, then you're almost certainly opening yourself up to SQL injections (and rather needlessly, I might add!).
I think the issue the parent is referring to is when you have optional clauses in your statement. Like if you have a table with 5 update-able columns (where someone might want to update any 1 to 5 columns that you don't know in advance), and you want some generic code that updates a row, if you're writing raw SQL then you have to do concatenations to build up the "set clause":
UPDATE sometable SET {set clause} WHERE id = ?
The "set clause" might just be "foo = ?", but it might be "foo = ?, bar = ?", or "foo = ?, baz = ?", etc.
So you mean I'm writing code like this, in a language somewhat like Typescript but simplified to my end.
function update(id: ID_TYPE, value: Partial<sometable>) {
const set_clause = value.keys.map(k => k + " = ?").join(", ")
const params = value.values.concat([id])
db.query("UPDATE sometable SET " + set_clause + " WHERE = ?", params)
}
How is code not terrible? It shouldn't pass code review. Once you concatenate strings to generate your sql, all bets are off. There will be a day when your object doesn't match your assumptions. Either you have a series of different functions, each of which will change under different circumstances; or you should just be generating this function like this directly from the schema in which case you might as well just generate code you want at build time so there's string concatenations anywhere.
Is there some other circumstance when you want that?
The answer to that is almost certainly "it depends on your DB". Most query planners should be smart enough to not try to write anything, but some are smarter than others. For example, SQL Server (if I'm understanding right) does perform a log write even if it should be obvious that the value won't change: https://www.sql.kiwi/2010/08/the-impact-of-non-updating-upda...
That being said, I'd skeptical of the performance impact (if any) being all that significant; even in a worst-case scenario of "yeah, it's going to stupidly write every field every time", the columns for each row should be pretty close together both in-memory and on-disk, so unless you're writing a whole bunch of giant strings or something at once (and even then) there shouldn't be a whole lot of seeking happening.
tl;dr: assuming there's a minor performance impact is reasonable, but it should probably be measured before trying to optimize it away.
Which doesn't work for table names, or for building up where clauses gradually, etc. SQLAlchemy makes those much more trivial to deal w/ the SQL Expression bits. (Though technically, that's not an ORM.)
Also, getting the library to properly expose the formatting interface is also a trick. We would do massive loads/dumps that we did not want to use Python for (but we worked in Python) as the DB-API pulls the entire result set into RAM, and we were pulling >RAM (and we didn't want to do something lower-level w/ the driver). We'd spawn psql, but that needs the query, as a complete, pre-templated string — it does not support any sort of positional parameters. And the Python library really doesn't (last I checked) have a public interface to the templater portion of the library. (I think this is b/c PG just sends the parameters, and the binding is done server-side. So, really, psql needs an interface to supply those values as arguments to the CLI.)
> Which doesn't work for table names, or for building up where clauses gradually, etc. SQLAlchemy makes those much more trivial to deal w/ the SQL Expression bits. (Though technically, that's not an ORM.)
Thin SQL wrappers/dsl's like the SQLAlchemy expression library are great. IMO those thin wrappers are what most people should reach for first, over a full blown ORM. A good mimimal sql wrapper will:
* Save people from pain, problems, and security issues involved in building queries through string manipulation.
* Map very closely to raw SQL
* Make it easy to compose query expressions and queries, using language native features.
* Help devs develop their SQL skills. Learning the wrapper IS learning SQL, for the most part.
I have a hard time seeing many good reasons to add any more layers of abstraction on top. That last point is particularly important to me. My first exposure to many advanced SQL techniques, was through such libraries. Since the code maps to sql quite naturally, that learning can be applied in a much wider variety of contexts. Teach someone how to do aggregations using Django's ORM, and you've taught them to do aggregations in Django's ORM, and basically nowhere else.
> We'd spawn psql, but that needs the query, as a complete, pre-templated string — it does not support any sort of positional parameters. [...] So, really, psql needs an interface to supply those values as arguments to the CLI.
I haven't tried this before, so caveat emptor, but per psql's manpage this should be supported:
QUERY="SELECT * FROM sometable WHERE name = :'foo'"
echo $QUERY | psql -v foo=asdf # ... other args ...
Basically: you can use colon-prefixed variables in the query text (with the variable name optionally quoted to indicate how the result should be quoted, apparently?), and set them with the -v option (which is equivalent to using the \set command within the query string itself).
This also happens to work for table names, at least per the example in the manpage:
testdb=> \set foo 'my_table'
testdb=> SELECT * FROM :"foo";
Except in this case it'd be more
echo 'SELECT * FROM :"foo";' | psql -v foo=my_table # ... other args ...
Still no option there for positional parameters, but it's a start, right?
> "migration commands based on the git history"... what does that even mean? What does git history has to do with the database?
I'm not sure what git-specific interaction the author is envisioning, but more generally: declarative schema management tools are based on the notion of using a repo to store only CREATE statements. It's an infrastructure-as-code style approach, where to modify an existing table, you just modify the CREATE definition. The tool figures out how to translate this to an ALTER.
I'm the author of an existing tool in this space, Skeema [1], which supports MySQL and MariaDB. Other recent tools in this space include migra [2] and sqldef [3]. From what I understand, in the SQL Server world there are a number of others.
One problem I’ve seen is that most non-trivial apps have both OLTP and OLAP elements in the same places. Think of how many “mini-dashboards” you see sprinkled throughout the UI of a modern CRM.
ORMs are generally not good at or even capable of efficient set-based operations spanning entitles.
Building a dashboard with an ORM results in pain and wildly oversized DB instances in my experience.
ORMs are amazingly useful in the limited case that you're performing basic CRUD operations or simple joins.
In any sufficiently complex codebase you'll be doing more than this, however, and the work to support the ORM properly then outweighs the work of simply coding the raw SQL and preparing statements as appropriate.
People hugely exaggerate this IMO. If you're writing a lot of queries that can't be composed, with sufficient performance, in ActiveRecord, but can be in raw sql, then you've probably done something hair-brained else-where.
And for those special (and IMO pretty rare) occasions, you can drop down to arel or raw SQL anyway. Why throw away the consistency and readability of something like AR for edge cases, when you can just treat your edge case as an edge case with raw sql and still keep AR for your other 95% of queries.
I maintain a relatively unremarkable but bespoke online discussion forum, which has hundreds of queries, few of which could be composed by an ORM, let alone composed and run performantly. The median complexity query in my code base probably has two or three joins, two or three subqueries, and some kind of aggregation or window function.
The result is a typical page runs around two or three queries total—one query to authenticate the user and load everything about their profile and permissions, one to load the entirety of the data being output on that page, and occasionally one to update a statistic somewhere.
(The authentication query runs on every page because there's absolutely no persistence in the application layer. The authentication query goes three layers deep in subqueries and includes half a dozen joins. It hits perfect indexes when it runs and takes only a few msec round trip.)
> People hugely exaggerate this IMO.
In my experience, people who think an ORM can do most things are simply under-experienced with SQL and set theory.
> The median complexity query in my code base probably has two or three joins, two or three subqueries, and some kind of aggregation or window function.
Well, just from that description, Django ORM could do it. Can you post an example of a median query? I'm curious to see why it can't be ORM'ed.
This is a heavily redacted, completely renamed and summarised version of a typical page data query. It's less complicated that the top three most common queries that run to build the most common pages.
SELECT
b.field, b.field, b.field,
group_concat(concat(y.field, useful_thing)) as useful_things
FROM (
SELECT t.field, f.field, w.field,
(case when w.wid is null then 0 else 1 end) as has_watched,
exists(select id from posts p where p.tid = t.tid and p.uid = :uid) as has_posted
FROM (
SELECT tid
DENSE_RANK() OVER (PARTITION BY foo ORDER BY bar DESC) AS useful_pseudo_id
FROM editorial_things
WHERE thing = :thing
ORDER BY something
LIMIT number
) as a
INNER JOIN thread as t ON t.tid = a.tid AND t.last > Now()-INTERVAL 2 DAY
INNER JOIN forum as f ON f.fid = t.fid AND f.fid in (:security)
LEFT JOIN watched as w ON w.tid = t.tid AND w.uid = :uid
ORDER BY something
) as b
GROUP BY something;
The other thing is that being returned objects just adds complexity and handling. The language I use has a perfectly nice native, iterable data type for returned database records (kind of like an array of dictionaries) and I'd rather just use that instead of an object middleman to satisfy some kind of OOP completionist fantasy.
CFML (i.e. ColdFusion) using the Lucee engine on the JVM. Don't believe the popular scorn: it's as good a language as any other for web development. Perhaps not as innovative as newer languages, but it's densly packed with pragmatic conveniences and (for better or worse) doesn't impose any particular architectural style upon you.
CFML has a "query" datatype that represents the rows and columns returned from a database combined with useful metadata and really neat features like n-level iteration where values repeat. To the programmer it's like a dictionary that magically changes its values by passing it to an iterator. Without the iterator it works like a dictionary of the first row. Or if you treat it like an array you can manually read any column in any row directly.
In most applications you're going to be doing both things, I remember Rail's ORM was always presented this way: it's the far better option for simple object relationships and CRUD style systems but you can still write raw SQL as needed for complex stuff or things requiring performance optimizations.
There's plenty of ways to manage complexity in software. Two or three different databases in a single app doesn't kill them, nor will two different DB interfaces.
I agree. Don’t know why you’ve been downvoted. Your experience directly mirrors mine.
What I did find interesting was the pushback I got from my team when I told them that I wanted to drop the Java ORM (JPA). One guy, who I respect, was super critical of the decision. He was telling me that SQL was the reason we had a performance problem, but when I investigated I was astounded by how fast SQL was, even over JDBC. It was clearly the ORM that was slowing us down.
In his defence, when I was able to show him literally 100x performance improvement after moving a service to PL/PGSQL and MyBatis (Java), he changed his mind.
New concept of the day: Object-Relational Mapping, converting an object, with all its members of diverse types, to a format suitable for storage in a database, roughly a table of scalars.
You'll have to pry Hibernate and Spring Data JPA from my cold, dead hands. Extending CrudRepository and getting a ready to use repo for an entity type, combined with annotations for customer JPQL queries? Yeah, I'll take that over any hand-rolled, low-level SQL interfacing for almost anything.
But, as always, "use the right tool for the job." I have gotten tremendous value from ORM's over the years, but I'm not going to insist on using one for everything just "because".
Ha. Funny but I’m the exact opposite. Had to use hibernate and spring once. Trying to debug with 45 XML config files scarred me for life. That coupled with MySQL who even Ellison will tell you, is a toy, I saw the writing on the wall and quit the job a couple of months before that whole team got fired while the company had no choice but to throw the code in the bin. My view is that ORMs can never compete with a proper enterprise scale RDBMS with people who know how to use it.
JPA/Hibernate has moved on a lot from the church-of-XML days. The basics are a lot smoother, with annotations and sensible defaults and so on, and there are some pretty nice additions.
My favourite neglected feature is JPQL with constructor expressions, which lets you write queries in an SQL-like language, but in terms of objects rather than tables, which is slightly easier, and materialise results directly into objects of your choice.
One of the nicer JPQL features is being able to navigate the Java object model and have that translated to the equivalent joins in SQL.
However, there are things that JPQL can’t do, but it’s easy enough to create a native SQL query and have its results map to JPA entities. I use Spring Boot (JPA, Spring Data, Spring Data REST) for the sheer convenience of it and speed of development, not to abstract away the database, which is always PostgreSQL. I am very comfortable writing SQL queries and making use of PostgreSQL-specific features and Spring Boot with JPA certainly lets me do that when I want to.
How so? I think the overall point of my post is clear, and consistent: I am a big fan of ORM's, won't be giving them up because some blogger-guy wrote another "ORM's are evil" post, and find them very useful for almost all of the things I do with databases. But the "almost" there is the key word. So, again, use the right tool for the job. Sometimes, in my experience, it is an ORM like Hibernate, other times something else makes sense. For example, I do some work with Redash for creating dashboards, and in that environment, you're working with SQL directly. shrug
I'm going to project my naivety here and ask how does one write raw SQL queries in code? Should said queries live in their own files and be referenced when needed or are the queries usually written where they are called?
I've personally seen and done both. At one job (first actual dev job) it was a lot of complex business queries for analytics. I put these into their own python module as string "constants" and imported them (I would not do this again). At my current job we put them in .sql files and have a 3 line function that opens and reads them into a string. I sometimes write them inline. I've come to only one real conclusion though is that unless it's super trivial I put the SQL outside of where they are being invoked and name the .sql something that makes sense. It's just noise in my code and I want to see the logical steps.
I've worked on a few apps that relied heavily on SQL stored procedures as the data access layer. In a database like SQL Server or Sybase, a stored procedure is like a function with a body of SQL that is stored on the server, receives typed parameters and can return more than one result set, each result set having a different shape (different columns). So, a call to get an entire graph of data would only require one request and one response, saving many round trips. You could write a stored procedure that selects a customer, their recent orders, the products on each of the orders and so forth. You can also insert/update into multiple tables in one trip and you can wrap any parts of your SQL in transactions.
The SQL variant used by SQL Server and Sybase, called Transact SQL, also has elements of procedural programming such as variables, flow control with IF/THEN/ELSE/switches/etc, looping, calling other SQL procedures or user defined functions, exception handling and so on.
These features have been part of SQL Server since the 90s. It's extremely powerful and if used well, can be much more elegant and flexible than an ORM in my opinion. It's a shame that PostgreSQL doesn't support stored procedures quite as well or have an SQL variant that's as well crafted as Transact SQL because I'd love to be able to write apps in that style again. Somewhat recently I believe PG got something like stored procedures that can return multiple heterogeneous result sets and although I can't remember the specifics, I don't think it's quite as robust as what you can do with TSQL and it lacked client/driver side support maybe... (Also PG has some support for running TSQL itself, but not with all the same features.)
The ‘somehow’ is usually a half-baked migration tool
Maybe don’t use a half baked ORM.
I’m experiencing schadenfreude that someone is having ORM problems in 2019 long after the NoSQL hype died.
I can remember on zero hands the number of times my not half baked ORM was the source of my problems such that I wanted to say “fuck it lets just write SQL”. ORM patterns are pretty well laid. Hell they haven’t changed much in at least a decade or more and they work great for the problem they solve. Which makes me think maybe a relational store is not right for whatever you’re using it for.
tl;dr If you have ORM problems I feel bad for you son, I got 99 problems but SQL ain’t one.
The sweet spot for me is a query builder: a tool to generate SQL and treat queries like code, and not get in the way like a heavy ORM. Shout out to http://knexjs.org/ for Node.
I've used Sequel in Ruby for that, and... it worked pretty well, to be honest. But it was for a service that did some complicated DB shenanigans, and not any user API. I wouldn't skip out on ActiveRecord when running a public facing Ruby app.
I don't use "raw Arel", I use most of the parts of ActiveRecord -- but I definitely appreciate that about Arel, and use that aspect of Arel intentionally. Composable query parts made of code.
I think it's a mistake to think you can't write good efficient SQL for well-normalized schemas with AR. You can. Usually anyway, for a great many use cases.
[Arel is _really nice_, but sadly Rails absorbed it and considers it "private API", especially in it's most sophisticated features. I use em anyway, they generally don't break... but if I wasn't using ActiveRecord as a whole, I'd probably use Sequel instead.]
I work with Rails almost every day and I'm still quite fuzzy on where Arel ends and ActiveRecord begins. It's an area of ambiguity that a technical post on the history and contemporary status would be appreciated by many.
For the most part, the stuff you do when constructing a query is Arel. Where you chain methods and such.
When it was first added to Rails, improving the query building possibilities, it was a separate gem that Rails depended on. I'm not sure if it was written specifically for Rails originally just maintained in a separate gem, or intended by it's original writers to be an independent project. But it was later absorbed into the Rails repo and we were later told by Rails maintainers that it's more sophisticated manual API (look up how to make a subquery with Arel for instance) were not intended as public API and had no backwards compat commitment.
On the other end you can often build better UI's to reduce the amount of dynamic query building required. Dynamic queries are most heavily needed when you give users a data table and let them sort/filter as they wish, more often than not this is a complete failure of the UI to understand users workflow and build around it.
We went from hibernate/eclipselink to MyBatis - and have never looked back. Needing to deal with the idiomatic behaviour of JPA, HQL and the target SQL is a nightmare. We’ve literally removed tens of thousands of lines of Java code by dropping JPA.
We’ve taken the view that audit should be done in the database layer using, for example, triggers. Audit at the application layer is less resilient then at the SQL layer, IMO.
In fact, an important part of our transition away from ORM was to invert the ORM-centric relationship between the application and the database. We wanted our tech people to be able to manipulate the database natively via SQL, which is generally much easier and more efficient than writing and deploying Java code to do the same thing.
For us, this meant moving most data manipulation logic into the database using PL/PGSQL. Doing so has empowered a team of non Java admins, massively improved performance, and significantly reduced LOC. I’d say that we now have about 30% (1/3rd) of the LOC in PL/PGSQL than we had in Java+JPA. Obviously this means less bugs, but performance is literally 100x in some cases.
I think that if you take a Java-first view of your application then all the complexity of JPA is part and parcel of any solution, and that’s fine for you. But we’ve found that taking an SQL-first approach has been great for our use cases, and has had lots of ongoing benefits.
Of course, we needed to build a bunch of tools to help with this. For example, we built a gradle plugin that treats SQL code like Java code so we can write libraries in SQL with transitive dependencies. We also needed to build testing tools and tools to automate schema migrations for CD.
I do think the lack of tooling around managing large SQL-based projects is a blocker to wider adoption of our approach, but the benefits of going against the ORM grain have been very significant for us.
No, I did look at JOOQ briefly, but MyBatis came somewhat recommended and perfectly scratched the itch I had at the time. So I don’t have an opinion either way with JOOQ. I am however super happy with MyBatis, we’ve been using it 2+ years in dozens of microservices, but we are KISS oriented and so only use a subset of its functionality (no XML config for example).
That's a fair criticism about auditing. Doing it through database triggers avoids the problem with audits being missed because of code that doesn't participate in the Hibernate lifecycle (including native SQL queries and Criteria updates, yikes!).
I will also concede that under some circumstances it's too slow to bring the data to the code, and instead you have to bring the code to the data (i.e. PL/PGSQL). The sort of speedup is mostly from eliminating round trips and data marshaling, however. That's not quite a like-for-like comparison of an ORM vs a mapper.
The reason I stick to ORMs is mostly because of RAD tools that save me so much time. For instance, JHipster generates liquibase migrations and JPA entities. This gives me a relational schema very quickly. To avoid any "surprise" queries that tank performance, I do turn Hibernate's statement logging on when developing new features.
Next time if I can find some more tools to help with an SQL-only + stored procedure approach, I'll give it a deeper consideration. Maybe convince your company to open source some of tools it has developed!
There is a lot of internal enthusiasm for open sourcing our tools, but we are heads down at the moment so it’s just a matter of time (or lack of). I think we will time it for the next pgAU conference so we can talk about it at the same time.
I definitely agree with you that tooling is a big deal. We did spend a lot of time manually proving it out before we spent the time on the tools. It was a bit of a leap of faith but it quickly became obvious that we were onto something.
And you’re right, the point of our approach was to move the code to the data. We deal with reasonably large, complex real time data sets Sotheby’s round trips and marshalling become the dominating contributor to total time. Hence our ability to improve some operations by 100x.
I can see that if you have a familiar and reliable tool chain and a compatible use case then ORMs could be great. I think our problem was that we had neither, so it was never going to work out too well for us!
Same. When writing complex queries, Hibernate just got in the way. MyBatis just lets you map any result set to objects as you see fit. I also make use non-standard features such as GIS, gin basesd queries, arrays and json in Postgres and this is just easier with MyBatis since I am still writing the queries in SQL. Performance seemed better too.
Imperative code is complicated when dealing with data, so we abstract imperative code into SQL.
SQL is complicated so we abstract it from SQL back into imperative code via an ORM.
Of course it's backwards.
It's like one of the biggest legacies of web development next to javascript, html and CSS. SQL is a very specific way to think about data and a very high level and leaky abstraction. So high and leaky, that you literally need to look at the query plan in order to optimize sql queries. If Database IO is the bottleneck for web development the best abstraction for this area is most likely a zero cost abstraction (rust for example). Instead we have SQL and we create a generation of SQL hackers who memorize a bunch of technology specific hacks to try to get things to work. Why is SELECT * bad? It's a hack you memorized, a leaky abstraction.
I'm not saying schema-less is better hear me out. I'm saying SQL is a bad API. You can probably make some api language that's more explicit and imperative on top of something like postgres. Change the API itself don't write something on top of it.
Either way, an ORM on top of SQL is like what type script is to javascript. You know when they make an API on top of an API it's to cover up something that doesn't work well. Unless it's a zero cost compilation such fixes only make performance worse.
If you insist on using some abstraction on top of sql the way to do it with zero cost is an api that is bijective to the syntax of SQL itself. Type script I'm guessing is relatively isomorphic to javascript, so it works out.
> I'm saying SQL is a bad API. You can probably make some api language that's more explicit and imperative on top of something like postgres. Change the API itself don't write something on top of it.
Something like VSAM or IMS?
The relational model and declarative query languages were invented because the previously existing imperative navigational model was far too inflexible and difficult to use. Access paths were baked into the data structures, you had to be your own optimizer, and if you optimized for the wrong usage patterns, you had to refactor everything or live with poor performance.
You can enforce patterns via types. You can also have default optimizations. The problem with SQL is to choose optimization you have to do it by poking the syntax.
Obviously the above example is trivial, wrong and probably won't work. But I hope it paints a fuzzy picture of my point. That is that the optimization or 'plan' should be explicit. You choose auto or you dive in and you change it yourself. No language or syntax hacks.
It seems that what you want is not an imperative API, but a more explicit one. I'm afraid the problem is that specifying the access methods or optimizations to use in the database is very product- or even version-specific, on the other hand you can already tell a database what to do using optimizer hints.
I want both. An imperative language makes it more able to be isomorphic to a web app api. You can have corresponding types and functions without something totally different.
Optimizer hints is a good idea. As far as I know this isn't a well known feature (if it even is a feature) for postgresql, which is the DB I have the most experience with.
No thanks. The benefit is not having to specify the plan. The optimizers themselves are fairly good. A well designed table with the right indexes should perform well.
Raw SQL is great at what it does.
ORMs are typically good at what they do.
Depending on the use case both have their pain points. Typically I see a mixture of ORM for most CRUD based stuff and raw SQL for cases where the ORM isn't suitable.
In my example code. Do you notice the string "auto"?
It means you can literally have the API do exactly what SQL does. Automatically specify the query plan. But you explicitly do it.
Meaning that sometimes the auto planner screws up. When it screws up, in those cases you explicitly make it use another algorithm rather then modify SQL to be slightly different and hope that it compiles into something more reasonable.
I'm not convinced on the UX of that. Developers are lazy. I think what would more than likely up happening is they explicitly set it to "auto" and modify the query to get a more performant plan. After a certain query complexity It would become exceedingly hard to start to be able to piece together plans by hand.
A bunch of performance problems aren't even solved by tweaking the query either. They're solved by changing the database structure. Adding the appropriate indexes and such. Making sure datatypes match between joined data and no implicit conversions are happening. Right-sizing columns.
No amount of specifying your own query plan will do anything to affect those kinds of issues.
In practice I find mostly it comes down to cardinality estimate issues as a very common source of problems as the database either over or under provisions enough memory for the query. If it estimates it's going to get back a lot more data than it actually does and it grants too much memory that will reduce parallelism because that memory can't be used by other queries. If it under-estimates it doesn't grant enough memory and when it gets back more rows that will fit into memory it has to write them temporarily to disk taking a massive hit in I/O performance.
How does your scheme work with figuring out how much memory the database should grant to a query when specifying a plan by hand?
What's more is plans change over time as statistics change. Leaving it up to the query optimizer means it's adaptive. Having to specify yourself means you have to know the optimizer isn't giving you the best plan at design time. There are some cases where you know that. There are some cases where you don't.
You can already specify query hints etc. I think SQL just has this covered already. I have no qualms if the query changes slightly.
Maybe I’m in the minority, but I’ve found that if the right ORM fits your brain, it can be incredibly useful for a lot of CRUD operations, especially if it offers escape hatches to lower levels of the abstractions; ultimately down to raw SQL.
SQLAlchemy for Python is that for me. I find it incredibly expressive and allows me to write complex queries that are readable, maintainable, and perfomant. Yes, it requires learning, but it’s the right tool for the job, personally.
Most ORMs I have used are by definition a leaky abstraction: an inferior and insufficient representation for the underlying language at which they try to improve upon.
In addition, ORMs greatly increase the surface area for troubleshooting. Instead of just being able to run and profile a query, you now need someone who is experienced in the internals of the ORM and how it "plans" a certain query. Of course, because ORMs do not understand how the data is actually laid out, when they say "plan" they mean create the SQL string.
So in place of a query you will need to learn the conventions of the particular ORM, leaks and all, and how that maps to the SQL. Then you will still need to understand how that SQL actually interacts with the table schema.
The end result is normally one backend person who is very territorial over their code, and who acts as a bottleneck for the rest of the team. If you're using an ORM I'm sure you already know who this guy is...
Yeah it's kind of a false dichotomy isn't it? Just use your ORM for CRUD operations and use proper SQL if you're doing anything much more complicated than basic joins. It's a lot better than the busy-work of hand-cranking every basic SELECT/INSERT/UPDATE/DELETE statement, preparing them and the associated boilerplate that you usually have to write to stick it in/extract it from your object model.
As someone who's used both SQL and Django ORM, I could ask: "Why SQL is so backward?"
Here's a datapoint I experienced:
The company I worked for didn't trust the Django ORM for DB migrations. They thought they could do it by hand. After lots of deployment failures where the migration script worked in the dev environment but failed in the prod environment, we switched to using django migrations. Haven't had an issue since.
The author even admits that migrations are a hard problem, but yet, he has no solutions other than just using raw SQL.
Edited to Add:
In Django, these migrations are handled pretty well with a single command to make the migrations. In SQL, someone would probably have to spend some time testing, and then would need to be reviewed in the PR.
In my experience Django's migrations are really solid, assuming you're using the PG or Sqlite backends. This is especially true if you're working from a new Django project, and not a "legacy system" that wasn't initially built for Django.
For simple queries, sure SQL is great but it is not composable. Ideally you want to send your whole query to the database at once and for a complex statement that may mean many many lines of reasonably complex joins etc. With Sqlalchmey you can write a bunch of simple components and join them together for submission at once instead of having a thousand lines of SQL that can be hard to read. You can re-use parts and even come back later and understand clearly what is happening. How do I know this? 25 years of writing SQL and at leat 6 using SQLAlchemy. A lot of the time I will still compose and test in SQL using, say DataGrip or psql, then build an SQLAlchemy query from that. This guy should look at alembic too. It has it's faults (like the half module half executable runtime), but it solves most of the problems of just having a folder with a million naively numbered SQL scripts like managing up and down versions as well as branches.
So, I've seen this go back and forth so many times, and I've come to the conclusion that the two extremes are irreconcilable. Not because they're incorrigible, but because they represent two very different ways of using a database.
For some, the database is not actually a database, per se, it's just a place to persist state. Usually folks in this camp think in an object-oriented way. They haven't read Codd's paper, they don't care about the relational algebra, they just want some way to take bags of properties and stick them somewhere that offers random access with good performance and is reasonably unlikely to not spontaneously erase data. If you're in this camp, ORM works great, because there isn't really much of an object/relational impedance situation in the first place. The data store wasn't structured in a way that creates one, there's no need burn a bunch of effort on doing so, and dragging in the whole relational way of doing things is like dragging a wooden rowboat on a hike through the forest because you'd like to use it to go fishing on the off chance you find a nice pond.
Others see a lot of value in the relational model. They're willing to put a bunch of time and effort into structuring the data and organizing the indexes in ways that will allow you to answer complex, possibly unanticipated questions quickly and efficiently. They, too, hate the syntax for recursive common table expressions, but are willing to hold their nose and use them anyway, for various reasons, but mostly because people think you're really cool when you can spend 30 minutes and make something go 2-3 orders of mangnitude faster than it used to. They don't think of the data in terms of bags of properties, they think of it in terms of tuples and relations and a calculus for rearranging them in interesting and useful ways. For them, there is potentially a huge problem with object/relational impedance; the data's organized in ways that just don't fit cleanly into Beans.
The thing is, neither of these ways of using a database is wrong. Each has it strengths and weaknesses. The trick is figuring out which way fits your business needs. Well, that's the easy trick. The hard trick comes when someone on your team doesn't understand this, believes there is one universal solution that will work for everyone, and is hellbent on jamming the One Righteous and Holy Peg, which happens to be square, into a round hole.
If you're thinking in the first terms, is a relational DB the right backing store, or wouldn't it be better to back your DB with something more like Mongo?
Do they still obtain some benefit from the schema, since from time to time the semantics of a property will change, and a schema can tell them what the current shape of the data is and guide the migration. Or would they prefer to just write a new property which does a lazy conversion from the old terms? (To an extent, I suppose the answer to this question determines the answer to the first. But I guess there's other tradeoffs to Monggo I'm not aware of, since schemalessness fills me with fear and I just don't want to look there.)
Regarding your final paragraph: I suspect that, for many apps, the choice between relational vs transparent persistency is largely determined by the team who is working on it. The "hard trick" is therefore trying to balance a strong personality with a strong view who disagrees with the rest of the team who have weaker personalities and weaker views, but who all agree on the other side of the fence. This is simply a standard management question with very little technical relevance.
> If you're thinking in the first terms, is a relational DB the right backing store, or wouldn't it be better to back your DB with something more like Mongo?
Certainly relational like for 90% of the cases, if not all.
The relational model is THE answer to nosql from the start (ie: it was the solution of the originals "nosql").
Is totally more flexible, powerful, dynamic, expressive... And that without talking about ACID!
You can model all "nosql" stores with tables. With limited exceptions it will work very fine for most uses...
> This is simply a standard management question with very little technical relevance.
I don't get what your are implicating here...
But nosql solutions are the ones to be suspected and the ones to requiere a harder qualifications and justifications to use. Is the wrong choice in the hands of the naive. "NoSql" is for experts and for niche/specific workloads.
I suppose it depends on whether you want schema-on-read or schema-on-write.
Even if you're working under the first model, there's still a lot an RDBMS can do to help you ensure data integrity. Largely by being less flexible. Databases like MongoDB allow for a more fexible schema, at the cost of pushing a lot of the work of ensuring data integrity into the application code.
For my part, I do a fair bit of working with databases that were built on the MongoDB of the '90s, Lotus Notes, and I've seen what they can grow into over the course of 25 years. It's not pretty. That experience has left me thinking that, while there's certainly a lot of value in the document store model, I wouldn't jump to a document store just because I don't need everything an RDBMS does. I'd only do it if I actively needed a document store.
The most basic question I'd ask: is the core of your application the data or is it the business logic? Or to put it another way: does it make more sense to build the application around data, or to build your data around the application?
Another question I'd ask is whether you're expecting to deal with millions of rows/objects or hundreds of millions. Modelling relational data correctly can have performance impacts in orders of magnitude.
When I look at most projects, I instinctively begin by modelling the data structure; then I think about why/when/how data can move from one state to another; then I think about how an application could prod the data between these states; then I build code which runs against the data.
If your application isn't data at its core (e.g. a document-based app) then it probably makes more sense to treat data elements as objects and use a CRM (or similar) to store and retrieve the objects.
An ORM does not usually limit how much you can model your data and create fast queries. The modeling you talk about can be done just as well in e.g. the Django ORM.
ORMs that I've experimented with tend to fall into one of two categories: either they treat the object model as prime, or they treat the relational model as prime.
The former almost invariably spurt out inefficient queries, or too many queries, or both. They usually require you to let the ORM generate tables. If you just want to have your object oriented design persist in a database, that's great.
The latter almost invariably results in trying to reinvent the SQL syntax in a quasi-language-native, quasi-database-agnostic way. They almost never manage to replicate more than a quarter of the power of real SQL, and in order to do anything non-trivial (or performant at scale) they force you to become an expert SQL and/or how it translates its own syntax into SQL.
And once you become more expert at SQL than your ORM, it's not long before you find the ORM is a net loss to productivity—in particular by how it encourages you to write too much data manipulation logic in code rather than directly in the database.
For the projects I've worked on, I've almost never wanted to turn data into objects. And on the occasions when I've thought otherwise, it has always turned out to be a mistake; de-objectifying it consistently results in simpler, shorter code with fewer data bugs.
I tend to find that the longer data spends being sieved through layers and tossed around inside your application, the more data bugs you'll end up having. It's much better to throw all data at the database as quickly as possible and do all manipulation within the database (where possible) or keep the turnaround into application code as short as possible. It means treating read-only outputs/reports more like isolated mini-applications; the false nirvana of code reuse be damned.
And that doesn't mean replacing an OOP or ORM fetish into a stored procedure/trigger fetish. It means realising that if your application is data at its core, it's your responsibility as a programmer to become an expert at native SQL.
The problem is that far too few programmers realise how deeply complex SQL can be; it's treated like a little side-hustle like regular expressions, when for so many programmers it's the most valuable skill to level up.
The advantage of using an ORM is that you can always not use it in the places where you are doing things that are not suited to the strength of the ORM.
I tend to hand-write almost all of my migrations and many of my queries that synthesis data from multiple tables to reach a conclusion. I can think of only a handful of times where it was worth writing custom code to persist state (usually only when there are a large number of records that need a couple of specific fields updated.)
Like many tools, it all depends on how well it is used and how well it fits its use-case.
> The trick is figuring out which way fits your business needs.
Rule of thumb - if you don't control the database, use an ORM; if you do control the database, work directly with it.
For example, let's say that your business is a software company that sells an on-prem product. Some of your customers have Postgres expertise, some have MySQL expertise, some MSSQL, some people are stuck on Oracle. Forcing customers to develop DBA expertise in a database they're not familiar with just for the privilege of buying your product is a sales disaster in the making. So you go with an ORM and set up QA testing that tests releases across all of the databases that you support, and the ORM helps you by making it much more likely that your development efforts will automagically succeed in working with each of the supported databases.
In most other situations, though, it makes much more sense to start with the data design. If your business grows, your databases are going to grow. You are almost guaranteed not to switch databases (absent overwhelming financial need, see: Oracle) over the lifetime of your company. Data analysts (data scientists now?) can extract serious value from your databases by getting into the weeds of the database schema, indexes, and queries and working with developers and DBAs to optimize them for business reporting. If you give up control to an automated tool that knows nothing about your business, your business will be less competitive as a result.
Data is far too valuable these days to refuse to develop expertise with the underlying databases.
Why do your customers even need to have expertise in the DBA you're using? We just sell them a black box (usually VM images), with a few endpoints to extract data in standard formats. They can use whatever they want to connect to those.
Maybe one customer wishes to run their databases in a cluster distributed across two continents.
Maybe another customer has bought Oracle and the installation still has room. Also, they have a custom backup scheme that takes their load patterns into account.
Customers wish many things, that doesn't mean they're relevant selling points. It just sounds bad judgment to me to tie yourself that way, unable to take advantage of the RDBMS to the full. And have you even tested running your (hypothetical) application in a distributed Oracle cluster across two continents? If not, how will you support it?
Django-rest-swagger to have a Swagger / OpenAPI spec on top of Django REST Framework, and then OpenAPITools/openapi-generator to generate Typescript code.
394 comments
[ 3.5 ms ] story [ 288 ms ] threadSometimes you have to concatenate, e.g. to specify order by clauses. This is something database vendors need to fix. There need to be ways to tell the database to modify a prepared statement without changing it's SQL. You might be able to do it with a crazy IIF, but then it's likely not to use indexes well.
I have hit this problem in just about any system using prepared statements. The performance overhead of query planning is minimal compared to the headaches it prevents.
> Sometimes you have to concatenate, e.g. to specify order by clauses.
So, should I concatenate or not?
> This is something database vendors need to fix.
How long do you propose I wait before giving up and using a library that allows me to safely and sanely compose queries?
That you view the situation as ORM or concatenation terrifies me. No wonder SQL injection remains so prevalent.
Prepared statements are what you use here. You provide a query with placeholders for the values you want to fill in, and then you call that prepared statement with the arguments you need. These are typically type-checked and hardened against SQL injection, but of course you should verify with your particular language/library combination.
With an ORM you can construct the prepared statements programmatically.
You're still working with SQL as a language, but you're manipulating it directly instead of trying to go via string formatting.
A quick search for "query builder golang" (We are on HN after all) provides the following library:
https://github.com/fragmenta/query
An example from the README:
https://docs.sqlalchemy.org/en/13/core/tutorial.html
Absolutely, prepared statements would not be appropriate for that.
> With an ORM you can construct the prepared statements programmatically.
That's not limited to ORMs though. You can also just use a query builder. In fact I would argue that an ORM that provides that functionality is a query builder with additional ORM features on top.
You can use functions to create basic "getters" for application data structures. Layering these through further functions for limits/offsets/etc is simple (and you can write those layers in a generic way).
You just have to get used to the mental mindshift that all your data manipulation code lives in the database, and your application simply consumes data from single function calls.
I don't think others mean concatenation of values where placeholders should be, but the fact that the query itself is a big string. Unless some sort of query builder is used to literally build this big string for you.
They explicitly contrast using an ORM to the practice of concatenating strings here:
> > SQL is backward to a programming language. Creating statements by concatenating strings?
> Unless some sort of query builder is used to literally build this big string for you.
I'm not entirely sure they're aware of the existence of query builders outside of an ORM given the dichotomy presented.
What would be great to have first class support in the language (or perhaps IDE) that would understand database schema and database types so you could have error highlighting and autocompletion.
Something like JOOQ for Java seems to do that. Seems like a good idea at the surface, though I don't currently program in Java and don't see anything similar in other languages, so I'm wondering what's wrong with it
But I've worked places where dba refused to use version control.
I bought into the idea myself for a while, but when I interrogate my belief there it was just something I picked up at uni as part of the general 3-tier approach, and I'm not sure how much of it is really practically grounded. I see the same ideas held dogmatically by newer employees, and when I ask them about it in detail, I see the same fuzziness.
After all, if you want to really be pedantic, then you could claim anything beyond having a single table with two columns, "key" and "value" is pushing BL into the DB.
I.e, what practically does the separation gain you? You may want to swap out the DB in future? Almost never happens, not without some significant other refactoring going on (e.g breaking out into separate DBs because the product's grown to the point of needing distributing across services, etc). If you want to really design with that in mind, you're almost certainly giving up a lot of functionality that the particular DB is going to give you. It's on the level of holding onto the possibility that you'll want to change the language you're writing in at some stage (and that event would favour pushing more into the DB anyway).
In reality, what I've found is when there's a reliable, transactional, relational datastore at the bottom (i.e we're not talking about a larger distributed system), then for your own sanity you want to be pushing as much as possible down the stack as possible, to reduce the surface area that someone could reach through to change data in the wrong way. Data consistency issues are some of the worst when it comes to eroding customer trust, and if your data access/mutation paths are many and varied then you can do your head in or burn through a lot of dev morale trying to diagnose them.
The strongest advocates otherwise I've found are those in office cultures where there's little trust between devs and DBAs and getting things through DB review are a rigamorale that end up driving devs towards doing as much in code as possible. I've always suspected that beyond Google envy, these sort of dynamics are what drove a lot of the initial NoSQL movement...
https://www.vertabelo.com/blog/business-logic-in-the-databas...
https://sivers.org/pg
https://www.martinfowler.com/articles/dblogic.html
The consequence has been catastrophic data consistency problems and the whole programme rapidly grinding to a halt.
I think the problem is the overly broad definition of 'business logic' that encompasses everything from data integrity to presentation logic.
There’s a quote from Gavin King somewhere exhorting developers that ORMs and raw sql are meant to be used concurrently, ORM for CRUD ops,sql for most everything else.
The problem I have with ORMs is lack of paging on collections; there’s always that one user that pulls in half the database.
If you have a single-user database system (e.g. webapp with database backend), there is relatively little to gain implementing data validity checking in the database, other than the declarative statements versus imperative rules discussion (which can already be a huge benefit, depending on the team).
But once you have a central database with multiple frontends (common in enterprise ERP solutions), enforcing data consistency in the backend becomes pretty much unavoidable -- otherwise a single bug or new feature in one frontend could disable entire production lines.
when there's a reliable, transactional, relational datastore at the bottom [..] you want to be pushing as much as possible down the stack, to reduce the surface area that someone could [..] change data in the wrong way
Yes, this. Very much this.
Usually there are two things:
1. Performance. Business logic is usually horizontally scalable, databases are usually not. You want to pull as much BL out of the database as possible, and put it into stateless microservices so they can be scaled.
2. Version control and deploying changes to business logic. Business logic changes frequently. Do you want to have to be making that frequent of changes to your database? Do you have practices around safe deployment and code review for stored procedures?
I've worked at a place where there was lots of business logic (millions of LOC) stored "in the database". In this case the database was MUMPS. It can be done, and it can be pleasant. The catch is that vertical scaling is the only option, and you have to spend years building your own tooling from scratch.
I think the line between database and code is destined to become even more blurred, but not by bringing the code down into the database, but by lifting more and more database concepts up into "application space" as distributed computing becomes more normalized.
The system was an enterprise-focused offering with weak product management, so in the early growth-oriented days of the company ended up saying "yes" to a lot of feature requests that we probably shouldn't have. Where this particularly impacted was the permissions system, which evolved over time from an ACL with odd warts to also include a bolted on hierarchy of users and their objects, and then a role based system, and then all sorts of enterprise-level oddities like cross-links in the hierarchy, shared subhierarchies, roles that could appear at multiple hierarchy points, virtual ACLs that behaved slightly differently etc. So potentially a large number of different access paths between a given user and resource.
Years ago, when this mess was starting to expand it was decided it was too hard to model this in the DB (and really that should've been a giant red flag), so the new approach was to load each customer's dataset up into an application server that would handle the BL, crucially including the permission logic. It very much wasn't stateless, as you mention, but I'm not sure how it could've been really, given you needed so much loaded up from the DB in order to make these permission decisions. Would've avoided a lot of headache if it had...
The consequence though of using this write-through in-memory cache was it became the source of truth.
The chief problem this led to was that shift into application land was a bell that couldn't be unrung. Everything others in this thread have complained about seeing BL spread across all sorts of SPs in a DB happened here, just in application code (which again hints I guess that the chief problem is architectural and lack of governance, not a wrong-layer problem). Nobody could properly describe the permissions system in response to support requests without a lot of code scouring, let alone hold it in their heads.
Even worse, as the application grew in size and needs, we found we still needed things we had left behind in the DB. A couple of smart devs working on the core service, because they were smart and trusted, convinced themselves that what we needed was an in-memory transaction system for managing this in-server cache (by now the core was being called the Object Cache, a name so generic that it also should've been a red flag). So a couple of years went into implementing a technically impressive reimplementation of a transaction system.
Meanwhile the system as a whole was well past the point of being needed to split up into multiple services, so a grand goal was set of moving the Object Cache into a standalone service: the Object Service. Slap an OData API on it, and then leave that API open for all internal teams to hit. By this point the core team who owned this was starting to become well aware they had fallen into a bad pit of reimplementing the Postgres DB everything still sat on: transactions, generic query API, configurable in-memory indexes for each type of object, partition loading logic for the timeseries sets, user permissions etc. Worse, the generic query API (and this is what's ultimately turned me off OData/GraphQL etc for cross-team interfaces) ran into all the same problems as an SQL interface - people in other teams would always be coming up with brand new queries your indexes hadn't anticipated, forcing new work on the keepers of the cache to support.
The way forward probably would've been to leave the permissions structure in place and pull as much as possible out of the service/cache into separate stanadlone services, i.e leave the objects in the object cache little more than just IDs you could look up elsewhere for actual attribut...
Your either generating and submitting the queries through prepared statements or just storing them in the database. It's not "leaking" IMHO to do the later and in fact can come with benefits; you can call the procs while connected to the database through various other clients be it command line or a BI system. So in effect you are encapsulating the logic in the place with the most potential for re-use.
The problem with it is technical: the tooling for migrating and managing schemas and stored procedures is hot garbage, and there aren't good ways to enforce consistency between the invariants of the applications and the database.
Really, it should be possible, on both application deploy and when stored procedures are updated, to import library functions that the stored procedures use from the database and run unit tests against them to ensure that all clients are compatible.
I.e. ORM vs query builder.
That said, I've come around to the Repository pattern and quite like the Ecto way of doing things. It's a lot more flexible at the expense of some simplicity. It makes some really hard things easier than AR can.
- .count on a collection calls SQL every time, but there is also a .count method for arrays - which means you need to remember to use .length if you just want to count the objects loaded into memory
- ‘build’ methods for an association usually only initialize a record in memory, but with has_one associations it deletes any record that already exists
So basically when it’s not clear about the fact that it’s doing database things.
'Employee.xml'
Now imagine this but with stored procedures and inserts with tens of parameters.
At some point they rewrote it as a web app with NHibernate and all that. Took them literally many hundreds of man-years, and it still runs like molasses. And profiling whatever crap NHibernate emits is rather joyless enterprise.
For example, Django's ORM protections against SQL injections: https://docs.djangoproject.com/en/2.2/topics/security/#sql-i.... Of course, not all ORMs have this feature.
pymysql / psycopg2 is doing this piece of it
Is there a driver out there that doesn't support parameter substitution?
Does no one teach prepared statements in schools/online classes/textbooks? Does no one go digging underneath their abstractions to learn what they're providing of value and what is easily available without that abstraction?
> ORM protections against SQL injection attacks are worth the clunky syntax. Change my mind.
You can create what's called a prepared statement in most databases. This is a statement that contains placeholders for parts of the query that you want to change at runtime. You then query against this prepared statement, providing arguments for each placeholder. These arguments are typically typechecked and protected against SQL injection.
(I only say "typically" because I'm sure someone out there can provide a database/driver combo that does the wrong thing, but I'm equally sure you can find a misbehaving ORM that does the wrong thing as well.)
Also, there is a whole section called "ORMs take over connection management and migration" and not a single point as to why connection management handled by the ORM is bad, only comments on migrations. Moreover, migrations are not an unsolved problem at all.
In Ruby, ActiveRecord solves all scenarios I have encountered gracefully, and trust me, I have dealt with tons of edge case projects.
For Python, I recommend Alembic: https://pypi.org/project/alembic/
My preferred solution for existing DBs is Ruby's Sequel or ROM if you want to go nuts. But I'm a diehard Rails lover and will choose ActiveRecord over all other solutions if I can. To me there's only one decent ORM and everything else is either good for special purposes or junk.
The biggest problem for me is this "proxy" behaviour. When you start treating objects as proxies for database rows, your database logic inevitably leaks into your business logic. Someone returns an ActiveRecord `User` object from a database method and now random other parts of your application are intricately linked to your database implementation.
On top of that you suffer terrible performance problems. Even if you're smart enough to avoid the whole N+1 query issue, you lose control (and more importantly visibility) over where database updates are coming from, and your ORM is either not smart enough to efficiently apply updates, or so smart that building the query takes longer than actually running it! (I have this problem with SQLAlchemy right now, where it's taking several seconds per request CPU bound in the ORM). Undoing this is literally impossible without a near total rewrite.
And it all serves no purpose! The application-specific interface to its database is usually quite simple. You can just have it be a literal interface/trait/whatever and implement each method by executing a bit of SQL, or calling a stored procedure, or however you like. Arguments and return values should be plain-old-data (no magic proxy objects) and each method should correspond roughly to a transaction so that the rest of the application doesn't have to worry about that database-specific stuff.
There are a ton of other benefits to doing stuff this way, too many to list here, but it really helps with complex migrations (like if you need to migrate from one database to another), maintainability, testing, etc.
Sprinkling calls to order.save! or order.update(foo: bar) in random places is a recipe for disaster.
CPU time building a query is rarely the business bottleneck until you're at a huge scale - scaling up the engineering team uniformly and having business logic at abstraction level closer to the rapidly evolving Product specs is the bottleneck that ActiveRecord solves for, and does pretty well.
If you'd like something that avoids object/hidden state - check out how Elixir/Phoenix's Ecto[1] was designed - it avoids many of the shortcomings of ActiveRecord and SLQAlchemy:
1: https://hexdocs.pm/ecto/Ecto.html
Once you fetch something from the db, you have it in variables in memory. With or without an ORM. That could no longer be sync'd with the db a ms later. That you will probably re-use for more than one access (if you need it more than once), because issuing the same SQL again the second time you need to reference any data returned, in a given routine, would be insane.
But if I had to increment specifically, and were using ActiveRecord... I'd use the ActiveRecord increment! method. Which has the proper db-atomicity semantics you want, it does execute `UPDATE x SET n = n+1 WHERE pk = y ...`
Having to think about these things because the abstraction is leaky instead of just using the SQL where the transformation is explicit is not worth the hassle.
Using Clojure and yesql was a real pleasure for this - Clojure is immutable so you're working with values all over the place - and working with SQL query is just passing values in it and getting values out - no magic mapping - it's just values - if you want the latest value - query again, if you want to store a new value - send the new value. No mutation, no magic, just data.
Nope. The DBMS is making the same transaction isolation guarantees to an ORM as to any other client.
I think the larger "source of truth" issue is how the schema is represented.
All of these systems that provide their set of schema objects that are then pushed to the database. This works fine for LAMP stacks, but as soon as you grow it becomes apparent that you've put the horse before the cart.
for the write? writes are not usually much of a performance issue except when people are inserting thousands of rows. You can replace flush with direct INSERT/UPDATE or use the bulk API. Pre-setting primary key values will also improve performance by an order of magnitude. Share some of your code and profile results on the mailing list and we can look into speeding it up.
SQLAlchemy always provides many techniques to optimize areas that have performance problems, not to mention the primary direction for most major releases is that of new performance features and improvements year after year, despite having to deal with the dog-slow cPython interpreter. No "total rewrite" of your application should be needed.
This is not just for writes, but also for loading complex relationships from the database: we've gone to great lengths to ensure we're using relationships optimally so as to cache query results and not fall into the N+1 query problem, but our application spends all its time inside SQLAlchemy.
The only way to get the performance improvements we needed was to stop using it as an ORM altogether. However, the ORM was too intricately tied with our business logic to do that without a near total rewrite.
What we've actually ended up doing to do this rewrite more incrementally is to create a Rust service which implements just the "GET" end-points: this gives us an order of magnitude performance improvement despite it using the exact same database and fetching the exact same data. Most of the business logic did not need to be duplicated as it is not used from "GET" requests, but users of our application will get much snappier loading times, and it will generally feel much better.
it most certainly does provide many techniques to reduce in-Python computation. Have you read the performance section in the FAQ and worked through the examples given ? Have you looked into baked queries , querying for columns and not objects ? These are often quick wins that make a huge difference. The baked queries concept is going to be much more cleanly integrated in an upcoming release.
> This is not just for writes, but also for loading complex relationships from the database: we've gone to great lengths to ensure we're using relationships optimally so as to cache query results and not fall into the N+1 query problem, but our application spends all its time inside SQLAlchemy.
that's typical because a CRUD application is all about its object model and SQLAlchemy is doing all of that work. if you wrote your own object layer using Core then you'd see all the time spent in your own objects<->core layer. This is just the reality of Python.
Here's a runsnakerun I make some years ago of the PyMYSQL driver loading data over a network-connected database: https://techspot.zzzeek.org/files/2015/pymysql_runsnake_netw... Notice how just the Python driver spends TWO THIRDS of the time an the actual waiting for the database 1/3rd ? that's Python. If the database is on localhost, that pink box shrinks to nothing and all of the time is spent in Python, just reading strings and building tuples. It's a very slow language.
Rust language however is blazingly fast and is one of the fastest languages you can use short of straight C code. This is not the fault of an ORM, this is just the reality of Python. You certainly wouldn't think that if you used an ORM that runs under Rust, it would be as slow as your Python application again. It would continue to be extremely fast. The Hibernate ORM is an enormous beast but runs immensely faster than SQLAlchemy because it's running on the JVM. These are platform comparisons. SQLAlchemy is really fast for the level of automation it provides under pure Python. You made the right choice rewriting in a fast compiled language for your performance critical features but that has little to do with whether or not you use an ORM. As long as your database code was in your Python application, you'd end up writing an ORM of your own in any case, it would just be vastly more code to maintain.
Most of those techniques boil down to "use SQLAlchemy as a query builder rather than an ORM" - something I wholeheartedly agree with and would love to do, but it is next to impossible because these ORM objects have leaked into the business logic of the application.
> that's typical because a CRUD application is all about its object model and SQLAlchemy is doing all of that work. if you wrote your own object layer using Core then you'd see all the time spent in your own objects<->core layer. This is just the reality of Python.
I'm not trying to say SQLAlchemy is a bad implementation - it's a great implementation with tons of powerful features. I just believe that using anything as an ORM is a bad idea and directly leads to these performance and maintainability issues. Whether that's ActiveRecord, SQLAlchemy or even if someone implemented a similar ORM in Rust.
Some of the performance issues are due to python, but that's not the whole story: if I use SQLAlchemy as a query builder, and only return plain-old-data, I do not see nearly the same performance issues. Not because SQLAlchemy ORM is badly written, just because it has to do more work, create more objects, with more "magic properties", maintain more entries in the session, etc. etc.
Indeed. Its a rookie mistake but hey, you work with rookies all the time! Orms can be useful but they certainly allow for some dangerous patterns that are hard to notice if you're a young developer.
SQLAlchemy author here. I can't imagine how someone would not consider SQLAlchemy to treat "SQL" as the "source of truth" as well. All of SQLAlchemy's constructs map directly to SQL syntactically. Our most grumpy users are the ones who don't know SQL. This blog post would certainly benefit from some actual examples. "ORM-light tools that coerce responses into native structs and allow for type-checking are less offensive to me." - that is...an ORM?
There is certainly a lot of information in a schema, and you definitely want your ORM's idea of the database and the database's idea of it to be in close alignment, but I feel like any serious attempt would find all sorts of holes like the above when it actually came to doing it.
Is multiple applications talking to a shared DB schema a common practice, especially nowadays?
In my mind, each app/service should have a DB schema which only it talks to, and other apps/services needing data in that DB go through services exposed by that app/service (REST, RPC, GraphQL, whatever) rather than talking to its DB directly. That means you can rearchitect the DB schema, change which DB you use completely, etc., and only the app/service which owns that DB needs to be modified.
>"ORM-light tools that coerce responses into native structs and allow for type-checking are less offensive to me." - that is...an ORM?
Yeah...its a light ORM that focuses on turning a result set into an object but not the syntax remapping of queries. Object mapping is almost universally liked but ORMs usually include query syntax mappings and not the addition of a transaction lifecycle into your data objects.
Then you call your alternative anti-orm and it generates... orm specs, and then you say "migration commands based on the git history"... what does that even mean? What does git history has to do with the database?
Hopefully a lot. Non-versioned database schemas are the devil.
I tried to think about it from a gitflow release cycle perspective to be generous. This would mean constant churn of migration code for each branch of the repo.
If you have complex migrations which go beyond simply renaming columns, it would require you to write special migration code on a release branch which would then need to be merged back. yuck!
Hopefully I have cleared that up.
Facebook, who operate one of the largest relational database fleets in the world, have used declarative schema management company-wide for nearly a decade. If engineered well, you end up with a system that allows developers to change schemas easily without needing any DBA intervention.
I viewed the author as targeting smaller dev shops who don't have a dedicated DBA/expert. Groups who might use an ORM's migration framework for example. In which case, it would appear that there is significant loss in migration flexibility if you remove application code from the architecture.
For example, say you need to populate a new column based on data in other columns, across an entire table. If the table is a billion rows, you can't safely run this as a single DML query. It will take too long to run and be disruptive to applications, given the locking impact; it will cause excessive journaling due to the huge transaction; there can be MVCC old-row-version-history pileups as well.
Typically companies build custom systems that handle this by splitting up DML into many smaller queries. It's then no longer atomic, which means such a system needs knowledge about application-level consistency assumptions.
Having auto-runnable migrations checked in alongside the code that depends on them allows you to review and test those migrations as part of your normal code review and CI processes.
I think there must be some misunderstanding here because I don't understand why you say either of these things:
> This would mean constant churn of migration code for each branch of the repo.
> it would require you to write special migration code on a release branch
There should only a be at most a few migrations per feature branch and merging is only an issue when you have two feature branches with inconsistent schema definitions. You should only have to add custom migrations to a release branch to fix bugs and those should be merged back into your develop branch as soon as possible to minimize effort spend merging later.
Those aren't the only alternatives. Query builders exist.
And there is an answer to that. The idealistic answer is good DB design from the start. The realistic answer is by using views (sometimes materialized views) to create a clean representation out of odd tables.
The monster query can be decomposed into simple and clean operations against these views (and if necessary, views upon views).
The only caveat is you need a SQL expert to understand the performance if you want to do this at scale.
The problem with this statement is that DB schemas like code can evolve badly over time, even if they're maintained by a DBA sometimes (since they're human afterall too).
And then the DBA starts to suggest that you use things like triggers in your DB, which may or may not be good, but often surprise developers. "I just wrote 0 into the DB, why did it turn into NULL? What line of code did that? Ohhhh... there's a trigger."
SQLAlchemy exists exactly because of the pain the "just use views" approach causes. The entire point is that you can structure a query of arbitrary complexity in a succinct manner using Python structures, while not losing any of the relational capabilities. Of course you can write queries that are too complex, if you're then lucky enough to be able to have materialized views at your disposal, you can turn the SQL you wrote into one. But that's an optimization you can use if you need it, or not.
I feel like if a DBA keeps thinking of the database as the solution to all of the problems (by implementing views, triggers, stored procedures, etc) then maybe that person should be willing to support them, otherwise they really shouldn't complain that the developers moved to an ORM.
Do I seriously make the correct assumption that the people who are writing the backend of many apps are actually separated from the people who decide how the data those apps process the data, and you may have no direct say about the database schema? that effectively just a client - perhaps the only client, but nothing more than a client.
Obviously you've described the disadvantage of this approach; what advantage does it have?
That would leave us at an impasse (he said / she said), unless we get more nuanced. So let's.
It sounds like some of your frustration is at some DBAs you knew. Firstly I've never worked with a DBA, I'm just a senior software engineer / DevOps and to me that includes an intimate knowledge of SQL perfomance, so maybe this is why I don't mind an in-database approach.
I'm fairly sure that whatever SQL Alchemy could do with a monstrously complex data-relationship views can do with better performance (the further the abstraction gets from the engine the less performance optimization it can do).
Now maybe you agree with that, and see ORMs as a tool for the kind of job where the dev is locked out of the database. If so then I can't really disagree as I haven't worked at such places much at all.
SQL Alchemy will spit out SQL query. Whatever the DB engine can do with queries hand-written on top of views, it can also do it with the query generated by SQL Alchemy.
No, on two levels. Firstly, there are many features in SQL that an ORM will not support (e.g. no substitute for a materialized view).
Secondly, once you get really good at SQL, you learn that very tiny seeming things can make the difference between a query running for an hour or a second (e.g. case-insensitive search against an indexed column). Part of being expert in DB technologies is knowing how/why some ways of writing a query are very fast, and others are very slow. The idea of an ORM is to hide that complexity, which is fine for a toy todo-mvc app. But once you start querying tables with 100k rows you need to understand that complexity and master it if you want to write a fast query.
But in any case, we are not discussing some abstract ORM whose "idea is to hide complexity", but SQL Alchemy whose idea (one of them, at least) is to allow you to write composable queries yet still retain full power of SQL.
You are incorrect in your other assertion. Sqlalchemy allows for optimising your queries at runtime in a way that just isn’t available to sql views. The beauty of sqlalchemy is that allows for arbitrary composition of blocks of sql. I too have had to fight with dbas in a previous life because they didn’t want me to construct a query in code (non sqla), but I eventually won because I was magnitudes faster because I had runtime knowledge they couldn’t use.
SQLAlchemy supports case-insensitive searches, per-column / per-expression collation settings, index and other SQL hint formats for all databases that support them, e.g. SQLAlchemy's query language supports most optimizing SQL syntaxes, with the possible exception of very esoteric / outdated Oracle things like which table you list first in the FROM clause (use index hints instead). We are adding esoteric performance features all the time to support not just SQL-level tricks but driver level tricks too which are usually much more consequential, such as the typing information applied to bound parameters matching up for indexes as well as special driver-level APIs to improve the speed of bulk operations.
SQLAlchemy has been around for thirteen years, plenty of SQL experts have come along and requested these features and we implement them all. Feel free to come up with examples of SQL-expert level performance optimizations that SQLAlchemy doesn't support and we'll look into them.
What I'm trying to challenge is the philosophy that an ORM is an adequate facade in place of learning how databases work. My point about views is that in my experience, the answer to disgusting queries is cleaning up the DB design (and views can be the tool to accomplish this). My point about case-insensitive searching (you can create a case-insensitive index if you want) is that a lot of db-performance stuff just can't be solved on the query side alone anyways.
It sounds like SqlAlchemy is designed with a lot of flexibility around how queries are run, so maybe you agree that understanding what's going on beneath the hood is important to handle these complicated cases.
Thank you for this response and I agree, we are likely on the same page. I don't know that anyone actually espouses that philosophy. This is the anxiety that ORM skeptics have, and certainly you get beginners who rush into using ORMs not knowing what they are doing, but if someone wants to be a DB expert, I'm pretty sure they go to read about databases :) These ORM beginners will fail either with the ORM or without out. I guess the idea is you'd prefer they "fail fast", e.g. the ORM covers for them while they proceed to screw up more deeply? This is arguable; if you've seen much of the non-ORM raw DB code I've seen, it too fails pretty hard. But even with this argument, if ORMs produce the problem of incompetents who are not found out fast enough, why hoist the denial of useful tools to those developers who do know what they're doing.
if you have the option to use materialized views, that can be helpful when appropriate, however materialized views have their own issues, not the least of which is that they need to be refreshed in order to have current data, they can take up a vast amount of storage, not to mention a lot of databases don't support them at all. It should not be necessary to emit CREATE MATERIALIZED VIEW with super-user privileges and to build the infrastructure to store them and keep them up-to-date for every query one needs to write or modify that happens to have some degree of composed complexity.
you don't need an ORM for these queries either, just a composable query builder (like SQLAlchemy Core) that allows the composition task to be more easily organizable in application code.
none of this means you can't use views but it's unnecessary to dictate that they should be the only tool available for dealing with large composed queries.
Having a separate "DBA" from the developers or having an adverserial relationship with said person is not really a technical problem I think.
Just like Data in a codebase ;-)
I don't feel like this answers the question. My ORM supports views.
It actually seems like a really cool idea. E.g.
* Commit A: create a schema.sql file "create table t (a INT);"
* Commit B: update the schema.sql to "create table t (a INT, b INT);"
* The tool can generate a migration from A..B "alter table t add column b INT;"
It seems like it probably would work really well for creating new tables and columns in simple projects. In more complex projects I've worked on, we usually had more complicated migrations to update the data to fit the new schema, and we often had to be careful to interleave schema, data and code updates (especially if you wanted to be able to rollback).
Is there some other circumstance when you want that?
Confirmed that this works exactly as expected in SQLite:
Are there any bad performance implications here? Like, will it still rewrite columns that don't need to be updated?
The answer to that is almost certainly "it depends on your DB". Most query planners should be smart enough to not try to write anything, but some are smarter than others. For example, SQL Server (if I'm understanding right) does perform a log write even if it should be obvious that the value won't change: https://www.sql.kiwi/2010/08/the-impact-of-non-updating-upda...
That being said, I'd skeptical of the performance impact (if any) being all that significant; even in a worst-case scenario of "yeah, it's going to stupidly write every field every time", the columns for each row should be pretty close together both in-memory and on-disk, so unless you're writing a whole bunch of giant strings or something at once (and even then) there shouldn't be a whole lot of seeking happening.
tl;dr: assuming there's a minor performance impact is reasonable, but it should probably be measured before trying to optimize it away.
Also, getting the library to properly expose the formatting interface is also a trick. We would do massive loads/dumps that we did not want to use Python for (but we worked in Python) as the DB-API pulls the entire result set into RAM, and we were pulling >RAM (and we didn't want to do something lower-level w/ the driver). We'd spawn psql, but that needs the query, as a complete, pre-templated string — it does not support any sort of positional parameters. And the Python library really doesn't (last I checked) have a public interface to the templater portion of the library. (I think this is b/c PG just sends the parameters, and the binding is done server-side. So, really, psql needs an interface to supply those values as arguments to the CLI.)
Thin SQL wrappers/dsl's like the SQLAlchemy expression library are great. IMO those thin wrappers are what most people should reach for first, over a full blown ORM. A good mimimal sql wrapper will:
* Save people from pain, problems, and security issues involved in building queries through string manipulation.
* Map very closely to raw SQL
* Make it easy to compose query expressions and queries, using language native features.
* Help devs develop their SQL skills. Learning the wrapper IS learning SQL, for the most part.
I have a hard time seeing many good reasons to add any more layers of abstraction on top. That last point is particularly important to me. My first exposure to many advanced SQL techniques, was through such libraries. Since the code maps to sql quite naturally, that learning can be applied in a much wider variety of contexts. Teach someone how to do aggregations using Django's ORM, and you've taught them to do aggregations in Django's ORM, and basically nowhere else.
I haven't tried this before, so caveat emptor, but per psql's manpage this should be supported:
Basically: you can use colon-prefixed variables in the query text (with the variable name optionally quoted to indicate how the result should be quoted, apparently?), and set them with the -v option (which is equivalent to using the \set command within the query string itself).This also happens to work for table names, at least per the example in the manpage:
Except in this case it'd be more Still no option there for positional parameters, but it's a start, right?I'm not sure what git-specific interaction the author is envisioning, but more generally: declarative schema management tools are based on the notion of using a repo to store only CREATE statements. It's an infrastructure-as-code style approach, where to modify an existing table, you just modify the CREATE definition. The tool figures out how to translate this to an ALTER.
I'm the author of an existing tool in this space, Skeema [1], which supports MySQL and MariaDB. Other recent tools in this space include migra [2] and sqldef [3]. From what I understand, in the SQL Server world there are a number of others.
[1] https://www.skeema.io
[2] https://github.com/djrobstep/migra
[3] https://github.com/k0kubun/sqldef/
Bookmarking it so I can reference it.
http://blogs.tedneward.com/post/the-vietnam-of-computer-scie...
I just use ORM for OLTP and raw SQL for OLAP (Better for being more expressive).
Migrations saves a lot of time.
ORMs are generally not good at or even capable of efficient set-based operations spanning entitles.
Building a dashboard with an ORM results in pain and wildly oversized DB instances in my experience.
In any sufficiently complex codebase you'll be doing more than this, however, and the work to support the ORM properly then outweighs the work of simply coding the raw SQL and preparing statements as appropriate.
Raw SQL also tends to be much more performant.
And for those special (and IMO pretty rare) occasions, you can drop down to arel or raw SQL anyway. Why throw away the consistency and readability of something like AR for edge cases, when you can just treat your edge case as an edge case with raw sql and still keep AR for your other 95% of queries.
That's not my experience.
I maintain a relatively unremarkable but bespoke online discussion forum, which has hundreds of queries, few of which could be composed by an ORM, let alone composed and run performantly. The median complexity query in my code base probably has two or three joins, two or three subqueries, and some kind of aggregation or window function.
The result is a typical page runs around two or three queries total—one query to authenticate the user and load everything about their profile and permissions, one to load the entirety of the data being output on that page, and occasionally one to update a statistic somewhere.
(The authentication query runs on every page because there's absolutely no persistence in the application layer. The authentication query goes three layers deep in subqueries and includes half a dozen joins. It hits perfect indexes when it runs and takes only a few msec round trip.)
> People hugely exaggerate this IMO.
In my experience, people who think an ORM can do most things are simply under-experienced with SQL and set theory.
Well, just from that description, Django ORM could do it. Can you post an example of a median query? I'm curious to see why it can't be ORM'ed.
What language are you using for that?
CFML has a "query" datatype that represents the rows and columns returned from a database combined with useful metadata and really neat features like n-level iteration where values repeat. To the programmer it's like a dictionary that magically changes its values by passing it to an iterator. Without the iterator it works like a dictionary of the first row. Or if you treat it like an array you can manually read any column in any row directly.
There's plenty of ways to manage complexity in software. Two or three different databases in a single app doesn't kill them, nor will two different DB interfaces.
What I did find interesting was the pushback I got from my team when I told them that I wanted to drop the Java ORM (JPA). One guy, who I respect, was super critical of the decision. He was telling me that SQL was the reason we had a performance problem, but when I investigated I was astounded by how fast SQL was, even over JDBC. It was clearly the ORM that was slowing us down.
In his defence, when I was able to show him literally 100x performance improvement after moving a service to PL/PGSQL and MyBatis (Java), he changed his mind.
But, as always, "use the right tool for the job." I have gotten tremendous value from ORM's over the years, but I'm not going to insist on using one for everything just "because".
My favourite neglected feature is JPQL with constructor expressions, which lets you write queries in an SQL-like language, but in terms of objects rather than tables, which is slightly easier, and materialise results directly into objects of your choice.
However, there are things that JPQL can’t do, but it’s easy enough to create a native SQL query and have its results map to JPA entities. I use Spring Boot (JPA, Spring Data, Spring Data REST) for the sheer convenience of it and speed of development, not to abstract away the database, which is always PostgreSQL. I am very comfortable writing SQL queries and making use of PostgreSQL-specific features and Spring Boot with JPA certainly lets me do that when I want to.
Bonus points for open source examples.
The SQL variant used by SQL Server and Sybase, called Transact SQL, also has elements of procedural programming such as variables, flow control with IF/THEN/ELSE/switches/etc, looping, calling other SQL procedures or user defined functions, exception handling and so on.
These features have been part of SQL Server since the 90s. It's extremely powerful and if used well, can be much more elegant and flexible than an ORM in my opinion. It's a shame that PostgreSQL doesn't support stored procedures quite as well or have an SQL variant that's as well crafted as Transact SQL because I'd love to be able to write apps in that style again. Somewhat recently I believe PG got something like stored procedures that can return multiple heterogeneous result sets and although I can't remember the specifics, I don't think it's quite as robust as what you can do with TSQL and it lacked client/driver side support maybe... (Also PG has some support for running TSQL itself, but not with all the same features.)
Here's an example of how you might call a stored procedure in C#, using a library written by Marc Gravell of StackOverflow - https://stackoverflow.com/questions/5962117/is-there-a-way-t...
Maybe don’t use a half baked ORM.
I’m experiencing schadenfreude that someone is having ORM problems in 2019 long after the NoSQL hype died.
I can remember on zero hands the number of times my not half baked ORM was the source of my problems such that I wanted to say “fuck it lets just write SQL”. ORM patterns are pretty well laid. Hell they haven’t changed much in at least a decade or more and they work great for the problem they solve. Which makes me think maybe a relational store is not right for whatever you’re using it for.
tl;dr If you have ORM problems I feel bad for you son, I got 99 problems but SQL ain’t one.
I think it's a mistake to think you can't write good efficient SQL for well-normalized schemas with AR. You can. Usually anyway, for a great many use cases.
[Arel is _really nice_, but sadly Rails absorbed it and considers it "private API", especially in it's most sophisticated features. I use em anyway, they generally don't break... but if I wasn't using ActiveRecord as a whole, I'd probably use Sequel instead.]
When it was first added to Rails, improving the query building possibilities, it was a separate gem that Rails depended on. I'm not sure if it was written specifically for Rails originally just maintained in a separate gem, or intended by it's original writers to be an independent project. But it was later absorbed into the Rails repo and we were later told by Rails maintainers that it's more sophisticated manual API (look up how to make a subquery with Arel for instance) were not intended as public API and had no backwards compat commitment.
No, they do not. If yours does, throw it away.
There are frameworks like JOOQ or MyBatis that work this way.
Also now that we need auditing there’s a pile of work we could have have gotten mostly free with an Envers annotation.
In fact, an important part of our transition away from ORM was to invert the ORM-centric relationship between the application and the database. We wanted our tech people to be able to manipulate the database natively via SQL, which is generally much easier and more efficient than writing and deploying Java code to do the same thing.
For us, this meant moving most data manipulation logic into the database using PL/PGSQL. Doing so has empowered a team of non Java admins, massively improved performance, and significantly reduced LOC. I’d say that we now have about 30% (1/3rd) of the LOC in PL/PGSQL than we had in Java+JPA. Obviously this means less bugs, but performance is literally 100x in some cases.
I think that if you take a Java-first view of your application then all the complexity of JPA is part and parcel of any solution, and that’s fine for you. But we’ve found that taking an SQL-first approach has been great for our use cases, and has had lots of ongoing benefits.
Of course, we needed to build a bunch of tools to help with this. For example, we built a gradle plugin that treats SQL code like Java code so we can write libraries in SQL with transitive dependencies. We also needed to build testing tools and tools to automate schema migrations for CD.
I do think the lack of tooling around managing large SQL-based projects is a blocker to wider adoption of our approach, but the benefits of going against the ORM grain have been very significant for us.
I will also concede that under some circumstances it's too slow to bring the data to the code, and instead you have to bring the code to the data (i.e. PL/PGSQL). The sort of speedup is mostly from eliminating round trips and data marshaling, however. That's not quite a like-for-like comparison of an ORM vs a mapper.
The reason I stick to ORMs is mostly because of RAD tools that save me so much time. For instance, JHipster generates liquibase migrations and JPA entities. This gives me a relational schema very quickly. To avoid any "surprise" queries that tank performance, I do turn Hibernate's statement logging on when developing new features.
Next time if I can find some more tools to help with an SQL-only + stored procedure approach, I'll give it a deeper consideration. Maybe convince your company to open source some of tools it has developed!
I definitely agree with you that tooling is a big deal. We did spend a lot of time manually proving it out before we spent the time on the tools. It was a bit of a leap of faith but it quickly became obvious that we were onto something.
And you’re right, the point of our approach was to move the code to the data. We deal with reasonably large, complex real time data sets Sotheby’s round trips and marshalling become the dominating contributor to total time. Hence our ability to improve some operations by 100x.
I can see that if you have a familiar and reliable tool chain and a compatible use case then ORMs could be great. I think our problem was that we had neither, so it was never going to work out too well for us!
Of course it's backwards.
It's like one of the biggest legacies of web development next to javascript, html and CSS. SQL is a very specific way to think about data and a very high level and leaky abstraction. So high and leaky, that you literally need to look at the query plan in order to optimize sql queries. If Database IO is the bottleneck for web development the best abstraction for this area is most likely a zero cost abstraction (rust for example). Instead we have SQL and we create a generation of SQL hackers who memorize a bunch of technology specific hacks to try to get things to work. Why is SELECT * bad? It's a hack you memorized, a leaky abstraction.
I'm not saying schema-less is better hear me out. I'm saying SQL is a bad API. You can probably make some api language that's more explicit and imperative on top of something like postgres. Change the API itself don't write something on top of it.
Either way, an ORM on top of SQL is like what type script is to javascript. You know when they make an API on top of an API it's to cover up something that doesn't work well. Unless it's a zero cost compilation such fixes only make performance worse.
If you insist on using some abstraction on top of sql the way to do it with zero cost is an api that is bijective to the syntax of SQL itself. Type script I'm guessing is relatively isomorphic to javascript, so it works out.
Something like VSAM or IMS?
The relational model and declarative query languages were invented because the previously existing imperative navigational model was far too inflexible and difficult to use. Access paths were baked into the data structures, you had to be your own optimizer, and if you optimized for the wrong usage patterns, you had to refactor everything or live with poor performance.
For example:
Obviously the above example is trivial, wrong and probably won't work. But I hope it paints a fuzzy picture of my point. That is that the optimization or 'plan' should be explicit. You choose auto or you dive in and you change it yourself. No language or syntax hacks.Optimizer hints is a good idea. As far as I know this isn't a well known feature (if it even is a feature) for postgresql, which is the DB I have the most experience with.
Raw SQL is great at what it does.
ORMs are typically good at what they do.
Depending on the use case both have their pain points. Typically I see a mixture of ORM for most CRUD based stuff and raw SQL for cases where the ORM isn't suitable.
No need to throw the baby out with the bathwater.
It means you can literally have the API do exactly what SQL does. Automatically specify the query plan. But you explicitly do it.
Meaning that sometimes the auto planner screws up. When it screws up, in those cases you explicitly make it use another algorithm rather then modify SQL to be slightly different and hope that it compiles into something more reasonable.
The baby is rosemary's baby.
A bunch of performance problems aren't even solved by tweaking the query either. They're solved by changing the database structure. Adding the appropriate indexes and such. Making sure datatypes match between joined data and no implicit conversions are happening. Right-sizing columns.
No amount of specifying your own query plan will do anything to affect those kinds of issues.
In practice I find mostly it comes down to cardinality estimate issues as a very common source of problems as the database either over or under provisions enough memory for the query. If it estimates it's going to get back a lot more data than it actually does and it grants too much memory that will reduce parallelism because that memory can't be used by other queries. If it under-estimates it doesn't grant enough memory and when it gets back more rows that will fit into memory it has to write them temporarily to disk taking a massive hit in I/O performance.
How does your scheme work with figuring out how much memory the database should grant to a query when specifying a plan by hand?
What's more is plans change over time as statistics change. Leaving it up to the query optimizer means it's adaptive. Having to specify yourself means you have to know the optimizer isn't giving you the best plan at design time. There are some cases where you know that. There are some cases where you don't.
You can already specify query hints etc. I think SQL just has this covered already. I have no qualms if the query changes slightly.
https://github.com/Microsoft/TypeScript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
SQLAlchemy for Python is that for me. I find it incredibly expressive and allows me to write complex queries that are readable, maintainable, and perfomant. Yes, it requires learning, but it’s the right tool for the job, personally.
In addition, ORMs greatly increase the surface area for troubleshooting. Instead of just being able to run and profile a query, you now need someone who is experienced in the internals of the ORM and how it "plans" a certain query. Of course, because ORMs do not understand how the data is actually laid out, when they say "plan" they mean create the SQL string.
So in place of a query you will need to learn the conventions of the particular ORM, leaks and all, and how that maps to the SQL. Then you will still need to understand how that SQL actually interacts with the table schema.
The end result is normally one backend person who is very territorial over their code, and who acts as a bottleneck for the rest of the team. If you're using an ORM I'm sure you already know who this guy is...
Is this really a good example? Sqlalchemy allows you to use raw queries when you want to. Just like almost every orm out there.
Here's a datapoint I experienced:
The company I worked for didn't trust the Django ORM for DB migrations. They thought they could do it by hand. After lots of deployment failures where the migration script worked in the dev environment but failed in the prod environment, we switched to using django migrations. Haven't had an issue since.
The author even admits that migrations are a hard problem, but yet, he has no solutions other than just using raw SQL.
Edited to Add:
In Django, these migrations are handled pretty well with a single command to make the migrations. In SQL, someone would probably have to spend some time testing, and then would need to be reviewed in the PR.
For some, the database is not actually a database, per se, it's just a place to persist state. Usually folks in this camp think in an object-oriented way. They haven't read Codd's paper, they don't care about the relational algebra, they just want some way to take bags of properties and stick them somewhere that offers random access with good performance and is reasonably unlikely to not spontaneously erase data. If you're in this camp, ORM works great, because there isn't really much of an object/relational impedance situation in the first place. The data store wasn't structured in a way that creates one, there's no need burn a bunch of effort on doing so, and dragging in the whole relational way of doing things is like dragging a wooden rowboat on a hike through the forest because you'd like to use it to go fishing on the off chance you find a nice pond.
Others see a lot of value in the relational model. They're willing to put a bunch of time and effort into structuring the data and organizing the indexes in ways that will allow you to answer complex, possibly unanticipated questions quickly and efficiently. They, too, hate the syntax for recursive common table expressions, but are willing to hold their nose and use them anyway, for various reasons, but mostly because people think you're really cool when you can spend 30 minutes and make something go 2-3 orders of mangnitude faster than it used to. They don't think of the data in terms of bags of properties, they think of it in terms of tuples and relations and a calculus for rearranging them in interesting and useful ways. For them, there is potentially a huge problem with object/relational impedance; the data's organized in ways that just don't fit cleanly into Beans.
The thing is, neither of these ways of using a database is wrong. Each has it strengths and weaknesses. The trick is figuring out which way fits your business needs. Well, that's the easy trick. The hard trick comes when someone on your team doesn't understand this, believes there is one universal solution that will work for everyone, and is hellbent on jamming the One Righteous and Holy Peg, which happens to be square, into a round hole.
If you're thinking in the first terms, is a relational DB the right backing store, or wouldn't it be better to back your DB with something more like Mongo?
Do they still obtain some benefit from the schema, since from time to time the semantics of a property will change, and a schema can tell them what the current shape of the data is and guide the migration. Or would they prefer to just write a new property which does a lazy conversion from the old terms? (To an extent, I suppose the answer to this question determines the answer to the first. But I guess there's other tradeoffs to Monggo I'm not aware of, since schemalessness fills me with fear and I just don't want to look there.)
Regarding your final paragraph: I suspect that, for many apps, the choice between relational vs transparent persistency is largely determined by the team who is working on it. The "hard trick" is therefore trying to balance a strong personality with a strong view who disagrees with the rest of the team who have weaker personalities and weaker views, but who all agree on the other side of the fence. This is simply a standard management question with very little technical relevance.
Certainly relational like for 90% of the cases, if not all.
The relational model is THE answer to nosql from the start (ie: it was the solution of the originals "nosql").
Is totally more flexible, powerful, dynamic, expressive... And that without talking about ACID!
You can model all "nosql" stores with tables. With limited exceptions it will work very fine for most uses...
> This is simply a standard management question with very little technical relevance.
I don't get what your are implicating here...
But nosql solutions are the ones to be suspected and the ones to requiere a harder qualifications and justifications to use. Is the wrong choice in the hands of the naive. "NoSql" is for experts and for niche/specific workloads.
Even if you're working under the first model, there's still a lot an RDBMS can do to help you ensure data integrity. Largely by being less flexible. Databases like MongoDB allow for a more fexible schema, at the cost of pushing a lot of the work of ensuring data integrity into the application code.
For my part, I do a fair bit of working with databases that were built on the MongoDB of the '90s, Lotus Notes, and I've seen what they can grow into over the course of 25 years. It's not pretty. That experience has left me thinking that, while there's certainly a lot of value in the document store model, I wouldn't jump to a document store just because I don't need everything an RDBMS does. I'd only do it if I actively needed a document store.
Another question I'd ask is whether you're expecting to deal with millions of rows/objects or hundreds of millions. Modelling relational data correctly can have performance impacts in orders of magnitude.
When I look at most projects, I instinctively begin by modelling the data structure; then I think about why/when/how data can move from one state to another; then I think about how an application could prod the data between these states; then I build code which runs against the data.
If your application isn't data at its core (e.g. a document-based app) then it probably makes more sense to treat data elements as objects and use a CRM (or similar) to store and retrieve the objects.
The former almost invariably spurt out inefficient queries, or too many queries, or both. They usually require you to let the ORM generate tables. If you just want to have your object oriented design persist in a database, that's great.
The latter almost invariably results in trying to reinvent the SQL syntax in a quasi-language-native, quasi-database-agnostic way. They almost never manage to replicate more than a quarter of the power of real SQL, and in order to do anything non-trivial (or performant at scale) they force you to become an expert SQL and/or how it translates its own syntax into SQL.
And once you become more expert at SQL than your ORM, it's not long before you find the ORM is a net loss to productivity—in particular by how it encourages you to write too much data manipulation logic in code rather than directly in the database.
For the projects I've worked on, I've almost never wanted to turn data into objects. And on the occasions when I've thought otherwise, it has always turned out to be a mistake; de-objectifying it consistently results in simpler, shorter code with fewer data bugs.
I tend to find that the longer data spends being sieved through layers and tossed around inside your application, the more data bugs you'll end up having. It's much better to throw all data at the database as quickly as possible and do all manipulation within the database (where possible) or keep the turnaround into application code as short as possible. It means treating read-only outputs/reports more like isolated mini-applications; the false nirvana of code reuse be damned.
And that doesn't mean replacing an OOP or ORM fetish into a stored procedure/trigger fetish. It means realising that if your application is data at its core, it's your responsibility as a programmer to become an expert at native SQL.
The problem is that far too few programmers realise how deeply complex SQL can be; it's treated like a little side-hustle like regular expressions, when for so many programmers it's the most valuable skill to level up.
I tend to hand-write almost all of my migrations and many of my queries that synthesis data from multiple tables to reach a conclusion. I can think of only a handful of times where it was worth writing custom code to persist state (usually only when there are a large number of records that need a couple of specific fields updated.)
Like many tools, it all depends on how well it is used and how well it fits its use-case.
> The trick is figuring out which way fits your business needs.
Rule of thumb - if you don't control the database, use an ORM; if you do control the database, work directly with it.
For example, let's say that your business is a software company that sells an on-prem product. Some of your customers have Postgres expertise, some have MySQL expertise, some MSSQL, some people are stuck on Oracle. Forcing customers to develop DBA expertise in a database they're not familiar with just for the privilege of buying your product is a sales disaster in the making. So you go with an ORM and set up QA testing that tests releases across all of the databases that you support, and the ORM helps you by making it much more likely that your development efforts will automagically succeed in working with each of the supported databases.
In most other situations, though, it makes much more sense to start with the data design. If your business grows, your databases are going to grow. You are almost guaranteed not to switch databases (absent overwhelming financial need, see: Oracle) over the lifetime of your company. Data analysts (data scientists now?) can extract serious value from your databases by getting into the weeds of the database schema, indexes, and queries and working with developers and DBAs to optimize them for business reporting. If you give up control to an automated tool that knows nothing about your business, your business will be less competitive as a result.
Data is far too valuable these days to refuse to develop expertise with the underlying databases.
Maybe another customer has bought Oracle and the installation still has room. Also, they have a custom backup scheme that takes their load patterns into account.
Absolutely not worth it, in my experience.
But this is the stack I often work with now:
- Postgres
- Django ORM
- Django REST Framework
- Auto-generated OpenAPI Typescript functions
- A Redux store
- My React app
In other words, four ORM-like non-relational frameworks stacked on top of each other between Postgres and my app.
A generic solution for "complex, possibly unanticipated questions" would need to work through all of those layers.