Database-Agnostic Architecture: Why and How

When we started building WellOS, an oil and gas field operations platform, we faced an unusual challenge: our application needed to run in three completely different environments with three different databases.

Cloud deployment: PostgreSQL for the main SaaS platform
On-premise installations: MongoDB for clients with existing NoSQL infrastructure
Mobile offline mode: SQLite for field workers without internet connectivity

Building three separate applications was out of the question. We needed a database-agnostic architecture that could work seamlessly across all three databases. After two years in production, this architecture has proven to be one of our best decisions.

Why Database-Agnostic Architecture?

Most applications are tightly coupled to their database. You use PostgreSQL-specific features, write raw SQL, or leverage ORM features specific to your database. This works great until you need to support multiple databases or migrate to a different one.

Database-agnostic architecture decouples your business logic from database implementation details. The benefits:

Flexibility: Support multiple database engines without code duplication
Easier migrations: Switch databases without rewriting business logic
Better testing: Use SQLite in tests, PostgreSQL in production
Offline support: Sync between SQLite (offline) and PostgreSQL (server)
Client choice: Let enterprise clients use their preferred database

The tradeoff is giving up database-specific optimizations. For WellOS, this was acceptable—business logic complexity far outweighed database complexity.

The Three-Layer Approach

We built database-agnostic architecture using three distinct layers:

1. Domain Layer: Pure Business Logic

The domain layer has zero database dependencies:

// domain/entities/well-inspection.ts
export class WellInspection {
  constructor(
    private readonly id: string,
    private wellId: string,
    private inspectorId: string,
    private inspectionDate: Date,
    private status: InspectionStatus,
    private findings: InspectionFinding[]
  ) {}
 
  addFinding(finding: InspectionFinding): void {
    if (this.status === InspectionStatus.COMPLETED) {
      throw new CompletedInspectionError();
    }
    this.findings.push(finding);
  }
 
  complete(): void {
    if (this.findings.length === 0) {
      throw new NoFindingsError('Cannot complete inspection without findings');
    }
    this.status = InspectionStatus.COMPLETED;
  }
 
  // Pure domain logic, no database code
}

Notice there's no imports from any database library. Just TypeScript classes representing business concepts.

2. Repository Interface: The Contract

Repositories define what operations are needed, not how they're implemented:

// domain/repositories/well-inspection.repository.ts
export interface WellInspectionRepository {
  findById(id: string): Promise<WellInspection | null>;
  findByWellId(wellId: string): Promise<WellInspection[]>;
  findPendingInspections(): Promise<WellInspection[]>;
  save(inspection: WellInspection): Promise<void>;
  delete(id: string): Promise<void>;
}

This interface is database-agnostic. It describes business operations without mentioning SQL, documents, or any database specifics.

3. Multiple Repository Implementations

Each database gets its own implementation:

// infrastructure/persistence/postgresql/well-inspection.repository.ts
@Injectable()
export class PostgreSQLWellInspectionRepository implements WellInspectionRepository {
  constructor(private readonly db: DrizzleService) {}
 
  async findById(id: string): Promise<WellInspection | null> {
    const result = await this.db
      .select()
      .from(wellInspections)
      .where(eq(wellInspections.id, id))
      .limit(1);
 
    return result[0] ? this.toDomain(result[0]) : null;
  }
 
  // ... other methods
}
 
// infrastructure/persistence/mongodb/well-inspection.repository.ts
@Injectable()
export class MongoDBWellInspectionRepository implements WellInspectionRepository {
  constructor(private readonly db: MongoClient) {}
 
  async findById(id: string): Promise<WellInspection | null> {
    const collection = this.db.collection('well_inspections');
    const doc = await collection.findOne({ _id: id });
 
    return doc ? this.toDomain(doc) : null;
  }
 
  // ... other methods
}
 
// infrastructure/persistence/sqlite/well-inspection.repository.ts
@Injectable()
export class SQLiteWellInspectionRepository implements WellInspectionRepository {
  constructor(private readonly db: DrizzleSQLite) {}
 
  async findById(id: string): Promise<WellInspection | null> {
    const result = await this.db
      .select()
      .from(wellInspections)
      .where(eq(wellInspections.id, id))
      .limit(1);
 
    return result[0] ? this.toDomain(result[0]) : null;
  }
 
  // ... other methods
}

All three implement the same interface, so business logic doesn't know which database it's using.

Choosing the Right Repository at Runtime

We use dependency injection to select the appropriate repository based on configuration:

// infrastructure/persistence/persistence.module.ts
@Module({})
export class PersistenceModule {
  static forRoot(config: DatabaseConfig): DynamicModule {
    const repositoryProviders = this.getRepositoryProviders(config.type);
 
    return {
      module: PersistenceModule,
      providers: [...repositoryProviders],
      exports: [...repositoryProviders],
    };
  }
 
  private static getRepositoryProviders(dbType: DatabaseType): Provider[] {
    switch (dbType) {
      case 'postgresql':
        return [
          {
            provide: 'WellInspectionRepository',
            useClass: PostgreSQLWellInspectionRepository,
          },
          // ... other PostgreSQL repositories
        ];
 
      case 'mongodb':
        return [
          {
            provide: 'WellInspectionRepository',
            useClass: MongoDBWellInspectionRepository,
          },
          // ... other MongoDB repositories
        ];
 
      case 'sqlite':
        return [
          {
            provide: 'WellInspectionRepository',
            useClass: SQLiteWellInspectionRepository,
          },
          // ... other SQLite repositories
        ];
    }
  }
}

Application code just injects the interface:

@Injectable()
export class CompleteInspectionUseCase {
  constructor(
    @Inject('WellInspectionRepository')
    private readonly repository: WellInspectionRepository
  ) {}
 
  async execute(inspectionId: string): Promise<void> {
    const inspection = await this.repository.findById(inspectionId);
 
    if (!inspection) {
      throw new NotFoundException();
    }
 
    inspection.complete();
 
    await this.repository.save(inspection);
  }
}

This use case works identically with PostgreSQL, MongoDB, or SQLite. The database is swapped by changing a config variable.

Using Drizzle ORM for Abstraction

We chose Drizzle ORM because it supports both SQL databases (PostgreSQL, SQLite) and has a consistent API:

// schema/well-inspection.schema.ts
import { pgTable, uuid, timestamp, text } from 'drizzle-orm/pg-core';
 
export const wellInspections = pgTable('well_inspections', {
  id: uuid('id').primaryKey(),
  wellId: uuid('well_id').notNull(),
  inspectorId: uuid('inspector_id').notNull(),
  inspectionDate: timestamp('inspection_date').notNull(),
  status: text('status').notNull(),
  findings: text('findings').notNull(), // JSON
});

For SQLite, we use the same schema with SQLite-specific imports:

// schema/well-inspection.schema.sqlite.ts
import { sqliteTable, text, integer } from 'drizzle-orm/sqlite-core';
 
export const wellInspections = sqliteTable('well_inspections', {
  id: text('id').primaryKey(),
  wellId: text('well_id').notNull(),
  inspectorId: text('inspector_id').notNull(),
  inspectionDate: integer('inspection_date', { mode: 'timestamp' }).notNull(),
  status: text('status').notNull(),
  findings: text('findings').notNull(), // JSON
});

Drizzle's query API is identical for both:

// Same code works for PostgreSQL and SQLite
const results = await db
  .select()
  .from(wellInspections)
  .where(eq(wellInspections.status, 'pending'));

For MongoDB, we wrote a thin adapter that mimics Drizzle's API:

export class MongoDBAdapter {
  constructor(private client: MongoClient) {}
 
  collection(name: string) {
    return this.client.db().collection(name);
  }
 
  async select() {
    return {
      from: (collection: string) => ({
        where: (filter: any) => this.collection(collection).find(filter).toArray(),
      }),
    };
  }
}

This isn't perfect but gave us 80% API compatibility.

Handling Database-Specific Features

Database-agnostic architecture means giving up some database-specific features. Here's how we handled common scenarios:

Transactions

Each database handles transactions differently. We created an abstract transaction interface:

export interface TransactionManager {
  runInTransaction<T>(work: () => Promise<T>): Promise<T>;
}
 
// PostgreSQL implementation
export class PostgreSQLTransactionManager implements TransactionManager {
  async runInTransaction<T>(work: () => Promise<T>): Promise<T> {
    return await this.db.transaction(async (tx) => {
      return await work();
    });
  }
}
 
// MongoDB implementation
export class MongoDBTransactionManager implements TransactionManager {
  async runInTransaction<T>(work: () => Promise<T>): Promise<T> {
    const session = this.client.startSession();
    try {
      session.startTransaction();
      const result = await work();
      await session.commitTransaction();
      return result;
    } catch (error) {
      await session.abortTransaction();
      throw error;
    } finally {
      session.endSession();
    }
  }
}

Full-Text Search

PostgreSQL has excellent full-text search. MongoDB has text indexes. SQLite has FTS5. We abstracted it:

export interface SearchRepository {
  searchWells(query: string): Promise<Well[]>;
}
 
// PostgreSQL uses ts_vector
export class PostgreSQLSearchRepository implements SearchRepository {
  async searchWells(query: string): Promise<Well[]> {
    const results = await this.db.execute(sql`
      SELECT * FROM wells
      WHERE search_vector @@ to_tsquery(${query})
    `);
    return results.map(this.toDomain);
  }
}
 
// MongoDB uses text index
export class MongoDBSearchRepository implements SearchRepository {
  async searchWells(query: string): Promise<Well[]> {
    const results = await this.collection.find({
      $text: { $search: query }
    }).toArray();
    return results.map(this.toDomain);
  }
}

JSON Columns

PostgreSQL has native JSONB. MongoDB is document-based. SQLite stores JSON as text:

// Domain model uses plain objects
export class WellInspection {
  private metadata: Record<string, any>;
}
 
// PostgreSQL stores as JSONB
const pgSchema = pgTable('inspections', {
  metadata: jsonb('metadata'),
});
 
// MongoDB stores natively
await collection.insertOne({
  metadata: { key: 'value' }
});
 
// SQLite serializes to text
const sqliteSchema = sqliteTable('inspections', {
  metadata: text('metadata'), // Store as JSON string
});

Syncing Between Databases

For offline mobile support, we sync between SQLite (mobile) and PostgreSQL (server). This required careful design:

Change Tracking

Every entity tracks its last modified timestamp:

export class WellInspection {
  private updatedAt: Date;
 
  update(): void {
    this.updatedAt = new Date();
  }
}

Sync Algorithm

export class SyncService {
  async syncInspections(lastSyncAt: Date): Promise<void> {
    // 1. Get changes from server since last sync
    const serverChanges = await this.serverRepository.findModifiedSince(lastSyncAt);
 
    // 2. Get local changes since last sync
    const localChanges = await this.localRepository.findModifiedSince(lastSyncAt);
 
    // 3. Apply server changes to local DB
    for (const change of serverChanges) {
      await this.localRepository.save(change);
    }
 
    // 4. Push local changes to server
    for (const change of localChanges) {
      await this.serverRepository.save(change);
    }
 
    // 5. Update sync timestamp
    await this.syncStateRepository.updateLastSync(new Date());
  }
}

Conflict Resolution

When the same entity is modified offline and on the server, we use last-write-wins:

async resolveConflict(
  local: WellInspection,
  server: WellInspection
): Promise<WellInspection> {
  if (local.updatedAt > server.updatedAt) {
    return local; // Local change is newer
  }
  return server; // Server change is newer
}

For critical data, we flag conflicts for manual resolution:

if (local.updatedAt === server.updatedAt && !local.equals(server)) {
  await this.conflictRepository.save(new Conflict(local, server));
  throw new ConflictError('Manual resolution required');
}

Testing Across Databases

Database-agnostic architecture made testing straightforward:

describe('WellInspectionService', () => {
  const databases = ['postgresql', 'mongodb', 'sqlite'];
 
  databases.forEach((dbType) => {
    describe(`with ${dbType}`, () => {
      let repository: WellInspectionRepository;
 
      beforeEach(async () => {
        repository = await createRepositoryForDatabase(dbType);
      });
 
      it('should complete inspection', async () => {
        const inspection = new WellInspection(/* ... */);
        await repository.save(inspection);
 
        inspection.complete();
        await repository.save(inspection);
 
        const retrieved = await repository.findById(inspection.id);
        expect(retrieved.status).toBe(InspectionStatus.COMPLETED);
      });
    });
  });
});

We run the full test suite against all three databases. This caught database-specific bugs early.

Lessons Learned

Lesson 1: Keep Repository Interface Simple

We initially added too many specialized query methods. This made it hard to implement across databases. We learned to keep the interface minimal and use specification pattern for complex queries.

Lesson 2: Accept Some Duplication

Having separate schema files for PostgreSQL and SQLite felt like duplication, but trying to abstract them was worse. Some duplication is okay.

Lesson 3: Document Database Limitations

We documented which features weren't available on which databases:

/**
 * Note: Full-text search quality varies by database:
 * - PostgreSQL: Excellent (ts_vector)
 * - MongoDB: Good (text indexes)
 * - SQLite: Basic (FTS5)
 */
export interface SearchRepository {
  searchWells(query: string): Promise<Well[]>;
}

Lesson 4: Use the Right Tool Per Environment

We don't force the same database everywhere:

Production SaaS: PostgreSQL for ACID guarantees
Mobile offline: SQLite for simplicity
On-premise: MongoDB if client prefers it

Database-agnostic architecture gave us this flexibility.

When to Use Database-Agnostic Architecture

Use it when:

Supporting multiple deployment environments
Building offline-first applications
Migrating between databases
Letting enterprise clients choose their database
Future database flexibility is valuable

Don't use it when:

You need advanced database-specific features
Performance optimizations require database-specific code
You're certain you'll never change databases
Team lacks discipline to maintain abstraction

Conclusion

Building WellOS with database-agnostic architecture let us support PostgreSQL, MongoDB, and SQLite with a single codebase. Field workers use SQLite offline, corporate users have PostgreSQL, and on-premise clients can use MongoDB.

The key is clean separation between domain logic and persistence, simple repository interfaces, and discipline to avoid database-specific features leaking into business code.

After 27 years of building applications, I've learned that staying database-agnostic provides invaluable flexibility. The slight complexity cost pays for itself when requirements inevitably change.