5 ms·

Because "getting to know your data storage technology better" isn't the business goal. On the surface, that's a great goal to have, but taking an approach that

Because "getting to know your data storage technology better" isn't the business goal. On the surface, that's a great goal to have, but taking an approach that requires you to do 100x as much work so you can learn SQL better isn't software engineering. It's over-billing. I throw together 200 lines of schema in Prisma.io and it creates 20,000 lines of GraphQL handling code plus more ORM-specific code that allows me to create type-safe access to the database. And "the database" right now is PostgreSQL, but one of the business requirements is that "the database" could also be SQL Server or MySQL. Having learned the salient details of the ORM in ... hmm ... about three days? ... I feel that the advantages of getting a full GraphQL API that can be wired up to an arbitrary backend database for 99% of the queries vastly outweigh the fact that most of my database queries are entirely inside a "black box." In six months I've encountered exactly one problem where the black box made my life more difficult. I think I wasted about an hour, maybe 90 minutes, on tracking down why it didn't work as expected. Given that "writing custom SQL" for all of the queries that I've created would have required 10s of thousands of lines of additional code plus system tests to verify that the queries were all working as expected? That's a profoundly huge win. I'd still be writing SQL queries months from now if I had taken the approach you're suggesting. Maybe it's advice that's good for consulting firms that charge by the hour and love to find ways to make their developers work more hours, but in my book it's not even ethical to recommend.

andrewmcwatters 5y ago
I don’t necessarily agree with the approach, but I completely agree with the thinking. Thank you for sharing.
prirun 5y ago
I develop a backup program, HashBackup. It uses SQLite. I wrote all the SQL. Most of the SQL stmts are a few lines long. Another backup program, Duplicati, uses SQLite. Sometimes they post SQL that is having problems, and one query will go on forever, like 50-100 lines of SQL. Duplicati is written in C# so I'm guessing (but don't know) that it uses some kind of ORM. Figuring out why a 100-line SQL statement was slow would be very unfun. Not intending to dis Duplicati; just saying that black boxes are only good if they work 100% of the time, and my experience is, they don't. And any black box you can reverse engineer in an hour is not giving much productivity gain IMO.
- SomeCallMeTim 5y ago
  Writing, what, a couple dozen (at most) SQL statements that are custom designed for a single application? Not really a big deal. Sure, write your SQLite queries by hand. Fine. Whatever; I wouldn't even say it's inefficient if it's that few. Writing ~20 SQL queries for each of 20 different data structures (including various JOINs and search options) to create a CRUD API? And maintaining them each individually? And needing to make changes to the query every time the clients need to make a slightly different search request? When I can literally write the 20 different schemas in one file, type a build command, and get a fully featured GraphQL API with full JOIN and custom boolean logic search capability in a few seconds? I'm saving myself probably 40,000 lines of code, counting all of the data wrangling and test cases that I'd need to create to provide the minimal functionality that I would need--and the GraphQL code wouldn't need to be modified to change the search or order-by options of a particular query. And lines of code you don't write are lines of code you don't need to maintain. It's a huge win. Not even close. And that said, the above is based on a real project I'm currently involved with, and that project does have six custom GraphQL queries that I wrote by hand--most of which also leveraging the ORM to some degree. In exactly three cases, I wrote raw SQL queries that the ORM didn't support directly. So I'm going to say no, the black boxes don't need to work 100% of the time. For me they're working 99% of the time, and I write the last 1% by hand, which is just fine. If I had run across a terrible query in that one corner case, I'd probably not try to dig inside the black box; instead I'd create a custom query that does exactly what I want and expose it for that bit of functionality. Done. [1] As to the ORM used by Duplicati? Crappy ORMs exist. That doesn't mean all ORMs are bad. I can see the exact queries (minus parameters) scroll by in my log that the ORM I'm using is writing out. My own custom SQL queries tend to be more lines of code in practice, and none of the queries that it has generated so far have been slow. [1] Figuring out why a 100 line SQL statement is slow is generally not that hard: You put "EXPLAIN ANALYZE" in front of it, and it will point out where the time is going. That's a PostgreSQL feature, so you'd need to have a Postgres backend in order to use it. Good thing we're talking about using an ORM that could easily switch between SQLite and PostgreSQL! Optimize the schema and indices on PostgreSQL and then switch to SQLite for embedded work.

andrewmcwatters 5y ago

I don’t necessarily agree with the approach, but I completely agree with the thinking. Thank you for sharing.

prirun 5y ago

I develop a backup program, HashBackup. It uses SQLite. I wrote all the SQL. Most of the SQL stmts are a few lines long. Another backup program, Duplicati, uses SQLite. Sometimes they post SQL that is having problems, and one query will go on forever, like 50-100 lines of SQL. Duplicati is written in C# so I'm guessing (but don't know) that it uses some kind of ORM. Figuring out why a 100-line SQL statement was slow would be very unfun. Not intending to dis Duplicati; just saying that black boxes are only good if they work 100% of the time, and my experience is, they don't. And any black box you can reverse engineer in an hour is not giving much productivity gain IMO.

SomeCallMeTim 5y ago
Writing, what, a couple dozen (at most) SQL statements that are custom designed for a single application? Not really a big deal. Sure, write your SQLite queries by hand. Fine. Whatever; I wouldn't even say it's inefficient if it's that few. Writing ~20 SQL queries for each of 20 different data structures (including various JOINs and search options) to create a CRUD API? And maintaining them each individually? And needing to make changes to the query every time the clients need to make a slightly different search request? When I can literally write the 20 different schemas in one file, type a build command, and get a fully featured GraphQL API with full JOIN and custom boolean logic search capability in a few seconds? I'm saving myself probably 40,000 lines of code, counting all of the data wrangling and test cases that I'd need to create to provide the minimal functionality that I would need--and the GraphQL code wouldn't need to be modified to change the search or order-by options of a particular query. And lines of code you don't write are lines of code you don't need to maintain. It's a huge win. Not even close. And that said, the above is based on a real project I'm currently involved with, and that project does have six custom GraphQL queries that I wrote by hand--most of which also leveraging the ORM to some degree. In exactly three cases, I wrote raw SQL queries that the ORM didn't support directly. So I'm going to say no, the black boxes don't need to work 100% of the time. For me they're working 99% of the time, and I write the last 1% by hand, which is just fine. If I had run across a terrible query in that one corner case, I'd probably not try to dig inside the black box; instead I'd create a custom query that does exactly what I want and expose it for that bit of functionality. Done. [1] As to the ORM used by Duplicati? Crappy ORMs exist. That doesn't mean all ORMs are bad. I can see the exact queries (minus parameters) scroll by in my log that the ORM I'm using is writing out. My own custom SQL queries tend to be more lines of code in practice, and none of the queries that it has generated so far have been slow. [1] Figuring out why a 100 line SQL statement is slow is generally not that hard: You put "EXPLAIN ANALYZE" in front of it, and it will point out where the time is going. That's a PostgreSQL feature, so you'd need to have a Postgres backend in order to use it. Good thing we're talking about using an ORM that could easily switch between SQLite and PostgreSQL! Optimize the schema and indices on PostgreSQL and then switch to SQLite for embedded work.