In any sufficiently complex application, something like a query builder that allows different parts of the application to work together to lazily compose queries (vs combining/filtering/sorting the data in memory) will be created at some point.
ORM is a tool that can make this easier, it's also a tool that can make it easier to shoot yourself in the foot (ie by making it easy to create N+1 queries without knowing). Like all tools, there are tradeoffs that need to be accounted for together with the actual use case to make a decision. Operating on SQL statement strings is not something I'd recommend in any case.
> In any sufficiently complex application, something like a query builder that allows different parts of the application to work together to lazily compose queries (vs combining/filtering/sorting the data in memory) will be created at some point.
Welcome to LINQ - introduced in 2007 (?).
LINQ isn’t exactly an ORM. You create strongly typed LINQ statements that are turned into expression trees that are then turned into a query by a query provider.
Iirc, Linq is exactly a query builder and not an orm. IMO what truly makes an orm is when object fields have data binding against the database (updating a field triggers an update in the db)
It's kinda gross because the developer needs to make a decision about what is authoritative source of truth, the programming model makes you feel like you can trust your code (probably the wrong choice), and all the footguns around distributed state kick in (possibly even worse if you have a frontend with two way data binding and data structures that last longer than an http request in your backend)
LINQ is a query builder and the .Net runtime creates expression trees. Entity Framework translates the expression tree to SQL and maps data back to objects.
Linq is really just a high level abstraction for querying any data that can be queried. EF adds some more abstractions mostly related to mapping C# objects to database tables. Then it adds database specific query providers (implementations) that combine the (abstract) linq query and the mapping to produce sql.
You can really use linq to query anything (APIs, for example), including databases without using EF. It's just not very common because building a custom query provider is a lot of work.
In my experience, across a wide variety of applications built wide a number of different teams, LINQ has almost always been an anti-pattern. Whenever you inject a leaky abstraction (as Spolsky would say), things start to go awry. The crimes of grotesque inefficiency I've seen because the magical LINQ is there to shield devs from proper data tiers.
LINQ in general is a very bad indicator if it isn't very restricted. I don't think it's a crazy claim whatsoever: When LINQ appears littered through code, it usually mean there has been perilously little data access planning, so instead of planning out and centralizing concise, planned, secure, optimized data access avenues, just pretend that LINQ being available everywhere is a solution. Every LINQ-heavy solution I've had the misfortune of using has always, with zero exceptions, been a performance nightmare. And instead of one big function you can just optimize, it is death by a trillion cuts.
I get that there are big LINQ advocates. It's convenient. I'm sure there are projects where it is use spectacularly. I've never seen such a project.
First time I’ve heard that claim was when I had to start working with Go. I was telling someone how much I miss LINQ/functional programming, and he said exactly the same thing “ugh, LINQ is why .NET is so slow”. I was perplexed. Do you even know what LINQ is? How does it make .NET slow? And to the best of knowledge they were comparing JIT startup time to Go’s AOT time. Smh
This comment make me question if you really have any experience with LINQ. If you had, you would not make a comment where you seem to think that LINQ is used only for data access.
LINQ is (at the most basic level) list comprehensions done better. It is functional programming for the imperative C# programmers. It has the potential to remove most/all loops and make the code more readable in the process.
>This comment make me question if you really have any experience with LINQ
The classic fallback.
>If you had, you would not make a comment where you seem to think that LINQ is used only for data access.
List comprehension is data access. Accessing sets in memory is data access.
>If you had, you would not make a comment where you seem to think that LINQ is used only for data access.
It has the potential, and almost the certainty, of allowing one to thoroughly shoot themselves in the foot. See without the "magic" of LINQ the grotesqueness of many patterns of data access (which, as previously mentioned, includes in memory structures. Pretty bizarre that anyone actually in this field thinks this only applies to databases, or that only DBs are "data") would lead one to rethink.
LINQ is almost always a bad indicator. It is actually a fantastic thing in one off scripts and hack type code, but when it appears in production code, I would say 90%+ of the time it is absolutely gross, but it hides how gross it actually is.
It is just a slightly slower than e.g fors, so unless this is hot path, then it is basically not relevant meanwhile it improves readability of the code
> LINQ is almost always a bad indicator. It is actually a fantastic thing in one off scripts and hack type code, but when it appears in production code, I would say 90%+ of the time it is absolutely gross, but it hides how gross it actually is.
Yeah, you definitely have no idea what LINQ, or an list, even is.
Absolutely, there are times where programmers try to do way too much in LINQ and don't realize that the query being generated to back up their chain of LINQ operations is a monstrosity of thousands of lines of SQL that will grind the server to a hault. Or the abstraction leaks badly due to the programmer using something EF doesn't know how to translate to SQL, so it ends up loading a much larger dataset and trying to complete the rest of the filtering in memory.
As developers get more acclimated to the .NET ecosystem they usually pick up a good spidey sense for what query "shapes" are appropriate to express in LINQ and which are asking for trouble.
However, for every one of those awful LINQ queries in a codebase, I think there are 40 to 50 run-of-the-mill queries that make it worthwhile. The biggest win that it delivers over a simpler, "stringier" ORM is the ability to return structured data. I have no problem writing SQL, but processing the results back out of flat rows into objects gets extremely annoying.
Say I want to load some Foos with their Bars. If I use straight SQL with a row mapper, I define a type to represent each FooBar row, load a List<FooBarRow> into memory from my simple left join, then I'll have to do a GroupBy in memory on that List to get the actual structure that I want: a list of Foos where each one has its own list of Bars. Notice that I also have to handle the empty-list case specially.
var fooBarRows = await connection.QueryAsync<FooBarRow>("select f.FooId, f.Name as FooName, b.BarId, b.Name as BarName from Foos f left join Bars b on b.FooId = f.FooId where whatever");
// re-shape result set manually from flat query results
var foos = fooBarRows
.GroupBy(fbr => fbr.FooId)
.Select(g => new Foo
{
FooId = g.Key,
FooName = g.First().Name,
Bars = g.First().BarId == null ? new List<Bar>() : g.Select(b => new Bar { BarId = b.BarId.Value, BarName = b.BarName }).ToList()
});
In EF it's just:
var foos = await db.Foos.Where(whatever).Include(f => f.Bars).ToListAsync();
Saving that clutter -- both the code and the otherwise useless intermediate FooBarRow type -- really adds up because the apps I write have a zillion queries very much like this.
So I'll take the occasional shitty query that has to be tracked down and optimized in exchange.
It's sad that in programming world, something is either anti-pattern and you must not use it at all, or something is so awesome that you need to use it everywhere.
>I have no problem writing SQL, but processing the results back out of flat rows into objects gets extremely annoying.
I'm pretty sure Dapper lets you do this fairly easily. You've got a couple options - you can either provide a mapping function, or you can have your query/sprocs return multiple result sets. Then you can assemble the result sets into the object structure.
It's not quite as magical as LINQ, but it's also not quite as annoying and fraught with so much marshalling code as your example.
When they say it forces client evaluation, what they mean is that any LINQ operations you run after the ToListAsync() will run on the client side - since it's just a normal list in memory at that point and no longer has any knowledge of the database. So if you want to make sure some code will execute client-side, you should ToList or AsEnumerable your query first, then do your additional client-side operations on that list.
The IQueryable operations composed before to the ToListAsync() will run as SQL on the server. In the case of my example it will look pretty similar in structure to the string SQL I wrote before it: a straightforward left join with a where clause. Performance will be similar too. It will not load the entire universe of Foos and Bars and then filter them down on the client.
One of the habits I have developed from working with EF for the past 10 years is to be explicit and as local as possible about forcing the query to evaluate. It lets me pretty reliably predict what the SQL is going to look like.
You can return IQueryables from methods and pass them around through many layers of your program, adding complexity to them as you go, and lazily getting the results at the last possible moment, five layers up the call stack from where the query first started.
It seems at first like that would be good, because that way the maximum amount of logic will run on the DB server, and letting the server do stuff is better, right? But that's also where you open yourself up to being very surprised about what your SQL looks like by the time it executes, with multiple layers of your program each tacking complexity onto the query. It can get ugly. Also you can have problems if you're hanging onto IQueryables that you still haven't executed after you've Dispose()d your DbContext. For this reason I try not to let IQueryables travel very far through my program before forcing evaluation.
Being able to run queries over data without having to implement the various boilerplate is excellent.
Unfortunately basic mistakes can result in so many of those N+1 type queries if you're not careful. It can also result in reading entire table(s), possibly inside those nested N+1s.
It gets expensive when it's a remote DB server and/or dealing with a lot of records.
Profiling tools are essential.
It's why I'm not a fan of LINQ (and Entity Framework) for talking to DB severs without very strict controls.
Many times I've been called in to deal with a "slow" server, only to find out the issue is someone chained together a bunch of calls through EF and we have hundreds of thousands of queries that can be replaced by one or two.
Would you consider SQL an abstraction over DynamoDB? ElasticSearch’s native query language? Apache Presto to query files? Mongo?
All of those can use SQL as a worse query language than their native counterparts.
You can also create very bad SQL if you don’t know the underlying engine. For instance if you try to write SQL for a columnar database like you would for a traditional database, you are in for a world of hurt.
You seem to be talking about databases who's native language is not SQL. In those cases, yes, SQL is definitely an abstraction. There's some layer that's transforming that into (say) Elastic query or Dynamo queries.
I would argue that when talking about writing SQL, most folks are talking about applications who's query planner talks some dialect of SQL. MS SQL Server, MariaDB, MySQL, Oracle, SQLite.
In those cases, it's not really an abstraction any more than writing assembly is an abstraction over (say) Intel's CPU microcode is.
The query planner takes your SQL and turns it into something else, sure, but you can't generally do that yourself. It's the lowest layer of abstraction that's reasonably available.
> Unfortunately basic mistakes can result in so many of those N+1 type queries if you're not careful. It can also result in reading entire table(s), possibly inside those nested N+1s.
I don't know which "basic mistake" would do this. Maybe when using Lazy loading Proxies?
We use EF Core and it is quite easy to control eager loading or load-on-demand on a per-case basis.
LINQ is just an interface, you need some sort of an implementation below it (be it the unmaintained LINQ-to-SQL that AFAIK nobody should be using anymore, or the latest EF rewrite).
It's also the first thing I recommend to anyone claiming that concatenating SQL strings together is a good use of your time in the 21st century. Makes writing things like complex HTML tables with configurable columns and tons of optional filters so much faster and more maintainable.
> In any sufficiently complex application, something like a query builder that allows different parts of the application to work together to lazily compose queries
I don't see this as a given and I don't accept it in my own applications. If you need data, go to the data access layer. If it doesn't provide you what you need, build a new repository / provider / whatever your pattern is.
Whether it's an ORM or a home-built query compositor or whatever, one thing I know from experience is that once your application is "sufficiently complex" that you start to (incorrectly) believe you need this, your application has become too complex to use it reliably.
You absolutely will be mistakenly evaluating these builders in the wrong layers, iterating results without realising you're generating N+1 queries, etc.
Why are a data access layer and an ORM mutually exclusive? An ORM is just an abstraction over your database. You might find use in it as a tool to access your database but contain the use of the ORM to within your data access layer.
Mind sharing an example of a large(ish) app that doesn't make use of an ORM? Last time this topic came up, I went looking for one (admittedly not too hard) and I came up empty handed.
Following this advice too closely and your application logic starts leaking into your data layer.
One simple example is when you need to make atomic changes to two different types of entities. In the data layer, this is usually trivial — just run two queries within a transaction — but you need to expose a function that boils down to `updateBothAandB`. Rinse and repeat enough times and your data layer is a soup of business logic.
There are patterns to resolve this. Depending how you structure your DAL, your data access classes can provide transaction handles. It's a data access layer, you don't need to abstract away the fact that you're dealing with a database.
An ORM is a specific kind of data layer. It is general-purpose, and as the name suggests, it is an Object-Relational Mapper whose primary purpose is to map relations to objects and vice-versa.
So no, bespoke data access layer code is not an ORM.
I mean, yeah, but the patterns all look like the thing you said you don’t accept in your own applications: “something like a query builder that allows different parts of the application to work together to lazily compose queries”.
Exactly, in my opinion, separating your business logic from your data access layer completely is futile. You will need to either have business logic in your data access layer or fine grained query controls (when to add a filter clause, when to execute a query, what should go into a transaction, etc) in your business logic for performance reasons. This is OK, it's incidental complexity.
> I don't see this as a given and I don't accept it in my own applications. If you need data, go to the data access layer. If it doesn't provide you what you need, build a new repository / provider / whatever your pattern is.
Whichever layer you put it in, you need a way to compose two query fragments. Otherwise you have to write N*M queries manually instead of N+M query fragments.
Either you use an ORM to help you with this or you don't, with all the usual tradeoffs about using a library or not. But you still have to solve the problem. (Or you do a lot of tedious copy-paste work - with all the usual tradeoffs of that)
Say you create a nice little utility function that makes a call to the db func1 and you write func2 that also makes a db query. If you have a func3
that needs func1 and func2 conceptually your options are:
* Accept that this will just be two trips to the DB.
* Write a new function func12 that writes a query to return all the needed data in one query and use that instead.
* Have your tool be able to automatically compose the queries.
If you do with the second option you have to do that with every combination of functions that end up being used together which is multiplicative in general.
sure, sometimes, rarely -- these are exceptions, not rules
in general, it should not be possible for user input to produce arbitrarily complex queries against your database
each input element in an HTML form should map to a well-defined parameter of a SQL query builder, like, you shouldn't be dynamically composing sub-queries based on the value of a text field, the value should add a where or join or whatever other clause to the single well-defined query
sometimes this isn't possible but these should be super rare exceptions
I prefer using something like Rails or Django to build 10 fully working CRUD interfaces with well-defined yet dynamic filters in a day instead of spending two weeks needlessly writing the equivalent code by hand.
You’ve never actually implemented a real world implementation, have you?
You’re going to have parameters that are compound. You’re going to end up filtering on objects 3 relations removed, or deal with nasty syncing of normalization. You’ll have endpoints with generic relations, like file uploads, where the parent isnt a foreign key.
It’s going to be a mess. They will NOT always be simple to write.
> it's pretty rare for queries to be dynamically composed from arbitrary sub-queries
I'm talking static, not dynamic. You still need to compose two pieces together into a single query, and you can either use an ORM to help with that or not.
> the interface between the application and the DB is actually a string! it's not an abstract data type, it doesn't benefit from being modeled by types
No it isn't. You can't send an arbitrary string to the database and expect it to work. At the very least you benefit from having an interface that's structured enough to tell you whether your parentheses are balanced and your quotes are matched rather than having to figure that out at runtime.
> when your app queries the db, the query is not composed from several pieces, it is well-defined in the relevant method
> this is a single query, not multiple
And when you want to query for multiple related things together, the whole point of having a relational database? For different purposes you need different views on your data, and those views are generally constructed out of a bunch of shared fragments; you can either figure out a way to share them, or copy-paste them everywhere you use them.
> the db accepts a string and parses it to an AST, it does not accept a typed value
> this means the interface is the string
The DB accepts a structured query, not a string. It might be represented as a string on the wire, but if that was what mattered then we'd use byte arrays for all our variables since everything's a byte array at runtime.
> unbalanced parens and whatever other invalid syntax is obviously caught by tests
i'm not sure what you're thinking about when you say "multiple related things"
every "view" on your DB should be modeled as a separate function
every possible "thing" that's input to a function which queries the database should be transformed into a part of the query string by that function
> The DB accepts a structured query, not a string. It might be represented as a string on the wire, but if that was what mattered then we'd use byte arrays for all our variables since everything's a byte array at runtime.
...no
the API literally receives a string and passes it directly to the DB's query parser
if the DB accepted structured queries, then the API would rely on something like protobuf to parse raw bytes to native types -- it doesn't
like `echo SELECT * FROM whatever; | psql` does not parse the `SELECT * FROM whatever;` string to a structured type, it sends the string directly to the database
> every "view" on your DB should be modeled as a separate function
OK, and when a significant amount of what those functions do is shared, how do you share it? (E.g. imagine we're building, IDK, some kind of CMS, and in one view we have authors (based on some criteria) and posts by those authors, and in another we have tags and posts under those tags, and in another we have date ranges and posts in that date range. How do you share the "fetching posts" bit of SQL between those three different (parameterized) queries?)
> if the DB accepted structured queries, then the API would rely on something like protobuf to parse raw bytes to native types -- it doesn't
> like `echo SELECT * FROM whatever; | psql` does not parse the `SELECT * FROM whatever;` string to a structured type, it sends the string directly to the database
In both cases the parsing happens on the server, not the client. "echo abcde | psql" and "curl -D abcde http://my-protobuf-service/" are both doing the same kind of thing - passing an unstructured string to a server which will fail to parse it and give some kind of error - and both equally useless.
> How do you share the "fetching posts" bit of SQL between those three different (parameterized) queries?)
your application has a fetch posts method, that method takes input including (optional) author(s), tag(s), etc., it builds a query that includes WHERE clauses for every provided parameter
the code that converts an author to a WHERE clause can be a function, the point is it outputs a string, or something that is input to a builder and results in a string
i'm not sure what a "fetching posts bit of SQL" is, a query selects specific rows, qualified by where clauses that filter the result set, joins that modify it, etc.
> the code that converts an author to a WHERE clause can be a function, the point is it outputs a string, or something that is input to a builder and results in a string
So you do string->string processing, and you explicitly won't treat the queries you're generating as structured values? Enjoy your rampant SQL injection vulnerabilities.
creating a query string that's parameterized on input usually means you model those input parameters as `?` or `$1` or whatever, and provide them explicitly as part of the query
Ok so now your WHERE clauses are no longer strings, you have to have some structured representation of them that knows which parameters go with which clauses, and something that understands the structure of your queries enough to line up the parameters when you stitch together different WHERE clauses to make your full query - exactly the kind of thing you were saying was unnecessary.
CTEs and views are both a bit bigger than the parts that you would normally want to share and reuse, and they're also not very well standardised. Plus no-one really agrees on how dynamic tables should be, so you end up with the Excel maintainability problem of no real separation between code and data.
> In any sufficiently complex application, something like a query builder that allows different parts of the application to work together to lazily compose queries (vs combining/filtering/sorting the data in memory) will be created at some point.
Right, but is an ORM a good way to achieve this? I would posit no. The problem, correct me if I’m wrong, is one of an in memory representation of working data and a data access layer (how to get/update the data). An ORM provides a framework for both of these, but in my opinion it’s very inefficient to build, maintain, and optimise. You don’t need to turn to raw SQL as the alternative data access layer, but having one that is easier to customise is better in my opinion.
Not at all an expert, probably nonsense, please correct me.
Good points! As someone who uses both ORM and raw SQL (I actually really like SQL as language) I sympathize with both sides of this debate. I prefer the readability of ORM in my models when developing, but closely monitor and optimize for N+1 and other inefficiencies, when needed. "When needed" is never cut-and-dry, but I try not to optimize too early. I actually tend to start new projects from the database and will scaffold with factories, seeders, and raw SQL queries to get a sense of the data model prior to coding.
ORM's such as Eloquent in Laravel also have some nice methods to resolve N+1 and perform lazy loading, but it's always tradeoff.
There is no other way — people who don’t know one should not touch the other. Otherwise they are either juniors, or crazies who just like to complain that “the plane is a bad vehicle because I can’t just sit inside and land it properly without years of training”.
There are libraries that flip the concept of an ORM on its head. Instead of a library that allows lazy query composition, you write your queries and the library generates the code for that query at compile time. It’s a much better model in my opinion.
E.g. you could write a query like this:
getUser:
SELECT * FROM users WHERE id = ?
And the library would generate a class like:
class GetUserQuery {
static getUser(id: String): GetUserQueryResult
}
IME, the accidental inefficiencies aren't a huge problem. What I hate about some ORMs is that they want to be considered the foundation of a system or, worse, multiple systems, rather than just a tool for implementing pesky infrastructure details. LINQ-to-SQL and Entity Framework feel like they want to be at the center of everything, though they don't have to be use that way. Something like Dapper, on the other hand, seems like it just wants to get your app the data it requires, through classes the application defines, and then get out of your way.
In short, I don't think ORMs should generate class libraries to be referenced by applications, they should help you get exactly what you need, when you need it, nothing more, nothing less, and no one expect the person that maintains that code should ever need to care how the data got there. If a column gets added to a table that has nothing to do with a specific application, that application shouldn't need to be updated.
I've seen them help with things like dirty checking, that's a lot of work to take on properly. But in the end I think relying on them for significant load and scale is naive.
We had a query builder in our app and ended up stripping it out in favour of raw SQL and string interpolation (for dyanmic queries, not for passing in data). We found the raw sql was much more readable.
We still used the library for inserts and updates.
> In any sufficiently complex application, something like a query builder that allows different parts of the application to work together to lazily compose queries (vs combining/filtering/sorting the data in memory) will be created at some point.
Then your devs have way too much time on their hands. Find them some actual problems to solve.
Different people have wildly different experiences with ORMs as they used them for wildly different levels of integration and tasks, and in wildly different languages with different features that make ORMs more or less useful, yet there's always someone willing to go out of their way and ignore all that and make absolutest statements about how it's good or bad.
We should just learn to recognize it for what it is, someone that's being controversial for attention, and move on. Let's save our attention for the ORM article and discussion that starts out along the lines of "ORMs can provide benefit, but it's important to recognize where, and not let the problems of their use outweigh their benefits. Here's what I've found."
> Different people have wildly different experiences with ORMs as they used them for wildly different levels of integration and tasks, and in wildly different languages with different features that make ORMs more or less useful, yet there's always someone willing to go out of their way and ignore all that and make absolutest statements about how it's good or bad.
I don't have a horse in this race, I could care less if you do or don't use an ORM. But, and maybe this is the cynic in me, there are practices in software development that absolutely, 100%, for certain, have no good reason and are perpetuated in part by this belief that there must have been a good reason for it to exist (Chesterton's fence and all that).
Null terminated C strings are a prime example. There is absolutely no good reason, other than the fact that the authors may have wanted to save 3 bytes, that C strings should be null terminated. Fortran was created in 1954, and passed the length of the string with the string itself. How many countless bugs and CVEs have risen due to errors in handling null terminated C strings (one example[0])? And for what? To save 3 bytes or just because of the authors decision at a whim's notice?
Likewise, decisions made at Javascript's inception have burdened it for its entire life. Decisions that were made at a whim's notice, like implicitly converting numbers to strings sometimes, and sometimes implicitly converting strings to numbers! (Tell me what `10 + "10"` is and what `"10" + 10` is without using the inspector). And the million and one ways to define something that's undefined.
Anyways, when somebody tells me there's absolutely no good reason for a development practice to exist, sometimes, that is the absolute truth. And I would rather have more people throwing away these crummy practices that lead to unnecessary headaches (or at least questioning them) then people continuing to laud the practice and perpetuate it ad infinitum.
> Decisions that were made at a whim's notice, like implicitly converting numbers to strings sometimes, and sometimes implicitly converting strings to numbers!
I see this as a problem that's horribly inflated by those who don't use JS on a daily basis.
Practitioners of the language largely don't care, because in actual code you rarely see cases where it would matter.
Now that we have template strings it's even less relevant.
I use TypeScript on a daily basis for my job. It was an entire language created to make up for JS's shortcomings. There's no horribly inflated reasoning going on when most of the industry has decided the best idea is to just throw away the language and use a different one that transpiles to it.
> Null terminated C strings are a prime example. There is absolutely no good reason, other than the fact that the authors may have wanted to save 3 bytes
Or, you know, they wanted to make the language track assembly as closely as possibly (which was possibly back at that time when processors were much simpler and instructions weren't constantly reordered), and if you've ever written assembly, you know you're not really working at a string level, you're working at a byte and character level. Sized strings are more complex than null terminated ones in that you either need to set a max size or you need to waste multiple bytes per string (which actually mattered on many systems C was used on when it was developed) or you have to use masks on those early bytes to or determine if the next byte is part of the string or a continuation of the size.
And honestly, when you're working on systems where ram (and maybe storage) is in kilobytes or less and speed is in kilohertz, the extra code to do that and the extra time to process them and the extra space to store sized strings is a lot less of an obvious choice to make.
Was C a good choice for the time and where it was used? Possibly. Is it a good choice these days? Probably not without a bunch of extra utils and compiler guards to beat back the worst problems. I do t blame C as much for that as I do the people that continue to use it without extra safeguards.
What was that you said about Chesterton's fence? That's one of those terms you should be careful about throwing around when supporting an absolutist position...
I could almost buy your argument, except for the fact that Fortran, which was created in 1954 when systems like the IBM 650 had a maximum of 35KB of memory[0] (which I'm assuming included program memory), and it still included the size of the string with the string as a convention.
But that's just me guessing. There's no reason for us to do that when Dennis Ritchie wrote down the reason for this:
>> This change was made partially to avoid the limitation on the length of a string caused by holding the count in an 8- or 9-bit slot, and partly because maintaining the count seemed, in our experience, less convenient than using a terminator.[1]
So this was a change made primarily for convenience. And if the limitations of 255 characters was really a huge blocker, they could have easily created a spec like UTF8 to allow variable length encoding depending on the size of the string, which funnily enough Ken Thompson who also worked with Ritchie, later did invent. You mentioned that the processing time would have been an issue, but C strings require you to process the entire length of the string to determine the length, and Ritchie notes that as an additional tradeoff for this convention.
But that wasn't done. And I can't blame Ritchie for that either, because he didn't think this language would become what it is today! Later on in the paper he alludes to this:
>> C has become successful to an extent far surpassing any early expectations[1]
All throughout the paper you can see him referring to decisions that were made out of convenience, and not because he had done extensive analysis to determine whether the tradeoff for the convenience was worth it:
>> Two ideas are most characteristic of C among languages of its class: the relationship between arrays and pointers, and the way in which declaration syntax mimics expression syntax...In both cases, historical accidents or mistakes have exacerbated their difficulty.
>> C treats strings as arrays of characters conventionally terminated by a marker...and as a result the language is simpler to describe and to translate than one incorporating the string as a unique data type.
All that to say, yes of course there were reasons that decisions were made the way they were. But, and this is what I've noticed more and more in programming communities, these decisions are often made with little to no analysis and usually made out of a subjective preference, or to make the implementors life a tad easier. So, yea, I think it's right to call out a lot of "best practices" because history has shown that programmers really don't put too much thought into their decisions. And then you end up with gurus proclaiming that a decision made out of convenience was actually the best decision available and we should never change the way we do things because clearly this is the right way.
> I could almost buy your argument, except for the fact that Fortran
Fortran was not created for the same purpose. Fortran existing as an invalidation of C's choices is like Java existing being an invalidation of C++'s choices. There's a reason Fortran and Java are not common choices to write in OS kernel in, while C and C++ are/were. There's a reason why C and C++ aren't often used for web development, but Interpreted languages are. Different design choices fir different niches better or worse.
> So this was a change made primarily for convenience.
Convenience can mean a lot of things, and in this context and in the absence of contrary evidence I interpret that statement to be entirely inline with what I said above. It was inconvenient to have a more complex type to deal with, for multiple reasons. I'm not sure why you would think it different, it's not like I said it could not work the other way, just that there were things that went into the reasoning that made it less obvious than in today's world.
> You mentioned that the processing time would have been an issue, but C strings require you to process the entire length of the string to determine the length, and Ritchie notes that as an additional tradeoff for this convention.
Yes, tradeoff. If what you're doing with strings most the time is parsing them, knowing the length ahead of time may be of little benefit, since you're going to step through them character by character anyway. For many operations, knowing the length ahead of time is irrelevant.
> decisions that were made out of convenience, and not because he had done extensive analysis
I'm not sure anyone is arguing they used extensive analysis. I'm certainly not. But when the reality you live and work in is that you are running up against real hardware constraints routinely, that's bound to affect your ad-hoc reasoning about what choice to make when you don't do extensive analysis.
> All that to say, yes of course there were reasons that decisions were made the way they were.
Given that this started because you wanted to support absolutist statements with "there is absolutely no good reason, other than the fact that the authors may have wanted to save 3 bytes, that C strings should be null terminated." and your prime example has now been walked back to the fact that yes, there were some considerations beyond that, including making it a simple language to describe, I think you've proved my original point.
Who is to say that C's simple description and ease of implementation for additional architectures isn't a major factor in it's success and spread? And yes, while we've been paying the price for that for quite a while now, it may also have allowed for a level of software portability that really helped advance computers beyond where they would currently be otherwise.
I don't like C all that much and I don't use it for anything, but I'm also willing to note that it must have done quite a few things right to get to where it did, and I'm not willing to call out any aspect of it as completely without merit while still acknowledging the immense benefit the language brought as a whole, because at that point we're getting into conjecture about alternate histories.
Isn't that the whole point of this discussion? We're talking about conventions programmers treat as absolutes that are detrimental to the maintenance and security of the programs.
So I don't see how this has much bearing on the ultimate point I'm making, which is that yes, conventions can be bad haha.
That has not been my experience. Then again, I’ve written tens of thousands of sql queries over the decades, so sql is as easy as breathing for me. No need for any sort of query builder.
Query builders are generally not intended for developers to build "static" queries, but to build dynamic queries programmatically while being safe (eg. from SQL injection) based on dynamic data, often with user inputs, the likes from ASTs from parsed user input in query fields.
In a sufficiently complex application, the database lives in another service that exposes a fixed set of queries through an API and certainly does not let you compose arbitrary queries.
How do you do anything with that? Like do you just accept that if some part of your app needs the data from query1 and query2 you make two trips to the database?
You file a ticket with the database team so they can add your fixed query to the service API. After a few rounds of exchanging messages and meetings it might be added.
If it's a third-party API (say, a weather forecast or market data) the answers will range from "no" to "yes, and it will cost you X".
the line between an ORM and a query builder is a very blurry one
your query builder isn't just string based to avoid a lot of potential bugs which are easy to introduce and miss in tests? It also has a simple way to (de)serialize row from/to POD structs? Now you already have a thin ORM.
The N+1 problem feels a bit like y2k in my experience; would definitely be a problem if smart folks didn’t come up with clever solutions, but since they did it’s not nearly the issue it’s made out to be for the average implementer.
I would challenge your assertion that "any sufficiently complex application" will require lazily composed queries. I have worked on quite complex applications that had no such need. Well encapsulated string construction in a Data Access Object pattern worked just fine.
If you find yourself needing to lazily compose queries, then by all means, reach for a query builder. But I would encourage you to first examine whether you've split code into services that really should live together and whether that code should live closer to the data layer before you build a whole query builder.
And don't reach for a query builder until you need it.
Query Builders are a subject I am thinking about at the moment. The question is whether or not OO is the right model.
JooQ in Java is a DSL for writing SQL in Java, it works amazingly well. It’s not guaranteed every JooQ statement compiles to valid SQL but I have pretty good experiences writing crazy complex queries using things like string_agg in pgsql. Here the relational model is primary and the representation as Java objects is secondary, given the SQL is persistent and the Java objects ephemeral this makes sense.
My RSS reader uses Arangodb and Python and I’d like to stop a bit and develop a query builder inspired by OWL class axioms which if you look at them the right way look like building blocks for a Scratch-like SQL query builder. I want both ordinary queries (browse my favorites, say an article with the keyword “niantic” that appeared on Tildes) and rules (don’t want read articles from the Guardian that have “- live” in the title)
SQL is an industry standard well understood by legions of senior developers and DBAs alike. SQL skills learned are repeatable, widely useful and build on past experiences.
Debugging and optimizing SQL queries is a well understood art, barring some rare DB specific optimizations (which ORMs don't even touch)
Getting expert SQL advice and support when you need is very practical and the results show.
Putting an ORM as the middle man generating your queries, negates all those benefits for some pithy premature laziness.
Operating on raw SQL is something I'd strongly recommend.
A problem that I haven’t seen a good solution to is that most (all?) ORMs are also caches. And once you step outside the object box, cache invalidation is incredibly hard. Taking an example from this post:
await postRepository
.createQueryBuilder()
.update(Post)
.set({ status: 'archived' })
.where("authorId IN (SELECT id FROM author WHERE company = :company)", { company: 'Hooli' })
.execute();
What happens to all the pre-existing Post objects when you do this?
(Maybe some ORMs try to get this right. It’s a very hard problem. Certainly the ORMs I’ve used don’t, but I admit I haven’t used very many.)
Also, most ORMs make it hard or ignore the possibility of using database transactions, checkpoints, partial or full rollbacks, etc. Which might be fine for trivial queries, low write load and unimportant data. But for anything moderately important or complex, transactions are essential.
They do? Every ORM I've used supports explicit transactions and rollbacks. EF, NHibernate, GORM, SQLAlchemy. What major ORMs deny you this functionality?
It isn't that there is no such thing as "a way to open/close transactions". It is that transactions usually break all the supposed ORM benefits like composability. Often, you cannot even method-chain transactions like foo.search().begin().insert().insert().commit(), you have to do it in multiple lines. Even worse, rollback handling is often an exception, breaking control flow of your application.
And most only support basic transactions, not checkpoints.
ActiveRecord approach. Objects aren't kept in cache calling update like that won't update other object in memory. Each time you call select you get a different object.
Hibernate approach. Object are kept in cache and running update will update existing objects in memory because each select returns the same object.
The only one I gave experience with is SqlAlchemy (1.x). You certainly can do a query builder update and get current subsequent data out of ORM objects, but it’s really easy to mess up, at least in my experience.
Coincidentally I had to solve this problem at work a few weeks ago. I don't know how (or if) other ORMs deal with this, but for TypeORM it was relatively painless to implement optimistic concurrency[0] along with transactions at function-scope level which all but eliminates this issue.
I’m a big fan of the “middle ground” micro-ORMs like Dapper or Diesel.
Write your queries in SQL, but you get to have your strong typing and automatic result set to objects mappings. Productivity combined with the full-featured “real” query language.
Also, I’ve become a fan of using the object mappers only for read-only queries. Updates occur via the command pattern — stored procedures. These can be mapped through to look like native methods.
Absolutely. Dapper is easily the most pleasurable data access solution I have worked with. Developers need to stop pretending SQL doesn't exist and just embrace it.
Personally I like not having to either write my own model functions which just map back to sql, or having to write a boilerplate php to do parameterized queries correctly. Eloquent abstracts enough of that crap out of my way i can focus on the logic.
Sincerely someone rewriting an 'old' php app that is just arrays and queries to something using models and eloquent, what a game changer
Eloquent is one of the worst ORM I worked with. It works until it doesn't, there is too much magic and it mixes SQL/Entity in the same class. Since then I use doctrine and/or repository pattern which makes the code more extensible and testable, it's also trivial to do your own raw SQL request and map it to your entity if you don't want to rely on an ORM.
Some ORMs are awful to work with, but that doesn't mean the genre is a bad idea. I've loved working with SQLAlchemy over the years because it doesn't try to re-invent the whole idea of SQL. Instead, it gives you a convenient layer for building queries, passing them around, etc., while it concentrates on getting the details right so you don't have to futz around with them.
My only real ORM experience is with SQLAlchemy (and a tiny bit of trying to learn ecto) and based on this thread I have to ask are all the other ORMs just terrible?
I've been learning some rust lately and writing out boilerplate code to handle simple CRUD is painstaking and it feels like a waste of my time.
SQLAlechemy's ORM can get in the way a bit for more complex queries (or ones you are trying to optimize) but you can always just drop down and write the SQL for those specifically.
From what I’ve seen yes. My first ORM was flask-sqlalchemy. Migrating a database was literally just updating an object and running a command. No other ORM has been this seamless.
ORMs are all problematic, but some are less problematic than others. Some years ago I started writing more Elixir with Phoenix and Ecto, and I quickly realized just how much better Ecto is when compared to ActiveRecord. I wrote about it back then [1] but the gist of is it: SQL > Query Builders with some ORM elements > ORM qua ORM, and the less you blur the boundary between your application and the database the better.
You say this in your post (and allude to it here), so not trying to be pedantic, but explicitly surfacing it here for other folks that don't click through: Ecto isn't an ORM.
I love Ecto. In my opinion, Phoenix has a lot of strengths, but Ecto is the superpower in that ecosystem.
That being said, at least _some_ of the brand of "problems" mentioned in the post can exist into the Ecto world. I've seen plenty of folks new to Ecto fall into N+1 queries using `preload` naively, so I'd argue it doesn't completely erase that blurred boundary. I also personally much prefer the level that Ecto sits at and think the tradeoffs are totally worth it.
ActiveRecord has always felt great to me: yes there are some corner cases to deal w/ but it hits the 80/20 spot, let's you kick out to SQL when you need to, adds a lot of excellent life-cycle related hooks that clean up domain logic, and is pretty easy to reason about
pragmatic ORMs that don't try to hide everything behind a "bundle" or introduce their own query language are great, it's the ones that try to totally "solve" the ORM problem that are hard to work with
Yes I can’t imagine going back to doing big CRUD apps without AR, readability and maintenance would take a hit. The catch is you need a prior good knowledge of SQL to use it correctly. Problems happen when people don’t learn SQL and then don’t understand the queries AR is generating and the limits.
when you write code to build a query in an ORM in an interpreted language like Ruby or Python, does it typically go out of its way to actually build the query once? or does the query get "re-built" (or cached?) every time you call the function that contains your query-building code?
I've been wondering about this and other things while working on my own mini-ORM thing for SQLite—I have it so queries are built once, at compile-time, type-checked, and stored in read-only memory in the executable. but I'm going down the route of having the user manually write SQL for everything that isn't super basic, instead of making a sort of mini-DSL for query-building. so far it's been going great but there's a lot of prior art in this space, and while I've used both Rails and Django before, forever ago, I've been kind of going into this all blindly (and so far it's been going great!).
Why bother caching queries that are pretty quick to generate? Most caching around ORMs focuses on caching the results of the query since they require more resources to generate.
well if you're using an interpreted language without any sort of build step then I suppose that's all you can do, but if your backend is written in a compiled language with compile-time execution functionality, then it seems useful to do stuff like this then, instead of at run-time, likely multiple times per request—that's a lot of unnecessarily repeated string-building, without any benefit for doing so!
> This is mostly false. ORMs are far more efficient than most programmers believe. However, ORMs encourage poor practices because of how easy it is to rely on host language logic (i.e., JavaScript or Ruby) to combine data.
The ORM encourages this, unfortunately.
> Now don’t get me wrong, ORMs are not as efficient as raw SQL queries. They are often a bit more inefficient, and in some choice cases, very inefficient.
Maybe a bit pedantic, but this and what's above contradict each other.
The problem is essentially this: you shouldn't use the abstraction until you understand what you're abstracting. But that's not how we write software typically.
It's more that ORMs are often perceived to be far slower on average, but in reality, they are not that much slower most of the time, and only far slower in choice scenarios.
But yes, agreed on your comment on general abstraction.
If the performance hit is seen as the overhead of the actual object oriented pattern (assigning fields to objects, caching, etc), then yeah, not that much slower, after all, the allocations would have to happen at some point. Patterns like DataMapper pattern present an alternate path that gives ergonomic advantages while not hiding the hard parts of dealing with state AND being incredibly performant.
But the real performance cost of ORMs has always been what typically gets left on the table: well written queries and schema. Poorly written schemas inspire poorly written queries which in turn completes the ouroboros by inspiring poorly written indexes. And ORM's like ActiveRecord encourage this. One easy example of this is the Paranoia gem as an addon to ActiveRecord, which encourages the misguided pattern of keeping around old data in your RDBMS.. There are many more examples.
I feel like if your query ends up being quite large in complexity then maybe it is a time to create a view or stored procedure depending on the use-case and have ORM call that one instead.
To me ORM is quite useful to have some data classes that I can use with it to automatically handle various generic operations and maybe some relationships. Many ORM frameworks will also auto-create tables with the relationship constraints, provided you setup your data classes correctly.
The moment you start linking up too many ORM operations together makes me want to abstract it away into a view/procedure. It will be easier to debug that view/procedure rather than inspecting what your ORM has generated and how to fix it.
I am interested to hear if someone has other insight into how to utilize ORM frameworks properly.
Unless you're on Java. Just need three columns out of twenty? That's too bad. The JPA spec mandates you pull all the columns in that table.
But fundamentally on any programming language, all popular ORMs treat the database like it's MySQL 5.7—the bare minimum in modern functionality. It's like owning an Audi but being told you have to drive it like a 1971 Chevy Vega—a lowest common denominator.
IMO SQL is one case where you should "just program the damn thing".
There's no point at all in learning some SQL or ORM library. Learn and write SQL directly.
Database access is a fundamental thing you'll be doing for your entire career probably. Best to learn the actual technology directly.
This is true for certain other technologies too - CSS for example - don't learn a CSS library - learn and use CSS. In fact that's true really for almost all aspects of front end development - don't use a "forms library", program the damn forms APIs in the browser.
Also my personal experience is that SQL is much easier to learn than certain ORMs anyway.
Indeed, my introduction to databases was extracting stuff using Perl's DBI and getting good at pushing the work out to the DB. When I was introduced to ORMs it was instantly clear that they were a game changer for MVC development.
I've transitioned a few legacy (large) Rails applications through Arel and often did not understand why we were putting the work in, other than to make it easier on new/future developers who did not understand SQL. In pretty much all of them we ended up having to keep some non-Arel queries -- you know, the ones where `STRAIGHT_JOIN` cuts 5 minutes off a query!
Isn't it more a case of "learn the fundamentals before learning the abstraction"
so you know what you are "paying for" (unless it's "zero-cost" abstraction)?
I'd say it's more of use the fundamentals. Learning them is, hopefully[0], table stakes. But even devs who know how to wield SQL, will be tempted to wrap it into some kind of ORM (either COTS or a DIY one), or a set of structures and functions, forming a flat and generic "data access layer" - all because that's how they've been taught the code should be structured. This is the pressure they need to resist.
That said, I myself haven't found the perfect balance. If you're going to honestly embrace the database, and follow a data-first approach, chances are you'll end up with an application that does most of its business logic on the database side.
On the one hand, it makes perfect sense - if your software is really about data transformation, there's no good reason to pull data from the DB, just so you can reshape it by hand using inferior tools not fit for purpose (i.e. most code you normally write), and then put it back into DB.
On the other hand, it feels wrong. Can't exactly say why, but somehow, having my app be a PostgreSQL database serving its own REST API, feels too direct, even if it literally does 100% of what I need. Closest I can come to explaining this is, RDBMS doesn't give me enough control - maybe I want the REST API to work slightly differently, or have more specific security needs, etc. But then, do I really need those alterations? Are they worth all the complexity and overhead that comes from writing software the "ordinary way", where RDBMS is only a dumb data store?
I think you are right, but databases are complicated enough where people can have different interpretations of what the fundamentals means.
For example, is being able to write the basic SQL query enough, or do you need to know what the difference between a hash join and nested loop is?
Without a pretty deep understanding, I think it can be hard to pick out faults with the ORM, and there is always the chance the ORM might avoid making a mistake you do yourself.
> Learn to write SQL directly...[it's] a fundamental thing you'll be doing for your entire career probably
SQL has been called "the eternal language". I can say that across many target development platforms from mainframe, to desktops, to client/server, to web, and across all the languages used on them, SQL has been a common thread through it all (acknowledging they all have had their own idiom). Completely agree any developer is a stronger developer if they are well familiar with the data layer and what's going on down there. Classic question is - if you're going to spend your time learning the peculiarities of an ORM, why not just spend the time invested in learning SQL? The compound interest on your SQL experience will likely be worth more in ten to fifteen years than some platform or language specific ORM. I know there are reasons to learn and use an ORM but speaking in broad terms here. Avoidance of having to learn SQL shouldn't be the sole reason one is biased towards an ORM.
Done that, been doing that since 1992 (using Pro*Pascal, CS130 - it's a wonder I didn't immediately quit and take up llama farming) but currently I am using what would be classed as an ORM (`crud` by `azer`) for its marshalling to/from Go types because Go's stdlib `sql` is bare bones and you essentially have to write your own object (un-)marshalling and Good Lord, it is tedious.
I'm always confused that ORMS don't standardize on a syntax where N+1 queries are literally illegal in all cases. With `forEach` and callbacks and everything you could easily have a syntax where you can't await until an `run` and curry everything into a single round trip. It lets you write "standard" javascript but disallows N+1 loops always.
It's amazing how many SQL Injection vulnerabilities I see in brand new code. At least with an ORM, this is abstracted away unless you try extremely hard to create such a vulnerability.
Parameterized queries are a thing in every popular driver in every popular language (except Lua). It's been quite a while since I saw a tutorial using string concatenation.
Thank heaven those dark days of horrible PHP/MySQL SQL injection tutorials everywhere came to a close.
For the record, I've seen SQL injection attacks in ORMs too, though far more rarely than the "100 separate queries to render this web page" insanity that avoiding SQL knowledge inevitably brings.
Heh, I remember lots of "100 separate queries to render this web page" insanity with doing some CMS work ages ago (actually 200+ was sometimes common). Only thing was it didn't have an ORM either :)
Someone will probably correctly guess which one it was...
Imho, ORMs make the easy queries easier (terser) but simply do not cut it for complex queries. So you need some solution for that too.
You could drop down to SQL-as-string-queries, but then you loose all type safety on the queries. We use jOOQ (sort of LINQ on the JVM) which brings significant type safety to the database interaction layer.
Would I use an ORM if I had to start over? No.
Would I use a db + jOOQ if I had to start over? In many cases, yes.
To me they are not so much a anti pattern, but usually come with many anti-patterns, like coupling validation to entities to caching to ...
jOOQ was one of the first times in my career where I (a junior, at the time) went to bat and convinced my company to shell out for a commercially licensed library. Our application was basically nothing but complex queries; getting our ORM, Hibernate 3 back then, to do anything sane & performantly was a nightmare.
Those licenses were money well spent for developer productivity alone, but honestly they've probably paid for themselves multiple times over just in terms of "energy usage at runtime" cost.
It's not always ORMs, but it's a pattern I see over-and-over again, somebody that doesn't know SQL tries to abstract away all the uncomfortable (to them) parts of SQL. It always ends up as an unmaintainable and non-performant disaster. _It's relations all the way down._ If you're going to fight SQL at every turn, you're better off using something other than SQL to store your data. (I still die a little inside every time I open up a SPROC that's just a giant cursor. Won't somebody _please_ think of the sets?)
A point I'm missing is: without using an ORM (or however else you're calling your database interface) you will eventually end up writing and maintaining one yourself, at least for CRUD stuff, and it will be filled with weird bugs, edge cases and security holes.
> A common complaint about ORMs is that they break two of the SOLID rules. If you aren’t familiar with SOLID, it is an acronym for the principles that are taught in college classes about software design.
And that right here is the problem. Academia is teaching people stuff that makes sense in an academic environment where time, maintainability, money and effort aren't of a concern (as long as you have grant money, but that's not relevant here) - and that leads to a very nasty collision when fresh graduates enter the workforce. They're used to shaving yaks and reinvent wheels all day long in the quest for a "perfect" solution, and if the company doesn't have (enough and qualified) senior/lead developers to catch that, you end up with ... very interesting products, budget overruns and/or dumpster fires. Or a combination of all of them.
ETA: There's something that can go even worse: when stuff designed by and for academics enters public availability. The best/worst example for that is OpenStack... enough knobs and twists to support the specialized environment of CERN and probably hundreds of universities worldwide, with components modularized to an insane degree, but almost impossible to get started with because it's so byzantine.
The third time I have to figure out whether to write obj.setCreatedAt(rs.getTimestamp("created_at", calendar)) or obj.setCreatedAt(rs.getLong("created_at")) I'm going to write code to look at the schema and the setter and figure it out automatically. And that’s where new ORMs come from.
Since a few departments and multiple companies struggle / fail because a super brilliant guy(s) was chasing perfection. No useful work could get done. Or it took 20-100x longer than a quick and dirty approach.
Perhaps the worst was when a team of highly skilled HTML designers were forced to learning functional programming.
Sure the back end wizard to configure some setting was a marvel of modern engineering and elegant design. It just took 10 people a year to build.
Most programming languages have long since gone towards OOP so it is only natural for people to think of their database in the same way (which coincicentally is the same reason why MongoDB got so popular).
The key thing is: most applications won't ever reach the scale where it's a better investment to ditch the ORM and most of your data model than to increase the "instance-class" parameter of your RDS database. So many projects fail because they think from the beginning what life would be as a unicorn and burn all their money on an insanely complex tech stack that could have been S3 buckets for the frontend, a couple of EC2 servers for the backend and RDS for the database. The really basic stuff can get you really far without exposing you to vendor-lock in or yak shavings.
> most applications won't ever reach the scale where it's a better investment to ditch the ORM
Aside from working in a large application that used ORM (and had many, many problems because of it), my personal experience with using them - specifically Hibernate - to develop an application from scratch was based on a relatively simple application to parse and help reconcile bank statements. This was never going to be an application that required "scale".
What I found was that, relative to just using SQL, the ORM version of my application was much more complex, required significantly more knowledge of the tools, and produced inferior code.
While I was able to get an MVP up and running slightly faster with the ORM, the productivity benefits diminished - and rapidly became negative - while the performance of the application for even a single user was very poor. Eventually, what should have taken milliseconds in SQL was literally taking seconds in the ORM. Not only did it generate stupid queries, each query had much greater latency. And what I was doing was extremely simple.
In addition to the poor performance and greater complexity, ORMs - like all dependencies - are not cost-free. There can be a significant learning curve just to get started with an ORM like Hibernate, and that learning curve then needs to apply to all developers who touch the code base.
In our larger application, we found that hibernate specifically had so many edge cases - not to mention its own SQL syntax - that the overhead of using it came to dominate the development time for new features. The need to create multiple objects (database entities AND entity objects) that could get out of sync, problems with cache consistency, latency, HQL... the list of gotchas and principle violations (DRY, KISS, SRP) was endless.
These days I simply refuse to use ORMs, and tend to do any complex database work in the database itself, i.e. with plpgsql. I find working directly with SQL to be insanely productive, and the resulting code to be in the order of 10x - 100x faster than what I used to be able to achieve in Java using ORMs.
> The really basic stuff can get you really far without exposing you to vendor-lock in or yak shavings
In my experience, vendor lock-in and yak-shaving are exactly what you get with ORMs.
> A point I'm missing is: without using an ORM (or however else you're calling your database interface) you will eventually end up writing and maintaining one yourself, at least for CRUD stuff, and it will be filled with weird bugs, edge cases and security holes.
The middle ground I've found is to use code generation for the CRUD boilerplate with a framework that makes it easy to do free-form SQL or easy query building. It gets rid of all the unnecessary contortions for advanced queries while still giving you a nice API for all the basic stuff because you get helpers instead of abstract objects. An example would be sqlboiler for Go.
This entire thread is filled with people complaining about ORM not matching vendor promises which is true.
* It won't let you swap a database with no effort
* It won't remove the need to learn and understand the SQL it generates
* It won't make schemas seamless
We use it since it's a standard abstraction that we would need to replicate otherwise. It makes caching the database access possible which is remarkably difficult to do at scale for manual SQL.
It comes with standardized tools and best practices that make issues like n+1 irrelevant.
The main problem with ORM are:
* Misaligned expectations
* Bad ORMs
All you need to do to appreciate ORM is open a project written without it and try to change ANYTHING in that project... ORMs are amazing!
We heard so much negativity about SQLAlchemy that we decided to make our own lightweight query builder instead. 8 months later, and we're using SQLAlchemy...
The SQL language is wildly complex. It is seriously not trivial to make a fully featured query builder. Forget the actual 'relational mapping' part.
It never was. Using ORM made the pool of candidates to work on a codebase bigger, and it made applications better on average (not because of some ultimate performance or security metric but because it evens out all the horrible hacks).
In a vacuum, on a super specialised team or in an academic setting it might be something one could write such an article about, but the average CRUD-app-programmer tends to not have the bandwidth or specialisation to spend effort on manual data wrangling. The same applies the other way as well: as soon as saving object-level details or fetching related data becomes something that requires thinking, plenty of junior and medior engineers end up forcing loads and saves by hand. Not because it's the best way or the only thing they could do, but because it works, it delivers the features and makes the company money, and everyone else also working on the code understands what is happening. In a way, 'bad' code that works and can be worked with is not all that 'bad'...
If you're using JS or Python, the SQL call just returns an array of objects/records. No muss. No fuss.
If you're using something like Sqlx in Rust, the SQL is not just checked for validity, it seamlessly assigns the result set to the exact type you define for that use case.
ORMs make basic CRUD fast and easy while making everything else massively more complex. If all you ever need is CRUD, I guess that's alright. Many of us work with projects that far exceed it though. And for that, you need to know SQL and not just some half-baked SQL punch through the ORM deigns to afford to you.
You can use cqrs to just build simple reporting views that orms get and set and for your real concurrency boundaries build aggregates that hydrate their state from all the events that have happened to it. Handles complexity to the nines and gets your reports and screens wicked fast at any scale you can imagine. You won't need to join much or optimize queries. Maybe figure out a few non obvious indexes.
My dislike of ORMs mainly stems from the tendency to treat modern SQL engines as glorified dumb bit buckets.
Where a CTE or LATERAL join or RETURNING clause would simplify processing immensely or (better yet) remove the possibility of inconsistent data making its way into the data set, ORMs are largely limited to simplistic mappings between basic tables and views to predefined object definitions. Even worse when the ORM is creating the tables.
SQL is at its heart a transformation language as well as a data extraction tool. ORMs largely ignore these facets to the point where most developers don't even realize anything exists in SQL beyond the basic INSERT/SELECT/UPDATE/DELETE.
It's like owning a full working tool shed but hiring someone to hand you just the one hammer, screwdriver, and hacksaw and convincing you it's enough. Folks go their whole careers without knowing they had a full size table saw, router, sander, and array of wedges just a few meters away.
To be fair, a lot of those tools have that one weird caveat you need to know, and are also strongly dependent on your dialect.
For example there's an aggregate function in DB/2, LISTAGG, that joins strings... the caveat being that if they get too long, the query blows up. It's in OracleSQL too, which has a syntax where you can tell it to truncate the string.
SELECT, INSERT, UPDATE, DELETE work more or less the same whether you're on MariaDB, Postgres, DB/2, OracleSQL, etc.
As opposed to the dizzying array of ORMs that all have multiple caveats you need to know and utterly incompatible in fundamental ways between each other?
Folks go on about how they're "stuck" with a single dialect of SQL but completely ignore how utterly impossible it is to switch ORMs without a full app rewrite. And ORMs are all language-specific, so if you have two different app clients in different languages, you can't always share even basic API access patterns.
Got a Java Spring app alongside a Django app? Good luck!
Most ORM frameworks have the ability to execute raw SQL, so using an ORM does not preclude you from using these features. ORMs tend to put you in the mindset of using simple CRUD statements most of the time - but I think that’s probably for the best, and these more advanced features should be used sparingly.
Not only does each ORM have a different API and access pattern to do this, once you hit this point, you're managing the object model ALONG WITH custom SQL for migrations.
There is also non-trivial danger in letting your ORM decide what data types your database schema should use. Got UUIDs? The ORM will store them as strings. Need start and stop timestamps? A range type with an exclusion constraint may be the right tool.
What you appear to consider "advanced features" are what some others of us consider "perfectly normal." Let's be honest: most devs consider knowing the difference between a LEFT JOIN and an INNER JOIN to be an advanced topic.
Devs will wax poetic about the marginal benefits of monads in limited scenarios, but throw up their hands in defeat when you mention a window function for a dashboard. They'd rather calculate it in the app layer with all the added latency that implies.
> Not only does each ORM have a different API and access pattern to do this
I think it is to be expected that different ORM frameworks would have differing APIs and access patterns. Similar to how different RDBMS's often have differences in the syntaxes and features that they support. It's not a big deal to look up the correct syntax in my opinion.
> you're managing the object model
Which means in the worst case that for some portion of your app you're basically back to where you were if you weren't using an ORM. Although I've found that JPA, for example, plays pretty nicely with native SQL queries (caching gets more difficult, but I think that's to be expected when moving logic out of the app and into the database regardless of whether you are using an ORM or not).
> ALONG WITH custom SQL for migrations
Personally I've always used custom SQL for all migrations. While ORMs often come with a tool to automatically generate a schema, I've never worked on a project that actually used this tool in production.
> There is also non-trivial danger in letting your ORM decide what data types your database schema should use. Got UUIDs? The ORM will store them as strings.
Some data types can be a bit tricky, but any competent ORM should at the very least have hooks to override the type on a field or implement your own custom handlers. [1]
> What you appear to consider "advanced features" are what some others of us consider "perfectly normal." Let's be honest: most devs consider knowing the difference between a LEFT JOIN and an INNER JOIN to be an advanced topic.
I said "more advanced features" - as in more advanced than basic SELECT/INSERT/UPDATE statements. I don't think any feature that you mentioned is particularly challenging to understand. And literally every entry-level developer I've ever hired has easily been able to explain the difference between LEFT and INNER joins in an interview.
SQL may be a language for all those things, but unfortunately it's horribly bad at it. Wilfully obtuse syntax. Incredibly poor compositionality. No testability worth the name. The deployment model is a half-baked pile of perl scripts, and that's if you're lucky.
So yeah, I use SQL like https://xkcd.com/783/ . I'm sure you have a bunch of cool data analysis tools in there, but please just shut up, give me the contents of my table, and let me process it in a real programming language where I have map/reduce/filter as regular composable functions that I can test and reuse, with a syntax that doesn't make my eyes bleed.
Give you the contents of your table, so you can process it in the app tier with map/filter/reduce?!
Ah, the hubris of devs who honestly believe they can whip out a solution in a day that beats a dedicated army of developers singularly focused on the task of large data management, storage, and serialization. And almost always forgetting that serialization off of disk and over a network isn't free.
There's a reason why SQL is going on 50 years when most technologies are lucky to remain dominant past 10 in this industry. And if you think it's just because of inertia or lack of imagination, you're deluded. SQL isn't perfect (nothing is), but as a DSL for set theory, it does a damn good job, even 50 years later. Far better than any map/filter/reduce whipped up yet again by someone who doesn't fully understand the scope of the problems being solved.
It's doubly troubling when you can't grok that SELECT = map, WHERE = filter, and GROUP BY + aggregator = reduce. I sincerely hope you aren't avoiding JOIN by loading both tables ahead of time.
Yeah, if your devs are loading entire tables into code and using filter on them ... I haven't personally seen that, but oh god.
Not that it isn't valid in some cases. It is. If for example, you're using all of the table in your logic and need to filter on to do something like pull out certain items for a certain need. Sure. Then it is good, absolutely!
> Ah, the hubris of devs who honestly believe they can whip out a solution in a day that beats a dedicated army of developers singularly focused on the task of large data management, storage, and serialization.
A "dedicated army of developers" who've been "going on 50 years" but whose flagship solutions are still single-point-of-failure, still noncompositional, still untestable, still have bizarre and incomprehensible performance characteristics. Yeah, no, I'm going to do stuff in regular application code, thanks.
You pay a huge cost in moving those bits over the network though. How do you even deal with tables that don't fit in memory or the lack of indexes for tables where sequential scanning is too slow?
I mean at that point you're getting into real big data stuff. Move the code to the data rather than moving the data to the code; have some way to stream in the data in its native format rather than having to read it in; do pre-aggregation and indexing as the data is written (but not in a way that's blocking your OLTP). Which, yes, is stuff that SQL RDBMSes do for you up to a point, but they do it in invisible and unmanageable ways; IME you're better off doing it explicitly and visibly.
In seriousness SQL databases can be good for ad-hoc exploratory queries, so having your data processing pipeline spit out a read-only SQL database dump on a regular schedule is worthwhile, but using that for anything more than prototyping is a mistake.
I am not particularly opinionated on the matter and i think i can appreciate both sides of the argument.
SQL is somewhat analogous to C. It is entirely legitimate to look at its archaic usability features and wonder if we could not do better and at the same time it has hit a sweet spot of adaptation to the domain that ensured its longevity.
Something that might shake up things a bit is graph databases / query languages. We can think of them as a generalization of the sql universe and an opportunity to modernize it
SQL is a declarative language. If you ask it for an address, it'll give you an address. Not sure what you're trying to say with that xkcd.
SQL is a real language. It is testable and reusable. It's a DSL, so the syntax isn't something you have to look at most of the time unless you're a DBA. That said, not everyone hates the syntax.
You're probably losing efficiency in your quest for functional paradigm perfection.
We use integration tests to test our SQL queries. Basically fire up our persistence layer and a database instance together, call some create/update methods, and assert that the expected results are returned. Writing tests is pretty straightforward - the tests proved incredibly valuable recently when we migrated to a different RDBMS provider.
But yeah I guess we don't really have a good way to write automated tests for migrations that run outside of our application code.
DDL is like code. DML is not. Tools like Sqitch are quite adept at testing DDL.
DML is declarative. Like HTML. SQL is a DSL for set theory. Do you test your HTML like the rest of your code too?
But to be clear, Sqitch and pg_tap (off the top of my head) are both effective testing tools for Postgres databases. MS SQL and Oracle both have extensive testing frameworks available.
Sure. HTML doesn't impact as much as the app as SQL does. Missing records in a SQL query can have a huge impact on many systems. A missing table row (HTML) doesn't usually impact that much.
> SQL may be a language for all those things, but unfortunately it's horribly bad at it.
Any piece of software used incorrectly will seem "horribly bad".
> Wilfully obtuse syntax
This is an opinion.
> Incredibly poor compositionality.
Another opinion.
> No testability worth the name
I would argue that "try it, observe side effects and rollback" as a language feature is more testable than many/most programming languages.
> The deployment model is a half-baked pile of perl scripts, and that's if you're lucky.
>> Any piece of software used incorrectly will seem "horribly bad".
I appreciate that you hate it. I don't even think you should care or learn it if you don't want to. But I think positioning things you don't understand or _want to use_ as "horrible" or "bad" or whatever is acting in bad faith. You're allowed to say that you don't like something and don't want to learn how to use it, without suggesting it's the tool's fault.
> So yeah, I use SQL like https://xkcd.com/783/ . I'm sure you have a bunch of cool data analysis tools in there, but please just shut up, give me the contents of my table, and let me process it in a real programming language where I have map/reduce/filter as regular composable functions that I can test and reuse, with a syntax that doesn't make my eyes bleed.
This comment directly demonstrates "I don't understand, I don't want to, just let me do whatever I want however I want", which is fine, but again, not the tool's fault you don't want to use it.
Quoting again because it's also relevant to this last comment:
>> Any piece of software used incorrectly will seem "horribly bad".
At some point there is an objective underlying reality. I've worked at a lot of places and never seen SQL done well; even the people who were happy with SQL had no automated testing, no compositionality to speak of, and a crappy deployment model (and would usually acknowledge all this! They just somehow didn't mind). If one person uses a tool badly that's maybe a problem for that person, but if everyone uses the tool badly that's a problem with the tool.
“Everyone” in this context means “the places one individual person is aware of”, and when compared to _actual_ “everyone”, is mostly irrelevant though. The things you’re complaining about are solved problems, you just don’t care/refuse to acknowledge those solutions, it seems. Which is fine! Like I said, you can just say you don’t like something without trying to smear it.
I do care, but the "solutions" are always vaporware. It's like when I complain about C++ memory unsafety and someone says "it's fine, under the new standard you can just use a safe subset of C++" and I say "ok, where's the definition of this subset and how can I tell whether a library follows it or not?" and they say "uhhh....". At some point you stop asking.
This is the second time you’ve said “it’s bad!” And then used a strange hand wavy analogy (in the first post, an xkcd comic) as an out. Again, if you don’t understand something and don’t want to, just say it, don’t paint it as bad.
Enjoy your vastly inefficient and expensive map/reduce configurations if that’s what you like.
You want to be concrete? OK, literally every real-world SQL setup I've seen, when not piggybacking off the application-level infrastructure:
- Has no way to deploy a specific historical version of the SQL (e.g. deploy this git tag)
- Has no automated checking that the SQL behaves as expected (e.g. this query with this test data should produce this output; if it doesn't, the git tag will not be created). Even checking that the SQL is syntactically valid is rare.
- Has no reuse of expressions smaller than a view/table
- Has not even basic type checking, e.g. something that alerts if you are combining an expression that might produce null with an expression that does not handle null well
- Has not even basic library/dependency management
- Has no practical structured values (since none of the infrastructure that would make using ad-hoc temporary tables safe exists). CTEs are an improvement but still thoroughly noncompositional since you can only put them in one place.
Let me guess, "these are solved problems" but no details of what you do to solve them, because none of the solutions actually work.
> - Has no way to deploy a specific historical version of the SQL (e.g. deploy this git tag)
Can you clarify this? Do you mean the version of a specific schema/query?
> - Has no automated checking that the SQL behaves as expected (e.g. this query with this test data should produce this output; if it doesn't, the git tag will not be created). Even checking that the SQL is syntactically valid is rare.
This is part of the purpose of transactions [0]
> - Has no reuse of expressions smaller than a view/table
Incorrect [1][2][3]
> - Has not even basic type checking, e.g. something that alerts if you are combining an expression that might produce null with an expression that does not handle null well
Incorrect [4] [5]
> - Has not even basic library/dependency management
Incorrect [6]
> - Has no practical structured values (since none of the infrastructure that would make using ad-hoc temporary tables safe exists). CTEs are an improvement but still thoroughly noncompositional since you can only put them in one place.
Can you clarify? How is a transaction + CTE/subquery not sufficient?
> Let me guess, "these are solved problems" but no details of what you do to solve them, because none of the solutions actually work.
Replace "none of the solutions actually work" with "I don't know how to use them", and yes you're correct.
I'm happy to answer more of your questions in good faith, but I'm not really interested in debating a person with their head in the sand. Please let me know if I can help you, if you're actually interested. But also, as I've said, it's also fine to not want/not like this type of stuff. Different strokes and all that.
> Can you clarify this? Do you mean the version of a specific schema/query?
I mean being able to version a system implemented in "advanced SQL" the same way I'd version one implemented in an applications language. Make some changes, keep them in VCS, at some point decide to do a release and deploy. A few days later, discover some issue with the changed logic, roll back to the previous release of my "code" (without affecting the data, so not just restoring a database backup).
> This is part of the purpose of transactions [0]
Right, but the actual workflow tooling around how to use them for this is missing, and for whatever reason the culture that would build and standardise it seems to be missing too. Like, in most ecosystems there's a standardised test-edit cycle that everyone understands; you make your changes and then you run npm test or cargo test or whatever, and you get some assurance that your changes were correct, and that same tooling is also in control of your release workflow and will prevent or at least warn you if you try to do a release where your tests are failing.
> Incorrect [1][2][3]
Postgresql functions with composite values (and more generally the fact that composite values exist at all) sound like exactly what I was looking for, so that would be a big improvement if I could use them (although for the record they're not a standard SQL feature; MySQL functions can only return a scala value, so there's a major missing middle between functions and views). But even then they have the same problem as temporary tables/views of existing "globally", in the place you'd expect data rather than code to be, and deployment/use tooling being inadequate to use them safely (or at least widely perceived as such). Like, everywhere I've worked has had a de facto rule of "no DDL on the production database except for deliberate long-term changes to the schema that have gone through a review process", and I don't think that's unreasonable (maybe you do?).
> Incorrect [4] [5]
Those functions exist, I'm talking about having tooling that can tell you where you need to use them. Even in something like Python you have linters that will catch basic mistakes, and they're integrated into the workflow tooling so that you won't accidentally release without running them.
> Incorrect [6]
A list of libraries != library/dependency management.
> Can you clarify? How is a transaction + CTE/subquery not sufficient?
If I want to pull out an expression that appears in two arbitrary points in my query, and use it as a CTE, that requires a lot more thought than it would in most languages, because it goes differently depending on whether it was in the SELECT or the WHERE or the GROUP BY or.... The grammar is just somehow less consistent than most programming languages, and the scoping is more confusing, I think because it's kind of lexically backwards (like, you have to declare things to be able to use them, but a lot of the time the declaration goes after the usage. But not always!).
> I mean being able to version a system implemented in "advanced SQL" the same way I'd version one implemented in an applications language. Make some changes, keep them in VCS, at some point decide to do a release and deploy.
It's tough to follow these posts in part because SQL is a programming language used to interract with a database. It seems like you want to treat the SQL you write synonymously with the system you're writing it against, which is...I don't know...confusing? SQL is declarative, you say make it so and the underlying engine makes it so. I've had trouble parsing whether you dislike _writing SQL to interract with a database_ or _the way RDMS systems manage data_. The only "logic" that exists in most databases is surrounding constraints, which, if there's a bug in your constraint, you tell your database to update its schema, and it does so. You're free to store your SQL queries however you'd like, just like any other programming language.
> A few days later, discover some issue with the changed logic, roll back to the previous release of my "code" (without affecting the data, so not just restoring a database backup).
As is the nature of this conversation, this is just inherently not how these systems are intended to work. Asking for "new data but old shape" is legitimately ridiculous.
> Right, but the actual workflow tooling around how to use them for this is missing, and for whatever reason the culture that would build and standardise it seems to be missing too.
I guess I'm confused as to what "workflow tooling" you're looking for? Every RDMS supports multiple databases and schemas, which enables you to create and run test configurations with test data in real-world environments. A transaction enables you to test real queries on real data without risk.
> Like, in most ecosystems there's a standardised test-edit cycle that everyone understands; you make your changes and then you run npm test or cargo test or whatever, and you get some assurance that your changes were correct, and that same tooling is also in control of your release workflow and will prevent or at least warn you if you try to do a release where your tests are failing.
Sure, and in database systems this is the same. Craft a query out of a transaction, when it does what you want, commit it (either via a transaction or to normal source control). Everything you're asking for literally already exists, exactly how you're asking for it.
> Those functions exist, I'm talking about having tooling that can tell you where you need to use them. Even in something like Python you have linters that will catch basic mistakes, and they're integrated into the workflow tooling so that you won't accidentally release without running them.
The more I read what you write, the more it seems like you think you're unable to write SQL in a file and execute it against a database? Which...you can do...it happens all the time. I didn't bring it up because it's so obvious and common I thought there was some other misunderstanding.
> A list of libraries != library/dependency management.
I'm not sure what else you want? Your RDMS of choice maintains a list similar to a package.json containing dependencies and their versions, and you can interract with either that list directly, or with RDMS-specific commands similar to "npm install react". Again, what you're asking for literally exists exactly how you're asking for it.
> If I want to pull out an expression that appears in two arbitrary points in my query, and use it as a CTE, that requires a lot more thought than it would in most languages, because it goes differently depending on whether it was in the SELECT or the WHERE or the GROUP BY or....
If you open a java file, are you allowed to write arbitrary code wherever you want? No, that would be ridiculous. Enforcing some shape or structure on source is ...
> The only "logic" that exists in most databases is surrounding constraints, which, if there's a bug in your constraint, you tell your database to update its schema, and it does so.
Well, complex operations on data - even if those operations are just selection and aggregation - require logic. So either that logic lives in SQL, or it lives in application code.
> I guess I'm confused as to what "workflow tooling" you're looking for? Every RDMS supports multiple databases and schemas, which enables you to create and run test configurations with test data in real-world environments. A transaction enables you to test real queries on real data without risk.
So where is the equivalent of npm/cargo/maven? And where is the unit testing framework? Transactions are low-level functionality that you could build this tooling on top of - but as far as I can see no-one has, or at least not to the extent that it's standardized and accepted in the community. Where in RDBMS-land can I check out an existing project, make a small edit to one of the queries, and then run that project's tests to confirm I haven't broken it? A few projects have some support tools for doing this, but they're inevitably ad-hoc and unpolished.
> The more I read what you write, the more it seems like you think you're unable to write SQL in a file and execute it against a database? Which...you can do...it happens all the time.
I want a project with a bit more structure than a single file. And I want to share and reuse pieces between multiple projects rather than writing everything from scratch every time. Again, that's the low-level functionality, but where is the workflow tooling that actually builds on that to let you do day-to-day things in a standardised way?
> I'm not sure what else you want? Your RDMS of choice maintains a list similar to a package.json containing dependencies and their versions, and you can interract with either that list directly, or with RDMS-specific commands similar to "npm install react".
What? Where? You linked to a list for postgresql that literally has 12 packages available, total (I'm pretty sure I've published more packages than that in Maven central myself).
> If you open a java file, are you allowed to write arbitrary code wherever you want?
Not quite, but I can select any subexpression of an expression almost anywhere and pull it out into either a local variable or a function - usually by doing nothing more than hitting a key combo in my IDE. I haven't found anything like that for SQL.
If you have fewer than ten thousand simultaneous users, this is not you. After ten million, you're not using an ORM in front of a bare relational database either.
Scale at high numbers adheres to no rules or off-the-shelf tools.
I wish. I'm constantly having to reinvent materialized views on engines that don't have good support for the concept (ms SQL, sqlite) because my users want to sort/filter on a calculated column, meaning that I have to revert to "dumb bucket of bits" for records in seven figures and user-counts in the dozens.
Relational algebra is good. I like the idea of nornalized data.
I hope to be able to use it one day instead of SQL.
So… use one of the engines with better support for materialized views?
This sentiment is like saying, "Java doesn't support traits, so I hate all programming languages."
If an engine doesn't support a feature you need, use a different engine. If your workplace does not allow this, that's an issue you have with your workplace, not the tools.
I'm lucky enough to work on projects usually lacking any kind of database anywhere. But when I do, I also tend to make sure to use a tiny subset of DB features. Simply because I consider "code" to be the part of the world I have any control over and "database" to be the dangerous and out-of-reach part where errors show up later (Such as in slower/larger integration tests). Any logic that is happening in a DB happens outside of low level testing reach too and that scares me. I'll probably never even use a select-from-select query... I'll happily leave most of the tools in the shed.
> Simply because I consider "code" to be the part of the world I have any control over and "database" to be the dangerous and out-of-reach part where errors show up later
Sounds more like a personal phobia than a substantive critique of databases, especially ACID databases. You can test any query outside of a huge integration test by… running the query in a smaller, more targeted test suite. This can be done for performance checks, conformance, sanity, etc. The thing is, compared to most other programming languages, SQL is largely side effect free. As long as no one is dropping tables or indexes, the query you ran today will return the same result structure you run tomorrow. Obviously changes in the volume of data will affect performance, but that's true no matter what data store you use.
"But what about testing writes," you ask? Same deal. Start a transaction, run the INSERT/UPDATE/DELETE and then ROLLBACK. You get your performance timing, your ability to detect errors, and no permanent changes to your dataset.
Data storage and persistence is hard. It doesn't become easier using NoSQL or avoiding data altogether. But if data makes you personally uncomfortable, perhaps it's best you keep your distance. It greatly limits your career choices, but that's ultimately your choice.
There are tons of antipatterns out there. Often the production database is completely different from anything I can or want to set up on my local machine (E.g. it's a cluster of database type A, while my local dev env is a single node of type B.
> But if data makes you personally uncomfortable
I wouldn't say uncomfortable, simply bored. Not because I think database are bad tech, they are awesome. But we still haven't solved the impedance mismatch between programs and data, so any time you need to work with them, you spend 10% of your time on business logic and 90% on deployments, migrations, ORM and CRUD. Which is just something I'm lucky enough to be able to avoid. I don't like NoSQL either (just like I don't like dynamic typing). I don't think anyone can "avoid data" in programming, but my way of staying sane has been to avoid the web.
I've worked on a few projects in the past that "didn't need all that fancy database stuff". It's frustrating to deal with the consequences. You spend most of your time building elaborate workarounds to re-invent basic data integrity safeguards. Roll your own database might be a fun learning experience, but not for production apps.
To me, the article doesn't make very compelling observations or arguments.
There are some interesting things to note about object-relational mapping:
* They map data to objects (so they are really tied to OO languages)
* They generally trade control for convenience
* Over the maturity of projects that use them, the convenience gets eroded, due emergence of system issues that require control to be wielded.
Honestly, if you are debating the use of an ORM, I would ask why. What is it that you are trying to get out of introducing an opaque abstraction between you and your data? It better be worth it, now and in the future, because it will probably take a good amount of effort to move away from it later.
The trade of control for convenience is usually a good trade at first (during prototyping and early stages of feature development). Later, there is usually an inflection point where it becomes more of a hinderance than good. The trouble is, at that point, migration away can be too risky/costly to justify.
Finally, I have noticed that, generally, mature, well-functioning code bases tend to move away from traditional OO-patterns. Specifically, code and data tend to separate as code bases grow and age because it is easier to reason about. This tends to diminish the value of ORM further.
Overall, I've moved away from ORM on the whole, in favor of query -> struct mapping.
797 comments
[ 2.0 ms ] story [ 353 ms ] threadORM is a tool that can make this easier, it's also a tool that can make it easier to shoot yourself in the foot (ie by making it easy to create N+1 queries without knowing). Like all tools, there are tradeoffs that need to be accounted for together with the actual use case to make a decision. Operating on SQL statement strings is not something I'd recommend in any case.
Welcome to LINQ - introduced in 2007 (?).
LINQ isn’t exactly an ORM. You create strongly typed LINQ statements that are turned into expression trees that are then turned into a query by a query provider.
It's kinda gross because the developer needs to make a decision about what is authoritative source of truth, the programming model makes you feel like you can trust your code (probably the wrong choice), and all the footguns around distributed state kick in (possibly even worse if you have a frontend with two way data binding and data structures that last longer than an http request in your backend)
You can really use linq to query anything (APIs, for example), including databases without using EF. It's just not very common because building a custom query provider is a lot of work.
You mean "LINQ" as an input for ORM? or LINQ in general?
either way this is crazy claim.
There's shitton of projects where queries aren't complex and LINQ will make your life saner instead of this stringly typed hell called SQL.
Just check what ORM is generating for your queries and if it is a mess then write it manually and you're good to go.
This way you get both benefits: sane type system and performance.
I get that there are big LINQ advocates. It's convenient. I'm sure there are projects where it is use spectacularly. I've never seen such a project.
Do you think that LINQ is only used for EF/LINQ2SQL?
LINQ is (at the most basic level) list comprehensions done better. It is functional programming for the imperative C# programmers. It has the potential to remove most/all loops and make the code more readable in the process.
The classic fallback.
>If you had, you would not make a comment where you seem to think that LINQ is used only for data access.
List comprehension is data access. Accessing sets in memory is data access.
>If you had, you would not make a comment where you seem to think that LINQ is used only for data access.
It has the potential, and almost the certainty, of allowing one to thoroughly shoot themselves in the foot. See without the "magic" of LINQ the grotesqueness of many patterns of data access (which, as previously mentioned, includes in memory structures. Pretty bizarre that anyone actually in this field thinks this only applies to databases, or that only DBs are "data") would lead one to rethink.
LINQ is almost always a bad indicator. It is actually a fantastic thing in one off scripts and hack type code, but when it appears in production code, I would say 90%+ of the time it is absolutely gross, but it hides how gross it actually is.
It is just a slightly slower than e.g fors, so unless this is hot path, then it is basically not relevant meanwhile it improves readability of the code
Yeah, you definitely have no idea what LINQ, or an list, even is.
As developers get more acclimated to the .NET ecosystem they usually pick up a good spidey sense for what query "shapes" are appropriate to express in LINQ and which are asking for trouble.
However, for every one of those awful LINQ queries in a codebase, I think there are 40 to 50 run-of-the-mill queries that make it worthwhile. The biggest win that it delivers over a simpler, "stringier" ORM is the ability to return structured data. I have no problem writing SQL, but processing the results back out of flat rows into objects gets extremely annoying.
Say I want to load some Foos with their Bars. If I use straight SQL with a row mapper, I define a type to represent each FooBar row, load a List<FooBarRow> into memory from my simple left join, then I'll have to do a GroupBy in memory on that List to get the actual structure that I want: a list of Foos where each one has its own list of Bars. Notice that I also have to handle the empty-list case specially.
In EF it's just: Saving that clutter -- both the code and the otherwise useless intermediate FooBarRow type -- really adds up because the apps I write have a zillion queries very much like this. So I'll take the occasional shitty query that has to be tracked down and optimized in exchange.It is weird that people jump into strange extremas when you can just combine best of both worlds.
I'm pretty sure Dapper lets you do this fairly easily. You've got a couple options - you can either provide a mapping function, or you can have your query/sprocs return multiple result sets. Then you can assemble the result sets into the object structure.
It's not quite as magical as LINQ, but it's also not quite as annoying and fraught with so much marshalling code as your example.
https://learn.microsoft.com/en-us/ef/core/querying/client-ev...
The IQueryable operations composed before to the ToListAsync() will run as SQL on the server. In the case of my example it will look pretty similar in structure to the string SQL I wrote before it: a straightforward left join with a where clause. Performance will be similar too. It will not load the entire universe of Foos and Bars and then filter them down on the client.
One of the habits I have developed from working with EF for the past 10 years is to be explicit and as local as possible about forcing the query to evaluate. It lets me pretty reliably predict what the SQL is going to look like.
You can return IQueryables from methods and pass them around through many layers of your program, adding complexity to them as you go, and lazily getting the results at the last possible moment, five layers up the call stack from where the query first started.
It seems at first like that would be good, because that way the maximum amount of logic will run on the DB server, and letting the server do stuff is better, right? But that's also where you open yourself up to being very surprised about what your SQL looks like by the time it executes, with multiple layers of your program each tacking complexity onto the query. It can get ugly. Also you can have problems if you're hanging onto IQueryables that you still haven't executed after you've Dispose()d your DbContext. For this reason I try not to let IQueryables travel very far through my program before forcing evaluation.
Being able to run queries over data without having to implement the various boilerplate is excellent.
Unfortunately basic mistakes can result in so many of those N+1 type queries if you're not careful. It can also result in reading entire table(s), possibly inside those nested N+1s.
It gets expensive when it's a remote DB server and/or dealing with a lot of records.
Profiling tools are essential.
It's why I'm not a fan of LINQ (and Entity Framework) for talking to DB severs without very strict controls.
Many times I've been called in to deal with a "slow" server, only to find out the issue is someone chained together a bunch of calls through EF and we have hundreds of thousands of queries that can be replaced by one or two.
All of those can use SQL as a worse query language than their native counterparts.
You can also create very bad SQL if you don’t know the underlying engine. For instance if you try to write SQL for a columnar database like you would for a traditional database, you are in for a world of hurt.
I would argue that when talking about writing SQL, most folks are talking about applications who's query planner talks some dialect of SQL. MS SQL Server, MariaDB, MySQL, Oracle, SQLite.
In those cases, it's not really an abstraction any more than writing assembly is an abstraction over (say) Intel's CPU microcode is.
The query planner takes your SQL and turns it into something else, sure, but you can't generally do that yourself. It's the lowest layer of abstraction that's reasonably available.
I don't know which "basic mistake" would do this. Maybe when using Lazy loading Proxies?
We use EF Core and it is quite easy to control eager loading or load-on-demand on a per-case basis.
It's also the first thing I recommend to anyone claiming that concatenating SQL strings together is a good use of your time in the 21st century. Makes writing things like complex HTML tables with configurable columns and tons of optional filters so much faster and more maintainable.
I don't see this as a given and I don't accept it in my own applications. If you need data, go to the data access layer. If it doesn't provide you what you need, build a new repository / provider / whatever your pattern is.
Whether it's an ORM or a home-built query compositor or whatever, one thing I know from experience is that once your application is "sufficiently complex" that you start to (incorrectly) believe you need this, your application has become too complex to use it reliably.
You absolutely will be mistakenly evaluating these builders in the wrong layers, iterating results without realising you're generating N+1 queries, etc.
You don't need an ORM.
One simple example is when you need to make atomic changes to two different types of entities. In the data layer, this is usually trivial — just run two queries within a transaction — but you need to expose a function that boils down to `updateBothAandB`. Rinse and repeat enough times and your data layer is a soup of business logic.
So no, bespoke data access layer code is not an ORM.
Whichever layer you put it in, you need a way to compose two query fragments. Otherwise you have to write N*M queries manually instead of N+M query fragments.
Either you use an ORM to help you with this or you don't, with all the usual tradeoffs about using a library or not. But you still have to solve the problem. (Or you do a lot of tedious copy-paste work - with all the usual tradeoffs of that)
* Accept that this will just be two trips to the DB.
* Write a new function func12 that writes a query to return all the needed data in one query and use that instead.
* Have your tool be able to automatically compose the queries.
If you do with the second option you have to do that with every combination of functions that end up being used together which is multiplicative in general.
it's pretty rare for queries to be dynamically composed from arbitrary sub-queries
if this is a problem you need to solve then ORMs certainly make more sense, but even in this case I find query builders to be more effective
the interface between the application and the DB is actually a string! it's not an abstract data type, it doesn't benefit from being modeled by types
in general, it should not be possible for user input to produce arbitrarily complex queries against your database
each input element in an HTML form should map to a well-defined parameter of a SQL query builder, like, you shouldn't be dynamically composing sub-queries based on the value of a text field, the value should add a where or join or whatever other clause to the single well-defined query
sometimes this isn't possible but these should be super rare exceptions
/users/:id should map to 1 endpoint that's parameterized on userid
/search?userid=:userid&tag=:tag should map to 1 endpoint that's parameterized on userid and tag(s)
endpoints should be simple to write
You’re going to have parameters that are compound. You’re going to end up filtering on objects 3 relations removed, or deal with nasty syncing of normalization. You’ll have endpoints with generic relations, like file uploads, where the parent isnt a foreign key.
It’s going to be a mess. They will NOT always be simple to write.
I'm talking static, not dynamic. You still need to compose two pieces together into a single query, and you can either use an ORM to help with that or not.
> the interface between the application and the DB is actually a string! it's not an abstract data type, it doesn't benefit from being modeled by types
No it isn't. You can't send an arbitrary string to the database and expect it to work. At the very least you benefit from having an interface that's structured enough to tell you whether your parentheses are balanced and your quotes are matched rather than having to figure that out at runtime.
when your app queries the db, the query is not composed from several pieces, it is well-defined in the relevant method
this is a single query, not multiplethe db accepts a string and parses it to an AST, it does not accept a typed value
this means the interface is the string
unbalanced parens and whatever other invalid syntax is obviously caught by tests
> this is a single query, not multiple
And when you want to query for multiple related things together, the whole point of having a relational database? For different purposes you need different views on your data, and those views are generally constructed out of a bunch of shared fragments; you can either figure out a way to share them, or copy-paste them everywhere you use them.
> the db accepts a string and parses it to an AST, it does not accept a typed value
> this means the interface is the string
The DB accepts a structured query, not a string. It might be represented as a string on the wire, but if that was what mattered then we'd use byte arrays for all our variables since everything's a byte array at runtime.
> unbalanced parens and whatever other invalid syntax is obviously caught by tests
Tests are a poor substitute for types.
every "view" on your DB should be modeled as a separate function
every possible "thing" that's input to a function which queries the database should be transformed into a part of the query string by that function
> The DB accepts a structured query, not a string. It might be represented as a string on the wire, but if that was what mattered then we'd use byte arrays for all our variables since everything's a byte array at runtime.
...no
the API literally receives a string and passes it directly to the DB's query parser
if the DB accepted structured queries, then the API would rely on something like protobuf to parse raw bytes to native types -- it doesn't
like `echo SELECT * FROM whatever; | psql` does not parse the `SELECT * FROM whatever;` string to a structured type, it sends the string directly to the database
OK, and when a significant amount of what those functions do is shared, how do you share it? (E.g. imagine we're building, IDK, some kind of CMS, and in one view we have authors (based on some criteria) and posts by those authors, and in another we have tags and posts under those tags, and in another we have date ranges and posts in that date range. How do you share the "fetching posts" bit of SQL between those three different (parameterized) queries?)
> if the DB accepted structured queries, then the API would rely on something like protobuf to parse raw bytes to native types -- it doesn't
> like `echo SELECT * FROM whatever; | psql` does not parse the `SELECT * FROM whatever;` string to a structured type, it sends the string directly to the database
In both cases the parsing happens on the server, not the client. "echo abcde | psql" and "curl -D abcde http://my-protobuf-service/" are both doing the same kind of thing - passing an unstructured string to a server which will fail to parse it and give some kind of error - and both equally useless.
your application has a fetch posts method, that method takes input including (optional) author(s), tag(s), etc., it builds a query that includes WHERE clauses for every provided parameter
the code that converts an author to a WHERE clause can be a function, the point is it outputs a string, or something that is input to a builder and results in a string
i'm not sure what a "fetching posts bit of SQL" is, a query selects specific rows, qualified by where clauses that filter the result set, joins that modify it, etc.
So you do string->string processing, and you explicitly won't treat the queries you're generating as structured values? Enjoy your rampant SQL injection vulnerabilities.
creating a query string that's parameterized on input usually means you model those input parameters as `?` or `$1` or whatever, and provide them explicitly as part of the query
nobody is doing printfs of values
Right, but is an ORM a good way to achieve this? I would posit no. The problem, correct me if I’m wrong, is one of an in memory representation of working data and a data access layer (how to get/update the data). An ORM provides a framework for both of these, but in my opinion it’s very inefficient to build, maintain, and optimise. You don’t need to turn to raw SQL as the alternative data access layer, but having one that is easier to customise is better in my opinion.
Not at all an expert, probably nonsense, please correct me.
ORM's such as Eloquent in Laravel also have some nice methods to resolve N+1 and perform lazy loading, but it's always tradeoff.
There is no other way — people who don’t know one should not touch the other. Otherwise they are either juniors, or crazies who just like to complain that “the plane is a bad vehicle because I can’t just sit inside and land it properly without years of training”.
E.g. you could write a query like this:
And the library would generate a class like:Go: https://github.com/kyleconroy/sqlc
Rust: https://github.com/cornucopia-rs/cornucopia
This is my preferred method of interacting with databases now, if available.
Very flexible.
In short, I don't think ORMs should generate class libraries to be referenced by applications, they should help you get exactly what you need, when you need it, nothing more, nothing less, and no one expect the person that maintains that code should ever need to care how the data got there. If a column gets added to a table that has nothing to do with a specific application, that application shouldn't need to be updated.
We still used the library for inserts and updates.
Then your devs have way too much time on their hands. Find them some actual problems to solve.
We should just learn to recognize it for what it is, someone that's being controversial for attention, and move on. Let's save our attention for the ORM article and discussion that starts out along the lines of "ORMs can provide benefit, but it's important to recognize where, and not let the problems of their use outweigh their benefits. Here's what I've found."
I don't have a horse in this race, I could care less if you do or don't use an ORM. But, and maybe this is the cynic in me, there are practices in software development that absolutely, 100%, for certain, have no good reason and are perpetuated in part by this belief that there must have been a good reason for it to exist (Chesterton's fence and all that).
Null terminated C strings are a prime example. There is absolutely no good reason, other than the fact that the authors may have wanted to save 3 bytes, that C strings should be null terminated. Fortran was created in 1954, and passed the length of the string with the string itself. How many countless bugs and CVEs have risen due to errors in handling null terminated C strings (one example[0])? And for what? To save 3 bytes or just because of the authors decision at a whim's notice?
Likewise, decisions made at Javascript's inception have burdened it for its entire life. Decisions that were made at a whim's notice, like implicitly converting numbers to strings sometimes, and sometimes implicitly converting strings to numbers! (Tell me what `10 + "10"` is and what `"10" + 10` is without using the inspector). And the million and one ways to define something that's undefined.
Anyways, when somebody tells me there's absolutely no good reason for a development practice to exist, sometimes, that is the absolute truth. And I would rather have more people throwing away these crummy practices that lead to unnecessary headaches (or at least questioning them) then people continuing to laud the practice and perpetuate it ad infinitum.
[0]: https://defendtheweb.net/article/common-php-attacks-poison-n...
I see this as a problem that's horribly inflated by those who don't use JS on a daily basis.
Practitioners of the language largely don't care, because in actual code you rarely see cases where it would matter.
Now that we have template strings it's even less relevant.
Or, you know, they wanted to make the language track assembly as closely as possibly (which was possibly back at that time when processors were much simpler and instructions weren't constantly reordered), and if you've ever written assembly, you know you're not really working at a string level, you're working at a byte and character level. Sized strings are more complex than null terminated ones in that you either need to set a max size or you need to waste multiple bytes per string (which actually mattered on many systems C was used on when it was developed) or you have to use masks on those early bytes to or determine if the next byte is part of the string or a continuation of the size.
And honestly, when you're working on systems where ram (and maybe storage) is in kilobytes or less and speed is in kilohertz, the extra code to do that and the extra time to process them and the extra space to store sized strings is a lot less of an obvious choice to make.
Was C a good choice for the time and where it was used? Possibly. Is it a good choice these days? Probably not without a bunch of extra utils and compiler guards to beat back the worst problems. I do t blame C as much for that as I do the people that continue to use it without extra safeguards.
What was that you said about Chesterton's fence? That's one of those terms you should be careful about throwing around when supporting an absolutist position...
But that's just me guessing. There's no reason for us to do that when Dennis Ritchie wrote down the reason for this:
>> This change was made partially to avoid the limitation on the length of a string caused by holding the count in an 8- or 9-bit slot, and partly because maintaining the count seemed, in our experience, less convenient than using a terminator.[1]
So this was a change made primarily for convenience. And if the limitations of 255 characters was really a huge blocker, they could have easily created a spec like UTF8 to allow variable length encoding depending on the size of the string, which funnily enough Ken Thompson who also worked with Ritchie, later did invent. You mentioned that the processing time would have been an issue, but C strings require you to process the entire length of the string to determine the length, and Ritchie notes that as an additional tradeoff for this convention.
But that wasn't done. And I can't blame Ritchie for that either, because he didn't think this language would become what it is today! Later on in the paper he alludes to this:
>> C has become successful to an extent far surpassing any early expectations[1]
All throughout the paper you can see him referring to decisions that were made out of convenience, and not because he had done extensive analysis to determine whether the tradeoff for the convenience was worth it:
>> Two ideas are most characteristic of C among languages of its class: the relationship between arrays and pointers, and the way in which declaration syntax mimics expression syntax...In both cases, historical accidents or mistakes have exacerbated their difficulty.
>> C treats strings as arrays of characters conventionally terminated by a marker...and as a result the language is simpler to describe and to translate than one incorporating the string as a unique data type.
All that to say, yes of course there were reasons that decisions were made the way they were. But, and this is what I've noticed more and more in programming communities, these decisions are often made with little to no analysis and usually made out of a subjective preference, or to make the implementors life a tad easier. So, yea, I think it's right to call out a lot of "best practices" because history has shown that programmers really don't put too much thought into their decisions. And then you end up with gurus proclaiming that a decision made out of convenience was actually the best decision available and we should never change the way we do things because clearly this is the right way.
[0]: https://en.m.wikipedia.org/wiki/IBM_650
[1]: https://www.bell-labs.com/usr/dmr/www/chist.html
Fortran was not created for the same purpose. Fortran existing as an invalidation of C's choices is like Java existing being an invalidation of C++'s choices. There's a reason Fortran and Java are not common choices to write in OS kernel in, while C and C++ are/were. There's a reason why C and C++ aren't often used for web development, but Interpreted languages are. Different design choices fir different niches better or worse.
> So this was a change made primarily for convenience.
Convenience can mean a lot of things, and in this context and in the absence of contrary evidence I interpret that statement to be entirely inline with what I said above. It was inconvenient to have a more complex type to deal with, for multiple reasons. I'm not sure why you would think it different, it's not like I said it could not work the other way, just that there were things that went into the reasoning that made it less obvious than in today's world.
> You mentioned that the processing time would have been an issue, but C strings require you to process the entire length of the string to determine the length, and Ritchie notes that as an additional tradeoff for this convention.
Yes, tradeoff. If what you're doing with strings most the time is parsing them, knowing the length ahead of time may be of little benefit, since you're going to step through them character by character anyway. For many operations, knowing the length ahead of time is irrelevant.
> decisions that were made out of convenience, and not because he had done extensive analysis
I'm not sure anyone is arguing they used extensive analysis. I'm certainly not. But when the reality you live and work in is that you are running up against real hardware constraints routinely, that's bound to affect your ad-hoc reasoning about what choice to make when you don't do extensive analysis.
> All that to say, yes of course there were reasons that decisions were made the way they were.
Given that this started because you wanted to support absolutist statements with "there is absolutely no good reason, other than the fact that the authors may have wanted to save 3 bytes, that C strings should be null terminated." and your prime example has now been walked back to the fact that yes, there were some considerations beyond that, including making it a simple language to describe, I think you've proved my original point.
Who is to say that C's simple description and ease of implementation for additional architectures isn't a major factor in it's success and spread? And yes, while we've been paying the price for that for quite a while now, it may also have allowed for a level of software portability that really helped advance computers beyond where they would currently be otherwise.
I don't like C all that much and I don't use it for anything, but I'm also willing to note that it must have done quite a few things right to get to where it did, and I'm not willing to call out any aspect of it as completely without merit while still acknowledging the immense benefit the language brought as a whole, because at that point we're getting into conjecture about alternate histories.
Absolute statements are generally bad ideas, and I've just been reminded of why that is the case haha.
It’s funny - it’s almost like C never had a string type and null-termination is more a convention than a primitive data type.
There’s nothing that prevents us from having Pascal-like strings as much as we want, provided we know we’ll need to reimplement everything we need.
So I don't see how this has much bearing on the ultimate point I'm making, which is that yes, conventions can be bad haha.
If it's a third-party API (say, a weather forecast or market data) the answers will range from "no" to "yes, and it will cost you X".
your query builder isn't just string based to avoid a lot of potential bugs which are easy to introduce and miss in tests? It also has a simple way to (de)serialize row from/to POD structs? Now you already have a thin ORM.
If you find yourself needing to lazily compose queries, then by all means, reach for a query builder. But I would encourage you to first examine whether you've split code into services that really should live together and whether that code should live closer to the data layer before you build a whole query builder.
And don't reach for a query builder until you need it.
JooQ in Java is a DSL for writing SQL in Java, it works amazingly well. It’s not guaranteed every JooQ statement compiles to valid SQL but I have pretty good experiences writing crazy complex queries using things like string_agg in pgsql. Here the relational model is primary and the representation as Java objects is secondary, given the SQL is persistent and the Java objects ephemeral this makes sense.
My RSS reader uses Arangodb and Python and I’d like to stop a bit and develop a query builder inspired by OWL class axioms which if you look at them the right way look like building blocks for a Scratch-like SQL query builder. I want both ordinary queries (browse my favorites, say an article with the keyword “niantic” that appeared on Tildes) and rules (don’t want read articles from the Guardian that have “- live” in the title)
Debugging and optimizing SQL queries is a well understood art, barring some rare DB specific optimizations (which ORMs don't even touch)
Getting expert SQL advice and support when you need is very practical and the results show.
Putting an ORM as the middle man generating your queries, negates all those benefits for some pithy premature laziness.
Operating on raw SQL is something I'd strongly recommend.
(Maybe some ORMs try to get this right. It’s a very hard problem. Certainly the ORMs I’ve used don’t, but I admit I haven’t used very many.)
JPA is at least a spec rather than just yet another single isolated implementation. Unfortunately it's not a good spec.
And most only support basic transactions, not checkpoints.
ActiveRecord approach. Objects aren't kept in cache calling update like that won't update other object in memory. Each time you call select you get a different object.
Hibernate approach. Object are kept in cache and running update will update existing objects in memory because each select returns the same object.
SqlAlchemy does permit both.
[0]https://en.wikipedia.org/wiki/Optimistic_concurrency_control
Write your queries in SQL, but you get to have your strong typing and automatic result set to objects mappings. Productivity combined with the full-featured “real” query language.
Also, I’ve become a fan of using the object mappers only for read-only queries. Updates occur via the command pattern — stored procedures. These can be mapped through to look like native methods.
Sincerely someone rewriting an 'old' php app that is just arrays and queries to something using models and eloquent, what a game changer
Some ORMs are awful to work with, but that doesn't mean the genre is a bad idea. I've loved working with SQLAlchemy over the years because it doesn't try to re-invent the whole idea of SQL. Instead, it gives you a convenient layer for building queries, passing them around, etc., while it concentrates on getting the details right so you don't have to futz around with them.
In this case, the phrasing of the question implies something not at all evident, that ORMs are an anti-pattern.
That's begging the question. It never was.
I've been learning some rust lately and writing out boilerplate code to handle simple CRUD is painstaking and it feels like a waste of my time.
SQLAlechemy's ORM can get in the way a bit for more complex queries (or ones you are trying to optimize) but you can always just drop down and write the SQL for those specifically.
From what I’ve seen yes. My first ORM was flask-sqlalchemy. Migrating a database was literally just updating an object and running a command. No other ORM has been this seamless.
1: https://blog.shrikanthup.com/2021-12-04-ecto-data/
I love Ecto. In my opinion, Phoenix has a lot of strengths, but Ecto is the superpower in that ecosystem.
That being said, at least _some_ of the brand of "problems" mentioned in the post can exist into the Ecto world. I've seen plenty of folks new to Ecto fall into N+1 queries using `preload` naively, so I'd argue it doesn't completely erase that blurred boundary. I also personally much prefer the level that Ecto sits at and think the tradeoffs are totally worth it.
pragmatic ORMs that don't try to hide everything behind a "bundle" or introduce their own query language are great, it's the ones that try to totally "solve" the ORM problem that are hard to work with
This is how an ORM should be.
I've been wondering about this and other things while working on my own mini-ORM thing for SQLite—I have it so queries are built once, at compile-time, type-checked, and stored in read-only memory in the executable. but I'm going down the route of having the user manually write SQL for everything that isn't super basic, instead of making a sort of mini-DSL for query-building. so far it's been going great but there's a lot of prior art in this space, and while I've used both Rails and Django before, forever ago, I've been kind of going into this all blindly (and so far it's been going great!).
> This is mostly false. ORMs are far more efficient than most programmers believe. However, ORMs encourage poor practices because of how easy it is to rely on host language logic (i.e., JavaScript or Ruby) to combine data.
The ORM encourages this, unfortunately.
> Now don’t get me wrong, ORMs are not as efficient as raw SQL queries. They are often a bit more inefficient, and in some choice cases, very inefficient.
Maybe a bit pedantic, but this and what's above contradict each other.
The problem is essentially this: you shouldn't use the abstraction until you understand what you're abstracting. But that's not how we write software typically.
But yes, agreed on your comment on general abstraction.
But the real performance cost of ORMs has always been what typically gets left on the table: well written queries and schema. Poorly written schemas inspire poorly written queries which in turn completes the ouroboros by inspiring poorly written indexes. And ORM's like ActiveRecord encourage this. One easy example of this is the Paranoia gem as an addon to ActiveRecord, which encourages the misguided pattern of keeping around old data in your RDBMS.. There are many more examples.
To me ORM is quite useful to have some data classes that I can use with it to automatically handle various generic operations and maybe some relationships. Many ORM frameworks will also auto-create tables with the relationship constraints, provided you setup your data classes correctly.
The moment you start linking up too many ORM operations together makes me want to abstract it away into a view/procedure. It will be easier to debug that view/procedure rather than inspecting what your ORM has generated and how to fix it.
I am interested to hear if someone has other insight into how to utilize ORM frameworks properly.
But fundamentally on any programming language, all popular ORMs treat the database like it's MySQL 5.7—the bare minimum in modern functionality. It's like owning an Audi but being told you have to drive it like a 1971 Chevy Vega—a lowest common denominator.
There's no point at all in learning some SQL or ORM library. Learn and write SQL directly.
Database access is a fundamental thing you'll be doing for your entire career probably. Best to learn the actual technology directly.
This is true for certain other technologies too - CSS for example - don't learn a CSS library - learn and use CSS. In fact that's true really for almost all aspects of front end development - don't use a "forms library", program the damn forms APIs in the browser.
Also my personal experience is that SQL is much easier to learn than certain ORMs anyway.
Don't waste the cost of learning the wrong thing.
I suppose the same with CSS -- knowing it means you'll have much more flexibility and understanding when picking a CSS library for convenience.
I've transitioned a few legacy (large) Rails applications through Arel and often did not understand why we were putting the work in, other than to make it easier on new/future developers who did not understand SQL. In pretty much all of them we ended up having to keep some non-Arel queries -- you know, the ones where `STRAIGHT_JOIN` cuts 5 minutes off a query!
That said, I myself haven't found the perfect balance. If you're going to honestly embrace the database, and follow a data-first approach, chances are you'll end up with an application that does most of its business logic on the database side.
On the one hand, it makes perfect sense - if your software is really about data transformation, there's no good reason to pull data from the DB, just so you can reshape it by hand using inferior tools not fit for purpose (i.e. most code you normally write), and then put it back into DB.
On the other hand, it feels wrong. Can't exactly say why, but somehow, having my app be a PostgreSQL database serving its own REST API, feels too direct, even if it literally does 100% of what I need. Closest I can come to explaining this is, RDBMS doesn't give me enough control - maybe I want the REST API to work slightly differently, or have more specific security needs, etc. But then, do I really need those alterations? Are they worth all the complexity and overhead that comes from writing software the "ordinary way", where RDBMS is only a dumb data store?
--
[0] - Who am I kidding...
For example, is being able to write the basic SQL query enough, or do you need to know what the difference between a hash join and nested loop is?
Without a pretty deep understanding, I think it can be hard to pick out faults with the ORM, and there is always the chance the ORM might avoid making a mistake you do yourself.
SQL has been called "the eternal language". I can say that across many target development platforms from mainframe, to desktops, to client/server, to web, and across all the languages used on them, SQL has been a common thread through it all (acknowledging they all have had their own idiom). Completely agree any developer is a stronger developer if they are well familiar with the data layer and what's going on down there. Classic question is - if you're going to spend your time learning the peculiarities of an ORM, why not just spend the time invested in learning SQL? The compound interest on your SQL experience will likely be worth more in ten to fifteen years than some platform or language specific ORM. I know there are reasons to learn and use an ORM but speaking in broad terms here. Avoidance of having to learn SQL shouldn't be the sole reason one is biased towards an ORM.
Done that, been doing that since 1992 (using Pro*Pascal, CS130 - it's a wonder I didn't immediately quit and take up llama farming) but currently I am using what would be classed as an ORM (`crud` by `azer`) for its marshalling to/from Go types because Go's stdlib `sql` is bare bones and you essentially have to write your own object (un-)marshalling and Good Lord, it is tedious.
But that is all I'd use an ORM for these days.
It's always good to define acronyms up front, especially if you're trying to attract a larger audience.
[1] https://en.wikipedia.org/wiki/Object%E2%80%93relational_mapp...
[2] https://stackoverflow.com/questions/1279613/what-is-an-orm-h...
Then people have to write "bad" code to N+1.
For simple projects, I don’t see a need for it.
Thank heaven those dark days of horrible PHP/MySQL SQL injection tutorials everywhere came to a close.
For the record, I've seen SQL injection attacks in ORMs too, though far more rarely than the "100 separate queries to render this web page" insanity that avoiding SQL knowledge inevitably brings.
Someone will probably correctly guess which one it was...
https://github.com/porsager/postgres
https://github.com/gajus/slonik
You could drop down to SQL-as-string-queries, but then you loose all type safety on the queries. We use jOOQ (sort of LINQ on the JVM) which brings significant type safety to the database interaction layer.
Would I use an ORM if I had to start over? No.
Would I use a db + jOOQ if I had to start over? In many cases, yes.
To me they are not so much a anti pattern, but usually come with many anti-patterns, like coupling validation to entities to caching to ...
Those licenses were money well spent for developer productivity alone, but honestly they've probably paid for themselves multiple times over just in terms of "energy usage at runtime" cost.
It's not always ORMs, but it's a pattern I see over-and-over again, somebody that doesn't know SQL tries to abstract away all the uncomfortable (to them) parts of SQL. It always ends up as an unmaintainable and non-performant disaster. _It's relations all the way down._ If you're going to fight SQL at every turn, you're better off using something other than SQL to store your data. (I still die a little inside every time I open up a SPROC that's just a giant cursor. Won't somebody _please_ think of the sets?)
> A common complaint about ORMs is that they break two of the SOLID rules. If you aren’t familiar with SOLID, it is an acronym for the principles that are taught in college classes about software design.
And that right here is the problem. Academia is teaching people stuff that makes sense in an academic environment where time, maintainability, money and effort aren't of a concern (as long as you have grant money, but that's not relevant here) - and that leads to a very nasty collision when fresh graduates enter the workforce. They're used to shaving yaks and reinvent wheels all day long in the quest for a "perfect" solution, and if the company doesn't have (enough and qualified) senior/lead developers to catch that, you end up with ... very interesting products, budget overruns and/or dumpster fires. Or a combination of all of them.
ETA: There's something that can go even worse: when stuff designed by and for academics enters public availability. The best/worst example for that is OpenStack... enough knobs and twists to support the specialized environment of CERN and probably hundreds of universities worldwide, with components modularized to an insane degree, but almost impossible to get started with because it's so byzantine.
Perhaps the worst was when a team of highly skilled HTML designers were forced to learning functional programming. Sure the back end wizard to configure some setting was a marvel of modern engineering and elegant design. It just took 10 people a year to build.
VC money makes people weird.
Only if you insist on formulating your logic based on "objects" instead of "rows"; see https://news.ycombinator.com/item?id=36498583
The key thing is: most applications won't ever reach the scale where it's a better investment to ditch the ORM and most of your data model than to increase the "instance-class" parameter of your RDS database. So many projects fail because they think from the beginning what life would be as a unicorn and burn all their money on an insanely complex tech stack that could have been S3 buckets for the frontend, a couple of EC2 servers for the backend and RDS for the database. The really basic stuff can get you really far without exposing you to vendor-lock in or yak shavings.
Shave the yaks when you actually need the fur.
Aside from working in a large application that used ORM (and had many, many problems because of it), my personal experience with using them - specifically Hibernate - to develop an application from scratch was based on a relatively simple application to parse and help reconcile bank statements. This was never going to be an application that required "scale".
What I found was that, relative to just using SQL, the ORM version of my application was much more complex, required significantly more knowledge of the tools, and produced inferior code.
While I was able to get an MVP up and running slightly faster with the ORM, the productivity benefits diminished - and rapidly became negative - while the performance of the application for even a single user was very poor. Eventually, what should have taken milliseconds in SQL was literally taking seconds in the ORM. Not only did it generate stupid queries, each query had much greater latency. And what I was doing was extremely simple.
In addition to the poor performance and greater complexity, ORMs - like all dependencies - are not cost-free. There can be a significant learning curve just to get started with an ORM like Hibernate, and that learning curve then needs to apply to all developers who touch the code base.
In our larger application, we found that hibernate specifically had so many edge cases - not to mention its own SQL syntax - that the overhead of using it came to dominate the development time for new features. The need to create multiple objects (database entities AND entity objects) that could get out of sync, problems with cache consistency, latency, HQL... the list of gotchas and principle violations (DRY, KISS, SRP) was endless.
These days I simply refuse to use ORMs, and tend to do any complex database work in the database itself, i.e. with plpgsql. I find working directly with SQL to be insanely productive, and the resulting code to be in the order of 10x - 100x faster than what I used to be able to achieve in Java using ORMs.
> The really basic stuff can get you really far without exposing you to vendor-lock in or yak shavings
In my experience, vendor lock-in and yak-shaving are exactly what you get with ORMs.
The middle ground I've found is to use code generation for the CRUD boilerplate with a framework that makes it easy to do free-form SQL or easy query building. It gets rid of all the unnecessary contortions for advanced queries while still giving you a nice API for all the basic stuff because you get helpers instead of abstract objects. An example would be sqlboiler for Go.
This entire thread is filled with people complaining about ORM not matching vendor promises which is true.
* It won't let you swap a database with no effort * It won't remove the need to learn and understand the SQL it generates * It won't make schemas seamless
We use it since it's a standard abstraction that we would need to replicate otherwise. It makes caching the database access possible which is remarkably difficult to do at scale for manual SQL.
It comes with standardized tools and best practices that make issues like n+1 irrelevant.
The main problem with ORM are:
* Misaligned expectations * Bad ORMs
All you need to do to appreciate ORM is open a project written without it and try to change ANYTHING in that project... ORMs are amazing!
The SQL language is wildly complex. It is seriously not trivial to make a fully featured query builder. Forget the actual 'relational mapping' part.
In a vacuum, on a super specialised team or in an academic setting it might be something one could write such an article about, but the average CRUD-app-programmer tends to not have the bandwidth or specialisation to spend effort on manual data wrangling. The same applies the other way as well: as soon as saving object-level details or fetching related data becomes something that requires thinking, plenty of junior and medior engineers end up forcing loads and saves by hand. Not because it's the best way or the only thing they could do, but because it works, it delivers the features and makes the company money, and everyone else also working on the code understands what is happening. In a way, 'bad' code that works and can be worked with is not all that 'bad'...
If you're using something like Sqlx in Rust, the SQL is not just checked for validity, it seamlessly assigns the result set to the exact type you define for that use case.
ORMs make basic CRUD fast and easy while making everything else massively more complex. If all you ever need is CRUD, I guess that's alright. Many of us work with projects that far exceed it though. And for that, you need to know SQL and not just some half-baked SQL punch through the ORM deigns to afford to you.
That and read replicas. Relying on the ORM will not save you, but knowledge of SQL (specifically materialized views) will.
Where a CTE or LATERAL join or RETURNING clause would simplify processing immensely or (better yet) remove the possibility of inconsistent data making its way into the data set, ORMs are largely limited to simplistic mappings between basic tables and views to predefined object definitions. Even worse when the ORM is creating the tables.
SQL is at its heart a transformation language as well as a data extraction tool. ORMs largely ignore these facets to the point where most developers don't even realize anything exists in SQL beyond the basic INSERT/SELECT/UPDATE/DELETE.
Pivot tables. Temporal queries. CUBE/ROLLUP. Window functions. Set-returning functions. Materialized views. Foreign tables. JSON processing. Date processing. Exclusion constraints. Types like ranges, intervals, domains. Row-level security. MERGE.
It's like owning a full working tool shed but hiring someone to hand you just the one hammer, screwdriver, and hacksaw and convincing you it's enough. Folks go their whole careers without knowing they had a full size table saw, router, sander, and array of wedges just a few meters away.
For example there's an aggregate function in DB/2, LISTAGG, that joins strings... the caveat being that if they get too long, the query blows up. It's in OracleSQL too, which has a syntax where you can tell it to truncate the string.
SELECT, INSERT, UPDATE, DELETE work more or less the same whether you're on MariaDB, Postgres, DB/2, OracleSQL, etc.
Folks go on about how they're "stuck" with a single dialect of SQL but completely ignore how utterly impossible it is to switch ORMs without a full app rewrite. And ORMs are all language-specific, so if you have two different app clients in different languages, you can't always share even basic API access patterns.
Got a Java Spring app alongside a Django app? Good luck!
Inject me an object that gets people objects that meet an interface (ok might need to be async) and you can switch out how that is done later.
But it is more boilerplate
There is also non-trivial danger in letting your ORM decide what data types your database schema should use. Got UUIDs? The ORM will store them as strings. Need start and stop timestamps? A range type with an exclusion constraint may be the right tool.
What you appear to consider "advanced features" are what some others of us consider "perfectly normal." Let's be honest: most devs consider knowing the difference between a LEFT JOIN and an INNER JOIN to be an advanced topic.
Devs will wax poetic about the marginal benefits of monads in limited scenarios, but throw up their hands in defeat when you mention a window function for a dashboard. They'd rather calculate it in the app layer with all the added latency that implies.
I think it is to be expected that different ORM frameworks would have differing APIs and access patterns. Similar to how different RDBMS's often have differences in the syntaxes and features that they support. It's not a big deal to look up the correct syntax in my opinion.
> you're managing the object model
Which means in the worst case that for some portion of your app you're basically back to where you were if you weren't using an ORM. Although I've found that JPA, for example, plays pretty nicely with native SQL queries (caching gets more difficult, but I think that's to be expected when moving logic out of the app and into the database regardless of whether you are using an ORM or not).
> ALONG WITH custom SQL for migrations
Personally I've always used custom SQL for all migrations. While ORMs often come with a tool to automatically generate a schema, I've never worked on a project that actually used this tool in production.
> There is also non-trivial danger in letting your ORM decide what data types your database schema should use. Got UUIDs? The ORM will store them as strings.
Some data types can be a bit tricky, but any competent ORM should at the very least have hooks to override the type on a field or implement your own custom handlers. [1]
> What you appear to consider "advanced features" are what some others of us consider "perfectly normal." Let's be honest: most devs consider knowing the difference between a LEFT JOIN and an INNER JOIN to be an advanced topic.
I said "more advanced features" - as in more advanced than basic SELECT/INSERT/UPDATE statements. I don't think any feature that you mentioned is particularly challenging to understand. And literally every entry-level developer I've ever hired has easily been able to explain the difference between LEFT and INNER joins in an interview.
[1] https://stackoverflow.com/a/12824827
So yeah, I use SQL like https://xkcd.com/783/ . I'm sure you have a bunch of cool data analysis tools in there, but please just shut up, give me the contents of my table, and let me process it in a real programming language where I have map/reduce/filter as regular composable functions that I can test and reuse, with a syntax that doesn't make my eyes bleed.
Give you the contents of your table, so you can process it in the app tier with map/filter/reduce?!
Ah, the hubris of devs who honestly believe they can whip out a solution in a day that beats a dedicated army of developers singularly focused on the task of large data management, storage, and serialization. And almost always forgetting that serialization off of disk and over a network isn't free.
There's a reason why SQL is going on 50 years when most technologies are lucky to remain dominant past 10 in this industry. And if you think it's just because of inertia or lack of imagination, you're deluded. SQL isn't perfect (nothing is), but as a DSL for set theory, it does a damn good job, even 50 years later. Far better than any map/filter/reduce whipped up yet again by someone who doesn't fully understand the scope of the problems being solved.
It's doubly troubling when you can't grok that SELECT = map, WHERE = filter, and GROUP BY + aggregator = reduce. I sincerely hope you aren't avoiding JOIN by loading both tables ahead of time.
Not that it isn't valid in some cases. It is. If for example, you're using all of the table in your logic and need to filter on to do something like pull out certain items for a certain need. Sure. Then it is good, absolutely!
A "dedicated army of developers" who've been "going on 50 years" but whose flagship solutions are still single-point-of-failure, still noncompositional, still untestable, still have bizarre and incomprehensible performance characteristics. Yeah, no, I'm going to do stuff in regular application code, thanks.
In seriousness SQL databases can be good for ad-hoc exploratory queries, so having your data processing pipeline spit out a read-only SQL database dump on a regular schedule is worthwhile, but using that for anything more than prototyping is a mistake.
SQL is somewhat analogous to C. It is entirely legitimate to look at its archaic usability features and wonder if we could not do better and at the same time it has hit a sweet spot of adaptation to the domain that ensured its longevity.
Something that might shake up things a bit is graph databases / query languages. We can think of them as a generalization of the sql universe and an opportunity to modernize it
SQL is a real language. It is testable and reusable. It's a DSL, so the syntax isn't something you have to look at most of the time unless you're a DBA. That said, not everyone hates the syntax.
You're probably losing efficiency in your quest for functional paradigm perfection.
But yeah I guess we don't really have a good way to write automated tests for migrations that run outside of our application code.
DML is declarative. Like HTML. SQL is a DSL for set theory. Do you test your HTML like the rest of your code too?
But to be clear, Sqitch and pg_tap (off the top of my head) are both effective testing tools for Postgres databases. MS SQL and Oracle both have extensive testing frameworks available.
Any piece of software used incorrectly will seem "horribly bad".
> Wilfully obtuse syntax
This is an opinion.
> Incredibly poor compositionality.
Another opinion.
> No testability worth the name
I would argue that "try it, observe side effects and rollback" as a language feature is more testable than many/most programming languages.
> The deployment model is a half-baked pile of perl scripts, and that's if you're lucky.
>> Any piece of software used incorrectly will seem "horribly bad".
I appreciate that you hate it. I don't even think you should care or learn it if you don't want to. But I think positioning things you don't understand or _want to use_ as "horrible" or "bad" or whatever is acting in bad faith. You're allowed to say that you don't like something and don't want to learn how to use it, without suggesting it's the tool's fault.
> So yeah, I use SQL like https://xkcd.com/783/ . I'm sure you have a bunch of cool data analysis tools in there, but please just shut up, give me the contents of my table, and let me process it in a real programming language where I have map/reduce/filter as regular composable functions that I can test and reuse, with a syntax that doesn't make my eyes bleed.
This comment directly demonstrates "I don't understand, I don't want to, just let me do whatever I want however I want", which is fine, but again, not the tool's fault you don't want to use it.
Quoting again because it's also relevant to this last comment:
>> Any piece of software used incorrectly will seem "horribly bad".
Enjoy your vastly inefficient and expensive map/reduce configurations if that’s what you like.
- Has no way to deploy a specific historical version of the SQL (e.g. deploy this git tag)
- Has no automated checking that the SQL behaves as expected (e.g. this query with this test data should produce this output; if it doesn't, the git tag will not be created). Even checking that the SQL is syntactically valid is rare.
- Has no reuse of expressions smaller than a view/table
- Has not even basic type checking, e.g. something that alerts if you are combining an expression that might produce null with an expression that does not handle null well
- Has not even basic library/dependency management
- Has no practical structured values (since none of the infrastructure that would make using ad-hoc temporary tables safe exists). CTEs are an improvement but still thoroughly noncompositional since you can only put them in one place.
Let me guess, "these are solved problems" but no details of what you do to solve them, because none of the solutions actually work.
Can you clarify this? Do you mean the version of a specific schema/query?
> - Has no automated checking that the SQL behaves as expected (e.g. this query with this test data should produce this output; if it doesn't, the git tag will not be created). Even checking that the SQL is syntactically valid is rare.
This is part of the purpose of transactions [0]
> - Has no reuse of expressions smaller than a view/table
Incorrect [1][2][3]
> - Has not even basic type checking, e.g. something that alerts if you are combining an expression that might produce null with an expression that does not handle null well
Incorrect [4] [5]
> - Has not even basic library/dependency management
Incorrect [6]
> - Has no practical structured values (since none of the infrastructure that would make using ad-hoc temporary tables safe exists). CTEs are an improvement but still thoroughly noncompositional since you can only put them in one place.
Can you clarify? How is a transaction + CTE/subquery not sufficient?
> Let me guess, "these are solved problems" but no details of what you do to solve them, because none of the solutions actually work.
Replace "none of the solutions actually work" with "I don't know how to use them", and yes you're correct.
I'm happy to answer more of your questions in good faith, but I'm not really interested in debating a person with their head in the sand. Please let me know if I can help you, if you're actually interested. But also, as I've said, it's also fine to not want/not like this type of stuff. Different strokes and all that.
[0] https://www.tutorialspoint.com/sql/sql-transactions.htm
[1] https://www.postgresql.org/docs/current/sql-createfunction.h...
[2] https://www.postgresql.org/docs/current/sql-createprocedure....
[3] https://www.postgresql.org/docs/current/datatype-enum.html
[4] https://www.postgresql.org/docs/current/functions-conditiona...
[5] https://www.postgresqltutorial.com/postgresql-tutorial/postg...
[6] https://www.postgresql.org/download/products/6-postgresql-ex...
I mean being able to version a system implemented in "advanced SQL" the same way I'd version one implemented in an applications language. Make some changes, keep them in VCS, at some point decide to do a release and deploy. A few days later, discover some issue with the changed logic, roll back to the previous release of my "code" (without affecting the data, so not just restoring a database backup).
> This is part of the purpose of transactions [0]
Right, but the actual workflow tooling around how to use them for this is missing, and for whatever reason the culture that would build and standardise it seems to be missing too. Like, in most ecosystems there's a standardised test-edit cycle that everyone understands; you make your changes and then you run npm test or cargo test or whatever, and you get some assurance that your changes were correct, and that same tooling is also in control of your release workflow and will prevent or at least warn you if you try to do a release where your tests are failing.
> Incorrect [1][2][3]
Postgresql functions with composite values (and more generally the fact that composite values exist at all) sound like exactly what I was looking for, so that would be a big improvement if I could use them (although for the record they're not a standard SQL feature; MySQL functions can only return a scala value, so there's a major missing middle between functions and views). But even then they have the same problem as temporary tables/views of existing "globally", in the place you'd expect data rather than code to be, and deployment/use tooling being inadequate to use them safely (or at least widely perceived as such). Like, everywhere I've worked has had a de facto rule of "no DDL on the production database except for deliberate long-term changes to the schema that have gone through a review process", and I don't think that's unreasonable (maybe you do?).
> Incorrect [4] [5]
Those functions exist, I'm talking about having tooling that can tell you where you need to use them. Even in something like Python you have linters that will catch basic mistakes, and they're integrated into the workflow tooling so that you won't accidentally release without running them.
> Incorrect [6]
A list of libraries != library/dependency management.
> Can you clarify? How is a transaction + CTE/subquery not sufficient?
If I want to pull out an expression that appears in two arbitrary points in my query, and use it as a CTE, that requires a lot more thought than it would in most languages, because it goes differently depending on whether it was in the SELECT or the WHERE or the GROUP BY or.... The grammar is just somehow less consistent than most programming languages, and the scoping is more confusing, I think because it's kind of lexically backwards (like, you have to declare things to be able to use them, but a lot of the time the declaration goes after the usage. But not always!).
It's tough to follow these posts in part because SQL is a programming language used to interract with a database. It seems like you want to treat the SQL you write synonymously with the system you're writing it against, which is...I don't know...confusing? SQL is declarative, you say make it so and the underlying engine makes it so. I've had trouble parsing whether you dislike _writing SQL to interract with a database_ or _the way RDMS systems manage data_. The only "logic" that exists in most databases is surrounding constraints, which, if there's a bug in your constraint, you tell your database to update its schema, and it does so. You're free to store your SQL queries however you'd like, just like any other programming language.
> A few days later, discover some issue with the changed logic, roll back to the previous release of my "code" (without affecting the data, so not just restoring a database backup).
As is the nature of this conversation, this is just inherently not how these systems are intended to work. Asking for "new data but old shape" is legitimately ridiculous.
> Right, but the actual workflow tooling around how to use them for this is missing, and for whatever reason the culture that would build and standardise it seems to be missing too.
I guess I'm confused as to what "workflow tooling" you're looking for? Every RDMS supports multiple databases and schemas, which enables you to create and run test configurations with test data in real-world environments. A transaction enables you to test real queries on real data without risk.
> Like, in most ecosystems there's a standardised test-edit cycle that everyone understands; you make your changes and then you run npm test or cargo test or whatever, and you get some assurance that your changes were correct, and that same tooling is also in control of your release workflow and will prevent or at least warn you if you try to do a release where your tests are failing.
Sure, and in database systems this is the same. Craft a query out of a transaction, when it does what you want, commit it (either via a transaction or to normal source control). Everything you're asking for literally already exists, exactly how you're asking for it.
> Those functions exist, I'm talking about having tooling that can tell you where you need to use them. Even in something like Python you have linters that will catch basic mistakes, and they're integrated into the workflow tooling so that you won't accidentally release without running them.
The more I read what you write, the more it seems like you think you're unable to write SQL in a file and execute it against a database? Which...you can do...it happens all the time. I didn't bring it up because it's so obvious and common I thought there was some other misunderstanding.
> A list of libraries != library/dependency management.
I'm not sure what else you want? Your RDMS of choice maintains a list similar to a package.json containing dependencies and their versions, and you can interract with either that list directly, or with RDMS-specific commands similar to "npm install react". Again, what you're asking for literally exists exactly how you're asking for it.
> If I want to pull out an expression that appears in two arbitrary points in my query, and use it as a CTE, that requires a lot more thought than it would in most languages, because it goes differently depending on whether it was in the SELECT or the WHERE or the GROUP BY or....
If you open a java file, are you allowed to write arbitrary code wherever you want? No, that would be ridiculous. Enforcing some shape or structure on source is ...
Well, complex operations on data - even if those operations are just selection and aggregation - require logic. So either that logic lives in SQL, or it lives in application code.
> I guess I'm confused as to what "workflow tooling" you're looking for? Every RDMS supports multiple databases and schemas, which enables you to create and run test configurations with test data in real-world environments. A transaction enables you to test real queries on real data without risk.
So where is the equivalent of npm/cargo/maven? And where is the unit testing framework? Transactions are low-level functionality that you could build this tooling on top of - but as far as I can see no-one has, or at least not to the extent that it's standardized and accepted in the community. Where in RDBMS-land can I check out an existing project, make a small edit to one of the queries, and then run that project's tests to confirm I haven't broken it? A few projects have some support tools for doing this, but they're inevitably ad-hoc and unpolished.
> The more I read what you write, the more it seems like you think you're unable to write SQL in a file and execute it against a database? Which...you can do...it happens all the time.
I want a project with a bit more structure than a single file. And I want to share and reuse pieces between multiple projects rather than writing everything from scratch every time. Again, that's the low-level functionality, but where is the workflow tooling that actually builds on that to let you do day-to-day things in a standardised way?
> I'm not sure what else you want? Your RDMS of choice maintains a list similar to a package.json containing dependencies and their versions, and you can interract with either that list directly, or with RDMS-specific commands similar to "npm install react".
What? Where? You linked to a list for postgresql that literally has 12 packages available, total (I'm pretty sure I've published more packages than that in Maven central myself).
> If you open a java file, are you allowed to write arbitrary code wherever you want?
Not quite, but I can select any subexpression of an expression almost anywhere and pull it out into either a local variable or a function - usually by doing nothing more than hitting a key combo in my IDE. I haven't found anything like that for SQL.
At scale, databases are OFTEN used as glorified dumb bit buckets for performance purposes with heavy denormalization techniques.
Scale at high numbers adheres to no rules or off-the-shelf tools.
Relational algebra is good. I like the idea of nornalized data.
I hope to be able to use it one day instead of SQL.
This sentiment is like saying, "Java doesn't support traits, so I hate all programming languages."
If an engine doesn't support a feature you need, use a different engine. If your workplace does not allow this, that's an issue you have with your workplace, not the tools.
Sounds more like a personal phobia than a substantive critique of databases, especially ACID databases. You can test any query outside of a huge integration test by… running the query in a smaller, more targeted test suite. This can be done for performance checks, conformance, sanity, etc. The thing is, compared to most other programming languages, SQL is largely side effect free. As long as no one is dropping tables or indexes, the query you ran today will return the same result structure you run tomorrow. Obviously changes in the volume of data will affect performance, but that's true no matter what data store you use.
"But what about testing writes," you ask? Same deal. Start a transaction, run the INSERT/UPDATE/DELETE and then ROLLBACK. You get your performance timing, your ability to detect errors, and no permanent changes to your dataset.
Data storage and persistence is hard. It doesn't become easier using NoSQL or avoiding data altogether. But if data makes you personally uncomfortable, perhaps it's best you keep your distance. It greatly limits your career choices, but that's ultimately your choice.
> But if data makes you personally uncomfortable
I wouldn't say uncomfortable, simply bored. Not because I think database are bad tech, they are awesome. But we still haven't solved the impedance mismatch between programs and data, so any time you need to work with them, you spend 10% of your time on business logic and 90% on deployments, migrations, ORM and CRUD. Which is just something I'm lucky enough to be able to avoid. I don't like NoSQL either (just like I don't like dynamic typing). I don't think anyone can "avoid data" in programming, but my way of staying sane has been to avoid the web.
They can be terribly leaky abstractions. It seems that many ORMs end up re-implementing SQL in their own domain-specific language.
However, an understanding of SQL is often needed to debug and optimize ORM queries.
So why not just use SQL directly then?
There are some interesting things to note about object-relational mapping:
Honestly, if you are debating the use of an ORM, I would ask why. What is it that you are trying to get out of introducing an opaque abstraction between you and your data? It better be worth it, now and in the future, because it will probably take a good amount of effort to move away from it later.The trade of control for convenience is usually a good trade at first (during prototyping and early stages of feature development). Later, there is usually an inflection point where it becomes more of a hinderance than good. The trouble is, at that point, migration away can be too risky/costly to justify.
Finally, I have noticed that, generally, mature, well-functioning code bases tend to move away from traditional OO-patterns. Specifically, code and data tend to separate as code bases grow and age because it is easier to reason about. This tends to diminish the value of ORM further.
Overall, I've moved away from ORM on the whole, in favor of query -> struct mapping.