Skip to main content

SQL

This document covers SQL usage in our projects, focusing on PostgreSQL (primary).

Overview​

SQL (Structured Query Language) is the standard language for interacting with relational databases. In this project, we use PostgreSQL as our primary relational database.

PostgreSQL​

When to use​

  • Backend services requiring data persistence.
  • APIs that need relational data with complex queries.

Key features​

  • Open-source, robust, ACID-compliant.
  • Rich data types (JSON, arrays, enums, ranges).
  • Advanced indexing and query optimization.
  • Full-text search, GIS, and extensions ecosystem.

Installation & setup​

PostgreSQL is typically run via Docker Compose during local development:

postgres:
  image: postgres:16-alpine
  environment:
    POSTGRES_PASSWORD: password
    POSTGRES_DB: app_db
  ports:
    - "5432:5432"

Usage with Prisma & NestJS​

Define your schema in prisma/schema.prisma:

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model User {
  id    Int     @id @default(autoincrement())
  email String  @unique
  name  String?
  posts Post[]
}

model Post {
  id    Int     @id @default(autoincrement())
  title String
  content String
  userId Int
  user   User    @relation(fields: [userId], references: [id])
}

Run migrations:

pnpm prisma migrate dev --name init

Use in NestJS service:

import { Injectable } from '@nestjs/common';
import { PrismaService } from './prisma.service';

@Injectable()
export class UserService {
  constructor(private prisma: PrismaService) {}

  async findAll() {
    return this.prisma.user.findMany({ include: { posts: true } });
  }
}

Best practices​

  • Keep migrations version-controlled (Liquibase or Prisma migrations).
  • Use indexes on frequently queried columns.
  • Leverage PostgreSQL-specific features (JsonB, enums) when appropriate.
  • Always validate and sanitize user inputs (handled by Prisma / Zod).

Example queries​

Raw queries in Prisma:

const users = await this.prisma.$queryRaw`
  SELECT * FROM "User" WHERE email LIKE ${pattern}
`;

Using Prisma client (recommended):

const user = await this.prisma.user.findUnique({ where: { email } });

General SQL best practices​

  1. Validation: Always validate and sanitize inputs (use Prisma / Zod).
  2. Indexing: Create indexes on foreign keys and frequently queried columns.
  3. Normalization: Follow database normalization to avoid data redundancy.
  4. Migrations: Use version control (Liquibase for SQL Server, Prisma migrations for PostgreSQL).
  5. Monitoring: Use tools like Prometheus exporters to track query performance and connection pools.
  6. Backup & Recovery: Automate backups and document recovery procedures.

Migration tools​

Use Prisma migrations for version-controlled schema updates:

pnpm prisma migrate dev --name add_new_table

For more advanced migration scenarios, Liquibase is also supported.

Further reading​