Breaking Up With ORMs – Part One: Reclaiming Control with Custom SQL Adapters

SOG-web — Sat, 17 May 2025 12:59:42 +0000

Breaking Up With ORMs – Part One: Reclaiming Control with Custom SQL Adapters

Why I built my own SQL adapter instead of using ORMs like Prisma and Drizzle — and how it gives me total control over querying from both the frontend and backend.

“It’s not you, it’s me. I just need more... control.”

✋ This Is Just the Beginning

This post is more of a breaker—an introduction to why I left ORMs behind and what motivated me to build my own database adapter.

A deeper, more technical dive is coming soon, where I’ll break down:

How the entire SDK works
How to build it from scratch
The architectural design choices
Live usage with real-world apps
Open source links to both the frontend SDK and the backend sdk
And more

So if this resonates with you, stay tuned—the code will be open and the full post will be linked shortly.

Introduction: Why I Broke Up With ORMs

I’ll be honest: I love ORMs. I’ve used Prisma, I’ve used Drizzle, and I admire the engineering behind them. They offer a lot—developer productivity, type safety, and beautiful DX (developer experience).

But after a while, I started running into walls:

I wanted more flexibility—more control over my queries, more visibility into the SQL, and more freedom to design my API the way I wanted. ORMs began to feel like an abstraction I had to fight against rather than something working with me.

ORMs abstract too much
I wanted to see and control the actual SQL
I wanted to dynamically generate complex queries from the client and still have authentication and authorization (like an RLS system)
I wanted to define my schema through APIs, not only through files
I needed a system that worked seamlessly with both frontend and backend

And I just didn’t want to fight the abstraction anymore.

So I did what any control-obsessed backend dev would do: I built my own SQL adapter.

The Problem: When ORMs Start to Get in the Way

Here are a few pain points I ran into while using ORMs:

Performance Cliffs: Some ORMs made poor choices under the hood (e.g. N+1 queries, unnecessary joins).
Migrations & Schema Control: I wanted to create and modify tables dynamically via API, not just manually define them in migration files.
Query Transparency: I wanted to see and shape the exact SQL being executed—not reverse-engineer it.
Vendor Lock-in: Switching ORMs or DB engines became a large undertaking.

The Solution: A KNEX Adapter

To solve this, I built the A KNEX Adapter—a lightweight, expressive query layer built on top of Knex.js, but designed for composability and API-first systems.

Here’s what it supports and includes:

Deeply nested filter groups (AND, OR, EXISTS)
Aggregates, window functions, and recursive CTEs
Grouped filters, complex pagination, raw joins (securely)
Full control over the query lifecycle
Dynamic schema creation via API (covered in Part 2)
A powerful frontend SDK: Think Prisma-style chaining, but built to run on the client.
A flexible backend SDK: Built on Knex with support for deeply structured queries, CTEs, RLS, and window functions.
A standard QueryParams format to allow structured, safe queries across the wire.
A sync layer, auditing hooks, and dynamic table creation (covered in future posts).

Every code in this post is from the frontend SDK, showing how to build deeply structured queries directly from the client to your API.

Example: Fluent Querying Without the ORM Bloat

Let’s say you want to find all active users who are either admins or IT managers:

db.table<User>("users")
  .where("status", "active")
  .andWhere((query) => {
    query.where("role", "admin").orWhere((subQuery) => {
      subQuery.where("role", "manager").where("department", "IT");
    });
  })
  .query();

Or do grouped aggregates with HAVING:

db.table<Order>("orders")
  .groupBy("customer_id", "status")
  .having("total_amount", ">", 1000)
  .sum("amount", "total_amount")
  .count("id", "order_count")
  .query();

All that without writing raw SQL or defining a separate model schema.

Why Not Just Stick With Knex?

Great question. Knex is powerful—but also low-level. It’s great for quick prototyping, but hard to standardize or reuse across a multi-service architecture.

The KNEX Adapter gives you:

A structured QueryParams interface: everything from filters to CTEs in a predictable, typed shape.
Safe, composable patterns for clients to generate queries.
Support for syncing, auditing, and RLS—coming in Part 3.
Secure query abstraction without losing power

What’s Next

👉 In the next post, I’ll show how I built it, and how you can use the same architecture to:

Build an API that can create and modify SQL tables dynamically
Support row-level security and fine-grained access controls
Use client SDKs to query with EXISTS, GROUPED, HAVING, WINDOWS, and more
Drop-in-replacement to any SQL database

🔗 Open Source Coming Soon

The SDK will be open-sourced on GitHub — follow me there or at Dynamic Database API (Experimental) to be the first to try it (this is an unclean and unpublished version.

Final Thoughts

This isn’t a rant against ORMs. They’re amazing for many projects. But for API-first systems where flexibility, transparency, and control matter most—going adapter-first gives you an edge.

In Part Two, we’ll dive into dynamic table creation through API, including how to build and manage schemas without migration files or CLI tools.

Stay tuned 👇

Follow me for the next part, or subscribe if you're into flexible backends, system design, and self-hostable dev tools

orm, #sql, #knex, #sdk, #backend, #typescript, #api, #database, #open-source, #devtools

DEV Community: SOG-web

Breaking Up With ORMs – Part One: Reclaiming Control with Custom SQL Adapters

Breaking Up With ORMs – Part One: Reclaiming Control with Custom SQL Adapters

✋ This Is Just the Beginning

Introduction: Why I Broke Up With ORMs

The Problem: When ORMs Start to Get in the Way

The Solution: A KNEX Adapter

Example: Fluent Querying Without the ORM Bloat

Why Not Just Stick With Knex?

What’s Next

🔗 Open Source Coming Soon

Final Thoughts

orm, #sql, #knex, #sdk, #backend, #typescript, #api, #database, #open-source, #devtools