blog/content/posts/go-databases.md

---
title: "Go Databases"
tinyname: "go_databases"
date: 2021-08-13T20:56:12+07:00
toc: true
description: |-
  There are a plethora of libraries available in Go.
  Here is a list of libraries that may be of interest.
tags:
  - go
  - databases
  - sql
author:
  name: Hamza Ali
  url: https://github.com/hhhapz
---

> This is currently under construction and incomplete.

## Prelude

When working on a Go project, there comes a time where you application needs
a database. As a developer, you have a plethora of options to pick from, some
good, others not so much. As a new developer, making a decision can be
difficult, especially when treading blind.

My goal with this post is to share some perspective and evaluate the different
tools that are available in the Go ecosystem. Perhaps you will be able to find
a new tool that helps reduce your code complexity, or gain more perspective
about why some other libraries might not be as appealing as they seem.

---

## What are my options? {#options}

I plan to compare a half dozen different libraries here that accomplish
different goals. There may be other similar tools that accomplish similar
goals, which I have omitted.

If you would like to suggest another tool, feel free to send me an [email](mailto:hamza@teamortix.com).

Apart from these tools, I strongly believe that looking for libraries that are
compatible with the Go standard library (`database/sqlite`) is really
important. All the tools I mentioned will work seamlessly with it.

- Utility libraries

  - **[github.com/jmoiron/sqlx](https://github.com/jmoiron/sqlx)**

    Quoting the README of the repository, sqlx is a library which provides
    extensions on Go's standard `database/sql` library with marshalling from
    sql rows to Go types.

  - **[github.com/keengancsmith/sqlf](https://github.com/keegancsmith/sqlf)**

    Create SQL queries that are safe from injections with printf, making
    composing queries and working with dynamic number of arguments (more on
    this later).

  - **[github.com/Masterminds/squirrel](https://github.com/Masterminds/squirrel)**

    A highly composable SQL generator for Go. Using a builder pattern, this
    library can really help writing SQL queries in a type safe manner.

- Code generation libraries

  - **[github.com/xo/xo](https://github.com/xo/xo)**

    A code generation libraries that creates a model and CRUD operations from
    your database, and generate type-safe functions from queries. (Limited
    Documentation)

  - **[github.com/kyleconrow/sqlc](https//github.com/kyleconrow/sqlc)**

    Similar to xo, a different style code generation library that should be
    mentioned seperately. Generates code by parsing your SQL.

- Object Relational Mappers - Map Go types in SQL and ORM likes.

  - **[github.com/ent/ent](https://github.com/ent/ent)**

    A composable entity framework that emphasizes on user control.

---

## What about GORM or XORM? {#gorm}

To much of my disappointment, many developers who work with Go fall in the trap
of using an ORM. This is really, really tempting to do, however there are
a number of flaws that most, if not all of these libraries exhibit, which can
severely cripple your project.

### What you Gain {#gain}

Firstly, it is difficult to deny that these libraries do appeal thousands of
developers. While not being the most accurate metric by any means, it is still
a strong indicator. GORM has almost 25,000 stars (at the time of writing this
post) on GitHub and Xorm has over 6,000 on its archived Github.

I will try to explain why many features ORMs are often celebrated and praised
are in fact a source of never ending gotchas.

#### 1. No need for SQL {#no-sql}

Many beginners fall into the trap of using an ORM because it means they don't
need to learn SQL, which can often come across as being challenging or
daunting. A complete new query language, which has its own quirks, learning
about selects, updates, *joins*, *indexes*?

From their point of shoes, all of this programmer jargon can be easily avoided
and ORMs are the way to go.

> *ORMs rules, SQL drools*.

In the contrary, I believe that if you are writing a backend application,
knowing SQL is a prerequisite. As an analogy, imagine trying to use a styling
framework, such as  Bootstrap or TailwindCSS, without knowing CSS. Most people
would agree that attempting to use it without knowing CSS is a recipe for
botched styling that is terrible to maintain, and generally a recipe for
disaster.

Similarly, writing SQL with clean queries that tell both, yourself and your
database exactly what to do ensure a healthy architecture that is both easy to
debug, extend, and refactor.

#### 2. Magically Simple {#magical}

ORMs are magic. You can call `db.Find(&users)`, and boom! All your rows are
returned. A simple filter can be done trivially. `db.First(&user, 10)` will
query the user whose primary key matches 10. However, there is a pain point
that always surfaces when a project uses an ORM more and more.

As a user, writing calling functions like `Find`, `Take`, `First`, `Last` lead
to a loss of control. You no longer have the ability to tune or modify your
queries.

This works fine for simple use cases, however, when pushing these to their
limits, (which is not very far).

For example, updating rows to the zero value of the Go type will simply be
ignored. This usually makes sense, however, very quickly turns into messy
design and hacks, such as resorting to pointer field types, or just returning
back to good old plain SQL.

Complex functionality (SQL) abstracted with another "simpler" layer (your ORM)
always poses the lingering risk of *increasing* complexity through complex
wrappers and hacks.

#### 3. Easy Migrations {#migrations}

ORMs always promote having the ability to automagically handle migrating your
schema as your Go data structures change. While valid, again, these migrations
are generally done under the hood, and leave you, the user with very little
control.

Furthermore, many tools exist already that already handle database migrations
significantly more effectively, and give you more fine-tuning power, so that
you always are able to stay in control.

When migrations become messy or non-trivial, GORM has the potential to break,
or simply make things worse. Using tools like
[migrate](https://github.com//golang-migrate/migrate) are much better
suites, support more databases, has cleaner documentation, and is a more robust
alternative use.

---

### Pain Points {#pain}

#### 1. Finding Help {#help}

Continuing from the points one and two, when working with an ORM, finding help
becomes monumentally more difficult.

Finding answers for generic SQL is significantly easier to do in your favorite
search engine. You will be able to find a plethora of information about
defining foreign keys, creating indexes, composite foreign keys, and any other
SQL feature on Stack Overflow, or a database forum. When transitioning from one
database to another, for example from MS SQL Server to PostgreSQL, or from
Oracle DB to SQLite3, a large portion of your knowledge will be transferable.

On the other side of the coin, migrating from Hibernate to Gorm leads leads to
very little directly transferable knowledge. Even if you are knowledgeable in
a SQL itself, many times you will be at the mercy of the documentation for the
ORM you are using to clearly define how to implement your use-case.

Attempting to find information about questions or concerns you have mean having
to find not only a community specific to the language you are using, but
a subset of that community that also has experience with the very specific
library you use, which might mean the time between finding your answer being
a few searches away to a few days, or being undocumented enough that it just
may never be solved.

#### 2. (Ab)using language features {#lang-features}

When working in Gorm in particular, defining primary keys, indexes, and check
constraints can be described lightly as a mess. Go struct tags are overloaded
and stuffed to the brim to gain simple functionality. Here are a few examples
from the Gorm documentation:

```go
type SomeTable struct {
  // Constraints
  Name  string `gorm:"check:name_checker,name <> 'jinzhu'"`

  // Composite Primary Keys
  CategoryID uint64 `gorm:"primaryKey;autoIncrement:false"`
  TypeID     uint64 `gorm:"primaryKey;autoIncrement:false"`

  // Foreign Key
  CreditCard CreditCard `gorm:"foreignKey:UserName"`

  // Indexes
  Name4 string `gorm:"uniqueIndex"`
}
```

Now, imagine if your project required a combination of these. Having a foreign
key alongside of an index, or attempting to define a many-to-many index.
Abusing struct tags make it easy to make a mistake, force you to give up all
compiler guarantees and with compile-time errors, and terrible to work with. As
a user, you are at the complete whim of the Gorm documentation, and their
support for these features, instead of having the full breadth of options and
customizability one would expect.