Skip to main content

Sproc vs ORM vs Inline vs Polyglot

With relational databases the common data access patterns tend to fall into three core options.

  • Direct access via inline SQL
  • Stored procedures using the standard library
  • ORM frameworks or libraries

Individually these have both pros and cons, often leading to heated debate and discussion.

Inline

  • Leaky abstractions.
  • Dangerous in places via SQL injection.
  • Quick and dirty solution.
  • Non testable by default.
  • Useful for integration testing where dynamic input is required and safe.

Stored Procedures (standard library)

  • Can be clunky and low level to use in places.
  • Non testable by default.
  • Allows the use of DB specific features internally.
  • Easy to tune and optimize as long as interface is stable.
  • Developers can optimise the execution of queries.

ORMs

  • Testable by default.
  • Complex, large and difficult to use correctly.
  • Leaky abstractions.
  • Optimisation is harder, especially for DB engineers.
  • Mini or lightweight alternatives exist, with less of the downsides.

Polyglot Persistence

The actual decision of which data access method to use can be a non issue providing a good abstraction is used. Whether you use inline SQL, stored procedures or full blown ORMs is beside the point. Instead of abstracting the implementation detail, focus on the role the object or function has to play. A benefit of this approach is the ability to mix and match data access patterns. Polyglot persistence is gaining more traction where alternate data storage solutions are more appropriate.

N+1

One common flaw that all these data access patterns can have is the N+1 problem.

Comments

Popular posts from this blog

Constant Object Anti Pattern

Most constants are used to remove magic numbers or variables that lack context. A classic example would be code littered with the number 7. What does this refer to exactly? If this was replaced with DaysInWeek or similar, much clarity is provided. You can determine that code performing offsets would be adding days, rather than a mysterious number seven.Sadly a common pattern which uses constants is the use of a single constant file or object. The beauty of constants is clarity, and the obvious fact such variables are fixed. When a constant container is used, constants are simply lumped together. These can grow in size and often become a dumping ground for all values within the application.A disadvantage of this pattern is the actual value is hidden. While a friendly variable name is great, there will come a time where you will want to know the actual value. This forces you to navigate, if only to peek at the value within the constant object. A solution is to simple perform a refactor …

Three Steps to Code Quality via TDD

Common complaints and problems that I've both encountered and hear other developers raise when it comes to the practice of Test Driven Development are: Impossible to refactor without all the tests breakingMinor changes require hours of changes to test codeTest setup is huge, slow to write and difficult to understandThe use of test doubles (mocks, stubs and fakes is confusing)Over the next three posts I will demonstrate three easy steps that can resolve the problems above. In turn this will allow developers to gain one of the benefits that TDD promises - the ability to refactor your code mercifully in order to improve code quality.StepsStop Making Everything PublicLimit the Amount of Dependencies you Use A Unit is Not Always a Method or ClassCode quality is a tricky subject and highly subjective, however if you follow the three guidelines above you should have the ability to radically change implementation details and therefore improve code quality when needed.

DRY vs DAMP in Tests

In the previous post I mentioned that duplication in tests is not always bad. Sometimes duplication becomes a problem. Tests can become large or virtually identically excluding a few lines. Changes to these tests can take a while and increase the maintenance overhead. At this point, DRY violations need to be resolved.SolutionsTest HelpersA common solution is to extract common functionality into setup methods or other helper utilities. While this will remove and reduce duplication this can make tests a bit harder to read as the test is now split amongst unrelated components. There is a limit to how useful such extractions can help as each test may need to do something slightly differently.DAMP - Descriptive and Meaningful PhrasesDescriptive and Meaningful Phrases is the alter ego of DRY. DAMP tests often use the builder pattern to construct the System Under Test. This allows calls to be chained in a fluent API style, similar to the Page Object Pattern. Internally the implementation wil…