Designing a Database Layer That Survives Product Changes

A backend usually starts clean. Then the product changes. A field is renamed. A relation is added. A filter becomes more complex. A dashboard needs the same data with different grouping. A migration goes out. Suddenly the database layer is everywhere.

That is the problem I try to avoid early.

The goal is not to hide the database completely. The goal is to keep database decisions in the right place.

Product code should not know too much SQL

I do not like seeing raw queries scattered across route handlers. It works in the beginning, but later every endpoint becomes hard to change.

Instead, I prefer one clear module for each domain:

export async function findActiveSubscriberByEmail(email: string) {
  return db.query.subscribers.findFirst({
    where: and(eq(subscribers.email, email), eq(subscribers.status, "active")),
  });
}

The route does not need to know how the subscriber table is shaped. It only needs the behavior.

Name functions by product intent

Database helpers should not be named only by implementation.

Bad:

getUserFromDb()

Better:

findUserForSession()
findPublicProfileBySlug()
listPublishedArticles()
markSubscriberUnsubscribed()

These names explain why the query exists. When the schema changes, the function name still describes the product behavior.

Do not return every column by default

select * is convenient until the table grows.

Returning extra data creates problems:

• More network transfer
• More accidental coupling
• More chances to expose private fields
• More frontend confusion

I like selecting the fields the caller actually needs.

const publicUserColumns = {
  id: users.id,
  name: users.name,
  avatarUrl: users.avatarUrl,
};

This is not just performance work. It is product safety.

Keep mutations boring

Mutations should be very explicit. When money, subscriptions, auth, or permissions are involved, I do not want clever hidden behavior.

export async function unsubscribeEmail(email: string) {
  return db
    .update(subscribers)
    .set({
      status: "unsubscribed",
      unsubscribedAt: new Date(),
      updatedAt: new Date(),
    })
    .where(eq(subscribers.email, email))
    .returning({ id: subscribers.id, status: subscribers.status });
}

There is no magic here. That is the point.

Migrations are part of the code

I treat migrations as code, not as a side effect.

Before a migration goes out, I want to know:

• Is it backward compatible?
• Can old app code run during deploy?
• Does it need a backfill?
• Does it lock a large table?
• Can it be rolled forward if something fails?
• Are nullable fields temporary or permanent?

Most production database issues are not caused by complex SQL. They are caused by casual migrations.

The service layer owns decisions

The database layer should fetch and mutate. The service layer should decide.

For example, “subscribe this email” may need logic:

• If email does not exist, create it
• If email exists and is active, return already subscribed
• If email exists and is unsubscribed, reactivate it
• If validation fails, return a clean error

That behavior belongs in a service function, not inside every route.

My rule

If the same database idea appears in two route handlers, it probably needs a function.

If the same product decision appears in two service functions, it probably needs a clearer domain model.

This is not about making the architecture fancy. It is about making future changes cheap.

The database is one of the most expensive parts of a product to change badly. A clean layer gives the product room to move without breaking everything around it.