Databases continued - Discoveries of our latest hackathon

Like every month, our developers met up last week to spend a day learning about a new topic. We picked up where we left off at our last hackathon: databases. The agenda was quite simple:

1. Everyone picks a topic that sounds interesting
2. We have 1 hour to prepare a basic overview
3. At the end, everyone presents their findings

Here’s what we learned.

SQL Injection

Although it's a well-known topic, SQL injection is something every developer should be familiar with—especially when syntactic sugar hides important security measures. Take the following example from JavaScript:

query(`select * from users where users.id = ${id}`)
// vs.
query`select * from users where users.id = ${id}`

The first line performs direct substitution and is therefore prone to SQL injection. The second line, however, can—for example—magically transform into query("select * from users where users.id = ?", [id]) using tagged template literals. The difference is subtle, but the consequences are enormous.

Triggers and Scheduler

Most databases offer the ability to listen for events or schedule recurring procedures.

First, we looked into how triggers work. For example, it’s possible to write a trigger that updates a “modified_at” timestamp every time a row is updated.

Then, we explored how some databases handle the scheduling of procedures. For instance, you could schedule a specific query to run after 1 hour, or run a procedure every Sunday at 6 AM. Notably, MySQL offers a wide variety of features for scheduling events.

Query Plans

SQL queries are not always written optimally. Furthermore, it can be difficult to debug a slow query.

A good starting point is to look at the query plan. In it the database shows how it will execute your query and which steps it takes in what order. It can also provide estimated costs and real run-time information about each executed step.

This makes it easier to pinpoint where a slow query breaks down. Some databases even provide suggestions for how to optimize the query. For example, adding a missing index in the right place can work wonders!

And Much More

We also looked at how JOINs work, tackled cascading rules, dug into meta tables, and discussed backup and recovery strategies.

Overall, we strengthened our skills, and everyone learned at least one new aspect of databases. We also had great technical discussions, which led to even more insights.

Understanding the fundamentals of what we’re doing is important to us. It has helped us multiple times in the past to better understand why certain errors occur during debugging which saves us a significant amount of time and headaches.