Skip to content

DATABASE.md

Latest commit

 

History

History
158 lines (100 loc) · 3.42 KB

DATABASE.md

File metadata and controls

158 lines (100 loc) · 3.42 KB

Database Setup – Neon + Drizzle ORM

This project uses serverless PostgreSQL via Neon and Drizzle ORM for type-safe schema and migration management.

The goal is to have:

  • a ready-to-use cloud database (Neon)
  • declarative TypeScript schema
  • versioned and reproducible migrations
  • full support for local development and CI

Why Neon

Neon was chosen because:

  • it provides managed serverless PostgreSQL
  • it does not require containers or complex local setups
  • it is suitable for demos, assignments, and CI environments
  • it exposes a standard PostgreSQL connection string

For local development, the use of docker-compose with PostgreSQL is still provided, maintaining the same application interface.

Environment Variables

Database connection occurs via environment variable:

DATABASE_URL=postgresql://...

The .env file is not versioned and is excluded from the repository. An .env.example file is provided as a reference instead.

This approach ensures:

  • credential security
  • clarity for anyone cloning the repository
  • compatibility with cloud and CI environments

Why Drizzle ORM

Drizzle was chosen over more "magical" ORMs because:

  • It is an explicit requirement of the assignment
  • it is type-safe with no runtime overhead
  • the schema is written in TypeScript (single source of truth)
  • migrations are explicit, versioned SQL
  • it does not hide database operations
  • it integrates well with the Nx monorepo

Drizzle does not generate runtime code: the database remains central and transparent.

Database Schema

The schema is defined in:

libs/api/db/src/schema.ts

Currently contains:

  • users table
  • access_logs table

Tables are modeled directly via Drizzle, including:

  • types
  • constraints
  • indexes
  • relations

Notes on indexes and configuration

The function:

(t) => ({
  userIdIdx: index(...).on(t.userId)
})

serves to define:

  • indexes
  • additional constraints
  • extra table configurations

Migrations

Migrations are automatically generated by Drizzle:

pnpm db:generate

This command:

  • compares the TypeScript schema with the database state
  • generates a versioned SQL migration

Migrations are applied with:

pnpm db:migrate

Drizzle Studio

To inspect the database, Drizzle Studio is available:

pnpm db:studio

Studio allows you to:

  • view tables and columns
  • verify constraints and indexes
  • check database state

Note: Drizzle Studio is currently in Beta.

Current Status

  • Schema applied correctly
  • Tables created on Neon
  • Initial migration versioned
  • Studio operational

The database is ready for integration with the NestJS API.

NestJS Integration

I centralized the Postgres connection and the Drizzle client in a dedicated Nx library. The Nest API consumes the client via a DI provider (token-based), allowing me to reuse the same configuration everywhere, maintain dependency isolation per project, and make the DB part testable and replaceable. The provider is exposed by DatabaseModule via the DB token.

Access logs (login audit)

The access_logs table is used to record user access upon login:

  • user_id (FK on users)
  • ip_address (if available)
  • user_agent (if available)
  • created_at

The API exposes a protected endpoint to retrieve the last accesses:

  • GET /me/access-history?limit=5

The query is ordered by created_at DESC and limited (default 5, max 50) to avoid excessive reads.