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).
- 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
- 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.
- 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!
- 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
- I learned most of it using the book Mastering PostgreSQL in App Dev (which is certainly the best book I've read on the topic).
- brandur.org has lots of great articles, e.g. Postgres job queues and Failure by MVCC
- SQL Data Analytics for Business Analysis features great recipes for many real-world problems, using many of the features above.
- try practical examples over at pgexcercises.com.
- the Postgres reference is a great resource as well.