17 ms·

SQL is an absolutely amazing domain specific language, but people keep building ORMs & other SQL abstractors. I get building one as a learning or resume project

SQL is an absolutely amazing domain specific language, but people keep building ORMs & other SQL abstractors. I get building one as a learning or resume project. I don't see how it's useful in most scenarios. Like, there's some difficulty breaking up the queries in a logical way (which leads to duplication), so the solution is another library that you need to write or install and learn how to use? Even if it's easy to use, I can't imagine it'd take less time to read the documentation than to write some SQL. And then there's another dependency; Another thing to check if there's performance issues, another attack vector.

genericlemon24 5y ago
Sometimes you need dynamic SQL, because the DB doesn't have stored procedures you can use for the same purpose (e.g. SQLite). I talk more about this use case here: https://death.andgravity.com/query-builder-why#the-problem https://death.andgravity.com/query-builder-why#the-problem
laszlokorte 5y ago
The issue with plain SQL is simply that you can not compose queries at runtime. For example let user decide which columns to select, in which order to fetch the rows, which table to query etc. Or not even let the user decide but decide based on some config file or the system state. You end up with a bunch of string manipulation that is fragile and does not compose well. What solution is there except from simulating SQL via your languages nestable data structures?
- genericlemon24 5y ago
  Someone on Reddit suggested stored procedures, which seems like a good alternative. Alas, SQLite doesn't have them, so query building it is.
  - musingsole 5y ago
    Stored procedures are a nightmare that shepherd your application into an illegible, unmanageable monstrosity. Stored procedures are the slipperiest slope I've seen as a developer.
    - empthought 5y ago
      Sounds like you’re pretty bad at writing stored procedures.
      - freedomben 5y ago
        Sounds like you've never worked on a team.
    - BoxOfRain 5y ago
      That's a bit of an absolutist stance I think, a good programmer can use stored procedures just fine and in some cases it can even improve performance. Yes it makes it easier for bad programmers to write bad code, but bad programmers will write bad code no matter what tools they have in their toolbox.
    - taffer 5y ago
      In my experience, such problems only occur when people believe that common software engineering practices do not apply when writing stored procedures. Just have your stored procedures version controlled, tested, and deployed like any other code.
      - kbenson 5y ago
        The real problem is that you're almost always shifting work from a language that is well known and understood by you and/or your team to one that is less, or even poorly, understood and known, and you end up incurring the cost of novice programmers, which can be a real problem for both security and performance. If you have good knowledge and experience in the language your preferred version of SQL implements, that's good. If you just have people that understand how to optimize schemas and queries, you might find that you encounter some of the same problems as if you shelled out to somewhat large and complex bash scripts. The value of doing so over using your core app language is debatable.
  - laszlokorte 5y ago
    Except if you build a complete query builder as a stored procedure I do not see the problem solved. Simply stated the problem is: Viewing code as data (in a lisp sense) and transforming arbitrary data into a query that can be executed.
  - justsomeuser 5y ago
    With stored procedures you run your code inside of the SQL server process. With SQLite, your entire application IS the process, and your SQLite data moves from disk to your app processes RAM. I think the SQLite model is much better as you get to use your modern language and tooling (which is better than the language used for stored procedures which has not changed in 20 years, and is generally a bear to observe, test, develop).
- forinti 5y ago
  It would be easier to do that with a template engine.
  - genericlemon24 5y ago
    I cover this solution here: https://death.andgravity.com/own-query-builder#jinjasql-sqlpy-pugsql https://death.andgravity.com/own-query-builder#jinjasql-sqlp...
- sumtechguy 5y ago
  I propose that the issue is at the ODBC layer. We have 2 modes of using that thing. Tightly bound. Basically you have a bit of query string with question marks in it and you bind out your data points. Either for sending/recieving. Loosely bound. Here is a totally composed string ready to go just run it. Also a good way to make SQL injection attacks. Both involve string manipulation. I think the issue is the ODBC interface does not really map to what SQL is and does. The column binding sort of does. But not table, not where, not having, not sort, etc. So we end up re-inventing something that pastes over that. Building a string to feed into an API that feeds that over the network to a string parser on the other side to decompose it again then runs it and goes back the other way in a similar manner.
- stadium 5y ago
  Spark SQL is great for this. SQL strings and data frames code can be used interchangeably, and performance is the same for either type of query. https://spark.apache.org/docs/2.1.1/sql-programming-guide.html#overview https://spark.apache.org/docs/2.1.1/sql-programming-guide.ht...
- phs2501 5y ago
  This is my middle-ground solution for Python: https://github.com/bdowning/sql-athame https://github.com/bdowning/sql-athame Still fundamentally manipulating SQL text (which is a feature as I don't want to learn a full DSL), but it handles wrangling embedded placeholders while you're composing stuff and some other common compositional tasks. It's worked well for me anyway but I'm under no illusions it'd be right for everyone. Not an original concept regardless; my original version of this was in Node: https://github.com/bdowning/sql-assassin https://github.com/bdowning/sql-assassin, but a few years after I wrote that (and mostly didn't use it) I found https://github.com/gajus/slonik https://github.com/gajus/slonik which was very similar and much more fleshed-out; I rolled _some_ of its concepts and patterns into sql-athame.
  - genericlemon24 5y ago
    Hey, this has roughly the same philosophy as mine, I might add it to my survey: https://death.andgravity.com/own-query-builder#sqlbuilder-mini-psycopg2-sql https://death.andgravity.com/own-query-builder#sqlbuilder-mi...
- xeromal 5y ago
  Honestly though, how often does this scenario actually happen. Devs love to build for what if, but there's not a ton of scenarios where you need to build completely custom queries based on user input. A nice SPROC with some inputs can do most of that while maintaining performance
  - UglyToad 5y ago
    The main place I've run into this is a table with sorting, pagination and filtering while lazy loading data and I encounter this need a lot. With some runtime query builder most databases will have decent performance for the space between "too much to reasonably load in memory" to "we need a dedicated query service like elastic". Unfortunately taking the example of sort by X then by Y where X and Y are dynamic I don't know of a nice solution in SQL only in MSSQL 2012 or MySQL 5.
    - taeric 5y ago
      I think you are playing into the comment, though. You'd be surprised how far you can go with a table that doesn't support sorting and filtering. Basic pagination can often be foregone, if you can instead resort to offering export and let them use whatever software they are used to for this purpose. (Pagination is also just easier if you forego custom sort and filtering.) At some point, yes, you will want these things. But the basics can get you really far.
      - UglyToad 5y ago
        For sure you can offer a good approximation through SQL but I'm unlucky enough that I've needed to support dynamic sorting and query builders against tables at least 4 times for different datasets in the past years. I'm particularly proud of the last one I built that still absolutely flies despite doing wildcard querying on text fields (no full text index) with multiple combined queries and a dynamically computed status from aggregate child properties in MySQL 5 with its lack of window functions. The solution was basically dynamically construct an efficient as possible inner query before applying the joins. To clarify I believe in doing as much as possible in raw SQL and have a fondness for the stored procedure driven system I worked with but this problem remains unsolvable in SQL alone and I just keep encountering it.
- agent327 5y ago
  Something like C++'s {fmt}, specialized for SQL. fmt ("SELECT foo, bar FROM mytable WHERE id = {}", id); 'id' gets properly quoted, so that's not a concern anymore.
  - joshuamorton 5y ago
    This is simpler than what gp is asking for, they want the ability to dynamically change which columns are requested too, so change it to "foo, bar, baz", but still do so with reasonable safety.
    - agent327 5y ago
      That's supported too: fmt ("SELECT {:v}, {:v} FROM {:v} WHERE id = {}", colname1, colname2, tablename, id); The :v means "don't quote this, insert this string exactly as specified." It also has specialized handling for NULL values (i.e. it can generate 'IS NULL' instead of '= ...' if the value passed on is intended as a NULL value).
- GLGirty 5y ago
  > simulating SQL via your languages nestable data structures I've gone down this rabbit hole... you've described the in-house query engine I work with. Instead of sql, queries are nested json representing a dumbest-possible sql parse tree. It's only a subset of sql features, but it provides, as you point out, composability. By having the query as a data struct, all sorts of manipulations become possible... toggle fields for access control; recovery from dropped or renamed columns (schema has a changelog); auto-join related tables as required; etc. It's magic when it works but it suffers from the drawbacks noted elsewhere in the thread about traditional ORMS... onboarding new devs, debugging when something goes wrong. Adding new features to the dsl is a pain because the surface area is not just _the query_ but _all the queries it might form_ when composed with other queries in the system O_o
  - genericlemon24 5y ago
    > It's magic when it works but it suffers from the drawbacks noted elsewhere in the thread about traditional ORMS... (Not that there are lots of other devs on my project at the moment, but) I am managing this by aggressively limiting the size / features of my query builder, so it's (relatively) easy to understand just by looking at the code. If it ever passes that point, it's probably time to switch to a "real" query builder / ORM.
    - GLGirty 5y ago
      I'm in a similar environment--a very small crew. I'm curious what feature subset you settled with. Ours got as far as aggregate ops, and the complexity of that turned out to be 'hydra'. (Surely I've cut off the last head!) By that point we were painted into a corner with the in-house query builder... there was no off-the-rack ORM that had semantics for combining queries. (i.e. meld together query A and query B so that the result cells are union or intersection.) You seem familiar with the problem space, I'm curious what your experience was. What feature(s) made you draw the line and say, 'when we need it we'll migrate to X'? What are your "real ORM" candidates for X to bridge that gap?
nobody0 5y ago
Your point is valid, it make sense just speak SQL to DB in some scenarios . A good ORM will enable people to do that easily as well. However, good orms like LINQ in C# really shines in a lot of places.
- teh_klev 5y ago
  Wee nitpick, LINQ isn't an ORM, it's a querying API. Maybe you meant the original LINQ to SQL ORM which was deprecated in favour of Entity Framework (EF). > A good ORM will enable people to do that easily as well I agree. EF isn't a terrible ORM and covers many use cases developers encounter when interacting with a database server in your average line of business app. But you do need to keep an eye on the SQL it generates with more complex queries i.e. beware of cartesian hand grenades. Fortunately MS provides some guidance on this kind of thing, but sadly many developers often don't bother to read the docs, and this is why ORM's get a bad name: https://docs.microsoft.com/en-us/ef/core/performance/efficient-querying https://docs.microsoft.com/en-us/ef/core/performance/efficie...
yasserf 5y ago
I agree with your point that adding multiple layers = more attack vectors and abstraction of a really good domain specific language. But what seems to happen on most of the projects I work on is we end up hiding away extremely common logic behind helper functions. It always starts off with SQL and then slowly gets moved into higher level functions that offer a better developer experience. Shameless plug, but I just posted a library I wrote (for node https://github.com/vramework/postgres-typed/blob/master/README.md https://github.com/vramework/postgres-typed/blob/master/READ...) which pretty much is a tiny layer ontop of pg-node (which is query based / with value parameters) and provides small util functions with typescript support derived straight from postgres tables. In an ideal world (one I think we are getting very close to) I think we will end up having SQL queries validated in our code against the actual DB structure the same way we have any other compilation error. But until then we'll need to rely on tests or helper libraries, and for the purpose of refactoring and development I find the latter more enjoyable (although still far from perfect).
- mcdonje 5y ago
  >SQL queries validated in our code against the actual DB structure the same way we have any other compilation error Good point about validation. The ideal scenario you propose would indeed be ideal.
- x-shadowban 5y ago
  F# (and surely there are others) offers type providers, which can reach into your db schemas at compile time and typecheck linq-ish or even raw sql strings and the subsequent uses of the result set. But in the simplest case it makes compilation a function of not only source files but also db schema state. You can extract schema defs and point the type provider at the "cached" defs, but make sure rebuilding those is part of the build process.
- throwaway858 5y ago
  > In an ideal world (one I think we are getting very close to) I think we will end up having SQL queries validated in our code against the actual DB structure the same way we have any other compilation error. This library for TypeScript works exactly like this https://github.com/MedFlyt/mfsqlchecker https://github.com/MedFlyt/mfsqlchecker
BoxOfRain 5y ago
I feel a lot of arguments for ORMs boil down to "I can't really be arsed to learn SQL properly" which is fair enough from a developer time / performance ratio point of view, but not from a purely technical point of view. Like you say, I find there's way more mental friction dealing with new ORMs and wading through the documentation than there is in just going straight for raw SQL. There's less "magic" that way too which is always nice when you're debugging or optimising things. Different strokes for different folks I guess.
- huetius 5y ago
  Ymmv, but I like the both/and approach. I’ve seen high quality codebases where the benefits of both are leveraged: ORM for simple queries, idiomatic code reuse; raw sql for complex or performance critical queries. Being smart about when to use which is the key, but I have seen it work very well.
  - GVIrish 5y ago
    This is what I think is the reasonable take on ORMs. If you're using one because you don't wanna learn SQL, that is a bad reason that will most likely result in more work in the long run. But if you're using it to increase productivity for straightforward database calls, it can be a useful tool in a lot of scenarios.
- jasode 5y ago
  >I feel a lot of arguments for ORMs boil down to "I can't really be arsed to learn SQL properly" I learned SQL almost 30 years ago and used it in Oracle/Informix/Sybase/IBMDB2/MSSQL/MySQL/SQLite/etc and have written exam questions on SQL for outer-joins, nested subqueries, crosstab, etc. All that said, concatenating raw strings to form SQL that has no compile time type-checking is tedious and error prone. Thankfully, my "expert" knowledge of SQL does not blind me to the benefits of what a well-written ORM can do for productivity. (E.g. results caching, compile-time validation, auto-completion in the IDE, etc) The real technical reason why ORMs persist as a desirable feature is the decades-old technical concept of "working memory of field variables" that mirror the disk file table structure. In the older languages like 1960s COBOL, and later 1980s/1990s 4GL type of business languages such as dBASE, PowerBuilder, SAP ABAP... they all have data tables/records as a 1st-class language syntax without using any quoted strings: - In COBOL, you had RECORDS and later versions of COBOL had embedded SQL without quoted strings - dBASE/Foxpro had SCATTER/GATHER which is sort of like a built-in ORM to pull disk-based data from dbf files into memory - SAP ABAP has built-in SQL (no quoted raw strings required) with no manual typing of variables or structs to pull data into working memory The issue with "general purpose" languages like Python/Javascript/C++ is that SQL is not 1st-class language syntax to read database data into memory variables and write them back out. The SCATTER/GATHER concept has to be bolted-on as a library ... hence you get the reinvention of of the "db-->memory-->db" data read/write paradigm into Python via projects like SQLAlchemy. After tediously typing out hundreds of variable names or copy-pasting fields from SQL tables via strings such as ... db.execquery("SELECT cust_id, name, address FROM customer WHERE zipcode=12345;") ... it finally dawns on some programmers that this is mindless repetitive work when you have hundreds of tables. You end up writing a homemade ORM just to reduce the error-prone insanity. Yes, a lot of ORM projects are bad quality, with terrible performance, bugs, etc. But that it still doesn't change the fact that non-trivial projects with hundreds of tables and thousands of fields need some type of abstraction to reduce the mindless boilerplate of raw SQL strings.
awinter-py 5y ago
benefits of ORMs: - allow certain apps to work with any sql dialect (though tbh I question the value of this in the age of docker) - makes simple selects easier - enables plugins for web libraries I think the 3rd thing is the most valuable. If you're in an ecosystem like django / rails, you can consume plugins that integrate with your sql backend and your schema more or less automatically.
- wutwutwutwut 5y ago
  > tbh I question the value of this in the age of docker Haha. In the world outside of HN, big companies enforce specific database servers for reasons such as centralized monitoring, security, compliance and competence. Setting all this aside because of "docker" is classic HN.
mozey 5y ago
Doing it the other way around makes more sense, i.e. write SQL to generate code. See https://github.com/kyleconroy/sqlc https://github.com/kyleconroy/sqlc, why aren't more people following this approach?
- rrrrrrrrrrrryan 5y ago
  This is awesome. Composing a SQL query is often the easiest part. The issue is that throwing that static SELECT statement string in your code has a ton of downsides (as discussed everywhere in this thread). And fiddling with an ORM is tremendously frustrating when you're just trying to get it to compose the SQL you've already written in your head. For a dev who's bothered to learn SQL, flipping the thing on its head makes a ton of sense.
emidln 5y ago
I've written a lot raw SQL in the dialect my language supports. This is fine for static queries. When queries are dynamic (maybe they come from an admin panel or some other part with lots of optionality), static SQL isn't enough. You then get to do one of three things: ad-hoc string manipulation, rely on a query AST->compiler (like SQLAchemy's core or HoneySQL), or an ORM. ad-hoc string manipulation is a real security and reliability nightmare, and if you don't have a good standalone query AST to SQL compiler, an ORM is the next best thing.
- codetrotter 5y ago
  > ad-hoc string manipulation is a real security and reliability nightmare Parameters. A couple of examples: https://www.psycopg.org/docs/usage.html#passing-parameters-to-sql-queries https://www.psycopg.org/docs/usage.html#passing-parameters-t... https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php https://www.php.net/manual/en/mysqli.quickstart.prepared-sta...
  - lowercased 5y ago
    You can't order/sort by parameters.
  - jteppinette 5y ago
    How do you parametrize things like dynamic joins, where clauses, field selections, or aggregations without string manipulation or gross duplication?
  - emidln 5y ago
    Parameters have zero bearing on whether you should dynamically construct SQL strings. If parameters can solve all of your problems, you don't have a dynamic SQL query, you have user-submitted values in a WHERE clause. What can be parameterized depends wildly on the SQL database in question. I haven't used one that could parameterize table names (for use in dynamic JOINs or CTEs) and many cannot parameterize values outside of a WHERE clause. Dynamically selecting which function to call, clauses to add or subtract, and sort orders are just a slice of places parameters don't help. In short, parameters alone do no eliminate the need for a query builder. A good query builder should appropriately parameterize values as the underlying database supports and hopefully uses a type system or validation to constrict the domain of values it uses to construct parts of the expression that cannot be parameterized.
Cthulhu_ 5y ago
My main argument for ORMs is the mapping between SQL columns and your data model; having to write out SQL and code to load an SQL result set into your data model feels like a lot of duplication. And it gets a lot more difficult when you have more complicated data models spanning multiple database columns - that is, if your data model is like a document that has to be inserted and updated into a set of normalized database tables. That said, if that wasn't as much of an issue, or if I still had the patience and typing skills to write a lot of boring code, I wouldn't object too much. I'd also treat SQL as a first class citizen, so dedicated .sql files that the application will load or embed and use. That way, the SQL can be developed, maintained and run outside of the context of your application - editors can execute them directly to a (local) development database.
- jakearmitage 5y ago
  What if your SQL is your data model? I believe object-oriented design is forcing us to do a lot of duplication for very little gains, just like it did on game design and the industry started moving towards data-oriented design and entity-component system. The sooner we move away from the big lie that is DDD, the more productive we can be.
  - deleted 5y ago
    [deleted]
  - true_religion 5y ago
    I think if sql is your data model, then it’s a good case for a query builder. But at my company, our in house app is written using django but treats the django model asan interface to elasticsearch, Postgres, and redis. The django model is sub optimal for sql, but it’s a good base for interacting with the system as a whole.
- vlunkr 5y ago
  Agreed, lots of comments here saying that people don't want to learn SQL, I don't buy that argument. I think most people already know SQL because at some point it's inevitable, and it's also not that hard. The value of the ORM is what the name implies: mapping your data from rows into something convenient to use in your language. What people are really complaining about is ORMs that needlessly try to re-invent SQL, but they don't all do this. Someone else brought up Ecto, it's a brilliant ORM because the resulting SQL is always obvious.
greenie_beans 5y ago
you're missing the forest for the trees. this is a good python tutorial for somebody at my skill level
- genericlemon24 5y ago
  Hey, thanks for that!
emodendroket 5y ago
Yeah, everyone has a strong aversion to doing it but it's kind of self perpetuating. Once you get comfortable writing SQL to do everything, the ORMs feel like a frustrating game of trying to get the library to generate the SQL you have in mind.
jamamp 5y ago
I work for an enterprise in-house web application, where over time we have a ton of tables, like over several hundred unfortunately. Writing queries for each any every table and manually mapping those to objects sounds exhausting. EntityFramework helps a lot with that, for CRUD operations. Additionally, I think unit testing would benefit from an ORM as well. Instead of calling an actual database, you can easily have an in-memory database with an ORM with mocked data. Though, I suppose that is technically possibly with straight SQL queries as well, but I imagine it's easier with an ORM.
- furstenheim 5y ago
  The main problem with in memory databases is that they are not the same database. I've had several issues which I had to find a fix that worked on one database and was legal syntax on the other. Say maybe one database allows force index but in memory does not. I've even seen one database layer for in memory and a completely different one for real database. That means that your unit tests are not really testing your production code
  - zaybqp 5y ago
    You can also make the same argument about ORM's mock DB layer.
- zaybqp 5y ago
  How would it be easier with ORM when switching to in-memory database for standard SQL is just changing connection string?
zzzeek 5y ago
if you want to write one SQL query, you should not use a SQL builder and/or ORM. if you want to write thousands of SQL queries that naturally derive from an object model of 30, 100, or 800 domain classes or so and would otherwise be thousands of lines of repetitive boilerplate if all typed out individually, then you use a query builder and/or ORM. different tools for different jobs.
jrm4 5y ago
This all day. I teach intro Web App stuff (meaning I've never built anything huge) and it's just painful to be like: Hey everyone, look at this language with this incredibly intuitive and simple syntax, perhaps the most "easy to read and understand" thing you'll see in computing... ...and now here's a bunch of painful stupid garbage that you'll have to learn about, because industry has accreted a bunch of crap around it to make it turn on and work.
kiliancs 5y ago
I always like to bring up Ecto. Really nice sweet spot IMO. query = from u in "users", where: u.age > 18 or is_nil(u.email), select: %{name: u.name, age: u.age} # Returns maps as defined in select Repo.all(query) https://hexdocs.pm/ecto/Ecto.html#module-query https://hexdocs.pm/ecto/Ecto.html#module-query
Emanation 5y ago
I built one because I wanted to generate all my SQL scripts from a graphql schema. Handjamming SQL where statements for arbitrarily deeply nested types is insanity.

genericlemon24 5y ago

Sometimes you need dynamic SQL, because the DB doesn't have stored procedures you can use for the same purpose (e.g. SQLite). I talk more about this use case here: https://death.andgravity.com/query-builder-why#the-problem https://death.andgravity.com/query-builder-why#the-problem

laszlokorte 5y ago

The issue with plain SQL is simply that you can not compose queries at runtime. For example let user decide which columns to select, in which order to fetch the rows, which table to query etc. Or not even let the user decide but decide based on some config file or the system state. You end up with a bunch of string manipulation that is fragile and does not compose well. What solution is there except from simulating SQL via your languages nestable data structures?

genericlemon24 5y ago
Someone on Reddit suggested stored procedures, which seems like a good alternative. Alas, SQLite doesn't have them, so query building it is.
- musingsole 5y ago
  Stored procedures are a nightmare that shepherd your application into an illegible, unmanageable monstrosity. Stored procedures are the slipperiest slope I've seen as a developer.
  - empthought 5y ago
    Sounds like you’re pretty bad at writing stored procedures.
    - freedomben 5y ago
      Sounds like you've never worked on a team.
  - BoxOfRain 5y ago
    That's a bit of an absolutist stance I think, a good programmer can use stored procedures just fine and in some cases it can even improve performance. Yes it makes it easier for bad programmers to write bad code, but bad programmers will write bad code no matter what tools they have in their toolbox.
  - taffer 5y ago
    In my experience, such problems only occur when people believe that common software engineering practices do not apply when writing stored procedures. Just have your stored procedures version controlled, tested, and deployed like any other code.
    - kbenson 5y ago
      The real problem is that you're almost always shifting work from a language that is well known and understood by you and/or your team to one that is less, or even poorly, understood and known, and you end up incurring the cost of novice programmers, which can be a real problem for both security and performance. If you have good knowledge and experience in the language your preferred version of SQL implements, that's good. If you just have people that understand how to optimize schemas and queries, you might find that you encounter some of the same problems as if you shelled out to somewhat large and complex bash scripts. The value of doing so over using your core app language is debatable.
- laszlokorte 5y ago
  Except if you build a complete query builder as a stored procedure I do not see the problem solved. Simply stated the problem is: Viewing code as data (in a lisp sense) and transforming arbitrary data into a query that can be executed.
- justsomeuser 5y ago
  With stored procedures you run your code inside of the SQL server process. With SQLite, your entire application IS the process, and your SQLite data moves from disk to your app processes RAM. I think the SQLite model is much better as you get to use your modern language and tooling (which is better than the language used for stored procedures which has not changed in 20 years, and is generally a bear to observe, test, develop).
forinti 5y ago
It would be easier to do that with a template engine.
- genericlemon24 5y ago
  I cover this solution here: https://death.andgravity.com/own-query-builder#jinjasql-sqlpy-pugsql https://death.andgravity.com/own-query-builder#jinjasql-sqlp...
sumtechguy 5y ago
I propose that the issue is at the ODBC layer. We have 2 modes of using that thing. Tightly bound. Basically you have a bit of query string with question marks in it and you bind out your data points. Either for sending/recieving. Loosely bound. Here is a totally composed string ready to go just run it. Also a good way to make SQL injection attacks. Both involve string manipulation. I think the issue is the ODBC interface does not really map to what SQL is and does. The column binding sort of does. But not table, not where, not having, not sort, etc. So we end up re-inventing something that pastes over that. Building a string to feed into an API that feeds that over the network to a string parser on the other side to decompose it again then runs it and goes back the other way in a similar manner.
stadium 5y ago
Spark SQL is great for this. SQL strings and data frames code can be used interchangeably, and performance is the same for either type of query. https://spark.apache.org/docs/2.1.1/sql-programming-guide.html#overview https://spark.apache.org/docs/2.1.1/sql-programming-guide.ht...
phs2501 5y ago
This is my middle-ground solution for Python: https://github.com/bdowning/sql-athame https://github.com/bdowning/sql-athame Still fundamentally manipulating SQL text (which is a feature as I don't want to learn a full DSL), but it handles wrangling embedded placeholders while you're composing stuff and some other common compositional tasks. It's worked well for me anyway but I'm under no illusions it'd be right for everyone. Not an original concept regardless; my original version of this was in Node: https://github.com/bdowning/sql-assassin https://github.com/bdowning/sql-assassin, but a few years after I wrote that (and mostly didn't use it) I found https://github.com/gajus/slonik https://github.com/gajus/slonik which was very similar and much more fleshed-out; I rolled _some_ of its concepts and patterns into sql-athame.
- genericlemon24 5y ago
  Hey, this has roughly the same philosophy as mine, I might add it to my survey: https://death.andgravity.com/own-query-builder#sqlbuilder-mini-psycopg2-sql https://death.andgravity.com/own-query-builder#sqlbuilder-mi...
xeromal 5y ago
Honestly though, how often does this scenario actually happen. Devs love to build for what if, but there's not a ton of scenarios where you need to build completely custom queries based on user input. A nice SPROC with some inputs can do most of that while maintaining performance
- UglyToad 5y ago
  The main place I've run into this is a table with sorting, pagination and filtering while lazy loading data and I encounter this need a lot. With some runtime query builder most databases will have decent performance for the space between "too much to reasonably load in memory" to "we need a dedicated query service like elastic". Unfortunately taking the example of sort by X then by Y where X and Y are dynamic I don't know of a nice solution in SQL only in MSSQL 2012 or MySQL 5.
  - taeric 5y ago
    I think you are playing into the comment, though. You'd be surprised how far you can go with a table that doesn't support sorting and filtering. Basic pagination can often be foregone, if you can instead resort to offering export and let them use whatever software they are used to for this purpose. (Pagination is also just easier if you forego custom sort and filtering.) At some point, yes, you will want these things. But the basics can get you really far.
    - UglyToad 5y ago
      For sure you can offer a good approximation through SQL but I'm unlucky enough that I've needed to support dynamic sorting and query builders against tables at least 4 times for different datasets in the past years. I'm particularly proud of the last one I built that still absolutely flies despite doing wildcard querying on text fields (no full text index) with multiple combined queries and a dynamically computed status from aggregate child properties in MySQL 5 with its lack of window functions. The solution was basically dynamically construct an efficient as possible inner query before applying the joins. To clarify I believe in doing as much as possible in raw SQL and have a fondness for the stored procedure driven system I worked with but this problem remains unsolvable in SQL alone and I just keep encountering it.
agent327 5y ago
Something like C++'s {fmt}, specialized for SQL. fmt ("SELECT foo, bar FROM mytable WHERE id = {}", id); 'id' gets properly quoted, so that's not a concern anymore.
- joshuamorton 5y ago
  This is simpler than what gp is asking for, they want the ability to dynamically change which columns are requested too, so change it to "foo, bar, baz", but still do so with reasonable safety.
  - agent327 5y ago
    That's supported too: fmt ("SELECT {:v}, {:v} FROM {:v} WHERE id = {}", colname1, colname2, tablename, id); The :v means "don't quote this, insert this string exactly as specified." It also has specialized handling for NULL values (i.e. it can generate 'IS NULL' instead of '= ...' if the value passed on is intended as a NULL value).
GLGirty 5y ago
> simulating SQL via your languages nestable data structures I've gone down this rabbit hole... you've described the in-house query engine I work with. Instead of sql, queries are nested json representing a dumbest-possible sql parse tree. It's only a subset of sql features, but it provides, as you point out, composability. By having the query as a data struct, all sorts of manipulations become possible... toggle fields for access control; recovery from dropped or renamed columns (schema has a changelog); auto-join related tables as required; etc. It's magic when it works but it suffers from the drawbacks noted elsewhere in the thread about traditional ORMS... onboarding new devs, debugging when something goes wrong. Adding new features to the dsl is a pain because the surface area is not just _the query_ but _all the queries it might form_ when composed with other queries in the system O_o
- genericlemon24 5y ago
  > It's magic when it works but it suffers from the drawbacks noted elsewhere in the thread about traditional ORMS... (Not that there are lots of other devs on my project at the moment, but) I am managing this by aggressively limiting the size / features of my query builder, so it's (relatively) easy to understand just by looking at the code. If it ever passes that point, it's probably time to switch to a "real" query builder / ORM.
  - GLGirty 5y ago
    I'm in a similar environment--a very small crew. I'm curious what feature subset you settled with. Ours got as far as aggregate ops, and the complexity of that turned out to be 'hydra'. (Surely I've cut off the last head!) By that point we were painted into a corner with the in-house query builder... there was no off-the-rack ORM that had semantics for combining queries. (i.e. meld together query A and query B so that the result cells are union or intersection.) You seem familiar with the problem space, I'm curious what your experience was. What feature(s) made you draw the line and say, 'when we need it we'll migrate to X'? What are your "real ORM" candidates for X to bridge that gap?

nobody0 5y ago

Your point is valid, it make sense just speak SQL to DB in some scenarios . A good ORM will enable people to do that easily as well. However, good orms like LINQ in C# really shines in a lot of places.

teh_klev 5y ago
Wee nitpick, LINQ isn't an ORM, it's a querying API. Maybe you meant the original LINQ to SQL ORM which was deprecated in favour of Entity Framework (EF). > A good ORM will enable people to do that easily as well I agree. EF isn't a terrible ORM and covers many use cases developers encounter when interacting with a database server in your average line of business app. But you do need to keep an eye on the SQL it generates with more complex queries i.e. beware of cartesian hand grenades. Fortunately MS provides some guidance on this kind of thing, but sadly many developers often don't bother to read the docs, and this is why ORM's get a bad name: https://docs.microsoft.com/en-us/ef/core/performance/efficient-querying https://docs.microsoft.com/en-us/ef/core/performance/efficie...

yasserf 5y ago

I agree with your point that adding multiple layers = more attack vectors and abstraction of a really good domain specific language. But what seems to happen on most of the projects I work on is we end up hiding away extremely common logic behind helper functions. It always starts off with SQL and then slowly gets moved into higher level functions that offer a better developer experience. Shameless plug, but I just posted a library I wrote (for node https://github.com/vramework/postgres-typed/blob/master/README.md https://github.com/vramework/postgres-typed/blob/master/READ...) which pretty much is a tiny layer ontop of pg-node (which is query based / with value parameters) and provides small util functions with typescript support derived straight from postgres tables. In an ideal world (one I think we are getting very close to) I think we will end up having SQL queries validated in our code against the actual DB structure the same way we have any other compilation error. But until then we'll need to rely on tests or helper libraries, and for the purpose of refactoring and development I find the latter more enjoyable (although still far from perfect).

mcdonje 5y ago
>SQL queries validated in our code against the actual DB structure the same way we have any other compilation error Good point about validation. The ideal scenario you propose would indeed be ideal.
x-shadowban 5y ago
F# (and surely there are others) offers type providers, which can reach into your db schemas at compile time and typecheck linq-ish or even raw sql strings and the subsequent uses of the result set. But in the simplest case it makes compilation a function of not only source files but also db schema state. You can extract schema defs and point the type provider at the "cached" defs, but make sure rebuilding those is part of the build process.
throwaway858 5y ago
> In an ideal world (one I think we are getting very close to) I think we will end up having SQL queries validated in our code against the actual DB structure the same way we have any other compilation error. This library for TypeScript works exactly like this https://github.com/MedFlyt/mfsqlchecker https://github.com/MedFlyt/mfsqlchecker

BoxOfRain 5y ago

I feel a lot of arguments for ORMs boil down to "I can't really be arsed to learn SQL properly" which is fair enough from a developer time / performance ratio point of view, but not from a purely technical point of view. Like you say, I find there's way more mental friction dealing with new ORMs and wading through the documentation than there is in just going straight for raw SQL. There's less "magic" that way too which is always nice when you're debugging or optimising things. Different strokes for different folks I guess.

huetius 5y ago
Ymmv, but I like the both/and approach. I’ve seen high quality codebases where the benefits of both are leveraged: ORM for simple queries, idiomatic code reuse; raw sql for complex or performance critical queries. Being smart about when to use which is the key, but I have seen it work very well.
- GVIrish 5y ago
  This is what I think is the reasonable take on ORMs. If you're using one because you don't wanna learn SQL, that is a bad reason that will most likely result in more work in the long run. But if you're using it to increase productivity for straightforward database calls, it can be a useful tool in a lot of scenarios.
jasode 5y ago
>I feel a lot of arguments for ORMs boil down to "I can't really be arsed to learn SQL properly" I learned SQL almost 30 years ago and used it in Oracle/Informix/Sybase/IBMDB2/MSSQL/MySQL/SQLite/etc and have written exam questions on SQL for outer-joins, nested subqueries, crosstab, etc. All that said, concatenating raw strings to form SQL that has no compile time type-checking is tedious and error prone. Thankfully, my "expert" knowledge of SQL does not blind me to the benefits of what a well-written ORM can do for productivity. (E.g. results caching, compile-time validation, auto-completion in the IDE, etc) The real technical reason why ORMs persist as a desirable feature is the decades-old technical concept of "working memory of field variables" that mirror the disk file table structure. In the older languages like 1960s COBOL, and later 1980s/1990s 4GL type of business languages such as dBASE, PowerBuilder, SAP ABAP... they all have data tables/records as a 1st-class language syntax without using any quoted strings: - In COBOL, you had RECORDS and later versions of COBOL had embedded SQL without quoted strings - dBASE/Foxpro had SCATTER/GATHER which is sort of like a built-in ORM to pull disk-based data from dbf files into memory - SAP ABAP has built-in SQL (no quoted raw strings required) with no manual typing of variables or structs to pull data into working memory The issue with "general purpose" languages like Python/Javascript/C++ is that SQL is not 1st-class language syntax to read database data into memory variables and write them back out. The SCATTER/GATHER concept has to be bolted-on as a library ... hence you get the reinvention of of the "db-->memory-->db" data read/write paradigm into Python via projects like SQLAlchemy. After tediously typing out hundreds of variable names or copy-pasting fields from SQL tables via strings such as ... db.execquery("SELECT cust_id, name, address FROM customer WHERE zipcode=12345;") ... it finally dawns on some programmers that this is mindless repetitive work when you have hundreds of tables. You end up writing a homemade ORM just to reduce the error-prone insanity. Yes, a lot of ORM projects are bad quality, with terrible performance, bugs, etc. But that it still doesn't change the fact that non-trivial projects with hundreds of tables and thousands of fields need some type of abstraction to reduce the mindless boilerplate of raw SQL strings.

awinter-py 5y ago

benefits of ORMs: - allow certain apps to work with any sql dialect (though tbh I question the value of this in the age of docker) - makes simple selects easier - enables plugins for web libraries I think the 3rd thing is the most valuable. If you're in an ecosystem like django / rails, you can consume plugins that integrate with your sql backend and your schema more or less automatically.

wutwutwutwut 5y ago
> tbh I question the value of this in the age of docker Haha. In the world outside of HN, big companies enforce specific database servers for reasons such as centralized monitoring, security, compliance and competence. Setting all this aside because of "docker" is classic HN.

mozey 5y ago

Doing it the other way around makes more sense, i.e. write SQL to generate code. See https://github.com/kyleconroy/sqlc https://github.com/kyleconroy/sqlc, why aren't more people following this approach?

rrrrrrrrrrrryan 5y ago
This is awesome. Composing a SQL query is often the easiest part. The issue is that throwing that static SELECT statement string in your code has a ton of downsides (as discussed everywhere in this thread). And fiddling with an ORM is tremendously frustrating when you're just trying to get it to compose the SQL you've already written in your head. For a dev who's bothered to learn SQL, flipping the thing on its head makes a ton of sense.

emidln 5y ago

I've written a lot raw SQL in the dialect my language supports. This is fine for static queries. When queries are dynamic (maybe they come from an admin panel or some other part with lots of optionality), static SQL isn't enough. You then get to do one of three things: ad-hoc string manipulation, rely on a query AST->compiler (like SQLAchemy's core or HoneySQL), or an ORM. ad-hoc string manipulation is a real security and reliability nightmare, and if you don't have a good standalone query AST to SQL compiler, an ORM is the next best thing.

codetrotter 5y ago
> ad-hoc string manipulation is a real security and reliability nightmare Parameters. A couple of examples: https://www.psycopg.org/docs/usage.html#passing-parameters-to-sql-queries https://www.psycopg.org/docs/usage.html#passing-parameters-t... https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php https://www.php.net/manual/en/mysqli.quickstart.prepared-sta...
- lowercased 5y ago
  You can't order/sort by parameters.
- jteppinette 5y ago
  How do you parametrize things like dynamic joins, where clauses, field selections, or aggregations without string manipulation or gross duplication?
- emidln 5y ago
  Parameters have zero bearing on whether you should dynamically construct SQL strings. If parameters can solve all of your problems, you don't have a dynamic SQL query, you have user-submitted values in a WHERE clause. What can be parameterized depends wildly on the SQL database in question. I haven't used one that could parameterize table names (for use in dynamic JOINs or CTEs) and many cannot parameterize values outside of a WHERE clause. Dynamically selecting which function to call, clauses to add or subtract, and sort orders are just a slice of places parameters don't help. In short, parameters alone do no eliminate the need for a query builder. A good query builder should appropriately parameterize values as the underlying database supports and hopefully uses a type system or validation to constrict the domain of values it uses to construct parts of the expression that cannot be parameterized.

Cthulhu_ 5y ago

My main argument for ORMs is the mapping between SQL columns and your data model; having to write out SQL and code to load an SQL result set into your data model feels like a lot of duplication. And it gets a lot more difficult when you have more complicated data models spanning multiple database columns - that is, if your data model is like a document that has to be inserted and updated into a set of normalized database tables. That said, if that wasn't as much of an issue, or if I still had the patience and typing skills to write a lot of boring code, I wouldn't object too much. I'd also treat SQL as a first class citizen, so dedicated .sql files that the application will load or embed and use. That way, the SQL can be developed, maintained and run outside of the context of your application - editors can execute them directly to a (local) development database.

jakearmitage 5y ago
What if your SQL is your data model? I believe object-oriented design is forcing us to do a lot of duplication for very little gains, just like it did on game design and the industry started moving towards data-oriented design and entity-component system. The sooner we move away from the big lie that is DDD, the more productive we can be.
- deleted 5y ago
  [deleted]
- true_religion 5y ago
  I think if sql is your data model, then it’s a good case for a query builder. But at my company, our in house app is written using django but treats the django model asan interface to elasticsearch, Postgres, and redis. The django model is sub optimal for sql, but it’s a good base for interacting with the system as a whole.
vlunkr 5y ago
Agreed, lots of comments here saying that people don't want to learn SQL, I don't buy that argument. I think most people already know SQL because at some point it's inevitable, and it's also not that hard. The value of the ORM is what the name implies: mapping your data from rows into something convenient to use in your language. What people are really complaining about is ORMs that needlessly try to re-invent SQL, but they don't all do this. Someone else brought up Ecto, it's a brilliant ORM because the resulting SQL is always obvious.

greenie_beans 5y ago

you're missing the forest for the trees. this is a good python tutorial for somebody at my skill level

genericlemon24 5y ago
Hey, thanks for that!

emodendroket 5y ago

Yeah, everyone has a strong aversion to doing it but it's kind of self perpetuating. Once you get comfortable writing SQL to do everything, the ORMs feel like a frustrating game of trying to get the library to generate the SQL you have in mind.

jamamp 5y ago

I work for an enterprise in-house web application, where over time we have a ton of tables, like over several hundred unfortunately. Writing queries for each any every table and manually mapping those to objects sounds exhausting. EntityFramework helps a lot with that, for CRUD operations. Additionally, I think unit testing would benefit from an ORM as well. Instead of calling an actual database, you can easily have an in-memory database with an ORM with mocked data. Though, I suppose that is technically possibly with straight SQL queries as well, but I imagine it's easier with an ORM.

furstenheim 5y ago
The main problem with in memory databases is that they are not the same database. I've had several issues which I had to find a fix that worked on one database and was legal syntax on the other. Say maybe one database allows force index but in memory does not. I've even seen one database layer for in memory and a completely different one for real database. That means that your unit tests are not really testing your production code
- zaybqp 5y ago
  You can also make the same argument about ORM's mock DB layer.
zaybqp 5y ago
How would it be easier with ORM when switching to in-memory database for standard SQL is just changing connection string?

zzzeek 5y ago

if you want to write one SQL query, you should not use a SQL builder and/or ORM. if you want to write thousands of SQL queries that naturally derive from an object model of 30, 100, or 800 domain classes or so and would otherwise be thousands of lines of repetitive boilerplate if all typed out individually, then you use a query builder and/or ORM. different tools for different jobs.

jrm4 5y ago

This all day. I teach intro Web App stuff (meaning I've never built anything huge) and it's just painful to be like: Hey everyone, look at this language with this incredibly intuitive and simple syntax, perhaps the most "easy to read and understand" thing you'll see in computing... ...and now here's a bunch of painful stupid garbage that you'll have to learn about, because industry has accreted a bunch of crap around it to make it turn on and work.

kiliancs 5y ago

I always like to bring up Ecto. Really nice sweet spot IMO. query = from u in "users", where: u.age > 18 or is_nil(u.email), select: %{name: u.name, age: u.age} # Returns maps as defined in select Repo.all(query) https://hexdocs.pm/ecto/Ecto.html#module-query https://hexdocs.pm/ecto/Ecto.html#module-query

Emanation 5y ago

I built one because I wanted to generate all my SQL scripts from a graphql schema. Handjamming SQL where statements for arbitrarily deeply nested types is insanity.