Why I chose not to use an ORM

PS: I will briefly cover ORM vs No ORM and then move forward to talk about what I used and how i went about it.

For a recent project, I had to choose between using an ORM or writing raw SQL queries myself. This project was the first instance where I had to use golang, and after going through multiple threads and reddit posts - it depends.

What if ORM?

I'll put it this way - if your project is small/mid size or is relatively simple on structure, there shouldn't be any problem going with an ORM. An ORM will hasten the development process and getting your project out there, which can be critical.

When using an ORM, it gets relatively simple to write queries and you're able to see them directly in your code. There isn't a need for hard-writing queries in some file or some place, it's right there - in the code as code. There are also many ORMs that allow you to do database migrations straight from the ORM itself, which can be very useful as it keeps both your application models and database structure in sync. Another situation where it might be a good idea to use ORMs is when working with a strong object model architecture, since that is what primarily an ORM is created for - to map data to objects. If you're working with such data in your database (and its structure is unlikely to change), ORMs should work beautifully.

However, the catch with any high-level abstraction, is that it will become a bottleneck when you reach a certain level of complexity. ORM generated queries will almost always be relatively slower than the ones manually written. Along with that, writing complex queries using an ORM will be much slower than if you were to write raw SQL queries. Yes, really good ORMs do allow you to write your own SQL queries and send them through, but then that leads to: if your application is complex enough, you'll end up writing most of the SQL anyway.

Another issue with using ORMs is that it tightly couples the database and the application, which, well, already breaks design principles. For a project distributed across teams, this tight coupling (which in one way is an advantage) turns into a disadvantage.

What if No ORM?

Well, on the first and major advantages that comes with No ORM is that you get the complete power of SQL. You can write complex queries and there isn't an additional overhead (conversion between the data and the object). For specific cases, which can become increasingly common as an application grows, this can be a significant advantage.

Along with the power of raw SQL, you also get the responsibility to manage the queries, put them into the right places and in the right way. The industry has, well, dealt with this for quite the time now and there are ways to go about it. You will end up having to write a significant amount of boilerplate code to replicate the same functionality (relative to an ORM). Raw SQL should, obviously, not be thrown around the application code just like that.

There will be pain, you'll have to write your own queries, and things might get messy as you continue developing. However, you will get incredible control over the queries that are going through, which isn't the case when using an ORM.

One important thing to note about the No ORM approach (which also applies to the ORM approach) is that before the database comes into existence, there should be a good enough understanding of the type of data that needs to be handled and its structure. Going ahead without this can lead to a lot of technical debt and immense headaches.

Funny thing with No ORM is - you'll end up creating your very own version of it that fits for your application.

What did I choose?

My goals aligned mainly with wanting to learn golang better and to understand developing software architecture that works for the long run, and hence, I chose No ORM (well, kind of). Not to say that using an ORM wouldn't have gotten my job done, the project isn't anything more than a simple CRUD application (in essence). However, I felt there was more opportunity to learn if I didn't choose to use an ORM.

The solution I chose is sqlc, which generates code for SQL queries that I write. Now, yes I know: 'code generation' has a bad reputation. But hey, it works.

The whole idea of working without an ORM is to follow the repository design pattern, which is essentially wrapping your SQL queries into functions before actually executing them. Ideally these functions should be part of some class. Yes, that sounds like what an ORM does, however, these structures are much more flexible and can be molded according to your application (because, well, you're writing the code).

Now when following the repository design pattern, a lot of boilerplate code needs to be written. This is what sqlc solves: all you do is write the SQL queries (with a small annotation) and sqlc takes care of the rest.

Along with sqlc, I also added golang-migrate which allows me to write database migrations. All this is essentially building up to my very own version of an ORM, but without the disadvantages of an ORM.

The Implementation

Alright, enough talking here's how it actually looks:

Final file structure:

├── cmd
│   └── app.go
├── internal
│   └── repository
│       ├── db.go
│       ├── models.go
│       └── users.sql.go
├── migrations
│   ├── 000001_initial.down.sql
│   └── 000001_initial.up.sql
├── query
│   └── users.sql
├── sqlc.yaml
├── go.mod
└── go.sum

We will first set up our database (postgresql in this example), which constitutes the first database migration:

migrate create -ext sql -dir ./migrations -seq initial

This will create two files - 000001_initial.up.sql, 000001_initial.down.sql (format: {version}_{name}.{up/down}.sql ) The up files are used to migrate up to a version and down files are used to migrate down to a version. We write the SQL for both of these files, which can look like:

000001_initial.up.sql

CREATE TABLE Users (
  id UUID PRIMARY KEY,
  username VARCHAR(255) NOT NULL UNIQUE,
  email VARCHAR(255) NOT NULL UNIQUE,
  passHash VARCHAR(255) NOT NULL
);

000001_initial.down.sql

DROP TABLE IF EXISTS Users;

For now, these migrations are enough to get started with our database, we can create more migrations as required later on. These migrations now need to be applied to the database:

migrate -path ./migrations -database "postgres://user:pass@localhost:5432/mydb?sslmode=disable" up

The above will migrate the database to version 1. An important thing to note is that sqlc parses versions lexographically but golang-migrate does it numerically (read more about it here). Now for the sqlc setup, which includes:

sqlc.yaml

version: "2" # sqlc configuration format version
sql:
  - engine: "postgresql" # database engine that we're using
    schema: "./migrations" # directory containing migration files that is used to parse the schema
    queries: "./query" # directory containing SQL query files that will be converted to golang code
    gen:
      go: # golang-specific code generation settings
        emit_json_tags: true # adds json tags to struct fields
        package: "repository" # name of the generated Go package
        out: "internal/repository" # output directory for generated Go files
        sql_package: "pgx/v5" # sql driver to use
        overrides:
          - db_type: "uuid" # maps UUID field type (refer migrations file)
            go_type:
              import: "github.com/google/uuid" # import path for the golang UUID type
              type: "UUID" # golang type to use for UUID fields

The above is the configuration for sqlc.yaml, which essentially defines where to reference the database schema from, where the raw SQL files are and where to save the generated code.

users.sql

-- name: FindPlayerByID :one
SELECT * FROM users WHERE id = $1;

-- name: FindPlayerByUsername :one
SELECT * FROM users WHERE username = $1;

-- name: FindPlayerByEmail :one
SELECT * FROM users WHERE email = $1;

-- name: Create :one
INSERT INTO users (id, username, email, passHash)
VALUES (uuid_generate_v4(), $1, $2, $3)
RETURNING *;

We define the above SQL queries that sqlc then references to generate the code. Above each query is an annotation defined which defines what the function's name will be and what the sqlc command to use (more about it here).

Well, we're finally done. All that we need to do now is run:

sqlc generate

This will then generate the code files: db.go , models.go , [query].go ; here the last file is generated based on the list of files present in the query folder (one file for each sql file).

And now we can use the generated code, something like this:

cmd/app.go

package main

import (
	"context"
	"fmt"

	"github.com/yourname/projectname/internal/repository"
	"github.com/jackc/pgx/v5"
)

func main() {
	ctx := context.Background()

	conn, err := pgx.Connect(ctx, "DATABASE_URL")
	if err != nil {
		panic(err)
	}
	defer conn.Close(ctx)

	repo := repository.New(conn)

	user, err := repo.Create(ctx, repository.CreateParams{
		Username: "testuser",
		Email:    "[e-mail]",
		Passhash: hash("samplepass"),
	})

	if err != nil {
		fmt.Println("failed to create user", err)
		return
	}
	
	fmt.Printf("user %s created\n", user.Username)
}

Conclusion

And that's it. Personally, I feel this solution is a good middle-ground between a complete ORM approach or a complete No ORM approach. The answer to the question "which one to choose" mainly depends on your use-case (I know, not the most satisfying answer), but what is definitive is that before going in and writing any database-related code, it's important to understand what kind of data you'll be working with, figure out its structure and how dynamic this structure is (how often it changes).

You can reach out to me at LinkedIn and Twitter.