Skip to content

migration-postgres-sqlite.md

Latest commit

 

History

History
107 lines (82 loc) · 5.56 KB

migration-postgres-sqlite.md

File metadata and controls

107 lines (82 loc) · 5.56 KB
Raw

Migration guide: in-memory → Postgres / SQLite

ADR-0001 ships an in-memory catalogue behind a HotelRepository interface precisely so a real database can be dropped in later without touching domain or route code. This guide documents exactly how that swap goes — the design intent made concrete, even though only the in-memory implementation ships.

The seam

Everything depends on one contract, src/repository/hotel-repository.ts:

export interface HotelRepository {
  findAll(): Property[];
  findById(id: string): Property | undefined;
}

Routes read app.repository (decorated in src/app.ts); the domain layer takes plain Property[] and never touches a data source at all. So a migration is scoped to:

  1. Add a new implementation of HotelRepository (e.g. postgres-hotel-repository.ts).
  2. Change one line in app.ts to construct it instead of the in-memory one.

No route, schema, or domain file changes. The propertySchema in src/domain/hotel.ts stays the single source of truth for a Property's shape.

What "async" costs

The in-memory methods are synchronous. A real database is I/O, so the interface would become async:

export interface HotelRepository {
  findAll(filters?: HotelFilters): Promise<Property[]>;
  findById(id: string): Promise<Property | undefined>;
}

The route handlers already run inside async Fastify callbacks, so this is a mechanical change: const property = app.repository.findById(id) becomes const property = await app.repository.findById(id). The in-memory implementation would wrap its returns in Promise.resolve(...) to keep satisfying the same interface, so the tests that inject a fixture repository keep working unchanged.

Pushing filters down

Today filterHotels and priceFrom (in src/domain/search.ts) run in application code over the full array. That was a deliberate choice for a 40-row seed, and the functions are written so the move into SQL is mechanical: each predicate maps to a WHERE clause. With a real store you'd pass the filters into the repository and let the database do the work:

Domain predicate SQL push-down
city (case-insensitive exact) WHERE lower(city) = lower($1)
star_rating (exact) AND star_rating = $2
min_price / max_price over price_from AND price_from BETWEEN $3 AND $4 (see the price_from note below)
(future) pagination LIMIT / OFFSET — the seam is where this would land, per ASSUMPTIONS.md

price_from is a derived value (the min room rate), so in a relational schema it's either a correlated sub-select, a JOIN … GROUP BY MIN(price_per_night), or a maintained column / materialized view if read volume warrants it. The pure priceFrom function stays useful as the canonical definition and for any in-app fallback.

Schema sketch

A Property has nested address, contact, policies, amenities[], and rooms[] (with available_dates[]). Two shapes fit:

  • Relational (Postgres/SQLite): properties, rooms (FK → property), and either a room_available_dates table (room_id, night DATE) or a date[] column on Postgres. Address/contact/policies can be columns on properties or 1:1 tables. Availability then becomes a query — a room is available when the count of its open nights intersecting the requested window equals the number of nights requested — but the repository can still return fully-hydrated Property objects so the domain code is unaffected.
  • Document/JSONB: store each Property as a JSONB blob mirroring propertySchema. Closest to the current seed; least migration effort; filters use JSONB operators.

Whichever shape, the repository's job is to rehydrate rows back into Property objects that satisfy propertySchema — validate on the way out just as loadCatalogue does today, so a drifted database fails loudly at the boundary rather than deep in a request.

SQLite specifics

SQLite (e.g. via better-sqlite3) is the lightest step up and can stay synchronous, so the interface wouldn't even need to go async — the swap really is just a new HotelRepository implementation plus one line in app.ts. Good for a single-node deployment or embedding the catalogue as a file artifact.

Postgres specifics

For Postgres (e.g. pg or an ORM), add a connection pool, run migrations to create the schema, and seed from data/hotels.json once. Wire the pool's lifecycle into Fastify (register as a plugin; close it in onClose) so connections are cleaned up on shutdown. The async interface above applies.

Checklist

  • New *-hotel-repository.ts implementing HotelRepository.
  • Validate rows back through propertySchema on read.
  • (Postgres) make the interface async; await the three call sites in hotels.ts (one findAll, two findById).
  • Push HotelFilters into the query; keep priceFrom as the canonical definition.
  • Swap the constructor in app.ts; leave routes, schemas, and domain untouched.
  • Point the injected test repository at the same interface — existing tests should pass.