Kontakt

Embrace Modern SQL

Most of our developers (myself included) often only do pretty basic SQL queries – however, modern SQL has the potential to answer complex questions in a highly performant and declarative way. That's why we are rating this as ADOPT.

Note: Most of the examples below are from the Postgres world, but many also work in MariaDB (and maybe MySQL as well).

Essentials

  • The blog of Michael Malis has a great three-part series on explaining transaction isolation and ACID properties: A must-read - I definitely learned something new. Part1, Part2, Part3
  • Do not use offset when paging through results.

Modern SQL Features

The following features greatly expand what is possible with SQL - so knowing them roughly helps to apply them in the right situations.

  • lateral join (postgres): loop over the a result set, and calculate some derived value for each row. Basically like a "foreach" looping over a result set; doing an additional query per-row.
  • recursive CTEs (Common Table Expressions) (Postgres, mariadb): traverse trees and hierarchical data structures
  • window functions (Postgres, mariadb): calculate a value for each row based on all other values in the result set
  • json (Postgres jsonb with GIN Index, mariadb with virtual column [less flexible])
  • select skip locked (postgres) - e.g. to implement queues

Benefits

  • SQL is a very declarative language, expressing data processing intent. This leads to a high expressivity with little code.
  • When using indexes correctly (EXPLAIN is your friend), SQL is often a lot faster than hand-written code.

Drawbacks

  • modern SQL is often coupled to a certain DB platform
  • many developers don't know the advanced SQL concepts; so make sure to comment your SQL code well!

Alternatives

  • Take care when using ORMs such as Doctrine or Hibernate. They can be useful for getting started quickly and getting data easily in and out of a DB, but to me it is especially hard to reason about transactionality in this regard.

Resources to learn modern SQL