Working on backend systems almost always necessitates the use of a database where application data can be stored. The majority of our applications use a relational database as a data storage solution.
SQL (structured query language) is the tool for communicating with the database.
We usually communicate with the database through the ORM (
ActiveRecord). But sometimes, the ORM is not powerful enough for a specific problem and you must write raw SQL.
To be able to solve complex problems and to get a better understanding of how the ORM works, here are some query examples and advice to help you with that!
All query examples will be compatible with the PostgreSQL standard.
SQL keywords are formatted in all capitals to make them stand out from the text, as in SELECT.
SQL tables and columns are spelled in lowercase, and words are separated by underscores, as in
It would make your life easier if you align and format a query you are currently working on. It makes it easier to debug and identify parts of the query.
SQL is correctly pronounced "ess-cue-ell", not "see-quell".
In the context of database-related usage, the word index refers to an ordered collection of information. The preferred plural of this word is indexes.
SQL Playground Database
We have set up a PostgreSQL database instance that you can use to test your queries. The credentials are stored in our 1Password.
DB GUI Clients
There are plenty of GUI tools for interacting with PostgreSQL. Here is the list of the most popular GUI clients - feel free to choose the one you like:
- Postico (only for Mac - we have a full version license that you can acquire from your team lead)
- Azure DataStudio
- JetBrains DataGrip
- SQLPro for Postgres
Tools & Resources
- How to Interpret PostgreSQL Explain Analyze Output
- PostgreSQL execution plan visualizer #1
- PostgreSQL execution plan visualizer #2
- PostgreSQL execution plan visualizer #3
- PostgreSQL Cheat Sheet
- Most Useful PostgreSQL Commands with Examples
- DB Fiddle
- PostgreSQL Ecosystem
- PG Wiki - Don't Do This
- How the PostgreSQL Query Optimizer Works