Why I made my database layer deliberately boring

Martin — Thu, 08 Oct 2026 10:14:00 +0000

Almost every side project I start begins the same way: a single SQLite file next to the code. No Docker, no connection strings, no hosted database to provision. It is the fastest way to get from an idea to something that runs.

And every project that survives long enough hits the same wall. It needs concurrent writers, or a second instance, or a hosted database that someone else backs up. That means moving to Postgres. And that used to mean rewriting the data layer.

Not because the logic changed. The app still creates puzzles, finds users and marks things as done. What changed was the SQL dialect, the driver API, how booleans and dates come back, how transactions are started, and the dozen small differences hiding in every query.

After doing that rewrite one time too many, I built repolayer.

The idea in one snippet

const repo = await createRepo<Puzzle>({
  driver: 'sqlite',                    // <- the only line that changes
  table: 'puzzles',
  schema: puzzleSchema,
  connection: { file: './data.db' },
});

You write your application code against a plain Repo<T> interface. Underneath, an adapter talks to SQLite, Postgres, MySQL or MariaDB. Moving from one to another means changing the config, not the code that uses it.

Defining a table

The schema is a plain object. There is no validator library to install and no decorators.

import { createRepo, defineSchema, type Infer } from 'repolayer';

const puzzleSchema = defineSchema({
  id:         { type: 'string',  primaryKey: true },
  title:      { type: 'string' },
  slug:       { type: 'string',  unique: true },
  difficulty: { type: 'integer' },
  solved:     { type: 'boolean' },
  tags:       { type: 'json',    nullable: true },
  createdAt:  { type: 'date',    column: 'created_at' },
  updatedAt:  { type: 'date',    column: 'updated_at' },
});

type Puzzle = Infer<typeof puzzleSchema>;

The TypeScript type is inferred from the schema, so there is a single source of truth. Column names can differ from field names (createdAt ↔ created_at), and types like boolean, date and json come back as real booleans, Date objects and parsed values regardless of how the engine stores them.

Using it

const repo = await createRepo<Puzzle>({
  driver: 'sqlite',
  table: 'puzzles',
  schema: puzzleSchema,
  connection: { file: './data.db' },
  timestamps: true,
  ensureTable: true,
});

const puzzle = await repo.create({ title: 'Sudoku', difficulty: 3, solved: false });

await repo.findById(puzzle.id);
await repo.findOne({ where: { slug: 'sudoku' } });
await repo.count({ where: [{ field: 'difficulty', op: 'gte', value: 5 }] });

await repo.findMany({
  where: [
    { field: 'solved', op: 'eq', value: false },
    { field: 'title',  op: 'ilike', value: 'sud%' },
  ],
  orderBy: [{ field: 'createdAt', direction: 'desc' }],
  limit: 20,
});

await repo.update(puzzle.id, { solved: true });   // throws NotFoundError if it is gone
await repo.deleteMany({ where: { solved: true } });

Transactions are a callback. Returning commits, throwing rolls back:

await repo.withTransaction(async (tx) => {
  // use tx exactly like repo
});

And for larger datasets there is streaming and keyset pagination:

for await (const row of repo.stream({ where: { solved: false } })) {
  // rows arrive in batches, not all at once
}

await repo.findPage(
  { orderBy: [{ field: 'createdAt', direction: 'desc' }] },
  { limit: 20 },
);

That is roughly the whole surface. You can read it in five minutes, and that is the point.

The swap

Here is what moving to Postgres looks like in practice:

const repo = await createRepo<Puzzle>({
  driver: process.env.DATABASE_URL ? 'postgres' : 'sqlite',
  table: 'puzzles',
  schema: puzzleSchema,
  connection: process.env.DATABASE_URL
    ? { connectionString: process.env.DATABASE_URL }
    : { file: './data.db' },
});

SQLite locally, Postgres in production, same application code. The Postgres and MySQL drivers (pg, mysql2) are optional peer dependencies that are loaded lazily, so a project that only uses SQLite never installs or loads them.

What it is not

This is the part I care most about.

repolayer is not an ORM. There is no query builder DSL, no migration engine, no relationship mapping and no lazy loading. It is a deliberately boring contract: a Repo<T> interface with predictable methods, a small serializable query shape, and four adapters that satisfy it identically.

That restriction is the whole design. Every abstraction over multiple databases faces the same tension: the more of each engine you expose, the less the abstraction holds. Most tools leak the moment you need something dialect specific, and once your code depends on a Postgres-only feature, "swap the database" stops being true.

So the filter language is kept small on purpose. Equality, comparisons, like/ilike, combinations of those, ordering and limits. Those are things every engine can do the same way. If I let the query shape grow to cover everything, I would end up with a worse version of SQL and a promise I could not keep.

When you genuinely need a window function or a recursive CTE, you should write SQL for it. repolayer covers the 90 % of data access that is plain CRUD, and stays out of the way for the rest.

How do I know the engines actually behave the same?

Saying "works on four databases" in a README is cheap. Proving it is the interesting part.

repolayer has one shared conformance suite: a single set of tests that describes what every Repo<T> method must do, including edge cases like ordering of nulls, case-insensitive matching, how missing rows are reported and what happens to a transaction that throws. Every adapter runs the same suite. SQLite runs in-process; Postgres, MySQL and MariaDB run against real databases in Docker in CI.

If an engine behaves differently, the suite fails, and the adapter gets fixed or the behaviour gets normalized. That suite is the actual product. The adapters are just the code that passes it.

The suite is also exported, so if you write your own adapter, you can run it against your implementation and get the same guarantee.

Testing without a database

Because the application only knows about Repo<T>, there is a fifth implementation: MemoryRepo, available from repolayer/memory. It keeps everything in memory and passes the same conformance suite as the real adapters.

That means unit tests can use an in-memory repo that behaves like the real thing, without spinning up a database or mocking every method by hand. The behaviour you test against is the behaviour you get in production.

No native dependencies

The SQLite adapter uses node:sqlite, the SQLite module built into Node itself. There is no better-sqlite3 to compile and no native build step that breaks on a new Node version or on Windows. The trade-off is that repolayer requires Node 22.5 or newer.

Why not Drizzle, Kysely or Knex?

They are great tools, and if you want a query builder, use one of them. They solve a different problem: giving you expressive, typed access to SQL. That power is exactly what ties your code to a dialect.

repolayer makes the opposite trade. You get less expressiveness, and in return a guarantee that the code you write today runs unchanged on another engine tomorrow. If that guarantee matters more to you than the full power of SQL, it might be a good fit. If not, it probably is not, and that is fine.

Try it

npm install repolayer
npm install pg       # only for Postgres
npm install mysql2   # only for MySQL or MariaDB

The repository has runnable examples for swapping engines, paging an API, streaming an export and testing with MemoryRepo, plus a roadmap that lists what is shipped, what is coming, and what is never coming.

GitHub: https://github.com/CuplexUser/Repolayer
npm: https://www.npmjs.com/package/repolayer

I am using it in my own projects, and I would love to hear where the abstraction breaks for you. Is the restricted query shape a dealbreaker, or exactly what you want? Issues and comments are very welcome.

DEV Community: Martin