Learn SQL

Dear new developer,

It’s a good idea to learn SQL (which stands for structured query language). This is the language that the vast majority of data is stored in for most companies. The reason for this is that relational databases (which is what SQL is the main interface for) are very good at a wide variety of data storage. Sure, at the edges of speed, scale and functionality there are other solutions, but you should reach for them when the relational database falls short, not at first.

You don’t need to be an expert at SQL, though it’s a mindbending way to interact with data, so you might want to put studying it on your list. Instead of being procedural or functional, SQL is set based. I confess, I’ve been using it for decades and still haven’t mastered it.

If you are using a modern language, there are often frameworks that sit between you and the database (for example, ActiveRecord for Rails, Hibernate for Java, SQLAlchemy for Python). These are helpful because they make simple operations simpler. If you want to look something up via primary key or a simple query, these tools can help. But if things get harder (joining across multiple tables, database specific functions) the abstraction breaks down. This is where knowing some SQL can be helpful.

There are also times when you are running queries that are punishing using a framework. For example, if you wanted to sum across a set of orders in a day to get a daily total, a naive framework would have to load all the data for the orders and then sum up the order value in memory. A more sophisticated framework would be able to generate SQL summing up the values in the database for you. Unfortunately, it’s hard to know whether the framework you are using is naive or sophisticated. But dropping into SQL will always work.

I have also found that some systems have a lot of non intuitive operations, but that at the end of the day, the magic is built on code and data storage. By looking at the data storage, you can understand some of the operations that these frameworks take care of for you. For instance, for a long time, rails migrations were magical to me. When I took a look at the database, it became clear that a fundamental piece of rails migrations was the datetime portion of the migration name stored in the database. When I got into a weird state because of running migrations then switching branches then re-running migrations, this understanding of the data structure behind them helped me out.

Some good resources to learn SQL:

• W3Schools SQL tutorial
• Install a SQL database on your computer (MySQL or PostgreSQL) and play around with it
• SQL Koans
• The reference documentation of your database (MySQL, PostgreSQL)

One final note. People have very strong opinions on the type of SQL database they use (a commercial offering like SQL Server or Oracle, or an open source solution like MySQL or PostgreSQL). As a new developer, you want to learn whatever your company is using. Honestly, the difference between them at the basic SQL level just isn’t that large. They start to differ in more advanced SQL functions and other performance and administration concerns. But that’ll matter later in your career.

Sincerely,

Dan