Database (TypeORM)

The API uses TypeORM with PostgreSQL for relational data storage.

Entity Relationships

Entities

Entities live in src/data/entities/ and define the database schema.

import { Entity, PrimaryGeneratedColumn, Column, ManyToOne } from "typeorm"

@Entity("books")
export class Book {
	@PrimaryGeneratedColumn("uuid")
	id: string

	@Column()
	title: string

	@Column({ nullable: true })
	description?: string

	@ManyToOne(() => User, (user) => user.books)
	owner: User

	@Column()
	ownerId: string

	@CreateDateColumn()
	createdAt: Date

	@UpdateDateColumn()
	updatedAt: Date
}

Repositories

Repositories extend TypeormRepository<T> for standardized data access:

import { TypeormRepository } from "./TypeormRepository"

export class BookRepository extends TypeormRepository<Book> {
	constructor() {
		super(Book)
	}

	async findByOwner(ownerId: string): Promise<Book[]> {
		return this.repository.find({
			where: { ownerId },
			order: { createdAt: "DESC" },
		})
	}
}

Built-in Methods

TypeormRepository<T> provides:

• findById(id) - Get by ID
• findAll() - Get all
• find(query) - Paged query
• create(data) - Insert
• update(id, data) - Update
• delete(id) - Delete
• verifyValue(value) - SQL injection protection

Caching

Repositories use Redis caching automatically:

// Cache is invalidated on insert/update/delete
const book = await this.bookRepository.findById(id)

Migrations

Generate Migration

After changing entities:

pnpm db:generate MigrationName

Run Migrations

pnpm db:migrate

Revert Migration

pnpm db:revert

Remote Migrations

For dev/prod environments (requires Tailscale):

./scripts/postgres/db-migrate.sh dev
./scripts/postgres/db-migrate.sh prod

Query Building

Use TypeORM's query builder for complex queries:

async findWithStats(ownerId: string) {
	return this.repository
		.createQueryBuilder("book")
		.leftJoinAndSelect("book.readings", "reading")
		.where("book.ownerId = :ownerId", { ownerId })
		.select([
			"book.id",
			"book.title",
			"COUNT(reading.id) as readCount",
		])
		.groupBy("book.id")
		.getRawMany()
}

Relations

Define Relations

@Entity("users")
export class User {
	@OneToMany(() => Book, (book) => book.owner)
	books: Book[]
}

Load Relations

// Eager load
const book = await this.repository.findOne({
	where: { id },
	relations: ["owner"],
})

// Lazy load (entity must define as Promise)
const owner = await book.owner