Different ways of working with SQL Databases in Go

Comparing database/sql, sqlx, GORM and sqlc.

Different programming languages have their own ways of working with relational databases and SQL. Ruby on Rails has its Active Record, Python has SQLAlchemy, Typescript - Drizzle, etc. Go, being a language with quite diverse standard library which includes well-known database/sql package, has its own libraries and solutions for working with SQL, that suit different needs, preferences and teams.

In this article, we'll explore and compare most popularly used Go packages with hands-on examples, pros and cons. We will also briefly touch on the topic of database migrations and how to manage them in Go. You'll get the most out of this article if you already have some experience with Go, SQL and relational databases (doesn't matter which one).

Demo Schema

For the purpose of this article, we'll use a simple schema with three tables: users, posts and blogs. For simplicity we'll be using SQLite as our database engine, choosing another database engine should not be a problem, as all the libraries we'll be exploring support multiple SQL dialects.

Here is our database schema in SQL:

CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE
);

CREATE TABLE blogs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    url TEXT NOT NULL UNIQUE
);

CREATE TABLE posts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    user_id INTEGER NOT NULL,
    blog_id INTEGER NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
    FOREIGN KEY (blog_id) REFERENCES blogs (id) ON DELETE CASCADE
);

And here is its Entity-Relationship Diagram (ERD):