SQL con SQLite y TypeScript: Base de Datos para DevJobs

Este módulo añade una base de datos SQLite real al backend de la API DevJobs, completamente tipada con TypeScript. A diferencia del módulo Express (que usaba un array en memoria), aquí los datos persisten en disco, las consultas utilizan SQL nativo con JOINs y los tipos de TypeScript se infieren directamente de los esquemas Zod.

Lo que aprenderás

SQLite con better-sqlite3 — driver síncrono de alto rendimiento para SQLite en Node.js
TypeScript en Express — tipado estricto de requests, responses y modelos de datos
Zod + inferencia de tipos — definir el esquema una sola vez y derivar los tipos TS automáticamente
SQL nativo — queries con JOIN, GROUP_CONCAT, WHERE dinámico y transacciones
WAL mode y foreign keys — configuración de SQLite para entornos de producción
Patrón DTO — CreateJobDTO, UpdateJobDTO y separación de entidades de API

Estructura del Proyecto

08-sql/backend/src/
├── app.ts                  # Entry point Express con TypeScript
├── types.ts                # Interfaces: Job, JobData, JobContent, DTOs y filtros
├── routes/
│   └── jobs.ts             # Router de empleos
├── controllers/
│   └── job.ts              # Controlador con tipos Request/Response
├── models/
│   └── job.ts              # Consultas SQL con better-sqlite3
├── schemas/
│   └── job.ts              # Esquemas Zod + tipos inferidos automáticamente
├── db/
│   ├── database.ts         # Conexión SQLite (WAL mode, foreign keys)
│   └── seed.ts             # CREATE TABLE y datos de ejemplo
└── middlewares/
    ├── cors.ts             # Middleware CORS
    └── validation.ts       # Middleware de validación Zod reutilizable

Esquema de la Base de Datos

El archivo db/seed.ts define las tablas con CREATE TABLE IF NOT EXISTS y las puebla con datos de ejemplo mediante una transacción atómica:

db.exec(`
  CREATE TABLE IF NOT EXISTS jobs (
    id TEXT PRIMARY KEY,
    title TEXT NOT NULL,
    company TEXT NOT NULL,
    location TEXT NOT NULL,
    description TEXT NOT NULL,
    modality TEXT NOT NULL CHECK(modality IN ('remote', 'onsite', 'hybrid')),
    level TEXT NOT NULL CHECK(level IN ('junior', 'mid', 'senior'))
  );

  CREATE TABLE IF NOT EXISTS job_technologies (
    job_id TEXT NOT NULL,
    technology TEXT NOT NULL,
    PRIMARY KEY (job_id, technology),
    FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE CASCADE
  );

  CREATE TABLE IF NOT EXISTS job_content (
    job_id TEXT PRIMARY KEY,
    description TEXT NOT NULL,
    responsibilities TEXT NOT NULL,
    requirements TEXT NOT NULL,
    about TEXT NOT NULL,
    FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE CASCADE
  );
`)

Relación entre tablas

jobs

Tabla principal. Almacena los datos básicos del empleo: título, empresa, ubicación, modalidad y nivel. Usa CHECK constraints para valores enumerados.

job_technologies

Relación N:M entre empleos y tecnologías. Clave primaria compuesta (job_id, technology). ON DELETE CASCADE elimina las tecnologías al borrar el empleo.

job_content

Contenido detallado del empleo (descripción larga, responsabilidades, requisitos, descripción de empresa). Relación 1:1 con jobs, también con ON DELETE CASCADE.

Seed de datos de ejemplo

const insertJob = db.prepare(`
  INSERT OR IGNORE INTO jobs (id, title, company, location, description, modality, level)
  VALUES (?, ?, ?, ?, ?, ?, ?)
`)

const insertTech = db.prepare(`
  INSERT OR IGNORE INTO job_technologies (job_id, technology) VALUES (?, ?)
`)

const seed = db.transaction(() => {
  insertJob.run('1', 'Senior Frontend Developer', 'Tech Corp', 'Madrid, Spain',
    'Looking for a senior frontend developer', 'hybrid', 'senior')
  insertTech.run('1', 'React')
  insertTech.run('1', 'TypeScript')
  insertTech.run('1', 'CSS')

  insertJob.run('2', 'Full Stack Developer', 'StartupX', 'Remote',
    'Join our team as a full stack developer', 'remote', 'mid')
  insertTech.run('2', 'Node.js')
  insertTech.run('2', 'React')
  insertTech.run('2', 'PostgreSQL')

  insertJob.run('3', 'Junior Backend Developer', 'FinTech Solutions', 'Barcelona, Spain',
    'Great opportunity for junior developers', 'onsite', 'junior')
  insertTech.run('3', 'Node.js')
  insertTech.run('3', 'TypeScript')
  insertTech.run('3', 'MongoDB')
})

seed()

db.transaction() envuelve múltiples inserts en una transacción atómica: si cualquier insert falla, todos se revierten. INSERT OR IGNORE evita duplicados al re-ejecutar el seed.

Modelo de Datos (TypeScript)

El archivo types.ts define las interfaces que representan las entidades y los DTOs (Data Transfer Objects):

// Entidad principal
export interface Job {
  id: string
  title: string
  company: string
  location: string
  description: string
  data: JobData
  content?: JobContent
}

// Sub-entidad: datos estructurados del puesto
export interface JobData {
  technology: string[]
  modality: "remote" | "onsite" | "hybrid"
  level: "junior" | "mid" | "senior"
}

// Sub-entidad: contenido detallado (opcional)
export interface JobContent {
  description: string
  responsibilities: string
  requirements: string
  about: string
}

// DTOs — derivados de la entidad con Utility Types
export type CreateJobDTO = Omit<Job, "id">       // Todo excepto el id
export type UpdateJobDTO = Partial<CreateJobDTO>   // Todo opcional para PATCH

// Filtros para las consultas
export interface JobFilters {
  tech?: string
  modality?: JobData["modality"]
  level?: JobData["level"]
}

Los tipos CreateJobDTO y UpdateJobDTO se construyen directamente desde Job usando Utility Types de TypeScript (Omit y Partial), manteniendo una única fuente de verdad para la estructura del empleo.

JobModel: Consultas SQL

El archivo models/job.ts implementa el acceso a datos con better-sqlite3. Las sentencias se preparan con db.prepare() para reutilización eficiente.

`getAll()` — JOIN y GROUP_CONCAT

static async getAll(filters?: JobFilters): Promise<Job[]> {
  let query = `
    SELECT j.*, GROUP_CONCAT(jt.technology) AS technologies
    FROM jobs j
    JOIN job_technologies jt ON j.id = jt.job_id
  `

  const conditions: string[] = []
  const params: unknown[] = []

  if (filters?.tech)     { conditions.push(``); params.push(filters.tech) }
  if (filters?.modality) { conditions.push(``); params.push(filters.modality) }
  if (filters?.level)    { conditions.push(``); params.push(filters.level) }

  if (conditions.length > 0) {
    query += 'WHERE ' + conditions.join(' AND ')
  }

  query += ' GROUP BY j.id'

  const rows = db.prepare(query).all(...params)

  return rows.map(row => ({
    id: row.id,
    title: row.title,
    company: row.company,
    location: row.location,
    description: row.description,
    data: {
      technology: row.technologies.split(','),
      modality: row.modality,
      level: row.level
    }
  }))
}

GROUP_CONCAT(jt.technology) agrega todas las tecnologías de un empleo en una sola cadena separada por comas (p. ej. "React,TypeScript,CSS"). Después se hace .split(',') para restaurar el array. El GROUP BY j.id es necesario para que la agregación funcione correctamente.

`create()`, `update()` y `delete()`

static async create(input: CreateJobDTO): Promise<Job> {
  const newJob: Job = {
    id: crypto.randomUUID(),
    ...input
  }
  jobs.push(newJob)
  return newJob
}

static async update(id: string, input: UpdateJobDTO): Promise<Job | null> {
  const index = jobs.findIndex(job => job.id === id)
  if (index === -1) return null

  jobs[index] = {
    ...jobs[index],
    ...input,
    // Merge profundo del objeto data
    data: input.data
      ? { ...jobs[index].data, ...input.data }
      : jobs[index].data
  }

  return jobs[index]
}

static async delete(id: string): Promise<boolean> {
  const index = jobs.findIndex(job => job.id === id)
  if (index === -1) return false

  jobs.splice(index, 1)
  return true
}

El método update hace un deep merge del objeto data para que un PATCH parcial (p. ej. solo cambiar level) no sobreescriba el resto de los campos del objeto anidado.

Validación con Zod

El archivo schemas/job.ts define los esquemas de validación y exporta los tipos TypeScript inferidos directamente de ellos, eliminando la necesidad de mantener tipos y validaciones en sincronía manualmente:

import { z } from 'zod'

export const jobDataSchema = z.object({
  technology: z.array(z.string()),
  modality: z.enum(["remote", "onsite", "hybrid"]),
  level: z.enum(["junior", "mid", "senior"])
})

export const jobContentSchema = z.object({
  description:      z.string(),
  responsibilities: z.string(),
  requirements:     z.string(),
  about:            z.string()
})

export const jobSchema = z.object({
  title: z.string({
    required_error: "Title is required",
    invalid_type_error: "Title must be a string"
  }).min(3, "Title must be at least 3 characters"),
  company:     z.string({ required_error: "Company is required" }),
  location:    z.string({ required_error: "Location is required" }),
  description: z.string({ required_error: "Description is required" }),
  data:        jobDataSchema,
  content:     jobContentSchema.optional()
})

// Tipos inferidos automáticamente — no duplicar la definición
export type JobInput        = z.infer<typeof jobSchema>
export type JobDataInput    = z.infer<typeof jobDataSchema>
export type JobContentInput = z.infer<typeof jobContentSchema>

// Funciones de validación
export function validateJob(input: unknown) {
  return jobSchema.safeParse(input)
}

export function validatePartialJob(input: unknown) {
  return jobSchema.partial().safeParse(input)
}

z.infer<typeof jobSchema> extrae el tipo TypeScript que equivale al esquema Zod. Esto garantiza que el tipo y la validación nunca se desincronicen.

Cómo Ejecutar

Entra al directorio e instala dependencias

cd 08-sql/backend
npm install

Arranca el servidor en modo desarrollo

npm run dev
# tsx watch src/app.ts

tsx transpila TypeScript on-the-fly y reinicia el servidor al detectar cambios.

Compila para producción

npm run build      # tsc → genera dist/
npm run start:prod # node dist/app.js

Prueba los endpoints

# Health check
curl http://localhost:3000/

# Listar empleos
curl "http://localhost:3000/jobs"

# Filtrar por tecnología y nivel
curl "http://localhost:3000/jobs?tech=React&level=senior"

# Crear un empleo
curl -X POST http://localhost:3000/jobs \
  -H "Content-Type: application/json" \
  -d '{
    "title": "Backend Developer",
    "company": "MiduCorp",
    "location": "Remote",
    "description": "Buscamos un backend developer",
    "data": {
      "technology": ["Node.js", "TypeScript"],
      "modality": "remote",
      "level": "mid"
    }
  }'

El archivo db/seed.ts debe ejecutarse una sola vez para crear las tablas e insertar los datos iniciales. Al usar INSERT OR IGNORE, re-ejecutarlo es seguro, pero si modificas el schema deberás eliminar el archivo .db manualmente para que los cambios surtan efecto.

Introducción

Módulos Web

Módulos Backend

Módulos Avanzados

SQL con SQLite y TypeScript: Base de Datos para DevJobs

Lo que aprenderás

Estructura del Proyecto

Esquema de la Base de Datos

Relación entre tablas

jobs

job_technologies

job_content

Seed de datos de ejemplo

Modelo de Datos (TypeScript)

JobModel: Consultas SQL

`getAll()` — JOIN y GROUP_CONCAT

`create()`, `update()` y `delete()`

Validación con Zod

Cómo Ejecutar

Build docs developers (and LLMs) love

Introducción

Módulos Web

Módulos Backend

Módulos Avanzados

Documentation Index

​Lo que aprenderás

​Estructura del Proyecto

​Esquema de la Base de Datos

​Relación entre tablas

jobs

job_technologies

job_content

​Seed de datos de ejemplo

​Modelo de Datos (TypeScript)

​JobModel: Consultas SQL

​getAll() — JOIN y GROUP_CONCAT

​create(), update() y delete()

​Validación con Zod

​Cómo Ejecutar

Build docs developers (and LLMs) love

Lo que aprenderás

Estructura del Proyecto

Esquema de la Base de Datos

Relación entre tablas

Seed de datos de ejemplo

Modelo de Datos (TypeScript)

JobModel: Consultas SQL

`getAll()` — JOIN y GROUP_CONCAT

`create()`, `update()` y `delete()`

Validación con Zod

Cómo Ejecutar