ORM or Not ORM, That Is the Question! + demo

We’ve been using ORM (Object-Relational Mappers) for a long time now. They became popular as a way to simplify database access and prevent SQL injection attacks. Over time, ORM have evolved into powerful tools, far more advanced than their early versions.

ORM and Migrations file

A key benefit of ORMs is their ability to map models to database tables. When you update a model, the ORM generates a migration file to reflect those changes in the database schema. Since relational databases require explicit schema definitions, the ORM’s migrator handles these updates automatically, keeping your database in sync with your code.

While this approach seems convenient, it comes with trade-offs. ORMs are designed to be database-agnostic, supporting multiple database systems, but this limits their ability to use database-specific features.

For example, PostgreSQL has native support for the UUID data type, while MySQL stores UUIDs as VARCHAR. To stay compatible with different databases, ORMs often default to a “one-size-fits-all” approach, like treating UUIDs as VARCHAR everywhere, which isn’t always ideal.

PostgreSQL UUID:

CREATE TABLE users (
    id UUID PRIMARY KEY,
    email TEXT UNIQUE NOT NULL
);        

MYSQL UUID:

CREATE TABLE users (
    id CHAR(36) PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL
);        

Relational databases offer much more than just tables; they include features like events, views, and extensions (especially in PostgreSQL).

However, most ORMs are designed to focus primarily on managing tables. If you want to use these advanced features, you’ll often have to write custom migration scripts manually, which defeats the purpose of the ORM’s promised convenience.

Sometimes ORMs misinterpret your changes, so it’s essential to review the generated migration code to ensure it matches your intentions.

I once made a small change to a field in a Django ORM model, but Django saw it as a field replacement instead of a modification. The migration it generated dropped the old column and created a new one. And when did I notice? After deploying to production. ??

Query Builder

Another major advantage of ORMs is their ability to build queries programmatically. They let you create queries based on your requirements, translate them into SQL, and convert the results into instances of your models or classes.

For simple use cases, ORMs are effective. For example:

Users.getById(10)        

This gets translated to:

SELECT id, name, email FROM users WHERE id = 10 LIMIT 1;        

However, problems arise with complex queries. Tasks like aggregations or highly customized queries can make ORMs cumbersome to use. You often end up writing convoluted and error-prone code, and constantly checking whether the generated SQL matches your intended logic. Debugging these situations can quickly become a frustrating challenge.

1. Design the desired SQL query.
2. Translate it into a form your ORM understands.
3. Verify that the ORM generates the intended query without introducing unexpected changes.

This creates unnecessary complexity and overhead, especially for tasks that could be handled more efficiently with raw SQL.

So, What’s the Solution?

I’ve been wondering if flipping the ORM approach could solve these issues. Instead of starting with models or classes and relying on the ORM to generate queries and migrations, what if we reversed the process?

Here’s the idea:

1. First, you create queries and migrations based on your exact requirements.
2. Then, the ORM is configured to use these pre-defined queries and migrations.

This approach offers several benefits:

1. You have full control over the queries and SQL, ensuring clarity and precision.
2. The output is simpler and more transparent.
3. You’re no longer tied to a single ORM or programming language, making the approach flexible and adaptable across different environments.

In the rest of this post, I’ll explore how this idea can be implemented in Golang, but the concept applies universally. With a bit of research, you can find tools and libraries to apply it in your preferred language or stack.

sqlc: SQL Code Generation

sqlc allows you to write raw SQL queries and generates type-safe Go code directly from those queries. This ensures:

1. You have complete control over the queries and can optimize them for specific databases.
2. Queries are transparent, reducing debugging overhead and avoiding surprises caused by ORM-generated SQL.

Here’s an example workflow:

1. Write your SQL query in a .sql file:

-- name: GetUserByID :one
SELECT id, name, email FROM users WHERE id = $1;        

2. sqlc generates the corresponding Go code:

func (q *Queries) GetUserByID(ctx context.Context, db DBQuerier, id int) (User, error)        

3. Use the generated code in your application:

user, err := queries.GetUserByID(context.Background(), db, 10)        

This approach ensures you’re always in control of the SQL logic while enjoying type-safe, boilerplate-free Go code.

SQLC: https://github.com/sqlc-dev/sqlc

goose: Simple Schema Migrations

For schema migrations, goose is a lightweight yet powerful tool. Unlike ORMs that generate migrations from models, goose lets you write raw SQL migration files for maximum flexibility.

For example:

? Create a migration file:

goose create add_users_table sql        

Write your SQL migration:

-- +goose Up
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username text NOT NULL
);

-- +goose Down
DROP TABLE users;        

Apply the migration:

goose up        

as you can see, there is not any dependencies between your programming language and goose

Goose: https://github.com/pressly/goose

By combining goose and sqlc, you control both schema and queries directly, avoiding the abstractions and compromises of ORMs.

here is a demo project you can check out

https://github.com/ryanparsa/golang-no-orm-demo

What do you think? do you have same experience? share with us