Introducing... SQL for Humans

Many of us write crappy SQL or do everything we can to avoid it altogether. It’s a common problem among developers who find themselves being held back by a lack of understanding of SQL and relational databases.

But you can write amazing, reliable, scalable SQL – and it’s easier than you think.

If you're like most developers, touching the database is the most scary part of your work. You don’t write much SQL. Instead, you write data layer code in JavaScript, Python, Java, PHP, C#, Go, Ruby, etc.

And why not? Every language has helpful database libraries such as query builders and ORMs. For example,

• JavaScript has TypeORM, knex and sequelize
• Python has Django and SQLAlchemy
• Java has Hibernate and ActiveJDBC
• C# has Entity Framework and Dapper
• PHP has Doctrine and Eloquent
• Ruby has ActiveRecord and DataMapper
• Go has GORM and sqlx

For most of us, that's enough. We don't need to go poking the database or writing our own SQL. Just thinking about writing raw SQL triggers fears of taking down the production database or grinding our app to a halt.

But what about when you hit the front page of Hacker News? Or when you need to answer business questions about user metrics and churn? Or when the database needs to start scaling horizontally?

Suddenly you need control over your schemas to handle huge loads, or you need to run complex queries across tons of tables, or you need to support a new use case... and the helpful libraries we rely on just shrug their shoulders and sulk. You've got no choice but to roll up your sleeves and write some SQL.

But then you're stuck trying to learn SQL at the worst possible time! Under pressure, under load, under-prepared.

These challenging situations prevent us from ever learning the fundamentals, and they shake our confidence. No wonder so many of us don't enjoy writing SQL.

But despite how it feels, SQL is actually an elegant, battle-tested and highly performant language. It feels unpredictable because reliable performance requires you to know how to work with your database, not against it. And for most of us, our database is yet another mystery.

Understanding SQL means understanding your database.

It doesn't matter how many years you spend dabbling with SQL — if you don't learn how the database executes queries, your mental model will always be incomplete.

Think about how most of us learn SQL: we learn the syntax and keywords of queries, instead of focusing on how database query planners take the queries and execute them. We learn rules of thumb and high-level concepts for specific situations, instead of the fundamentals that can be applied broadly to any situation.

It's like trying to give directions to a tourist when you don't even speak the same language. Except it's even worse, because we only try it when the tourist is screaming in our face!

Nobody knows how to write SQL when they start their career.

We assume that computer science geniuses or bootcamp developers are all imbued with a deep knowledge of relational databases. Or that SQL is just unpredictable and messy. Or that we're just not good at this stuff.

But that's not true, and it's not fair on you! If you learn how queries are executed and how your database software actually works, you too can be a query-writing mastermind.

Becoming competent and confident with SQL takes you to the next level. Suddenly you can stop being afraid of your database. You can build applications faster. You can answer questions that everyone assumed would require a DBA or data scientist. All of a sudden, the most scary part of your job becomes a joy!

As developers, we spend so much energy trying to avoid writing SQL, when we could be embracing it. We could be using it to build reliable, high-performance and flexible data layers.

So how do you learn the fundamentals of SQL? Well, you can go read the Postgres and MySQL documentation start-to-finish and trawl through books on syntax, query planning, relational algebra, and algorithms. Then you can spend a few years working on big data analytics platforms, and a few more building web applications from scratch. That's what I did. It taught me well—but I can't help but feel like I took the long way around.

So let me take you on the guided tour! Since I quit my job at Google in 2020, I've been transforming my knowledge and experience into a comprehensive, interactive, self-paced online course. And any developer can follow along, turning themselves into a database expert.

I call it SQL for Humans.

In this course, you will learn the basics of SQL, how to write advanced queries, how your database really works, and most importantly: how to design and architect your applications so that databases don't become a growing pain as you scale.

And the best part? SQL is evergreen! You only need to learn these skills once, but you can use them for years to come.

Hi! I'm Ben. 👋

I'm Ben Barbersmith, an ex-Google full-stack developer turned bootstrapping entrepreneur. I'm building several SaaS products from zero to ramen profitability in public on Twitter.

I first learned how to be effective in SQL while generating multi-million dollar insights at Google. Then I learned how to design scalable database schemas while building my own products. Now I want to teach you everything I know.

I'm building my new interactive course, SQL for Humans, to unlock the power of modern databases for the everyday developer. It'll feature over 50 lessons, guided screencasts, interactive code exercises, side-by-side code comparisons, and more.

The course will give you a solid understanding of SQL and relational databases. Once you complete it, you'll be able to:

• Use your chosen ORMs and database abstractions fearlessly, knowing how they work under the hood.
• Benefit from the insanely optimised performance that databases offer.
• Write concise and reliable data-crunching queries.
• Produce code that can be re-used by anything that touches the database, in true DRY fashion.
• Design queries for analytics platforms and data warehouses.
• Easily answer ad-hoc questions about user behaviour.
• Solve data issues introduced by backend or frontend bugs without resorting to hacky patches.
• And so much more.

Invest in the fundamentals and you'll unlock the next level of productivity.

Coming soon!

I'm building this course in public on Twitter, and it launches this summer. But for a limited time only, and for a limited number of devs, I'm offering early access to the work in progress course.

Sign up now to get the earliest of early bird discounts, exclusive access to the #buildinpublic preview, and a chance to shape the final syllabus and material of the course to reflect your most pressing issues!

Ben Barbersmith Instructor, SQL for Humans

Course curriculum

In this course, you will learn the basics of SQL, how to write advanced queries, how your database really works, and most importantly: how to design and architect your applications so that databases don’t become a growing pain as you scale.

Throughout the course we’ll take real examples from web-apps, open source projects, and scenarios suggested by indie hackers from around the world. We’ll see how to use the basics in everyday scenarios, how to solve common problems that occur, how to optimise real queries, and how to rearchitect our database schemas as we grow.

This curriculum is a work-in-progress!

Please send me your feedback via email or via Twitter. I reply to every email and tweet.

Now without further ado, let’s explore the course…

Module 0: Introduction

Let’s start by setting the scene and making sure we’re all on the same page. What are relational databases? What is SQL, anyway? Why is it so hard to grok? And what about NoSQL alternatives?

• The basics of relational databases
• SQL as a database interface
• SQL compared to imperative and functional languages
• Relational vs. document databases
• SQL vs. NoSQL 😬

Module 1: The basic building blocks of a query

Even those who “know” SQL might be surprised about how queries are actually executed by the database engine. It’s time to walk through the basic keywords and introduce some key concepts about query execution.

• Where does the data come FROM?
• How can I JOIN tables?
• WHERE do I put the conditional logic?
• Can I GROUP results?
• What if I’m HAVING to apply conditions after I group?
• How do I SELECT specific fields?

Interlude: Elements of style

A quick overview of common formatting styles and conventions, recommended editor extensions, and tools you might like to use as you write SQL.

Module 2: Getting your database set up

This module covers installation, permissions, basic security, clients, and more for the most common relational databases.

• Postgres
• MySQL
• SQLite
• Modern options: why do it yourself?

Module 3: Designing schemas like your ORM

Most developers have never actually written SQL. But most developers have touched a database. So how does our data layer code actually work? How does our code interact with the database engine? What do the underlying database tables and relations look like? It’s time to find out.

• What’s a schema?
• What’s an ORM and why should I use one?
• Creating tables like an ORM
  • One table per class
  • Basic types
  • Modern types
  • Basic relations
  • Adding constraints
• Typical ORM-generated queries
• ORM-generated migrations

Module 4: CRUD operations in SQL

We’ve got a database. We understand how “normal” code interacts with it, and what’s going on under the hood of our ORM. Now let’s get our hands dirty and try some simple Create, Read, Update and Delete (CRUD) operations in SQL.

• Schema statements
  • Create with CREATE
  • Read with SELECT
  • Update with ALTER
  • Delete with DROP
  • Building your own migrations
• Data statements
  • Create with INSERT
  • Read with SELECT
    • Uniqueness constraints
  • Update with UPDATE
    • Relying on uniqueness constraints
  • Upserts with INSERT
    • Conflict options
  • Delete with DELETE
• Transaction statements
• Building and executing SQL safely

Module 5: Transforming query results

It’s pretty rare that we want to use data precisely as it’s stored in our database. More often we want to perform some operations on the results we get back — adding values, summing them up, transforming strings, checking for nulls, etc. And usually we do this in application code. But why not harness your database to do it more quickly, more safely, and consistently across all APIs?

• Aggregate functions
• Handling nulls
• String functions
• Numeric functions
• Date, time, and datetime manipulation
• Basic analytic functions

Interlude: how relational databases actually work

We’ve come a long way. We understand the building blocks of queries. We know how to write our own SQL in lieu of using ORMs. We know how to perform a ton of common analysis in SQL rather than in application code — allowing us to take advantage of amazing database performance.

Now it’s time to find out how databases actually execute these queries in detail, how query planners work, and how we can use that knowledge to take our database skills to the next level.

Module 6: Designing schemas like a database analyst

In module 3 we looked at how database schemas look when they’ve been created by ORMs. Then in module 4 we looked at the syntax to create and modify our own schemas.

But what if we go beyond the syntax? How should we design schemas if we want to be smarter than our ORM?

• Making trade-offs
• Storage, performance, flexibility: pick 2
• Extensibility through flags, JSONB and other advanced types
• Normalisation: how much is enough?
• The pros and cons of foreign keys
• Using views to combine the benefits of normalization and the simplicity of ORMs

Module 7: Beyond CRUD

Sometimes we get to a point when our ORM fails us utterly. We can’t figure out how to get the results we need without a combining several queries and a ton of logic. Or we can write the queries, but they’re hopelessly slow.

This is crazy! Databases are powerful, they are hyper-optimised, and they are amazing tools for most data processing tasks. So let’s learn how to step beyond ORMs and CRUD operations and get the most from our database engine.

• Complex joins
• Subqueries
• CTEs
• Temporary tables
• Loading data from files (a.k.a. basic ETL)

Module 8: Analytics

You’ve built a successful product. Your database is humming along nicely, with tons of user data and high performance. Now you want to roll up your sleeves and learn something about how your users are behaving, or how your system is being used. You want some fancy analytics.

Before you throw everything into a data warehouse or big data solution, stop a second and consider: can you solve this problem with your existing database too? Usually, the answer is a resounding yes!

• Building your own analytics
• Pivot tables
• Roll ups
• Complex aggregations
• Advanced analytical functions
• Window functions

Module 9: Optimization

Uh-oh. We’re hand-writing SQL queries, we’re making sensible trade-offs about our database schema, and we understand how things are supposed to work. But they’re still slow. What’s the big deal? Now can we use a big data solution?

Hold up! Before you go running to the cloud, it’s time to learn how to optimise our queries to use fewer resources and run faster than ever.

• Reducing I/O
• Using indices well
• Sargable operations
• Just ask the database what it’s doing!
• Rules of thumb

Module 10: Levelling up

It seems like we’ve won. We’ve built a performant product that can handle all the data we throw at it, that allows us to run amazingly complex queries in sensible timeframes, and that provides all the analytics we need. Good job!

But let’s not rest on our laurels. It’s time to learn how to produce more maintainable and reuseable code, and how to use even more advanced features of our database.

• Stored procedures a.k.a. custom functions
• Macros
• Triggers
• Extract, Transform, Load (ETL)
• Streaming
• Migration tools
• Full text search
• REST APIs
• Real-time updates

Module 11: Production systems

We’re not all in devops, but we many of us — especially startup engineers and indie hackers — need to think about deployment, too. How do we actually host these databases? How should we be securing them, backing them up, maintaining them over time? And what services can make these easier for us? Let’s take a look.

• Security
• Backups
• Replication
• Sharding
• Maintenance
• Hosted solutions

Thanks for reading this far!

If you spent this long checking out the curriculum, please send me your feedback via email or via Twitter. I reply to every email and tweet.

Ben Barbersmith Instructor, SQL for Humans