4 ms·

Where I'm at now, we have a big huge Java app that uses MyBatis (https://mybatis.org/mybatis-3/ https://mybatis.org/mybatis-3/) It involves writing SQL inside

Where I'm at now, we have a big huge Java app that uses MyBatis (https://mybatis.org/mybatis-3/ https://mybatis.org/mybatis-3/) It involves writing SQL inside of XML files and doing a lot of manual mapping from columns back to the objects. At first I REALLY disliked it but over time I have grown to absolutely love being able to just write SQL queries that can return whatever. Writing raw SQL really lets you optimize and create complex queries when you need to in a way that you can't with an ORM.

hu3 5y ago
I love SQL and being able to squeeze performance from these magical blackboxes called database servers. But how do you deal with refactoring? If I want to change a column on a db table or a property in a Java/C# class can the compiler tell me where I forgot to update without much ceremony? This is a problem I had in the past on a large project. The result is that we devs started to get afraid of changing schema or code that touched database because that could create runtime errors as opposed to compilation errors. Being afraid to refactor is really bad and piles up quickly as technical debt. Some ORMs solve this by scanning the database schema and creating a class per table and a field/property per column. This way we get compilation errors if database doesn't match code and have the opportunity to investigate it before it blows on the customer screen as a runtime error.
- dansomething 5y ago
  > But how do you deal with refactoring? We solved this problem by having thorough automated test coverage for all SQL queries. Here's basic lifecycle of a test: 1) Create the test database 2) Migrate the database to the latest schema version 3) Insert test data 4) Execute the SQL query 5) Validate the results of the query
  - hu3 5y ago
    Tests are nice. But I see disadvantages with relying on them: 1) We now need 100% test coverage for queries. Forget one of them and the castle might fall on the client side. This can be mitigated with gatekeepers on CI/CD pipeline to ensure 100% coverage. But it's still quite the effort. 2) Our feedback loop for developing refactoring is now slower now since we need to run all tests to see what broke after a schema change. In contrast to an IDE giving compilation errors. 3) If we build SQL queries by composing them, which is common pattern for business rule validations, there will be many branches to be tested. 4) We won't have auto-completion of table and column names in the IDE. And if you mistype them your feedback is slower. I say that as someone who went all in SQL query builders and composability in a project but when it grew large we were still afraid of changing schema despite having a very high test coverage.
- TranquilMarmot 5y ago
  > devs started to get afraid of changing schema or code that touched database because that could create runtime errors as opposed to compilation errors. Being afraid to refactor is really bad and piles up quickly as technical debt Yes, this definitely happens! Like somebody else said, tests can cover a lot of this. But of course they aren't perfect. Where I'm at right now, we have multiple microservices reading from the same database so schema changes are a terrifying nightmare ("which service will break when we remove this column?!"). Unused columns have piled up over the years; in our `users` table we have almost a dozen unused columns. Onboarding new folks is always fun; "No, you don't want to look at the `role` column, you want to look at `role_id`!" Note that this issue would still be a problem if we were using an ORM (unless we had a shared library between all microservices that are talking to the database ) I've used ORMs extensively before and they do make refactoring a little bit easier, but I still would rather write raw SQL. Basically personal preference at this point; if I were to join somewhere that was using an ORM I would learn it and probably be happy with it!
  - dragonwriter 5y ago
    > Where I'm at right now, we have multiple microservices reading from the same database Like, all from the same base tables or each with their own schema with service-specific views? Also, isn't shared-DB integration by-definition incompatible with “microservices”, which are isolated and sovereign over their own data?
    - TranquilMarmot 5y ago
      From the same base tables. And yes, it is very incompatible with the definition of a microservice and causes plenty of horrible issues. I didn't say it was good architecture. It's a remnant of when we were a startup in "move fast break things" mode (we have since been acquired) and as an org paying down tech debt has never been a priority so we're left with a lot of bad decisions that we have to live with (to the point where it's a miracle when we actually ship any meaningful features...)

hu3 5y ago

I love SQL and being able to squeeze performance from these magical blackboxes called database servers. But how do you deal with refactoring? If I want to change a column on a db table or a property in a Java/C# class can the compiler tell me where I forgot to update without much ceremony? This is a problem I had in the past on a large project. The result is that we devs started to get afraid of changing schema or code that touched database because that could create runtime errors as opposed to compilation errors. Being afraid to refactor is really bad and piles up quickly as technical debt. Some ORMs solve this by scanning the database schema and creating a class per table and a field/property per column. This way we get compilation errors if database doesn't match code and have the opportunity to investigate it before it blows on the customer screen as a runtime error.

dansomething 5y ago
> But how do you deal with refactoring? We solved this problem by having thorough automated test coverage for all SQL queries. Here's basic lifecycle of a test: 1) Create the test database 2) Migrate the database to the latest schema version 3) Insert test data 4) Execute the SQL query 5) Validate the results of the query
- hu3 5y ago
  Tests are nice. But I see disadvantages with relying on them: 1) We now need 100% test coverage for queries. Forget one of them and the castle might fall on the client side. This can be mitigated with gatekeepers on CI/CD pipeline to ensure 100% coverage. But it's still quite the effort. 2) Our feedback loop for developing refactoring is now slower now since we need to run all tests to see what broke after a schema change. In contrast to an IDE giving compilation errors. 3) If we build SQL queries by composing them, which is common pattern for business rule validations, there will be many branches to be tested. 4) We won't have auto-completion of table and column names in the IDE. And if you mistype them your feedback is slower. I say that as someone who went all in SQL query builders and composability in a project but when it grew large we were still afraid of changing schema despite having a very high test coverage.
TranquilMarmot 5y ago
> devs started to get afraid of changing schema or code that touched database because that could create runtime errors as opposed to compilation errors. Being afraid to refactor is really bad and piles up quickly as technical debt Yes, this definitely happens! Like somebody else said, tests can cover a lot of this. But of course they aren't perfect. Where I'm at right now, we have multiple microservices reading from the same database so schema changes are a terrifying nightmare ("which service will break when we remove this column?!"). Unused columns have piled up over the years; in our `users` table we have almost a dozen unused columns. Onboarding new folks is always fun; "No, you don't want to look at the `role` column, you want to look at `role_id`!" Note that this issue would still be a problem if we were using an ORM (unless we had a shared library between all microservices that are talking to the database ) I've used ORMs extensively before and they do make refactoring a little bit easier, but I still would rather write raw SQL. Basically personal preference at this point; if I were to join somewhere that was using an ORM I would learn it and probably be happy with it!
- dragonwriter 5y ago
  > Where I'm at right now, we have multiple microservices reading from the same database Like, all from the same base tables or each with their own schema with service-specific views? Also, isn't shared-DB integration by-definition incompatible with “microservices”, which are isolated and sovereign over their own data?
  - TranquilMarmot 5y ago
    From the same base tables. And yes, it is very incompatible with the definition of a microservice and causes plenty of horrible issues. I didn't say it was good architecture. It's a remnant of when we were a startup in "move fast break things" mode (we have since been acquired) and as an org paying down tech debt has never been a priority so we're left with a lot of bad decisions that we have to live with (to the point where it's a miracle when we actually ship any meaningful features...)