16 ms·

I'm a Go developer and I notice a lot of other Go developers instantly suggest things like GORM to noobs writing applications. Whereas I always suggest against

I'm a Go developer and I notice a lot of other Go developers instantly suggest things like GORM to noobs writing applications. Whereas I always suggest against it. I'm a big advocate of understand your data model at the database level. Need to join on too many tables is too easy to do with an ORM. My go-to strategy for SQL is simple. Abstract your SQL as far away from your application as possible. Not just by using interfaces in your application, but by using stored procedures in your database. In my mind SQL is just another service your application depends on. And you want that interface to be as simple as possible for your application. If you need to tweak SQL performance it should not need any input from your application. I could completely migrate to a new table relationship structure without the application realising if that was what was needed. You could even go as far as to unit test just your SQL code if you wanted, something you can't do too easily when it's in your application. Yes, if you need to return new data then you need to update your stored proc and code. But that's so worth it in my opinion for that extra layer of abstraction. My opinion is slightly skewed from a decently sized business perspective, but I do still follow this pattern in personal projects. When migrating applications to different tech stacks (like Java to Go, or C# to Go) this abstraction has meant the world to us.

C1sc0cat 6y ago
I agree and for any serious database used in production SPROC's have so many advantages improved security as well.
lmilcin 6y ago
I have been working with Java for the past 20 years. I have noticed that over the years the knowledge of SQL eroded a lot in population of Java developers. Not only that, but the design of application seems to be lacking. Where problems could easily be solved with a little bit of efficient SQL people mindlessly accept huge performance losses due to ORM as if they just did not see other possibility.
- hackerfromthefu 6y ago
  It makes sense .. if all you have is a hammer .. The problem is exacerbated though by the amount of churn on styles of hammers, and newer developers are often too hammered to pickup other tools ..
- cptwunderlich 6y ago
  And layers of abstraction make it worse. Devs don't know SQL because Hibernate, now new devs rely on the "auto-magic" of Spring Data JPA and don't now Hibernate...
  - forinti 6y ago
    I've heard quite a few times the argument that by using Hibernate, you don't need to know SQL. But Hibernate's manual is huge.
- TeMPOraL 6y ago
  I've noticed this about few things. Regular expressions are another - they have this bad reputation as "write-only, magic one liner hacks" that should absolutely be avoided. The proposed alternative is usually one or more dozens of lines of manual string operations in a loop. It feels like it doesn't enter the minds of people in some companies I worked in that regular expressions are a separate language, one that you should learn.
guggle 6y ago
> but by using stored procedures in your database. This used to be the standard when clients used to connect directly to the database. Now that the world depends on web services, things are a little different and there's less incentive to maintain stored procs as an interface to your database. It's still a good idea though, with unparalleled performance. I suppose developers don't do it for a variety of reasons: They may lack the SQL knowledge, they may not want to maintain the extra code, they may fantasize about database portability, etc.
- Philip-J-Fry 6y ago
  The argument against it I usually see is "you can't version control SQL!" Where I work we have a custom system which manages releasing immutable SQL snapshots between environments and they get merged to master once in prod. The only thing needed is a process, and then version control is easy.
  - xupybd 6y ago
    Yeah you can just use a migration system and store your functions in text files checked into the version control. Nothing but the migrations are allowed in staging and production so everyone is forced to use it.
    - joeyjojo 6y ago
      As a front-end dev, I've been interested in learning about such a process but don't know where to look. What kind of tooling would you use to manage your migrations in a CI environment?
      - smada 6y ago
        ive used liquibase in the past (not affiliated) with great results https://www.liquibase.org https://www.liquibase.org
      - funcDropShadow 6y ago
        Flyway or Liquibase.
throw_m239339 6y ago
> I'm a Go developer and I notice a lot of other Go developers instantly suggest things like GORM to noobs writing applications. And there is nothing wrong with that. It's like recommending against the use of HTTP before people learn TCI/IP, or recommending against PHP/Apache just because people aren't really writing servers. Learning how to write a web app and learning database management are 2 separate topics. Furthermore, GORM is rather barebone, compared to Entity Framework or Hibernate, due to Go type limitations...
- Philip-J-Fry 6y ago
  I agree that ORMs lower the barrier of entry for creating a full stack app. But it's not just the suggestion of GORM. It's that it is often suggested as the solution and anything else is unviable. I think writing a web app and knowing how the database works are interlinked. It's incredibly valuable knowledge as a web developer IMO.
- BigJono 6y ago
  There's a big difference between HTTP, Apache, and ORMs. People not understanding why they're different is what sinks companies all the time. Using any abstraction is a gamble that it doesn't leak before you have the resources to fix it. HTTP is a terrible comparison because in almost every domain you will either make millions or (far more likely) fail for other reasons before you need to understand how TCP/IP works. I bet there's loads of companies that have died a slow death, releasing everything a month later than they want to, because the back-end developers aren't comfortable with whatever the stack of the day is and they were too busy learning yesterday's stack of the day instead of the intricacies of the language they're working with, or the standard library.
- fauigerzigerk 6y ago
  The thing is, RDBMS are set oriented. Manipulating sets of tuples is a very high level kind of thinking compared to manipulating individual objects and their relationships in a procedural manner. Of course sometimes the task is to write load/edit/store logic for individual records and their relationships. There's nothing wrong with using an ORM for that. But extending this sort of thinking to all data processing is extremely detrimental to both productivity and performance. If you're using an RDBMS you're well advised to apply a sets first approach to data handling. That's the danger of ORMs. They encourage people to do the wrong thing. They also add considerable complexity to a project. So after having written my own ORMs many years ago and using some of the popular ones I would stay away. The cases where they are the right tool for the job are just not worth the friction they create.
jayd16 6y ago
I salute your level of abstraction but I personally draw my line in the sand a bit higher. A library project for the data access layer with a stable app facing code API gives me the best bang for my buck in terms of flexibility and abstraction. That said, its easy for me to push out new code versions. I realize it doesn't work for everyone.
tdons 6y ago
So much this. ORMs? That way lies madness. For example if you use Hibernate in Java you can end up writing stuff like this: interface MyRepository { Page<SomeObject> findEntitiesByIdAndCreatedAtAfterOrderByCreatedAtDesc(int id, Instant after, Pageable page); } This abstracts away executing a query along the lines of: select * from entities where id = $1 and created_at >= $2 order by created_at desc I'll take the latter any day.
- register 6y ago
  That is Java. In .NET using linq with entity framework looks almost like SQL.
  - nullsense 6y ago
    Debatable. Method syntax vs query syntax is available. I mostly see method syntax in my codebase. I also see terribly performing linq queries all over the place. I think it's neither good nor bad, it's just a tool. Tools are useful for making things, but if wielded improperly they will hurt you. I'm sure same arguments can be made against raw SQL. It's possible to have good codebases in any paradigm, and bad codebases in any paradigm. It's really all down to the quality of implementation. As a side note if you want a creative way to backup a database just use try logging an EntityFramework tracked object in Serilog with the destructuring operator. Watch as the logging framework inadvertently causes an entire object graph traversing navigation properties endlessly make infinite SQL queries while your web server slowly runs out of ram. Fun times! Powerful tools are usually sharp.
- thesimon 6y ago
  While the abstraction might be a bit longer, I've found them relatively easy to write using IDE support (e.g. IntelliJ). Pageable adds of course a bit of functionalities, as does the convenience of passing an Instant instead of a String :)
- watt 6y ago
  Pageable also adds window functions (limit, offset). If you are going to compare, please be courteous and do it right. When app starts up, Java compiles these declarations and will abort if the declared function actually does not match the entities. This is a huge safety net, which you would not have when you write SQL. And also when you write SQL you have to take care and unpack the "select *" into your fields etc etc. I suspect this is much more important downside than you might realize.
bbarnett 6y ago
You're sadly fighting against the "but... but... it's how people DO it" mentality here. Not to mention, as computing expands, the average capability of people is dropping. Seats need to be filled, and those seats don't all need to be high quality devs. So ORMs are there to reduce the workload, in terms of what needs to be learned to be productive. Database tuning is becoming a lost art. (For DEVs not getting this, on a largish DB, I can literally write a single query which can take 10 seconds in MySQL's console, or hours. Yes, hours. This isn't a flaw, any more than doing a tree search wrong is a flaw in <insert language here> when it is slow. And most ORMs don't come close to properly handling all edge cases here.) Beyond all of that, there is another bonus to using stored procedures or functions. Security. There are many tables I want to give read only access to, yet want to allow writing in very limited scenarios. A stored procedure can have different user permissions (at least under most DBMS), which means you can: - make a table read only for a web application user - then, allow a stored procedure to perform INSERT/UPDATES, via a different user - however, let the web user execute the stored procedure The stored procedure thereby limiting the INSERT/UPDATE to a very specific action. Mostly, I've used this when dealing with tables/databases which have a financial component. Said stored procedures can even perform additional checks on other tables, whatever is required to ensure that the update == OK. Beyond that, it also allows for tiered security, in case you have someone in house, maybe a new dev, who decides they'd like to modify that financial table a bit. And beyond that, it also allows logging to be done at the DB level. While you are more limited here, in that you really do not want to be burdening the DB with more writes, you can definitely log all modifications to said financial table. (You could use triggers for this last bit too, of course, but still...)
- fiddlerwoaroof 6y ago
  > Not to mention, as computing expands, the average capability of people is dropping. Seats need to be filled, and those seats don't all need to be high quality devs. Also, a lot of businesses have analysts/data scientists that know SQL and have access to the DBs: stores procedures make business domain-level functionality more available to them because they don’t have to guess what application code is actually doing.
- commandlinefan 6y ago
thirtythree 6y ago
We do this in work and it's not great in my opinion. There can be issues with package versions compiled to the db. It makes continuously integration harder. We do automated builds and testing in the application layer but we don't have anything in the db layer. It's harder to create a new environment (for a developer who does not have full db permissions). Business logic ends up in the stored procedure layer AND the application layer.
- dagss 6y ago
  Including a real DB in continuous integration is a must for any development IMO. (And these days usually very easy to throw up a database using Docker -- each of our full CI runs runs in 2 minutes and does hundreds of "create database" calls and populate real SQL databases from scratch..) For instance: 1) You want to implement idempotency in your backend by using uniqueness constraints in your DB 2) That's important to test 3) That's not really tested unless you include a real DB After including a real DB in our CI setup I will never move back.
  - marcus_holmes 6y ago
    I use a Docker instance for localhost development, so much easier. There are so many problems that can occur in that communication to the database, it's invaluable to test it
- nicoburns 6y ago
  > for a developer who does not have full db permissions Are you using a commercial database (SQL Server, Oracle, or similar?). With open source databases this is non issue as developers can run a complete instance locally with full permissions, completely isolated from prod/staging servers.
  - hiram112 6y ago
    It looks like even the expensive commercial databases have docker images that would be okay for development purposes: * Oracle Express: https://github.com/oracle/docker-images/tree/master/OracleDatabase https://github.com/oracle/docker-images/tree/master/OracleDa... * Sql Server: https://hub.docker.com/_/microsoft-mssql-server https://hub.docker.com/_/microsoft-mssql-server * DB2 Community: https://hub.docker.com/r/ibmcom/db2 https://hub.docker.com/r/ibmcom/db2 * Sybase: https://hub.docker.com/r/nguoianphu/docker-sybase https://hub.docker.com/r/nguoianphu/docker-sybase
register 6y ago
I am against using stored procedures. In the projects I saw where SQL code was encapsulated by stored procedures the business logic was inconsistently split between the application and the database making maintenance much more complicated than it should have been. I am in favour of stored procedures to avoid several roundtrips to the database but only when performance really matters. The case on tweaking SQL performance does not convince me. Where I work we execute performance and load tests before every release of the application and we are expected to meet performance targets that are documented in the release notes.
- friend-monoid 6y ago
  I’m interested in this because I have the opposite experience. Not saying any choice is wrong, but in my experience, moving that logic into the database has made all our codebases easier to reason about. The API’s don’t have to botch error handling due to “open transaction need to be ROLLBACKed”, I don’t have to worry about bad devs doing stuff non-atomically and putting the db in a bad state, and the code is easier to read because it’s just a single query that does “what it’s supposed to do”. The API usually ends up just being a small adapter for DB functions. It also makes sense to me because the “database API” (e.g. the database schema with some methods in it - the tables themselves are usually not in the same schema) describes the valid operations of the data, and can make sure the data is consistent. It also makes adding new APIs easier.
  - teraku 6y ago
    I think what he has experiences is something I have also seen a lot of times (and am still seeing in my current company): Some developers put their application into SQL. That means, that the whole business logic of the app is SQL, and the app is just executing SQLs in order and does some front-ending. But I don't suspect that is what the original author meant.
  - register 6y ago
    >I don’t have to worry about bad devs doing stuff non-atomically and putting the db in a bad state Premise: I assume we are speaking of relational databases. The fact that a developer can "corrupt" the database with non-atomic stuff is an hint to me that the database probably doesn't have the right referential integrity constraints in place and probably is not normalized either. The relational model is built out of the box to keep a consistent state of the data and this is one of its main value propositions. Another danger that I didn't mention is that junior developers will be tempted to prefer "Turing like code" inside stored procedures (for loops,cursors, conditionals) instead of relational constructs (joins, subqueries,grouping,table variables). I have seen that several times and this is really a killer for database performances.
AmericanChopper 6y ago
Having worked as a DBA in numerous large financial institutions, and also as a SWE in a lot of node/python/MEAN-style startups. I can assure you that there are some very significant trade offs made with stored procs (or any business logic that is compiled to your database). It takes your one codebase and turns it into at least two seperate codebases. If you have a monolith, now you’ve got a distributed system. Any stored proc codebase is guranteed over time to become a maze of complexity and footguns. The more db features you use in this area, the worse it gets. If you’re using stored procs, then somebody is eventually going to create a table trigger. If a table trigger exists, then somebody will eventually write to that table not knowing it’s there. The complexity it adds to release management alone makes it not worthwhile in a lot of situations. It’s like doing full blown OOP design. Your codebase has to pass a certain (rather large) size before the complexity trade offs can even begin to make sense, and even then they often don’t.
- hackerfromthefu 6y ago
  >> Any stored proc codebase is guranteed over time to become a maze of complexity and footguns. I think this is a very valuable and important point and wanted to highlight it. >> Any stored proc codebase is guranteed over time to become a maze of complexity and footguns. There's no absolute law that states that SP using environments will always end up as a mess, but there should be such a law, because they do! I've worked across dozens to hundreds of enterprise environments, and the pattern is clear. I suppose it comes from personality and culture with the people working on db coding more often in the 'get it done' end of the spectrum vs programmers where you find a significant number interested in 'get it right'. An even stronger reason is probably that db coders are more likely to be autonomous instead of in large centrally driven teams. That central hierarchical organisation is a necessary but not sufficient condition for working to tame the entropy of large complex systems, without which they evolve into "a maze of complexity and footguns".
  - marcus_holmes 6y ago
    yeah, I don't think this is a problem with sprocs, per se. I think this is a problem because the database code is treated differently from the application code, because so few coders understand SQL well enough to be comfortable in it. So, as you say, the DBA's tend to be autonomous, and held to different standards. And with less ability to say "we have tech debt, we need to refactor our sprocs" because of the knock-on effect on the application code. It's a management problem rather than a technical certainty, I think.
buro9 6y ago
There is a middle-ground... helpers. Things like search interfaces might offer filters over multiple columns... your choices here: 1. Use an ORM 2. Write SQL for each permutation of filter 3. Write a helper that will compose the SQL based on the filters needed And that 3rd one is the only time I stray from plain SQL towards something that looks like a lightweight abstraction over the SQL. Similarly... stored procs, I avoid these but there are times when they are necessary. If you need the boundary of a transaction to span multiple SQL calls then it is safer to do so within a stored proc. Yes you can use a transaction and rollback, but from a maintainance perspective I've seen refactors break transactions that were not fully encapsulated in a single Go (insert other language) function . Yes keep things simple with SQL and do everything in the application... but also break those rules rather than do something daft or with side effects.
- ashtonian 6y ago
  Or use something lightweight and configurable like https://github.com/a8m/rql https://github.com/a8m/rql to just handle filters.
- nullsense 6y ago
  Seeing the word "helpers" in a codebase gives me PTSD flashbacks.
marcus_holmes 6y ago
Came here to say exactly this. Also a gopher, who is also vaguely horrified at all the "use GORM, and a framework" advice to newbie gophers. I also use stored procedures, and treat them as function calls from my application code. So I effectively build a "data storage API" in the database, that I then call (as you say, like any other service) from my code. I also have views for all the data sets that will be returned to the application, so I can use them easily as a type for function returns, and enforce consistency.
EdgarVerona 6y ago
This is the approach I like as well, with the caveat that the stores procedures themselves should be kept as dumb and as late in the process as possible. I have made many mistakes in my career in terms of systems that were either overly complicated or inefficient or both, and it has almost always ended up boiling down to either trusting the SQL that an ORM produces, or putting too much of the business logic in hand crafted stored procedures. I have done both, regretted both, and nowadays advocate for sticking to simple, predictable, and dumb stored procedures so that you can both have the benefit of knowing/controlling exactly what will execute in the database and having things like validation occur code side whenever possible. In the real world, that simplification of stored procedures isn't always possible. But if it's your starting assumption, then you can fight for it until you are certain that there isn't a practical alternative to pushing the logic into the stored procedure. And if a compromise of these principles needs to happen, I would push complexity into a stored procedure before trusting the SQL produced by an ORM every time. But where I fall on that spectrum may just be due to the severity and types of problems I have seen happen with each approach, do YMMV. And I wouldn't go so far as to call myself an expert, so also YMMV on that front. I am just a guy who has made a lot of mistakes. But if it'll help others, this is my takeaway.
baq 6y ago
how do you unit test your stored procedures on a CI agent? serious question - this is nigh impossible in every db i've ever used.
- dkersten 6y ago
  Run the DB in docker, import the stored procedures as part of the setup migrations and run tests against that from your normal test suite.
davedx 6y ago
> You could even go as far as to unit test just your SQL code if you wanted, something you can't do too easily when it's in your application. How do you unit test SQL code? You could integration test against something like sqlite but unit testing stored procs sounds very difficult. I worked with a big C# .net core ecommerce platform that had a big stored proc used for ingest. It was an absolute pain in the ass to debug and fix issues because testing it was so difficult. Eventually we replaced it with C# code. Access to full software dev tools (debugger, automated tests) is really something you don't want to be without on large projects...
- hans_castorp 6y ago
  > How do you unit test SQL code? e.g. https://pgtap.org/documentation.html https://pgtap.org/documentation.html There are similar solutions for Oracle's PL/SQL and SQL Server
Dave3of5 6y ago
Just a friendly reminder to anyone reading this about v1 of GORM. It doesn't properly support JOINS: https://github.com/go-gorm/gorm/issues/1436 https://github.com/go-gorm/gorm/issues/1436 Note that there is some work for v2 in allowing users to create JOIN queries but without JOIN support it's effectively unusable in anything other than very small databases.
- giantDinosaur 6y ago
  wait, what? doesn't support joins? Things like this make me doubt my fundamentals. 'Are joins really necessary?' is the question now running through my mind ;-)
  - Dave3of5 6y ago
    It does not support joins. The expectation is that you "Preload" your data which is a fancy way of saying load it all into memory. The performance of this is trash so don't use that ORM there are a few better ORMs for GO mentioned in the issue. Yes joins are really necessary in SQL they are a foundational part of using any relational database.
    - giantDinosaur 6y ago
      Yes, they're literally the second thing one learns about SQL, really. I'm not being too serious about rethinking whether they're necessary, to be clear! What a strange design decision though. I don't really understand why it is doing this? Why would anyone really think it's a good idea?
      - Dave3of5 6y ago
        > I'm not being too serious about rethinking whether they're necessary, to be clear! My apologies if I came off condescending, it's hard to impart tone of voice in text. > I don't really understand why it is doing this? I think it's because it makes the SQL Generation harder. I think the lib at the moment is simply a search and replace on a bunch of SQL strings.
sshb 6y ago
I've recently came up with following approach: make it easy to generate CRUD applications without ORM's, and then modify them any way you like without extra dependencies. Made a tool to achieve just that: https://github.com/sashabaranov/pike/ https://github.com/sashabaranov/pike/
_me_go_too_ 6y ago
>> I'm a Go developer and I notice a lot of other Go developers instantly suggest things like GORM to noobs writing applications I've made the opposite observation. Compared to Python, C#, Java and NodeJS, ORMs are actually not a big thing in Go. Which is good - I agree with you.
Cthulhu_ 6y ago
I'm trying Gorm right now, and I'm running into some serious issues where it's not as straightforward as an ORM should be (mostly having to do with transparently saving / updating relationships). I'm reluctant to go to straight SQL because then I have to: - Write a ton of 'mapping' code between resultsets and data model - Do a lot of diffing to determine which relations to add, update or remove - Manage my own database migrations - challenging because I'm stuck with using sqlite - etc I want my tools to make things easier for me, but gorm seems to be the "now you have two problems" direction - for now. Maybe I just don't get it yet. Maybe I just don't get databases anymore either.
- vptr 6y ago
  Depending on which DB you're using, I'd suggest trying out https://sqlc.dev https://sqlc.dev instead of gorm if on PG. Mysql support is a bit flaky last time I checked.
hodgesrm 6y ago
Your view is the original design idea behind stored procedures. Stored procedures as far as I know were first implemented in Sybase SQL Server. We tended to think of them as enabling RPC (remote procedure call) access to the DBMS. Sybase was also the first major relational database to offer client access through the network. Our connectivity was influenced by distributed system concepts like RPCs that were top-of-mind in the 1980s.

C1sc0cat 6y ago

I agree and for any serious database used in production SPROC's have so many advantages improved security as well.

lmilcin 6y ago

I have been working with Java for the past 20 years. I have noticed that over the years the knowledge of SQL eroded a lot in population of Java developers. Not only that, but the design of application seems to be lacking. Where problems could easily be solved with a little bit of efficient SQL people mindlessly accept huge performance losses due to ORM as if they just did not see other possibility.

hackerfromthefu 6y ago
It makes sense .. if all you have is a hammer .. The problem is exacerbated though by the amount of churn on styles of hammers, and newer developers are often too hammered to pickup other tools ..
cptwunderlich 6y ago
And layers of abstraction make it worse. Devs don't know SQL because Hibernate, now new devs rely on the "auto-magic" of Spring Data JPA and don't now Hibernate...
- forinti 6y ago
  I've heard quite a few times the argument that by using Hibernate, you don't need to know SQL. But Hibernate's manual is huge.
TeMPOraL 6y ago
I've noticed this about few things. Regular expressions are another - they have this bad reputation as "write-only, magic one liner hacks" that should absolutely be avoided. The proposed alternative is usually one or more dozens of lines of manual string operations in a loop. It feels like it doesn't enter the minds of people in some companies I worked in that regular expressions are a separate language, one that you should learn.

guggle 6y ago

> but by using stored procedures in your database. This used to be the standard when clients used to connect directly to the database. Now that the world depends on web services, things are a little different and there's less incentive to maintain stored procs as an interface to your database. It's still a good idea though, with unparalleled performance. I suppose developers don't do it for a variety of reasons: They may lack the SQL knowledge, they may not want to maintain the extra code, they may fantasize about database portability, etc.

Philip-J-Fry 6y ago
The argument against it I usually see is "you can't version control SQL!" Where I work we have a custom system which manages releasing immutable SQL snapshots between environments and they get merged to master once in prod. The only thing needed is a process, and then version control is easy.
- xupybd 6y ago
  Yeah you can just use a migration system and store your functions in text files checked into the version control. Nothing but the migrations are allowed in staging and production so everyone is forced to use it.
  - joeyjojo 6y ago
    As a front-end dev, I've been interested in learning about such a process but don't know where to look. What kind of tooling would you use to manage your migrations in a CI environment?
    - smada 6y ago
      ive used liquibase in the past (not affiliated) with great results https://www.liquibase.org https://www.liquibase.org
    - funcDropShadow 6y ago
      Flyway or Liquibase.

throw_m239339 6y ago

> I'm a Go developer and I notice a lot of other Go developers instantly suggest things like GORM to noobs writing applications. And there is nothing wrong with that. It's like recommending against the use of HTTP before people learn TCI/IP, or recommending against PHP/Apache just because people aren't really writing servers. Learning how to write a web app and learning database management are 2 separate topics. Furthermore, GORM is rather barebone, compared to Entity Framework or Hibernate, due to Go type limitations...

Philip-J-Fry 6y ago
I agree that ORMs lower the barrier of entry for creating a full stack app. But it's not just the suggestion of GORM. It's that it is often suggested as the solution and anything else is unviable. I think writing a web app and knowing how the database works are interlinked. It's incredibly valuable knowledge as a web developer IMO.
BigJono 6y ago
There's a big difference between HTTP, Apache, and ORMs. People not understanding why they're different is what sinks companies all the time. Using any abstraction is a gamble that it doesn't leak before you have the resources to fix it. HTTP is a terrible comparison because in almost every domain you will either make millions or (far more likely) fail for other reasons before you need to understand how TCP/IP works. I bet there's loads of companies that have died a slow death, releasing everything a month later than they want to, because the back-end developers aren't comfortable with whatever the stack of the day is and they were too busy learning yesterday's stack of the day instead of the intricacies of the language they're working with, or the standard library.
fauigerzigerk 6y ago
The thing is, RDBMS are set oriented. Manipulating sets of tuples is a very high level kind of thinking compared to manipulating individual objects and their relationships in a procedural manner. Of course sometimes the task is to write load/edit/store logic for individual records and their relationships. There's nothing wrong with using an ORM for that. But extending this sort of thinking to all data processing is extremely detrimental to both productivity and performance. If you're using an RDBMS you're well advised to apply a sets first approach to data handling. That's the danger of ORMs. They encourage people to do the wrong thing. They also add considerable complexity to a project. So after having written my own ORMs many years ago and using some of the popular ones I would stay away. The cases where they are the right tool for the job are just not worth the friction they create.

jayd16 6y ago

I salute your level of abstraction but I personally draw my line in the sand a bit higher. A library project for the data access layer with a stable app facing code API gives me the best bang for my buck in terms of flexibility and abstraction. That said, its easy for me to push out new code versions. I realize it doesn't work for everyone.

tdons 6y ago

So much this. ORMs? That way lies madness. For example if you use Hibernate in Java you can end up writing stuff like this: interface MyRepository { Page<SomeObject> findEntitiesByIdAndCreatedAtAfterOrderByCreatedAtDesc(int id, Instant after, Pageable page); } This abstracts away executing a query along the lines of: select * from entities where id = $1 and created_at >= $2 order by created_at desc I'll take the latter any day.

register 6y ago
That is Java. In .NET using linq with entity framework looks almost like SQL.
- nullsense 6y ago
  Debatable. Method syntax vs query syntax is available. I mostly see method syntax in my codebase. I also see terribly performing linq queries all over the place. I think it's neither good nor bad, it's just a tool. Tools are useful for making things, but if wielded improperly they will hurt you. I'm sure same arguments can be made against raw SQL. It's possible to have good codebases in any paradigm, and bad codebases in any paradigm. It's really all down to the quality of implementation. As a side note if you want a creative way to backup a database just use try logging an EntityFramework tracked object in Serilog with the destructuring operator. Watch as the logging framework inadvertently causes an entire object graph traversing navigation properties endlessly make infinite SQL queries while your web server slowly runs out of ram. Fun times! Powerful tools are usually sharp.
thesimon 6y ago
While the abstraction might be a bit longer, I've found them relatively easy to write using IDE support (e.g. IntelliJ). Pageable adds of course a bit of functionalities, as does the convenience of passing an Instant instead of a String :)
watt 6y ago
Pageable also adds window functions (limit, offset). If you are going to compare, please be courteous and do it right. When app starts up, Java compiles these declarations and will abort if the declared function actually does not match the entities. This is a huge safety net, which you would not have when you write SQL. And also when you write SQL you have to take care and unpack the "select *" into your fields etc etc. I suspect this is much more important downside than you might realize.

bbarnett 6y ago

You're sadly fighting against the "but... but... it's how people DO it" mentality here. Not to mention, as computing expands, the average capability of people is dropping. Seats need to be filled, and those seats don't all need to be high quality devs. So ORMs are there to reduce the workload, in terms of what needs to be learned to be productive. Database tuning is becoming a lost art. (For DEVs not getting this, on a largish DB, I can literally write a single query which can take 10 seconds in MySQL's console, or hours. Yes, hours. This isn't a flaw, any more than doing a tree search wrong is a flaw in <insert language here> when it is slow. And most ORMs don't come close to properly handling all edge cases here.) Beyond all of that, there is another bonus to using stored procedures or functions. Security. There are many tables I want to give read only access to, yet want to allow writing in very limited scenarios. A stored procedure can have different user permissions (at least under most DBMS), which means you can: - make a table read only for a web application user - then, allow a stored procedure to perform INSERT/UPDATES, via a different user - however, let the web user execute the stored procedure The stored procedure thereby limiting the INSERT/UPDATE to a very specific action. Mostly, I've used this when dealing with tables/databases which have a financial component. Said stored procedures can even perform additional checks on other tables, whatever is required to ensure that the update == OK. Beyond that, it also allows for tiered security, in case you have someone in house, maybe a new dev, who decides they'd like to modify that financial table a bit. And beyond that, it also allows logging to be done at the DB level. While you are more limited here, in that you really do not want to be burdening the DB with more writes, you can definitely log all modifications to said financial table. (You could use triggers for this last bit too, of course, but still...)

fiddlerwoaroof 6y ago
> Not to mention, as computing expands, the average capability of people is dropping. Seats need to be filled, and those seats don't all need to be high quality devs. Also, a lot of businesses have analysts/data scientists that know SQL and have access to the DBs: stores procedures make business domain-level functionality more available to them because they don’t have to guess what application code is actually doing.
commandlinefan 6y ago

thirtythree 6y ago

We do this in work and it's not great in my opinion. There can be issues with package versions compiled to the db. It makes continuously integration harder. We do automated builds and testing in the application layer but we don't have anything in the db layer. It's harder to create a new environment (for a developer who does not have full db permissions). Business logic ends up in the stored procedure layer AND the application layer.

dagss 6y ago
Including a real DB in continuous integration is a must for any development IMO. (And these days usually very easy to throw up a database using Docker -- each of our full CI runs runs in 2 minutes and does hundreds of "create database" calls and populate real SQL databases from scratch..) For instance: 1) You want to implement idempotency in your backend by using uniqueness constraints in your DB 2) That's important to test 3) That's not really tested unless you include a real DB After including a real DB in our CI setup I will never move back.
- marcus_holmes 6y ago
  I use a Docker instance for localhost development, so much easier. There are so many problems that can occur in that communication to the database, it's invaluable to test it
nicoburns 6y ago
> for a developer who does not have full db permissions Are you using a commercial database (SQL Server, Oracle, or similar?). With open source databases this is non issue as developers can run a complete instance locally with full permissions, completely isolated from prod/staging servers.
- hiram112 6y ago
  It looks like even the expensive commercial databases have docker images that would be okay for development purposes: * Oracle Express: https://github.com/oracle/docker-images/tree/master/OracleDatabase https://github.com/oracle/docker-images/tree/master/OracleDa... * Sql Server: https://hub.docker.com/_/microsoft-mssql-server https://hub.docker.com/_/microsoft-mssql-server * DB2 Community: https://hub.docker.com/r/ibmcom/db2 https://hub.docker.com/r/ibmcom/db2 * Sybase: https://hub.docker.com/r/nguoianphu/docker-sybase https://hub.docker.com/r/nguoianphu/docker-sybase

I am against using stored procedures. In the projects I saw where SQL code was encapsulated by stored procedures the business logic was inconsistently split between the application and the database making maintenance much more complicated than it should have been. I am in favour of stored procedures to avoid several roundtrips to the database but only when performance really matters. The case on tweaking SQL performance does not convince me. Where I work we execute performance and load tests before every release of the application and we are expected to meet performance targets that are documented in the release notes.

friend-monoid 6y ago
I’m interested in this because I have the opposite experience. Not saying any choice is wrong, but in my experience, moving that logic into the database has made all our codebases easier to reason about. The API’s don’t have to botch error handling due to “open transaction need to be ROLLBACKed”, I don’t have to worry about bad devs doing stuff non-atomically and putting the db in a bad state, and the code is easier to read because it’s just a single query that does “what it’s supposed to do”. The API usually ends up just being a small adapter for DB functions. It also makes sense to me because the “database API” (e.g. the database schema with some methods in it - the tables themselves are usually not in the same schema) describes the valid operations of the data, and can make sure the data is consistent. It also makes adding new APIs easier.
- teraku 6y ago
  I think what he has experiences is something I have also seen a lot of times (and am still seeing in my current company): Some developers put their application into SQL. That means, that the whole business logic of the app is SQL, and the app is just executing SQLs in order and does some front-ending. But I don't suspect that is what the original author meant.
- register 6y ago
  >I don’t have to worry about bad devs doing stuff non-atomically and putting the db in a bad state Premise: I assume we are speaking of relational databases. The fact that a developer can "corrupt" the database with non-atomic stuff is an hint to me that the database probably doesn't have the right referential integrity constraints in place and probably is not normalized either. The relational model is built out of the box to keep a consistent state of the data and this is one of its main value propositions. Another danger that I didn't mention is that junior developers will be tempted to prefer "Turing like code" inside stored procedures (for loops,cursors, conditionals) instead of relational constructs (joins, subqueries,grouping,table variables). I have seen that several times and this is really a killer for database performances.

AmericanChopper 6y ago

Having worked as a DBA in numerous large financial institutions, and also as a SWE in a lot of node/python/MEAN-style startups. I can assure you that there are some very significant trade offs made with stored procs (or any business logic that is compiled to your database). It takes your one codebase and turns it into at least two seperate codebases. If you have a monolith, now you’ve got a distributed system. Any stored proc codebase is guranteed over time to become a maze of complexity and footguns. The more db features you use in this area, the worse it gets. If you’re using stored procs, then somebody is eventually going to create a table trigger. If a table trigger exists, then somebody will eventually write to that table not knowing it’s there. The complexity it adds to release management alone makes it not worthwhile in a lot of situations. It’s like doing full blown OOP design. Your codebase has to pass a certain (rather large) size before the complexity trade offs can even begin to make sense, and even then they often don’t.

hackerfromthefu 6y ago
>> Any stored proc codebase is guranteed over time to become a maze of complexity and footguns. I think this is a very valuable and important point and wanted to highlight it. >> Any stored proc codebase is guranteed over time to become a maze of complexity and footguns. There's no absolute law that states that SP using environments will always end up as a mess, but there should be such a law, because they do! I've worked across dozens to hundreds of enterprise environments, and the pattern is clear. I suppose it comes from personality and culture with the people working on db coding more often in the 'get it done' end of the spectrum vs programmers where you find a significant number interested in 'get it right'. An even stronger reason is probably that db coders are more likely to be autonomous instead of in large centrally driven teams. That central hierarchical organisation is a necessary but not sufficient condition for working to tame the entropy of large complex systems, without which they evolve into "a maze of complexity and footguns".
- marcus_holmes 6y ago
  yeah, I don't think this is a problem with sprocs, per se. I think this is a problem because the database code is treated differently from the application code, because so few coders understand SQL well enough to be comfortable in it. So, as you say, the DBA's tend to be autonomous, and held to different standards. And with less ability to say "we have tech debt, we need to refactor our sprocs" because of the knock-on effect on the application code. It's a management problem rather than a technical certainty, I think.

buro9 6y ago

There is a middle-ground... helpers. Things like search interfaces might offer filters over multiple columns... your choices here: 1. Use an ORM 2. Write SQL for each permutation of filter 3. Write a helper that will compose the SQL based on the filters needed And that 3rd one is the only time I stray from plain SQL towards something that looks like a lightweight abstraction over the SQL. Similarly... stored procs, I avoid these but there are times when they are necessary. If you need the boundary of a transaction to span multiple SQL calls then it is safer to do so within a stored proc. Yes you can use a transaction and rollback, but from a maintainance perspective I've seen refactors break transactions that were not fully encapsulated in a single Go (insert other language) function . Yes keep things simple with SQL and do everything in the application... but also break those rules rather than do something daft or with side effects.

ashtonian 6y ago
Or use something lightweight and configurable like https://github.com/a8m/rql https://github.com/a8m/rql to just handle filters.
nullsense 6y ago
Seeing the word "helpers" in a codebase gives me PTSD flashbacks.

marcus_holmes 6y ago

Came here to say exactly this. Also a gopher, who is also vaguely horrified at all the "use GORM, and a framework" advice to newbie gophers. I also use stored procedures, and treat them as function calls from my application code. So I effectively build a "data storage API" in the database, that I then call (as you say, like any other service) from my code. I also have views for all the data sets that will be returned to the application, so I can use them easily as a type for function returns, and enforce consistency.

EdgarVerona 6y ago

This is the approach I like as well, with the caveat that the stores procedures themselves should be kept as dumb and as late in the process as possible. I have made many mistakes in my career in terms of systems that were either overly complicated or inefficient or both, and it has almost always ended up boiling down to either trusting the SQL that an ORM produces, or putting too much of the business logic in hand crafted stored procedures. I have done both, regretted both, and nowadays advocate for sticking to simple, predictable, and dumb stored procedures so that you can both have the benefit of knowing/controlling exactly what will execute in the database and having things like validation occur code side whenever possible. In the real world, that simplification of stored procedures isn't always possible. But if it's your starting assumption, then you can fight for it until you are certain that there isn't a practical alternative to pushing the logic into the stored procedure. And if a compromise of these principles needs to happen, I would push complexity into a stored procedure before trusting the SQL produced by an ORM every time. But where I fall on that spectrum may just be due to the severity and types of problems I have seen happen with each approach, do YMMV. And I wouldn't go so far as to call myself an expert, so also YMMV on that front. I am just a guy who has made a lot of mistakes. But if it'll help others, this is my takeaway.

baq 6y ago

how do you unit test your stored procedures on a CI agent? serious question - this is nigh impossible in every db i've ever used.

dkersten 6y ago
Run the DB in docker, import the stored procedures as part of the setup migrations and run tests against that from your normal test suite.

davedx 6y ago

> You could even go as far as to unit test just your SQL code if you wanted, something you can't do too easily when it's in your application. How do you unit test SQL code? You could integration test against something like sqlite but unit testing stored procs sounds very difficult. I worked with a big C# .net core ecommerce platform that had a big stored proc used for ingest. It was an absolute pain in the ass to debug and fix issues because testing it was so difficult. Eventually we replaced it with C# code. Access to full software dev tools (debugger, automated tests) is really something you don't want to be without on large projects...

hans_castorp 6y ago
> How do you unit test SQL code? e.g. https://pgtap.org/documentation.html https://pgtap.org/documentation.html There are similar solutions for Oracle's PL/SQL and SQL Server

Dave3of5 6y ago

Just a friendly reminder to anyone reading this about v1 of GORM. It doesn't properly support JOINS: https://github.com/go-gorm/gorm/issues/1436 https://github.com/go-gorm/gorm/issues/1436 Note that there is some work for v2 in allowing users to create JOIN queries but without JOIN support it's effectively unusable in anything other than very small databases.

giantDinosaur 6y ago
wait, what? doesn't support joins? Things like this make me doubt my fundamentals. 'Are joins really necessary?' is the question now running through my mind ;-)
- Dave3of5 6y ago
  It does not support joins. The expectation is that you "Preload" your data which is a fancy way of saying load it all into memory. The performance of this is trash so don't use that ORM there are a few better ORMs for GO mentioned in the issue. Yes joins are really necessary in SQL they are a foundational part of using any relational database.
  - giantDinosaur 6y ago
    Yes, they're literally the second thing one learns about SQL, really. I'm not being too serious about rethinking whether they're necessary, to be clear! What a strange design decision though. I don't really understand why it is doing this? Why would anyone really think it's a good idea?
    - Dave3of5 6y ago
      > I'm not being too serious about rethinking whether they're necessary, to be clear! My apologies if I came off condescending, it's hard to impart tone of voice in text. > I don't really understand why it is doing this? I think it's because it makes the SQL Generation harder. I think the lib at the moment is simply a search and replace on a bunch of SQL strings.

sshb 6y ago

I've recently came up with following approach: make it easy to generate CRUD applications without ORM's, and then modify them any way you like without extra dependencies. Made a tool to achieve just that: https://github.com/sashabaranov/pike/ https://github.com/sashabaranov/pike/

_me_go_too_ 6y ago

>> I'm a Go developer and I notice a lot of other Go developers instantly suggest things like GORM to noobs writing applications I've made the opposite observation. Compared to Python, C#, Java and NodeJS, ORMs are actually not a big thing in Go. Which is good - I agree with you.

Cthulhu_ 6y ago

I'm trying Gorm right now, and I'm running into some serious issues where it's not as straightforward as an ORM should be (mostly having to do with transparently saving / updating relationships). I'm reluctant to go to straight SQL because then I have to: - Write a ton of 'mapping' code between resultsets and data model - Do a lot of diffing to determine which relations to add, update or remove - Manage my own database migrations - challenging because I'm stuck with using sqlite - etc I want my tools to make things easier for me, but gorm seems to be the "now you have two problems" direction - for now. Maybe I just don't get it yet. Maybe I just don't get databases anymore either.

vptr 6y ago
Depending on which DB you're using, I'd suggest trying out https://sqlc.dev https://sqlc.dev instead of gorm if on PG. Mysql support is a bit flaky last time I checked.

hodgesrm 6y ago

Your view is the original design idea behind stored procedures. Stored procedures as far as I know were first implemented in Sybase SQL Server. We tended to think of them as enabling RPC (remote procedure call) access to the DBMS. Sybase was also the first major relational database to offer client access through the network. Our connectivity was influenced by distributed system concepts like RPCs that were top-of-mind in the 1980s.