Guia practica de Drizzle ORM - Aprovechando un ORM type-safe similar a SQL

Que es Drizzle ORM

Drizzle ORM es un ORM ligero disenado TypeScript-first. Logra consultas type-safe con una sintaxis cercana a SQL, mientras busca cero overhead en tiempo de ejecucion.

flowchart TB
    subgraph App["TypeScript Application"]
        AppCode["Codigo de aplicacion"]
    end

    subgraph DrizzleORM["Drizzle ORM Layer"]
        Schema["Schema Definition"]
        QueryBuilder["Query Builder<br/>(SQL-like)"]
        Relations["Relations API"]
    end

    subgraph Databases["Databases"]
        PostgreSQL["PostgreSQL"]
        MySQL["MySQL"]
        SQLite["SQLite"]
    end

    App --> DrizzleORM
    Schema & QueryBuilder & Relations --> PostgreSQL & MySQL & SQLite

Configuracion

# Para PostgreSQL
npm install drizzle-orm postgres
npm install -D drizzle-kit

# Para MySQL
npm install drizzle-orm mysql2

# Para SQLite
npm install drizzle-orm better-sqlite3

Archivo de configuracion

// drizzle.config.ts
import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  schema: './src/db/schema.ts',
  out: './drizzle',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
  verbose: true,
  strict: true,
});

Definicion de schema

// src/db/schema.ts
import {
  pgTable,
  serial,
  varchar,
  text,
  timestamp,
  integer,
  boolean,
  pgEnum,
  uuid,
  jsonb,
} from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';

// Definicion de Enum
export const userRoleEnum = pgEnum('user_role', ['admin', 'user', 'guest']);
export const postStatusEnum = pgEnum('post_status', ['draft', 'published', 'archived']);

// Tabla de usuarios
export const users = pgTable('users', {
  id: uuid('id').primaryKey().defaultRandom(),
  email: varchar('email', { length: 255 }).notNull().unique(),
  name: varchar('name', { length: 100 }).notNull(),
  role: userRoleEnum('role').default('user').notNull(),
  profile: jsonb('profile').$type<{
    bio?: string;
    website?: string;
    social?: Record<string, string>;
  }>(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
});

// Tabla de publicaciones
export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: varchar('title', { length: 255 }).notNull(),
  content: text('content').notNull(),
  status: postStatusEnum('status').default('draft').notNull(),
  authorId: uuid('author_id')
    .notNull()
    .references(() => users.id, { onDelete: 'cascade' }),
  viewCount: integer('view_count').default(0).notNull(),
  publishedAt: timestamp('published_at'),
  createdAt: timestamp('created_at').defaultNow().notNull(),
});

// Tabla de etiquetas
export const tags = pgTable('tags', {
  id: serial('id').primaryKey(),
  name: varchar('name', { length: 50 }).notNull().unique(),
  slug: varchar('slug', { length: 50 }).notNull().unique(),
});

// Tabla intermedia publicaciones-etiquetas
export const postsToTags = pgTable('posts_to_tags', {
  postId: integer('post_id')
    .notNull()
    .references(() => posts.id, { onDelete: 'cascade' }),
  tagId: integer('tag_id')
    .notNull()
    .references(() => tags.id, { onDelete: 'cascade' }),
});

// Tabla de comentarios
export const comments = pgTable('comments', {
  id: serial('id').primaryKey(),
  content: text('content').notNull(),
  postId: integer('post_id')
    .notNull()
    .references(() => posts.id, { onDelete: 'cascade' }),
  authorId: uuid('author_id')
    .notNull()
    .references(() => users.id, { onDelete: 'cascade' }),
  parentId: integer('parent_id'), // Auto-referencia
  createdAt: timestamp('created_at').defaultNow().notNull(),
});

Definicion de relaciones

// src/db/relations.ts
import { relations } from 'drizzle-orm';
import { users, posts, comments, tags, postsToTags } from './schema';

export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
  comments: many(comments),
}));

export const postsRelations = relations(posts, ({ one, many }) => ({
  author: one(users, {
    fields: [posts.authorId],
    references: [users.id],
  }),
  comments: many(comments),
  postsToTags: many(postsToTags),
}));

export const tagsRelations = relations(tags, ({ many }) => ({
  postsToTags: many(postsToTags),
}));

export const postsToTagsRelations = relations(postsToTags, ({ one }) => ({
  post: one(posts, {
    fields: [postsToTags.postId],
    references: [posts.id],
  }),
  tag: one(tags, {
    fields: [postsToTags.tagId],
    references: [tags.id],
  }),
}));

export const commentsRelations = relations(comments, ({ one, many }) => ({
  post: one(posts, {
    fields: [comments.postId],
    references: [posts.id],
  }),
  author: one(users, {
    fields: [comments.authorId],
    references: [users.id],
  }),
  parent: one(comments, {
    fields: [comments.parentId],
    references: [comments.id],
    relationName: 'replies',
  }),
  replies: many(comments, { relationName: 'replies' }),
}));

Conexion a base de datos

// src/db/index.ts
import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';
import * as schema from './schema';
import * as relations from './relations';

const connectionString = process.env.DATABASE_URL!;

// Cliente para consultas
const queryClient = postgres(connectionString);

// Instancia de Drizzle
export const db = drizzle(queryClient, {
  schema: { ...schema, ...relations },
  logger: process.env.NODE_ENV === 'development',
});

export type Database = typeof db;

Operaciones CRUD basicas

Create (Insercion)

import { db } from './db';
import { users, posts } from './db/schema';

// Insercion de un solo registro
const newUser = await db
  .insert(users)
  .values({
    email: 'user@example.com',
    name: 'John Doe',
    role: 'user',
    profile: { bio: 'Developer', website: 'https://example.com' },
  })
  .returning();

// Insercion de multiples registros
const newPosts = await db
  .insert(posts)
  .values([
    { title: 'First Post', content: 'Hello World', authorId: newUser[0].id },
    { title: 'Second Post', content: 'Drizzle is awesome', authorId: newUser[0].id },
  ])
  .returning();

// ON CONFLICT (Upsert)
const upsertedUser = await db
  .insert(users)
  .values({
    email: 'user@example.com',
    name: 'Updated Name',
  })
  .onConflictDoUpdate({
    target: users.email,
    set: { name: 'Updated Name', updatedAt: new Date() },
  })
  .returning();

Read (Consulta)

import { eq, and, or, gt, like, inArray, desc, asc, sql } from 'drizzle-orm';

// Obtener todos
const allUsers = await db.select().from(users);

// Con condicion
const activeUsers = await db
  .select()
  .from(users)
  .where(eq(users.role, 'user'));

// Condiciones compuestas
const filteredPosts = await db
  .select()
  .from(posts)
  .where(
    and(
      eq(posts.status, 'published'),
      gt(posts.viewCount, 100),
      or(
        like(posts.title, '%TypeScript%'),
        like(posts.title, '%Drizzle%')
      )
    )
  );

// Ordenamiento y paginacion
const paginatedPosts = await db
  .select()
  .from(posts)
  .orderBy(desc(posts.createdAt))
  .limit(10)
  .offset(20);

// Obtener solo columnas especificas
const userEmails = await db
  .select({
    id: users.id,
    email: users.email,
  })
  .from(users);

// Funciones de agregacion
const stats = await db
  .select({
    totalPosts: sql<number>`count(*)`,
    totalViews: sql<number>`sum(${posts.viewCount})`,
    avgViews: sql<number>`avg(${posts.viewCount})`,
  })
  .from(posts)
  .where(eq(posts.status, 'published'));

Consultas JOIN

// INNER JOIN
const postsWithAuthors = await db
  .select({
    post: posts,
    author: users,
  })
  .from(posts)
  .innerJoin(users, eq(posts.authorId, users.id));

// LEFT JOIN
const usersWithPosts = await db
  .select({
    user: users,
    postCount: sql<number>`count(${posts.id})`,
  })
  .from(users)
  .leftJoin(posts, eq(users.id, posts.authorId))
  .groupBy(users.id);

// JOIN de multiples tablas
const fullPostData = await db
  .select({
    postId: posts.id,
    postTitle: posts.title,
    authorName: users.name,
    commentCount: sql<number>`count(distinct ${comments.id})`,
  })
  .from(posts)
  .innerJoin(users, eq(posts.authorId, users.id))
  .leftJoin(comments, eq(posts.id, comments.postId))
  .groupBy(posts.id, users.name);

Consultas con relaciones (Query API)

// Obtener datos anidados
const postsWithRelations = await db.query.posts.findMany({
  with: {
    author: true,
    comments: {
      with: {
        author: true,
      },
      limit: 5,
      orderBy: (comments, { desc }) => [desc(comments.createdAt)],
    },
    postsToTags: {
      with: {
        tag: true,
      },
    },
  },
  where: eq(posts.status, 'published'),
  orderBy: (posts, { desc }) => [desc(posts.publishedAt)],
  limit: 10,
});

// Obtener un solo registro
const post = await db.query.posts.findFirst({
  where: eq(posts.id, 1),
  with: {
    author: {
      columns: {
        id: true,
        name: true,
        profile: true,
      },
    },
  },
});

Update (Actualizacion)

// Actualizar con condicion
const updatedPosts = await db
  .update(posts)
  .set({
    status: 'published',
    publishedAt: new Date(),
  })
  .where(eq(posts.id, 1))
  .returning();

// Actualizar con multiples condiciones
await db
  .update(posts)
  .set({ viewCount: sql`${posts.viewCount} + 1` })
  .where(
    and(
      eq(posts.status, 'published'),
      gt(posts.publishedAt, new Date('2024-01-01'))
    )
  );

Delete (Eliminacion)

// Eliminar con condicion
const deletedPosts = await db
  .delete(posts)
  .where(eq(posts.status, 'archived'))
  .returning();

// Eliminar con multiples condiciones
await db
  .delete(comments)
  .where(
    and(
      eq(comments.authorId, userId),
      inArray(comments.postId, [1, 2, 3])
    )
  );

Transacciones

// Transaccion basica
const result = await db.transaction(async (tx) => {
  const [newPost] = await tx
    .insert(posts)
    .values({
      title: 'New Post',
      content: 'Content',
      authorId: userId,
    })
    .returning();

  await tx.insert(postsToTags).values([
    { postId: newPost.id, tagId: 1 },
    { postId: newPost.id, tagId: 2 },
  ]);

  return newPost;
});

// Transacciones anidadas (savepoints)
await db.transaction(async (tx) => {
  await tx.insert(users).values({ email: 'a@example.com', name: 'A' });

  await tx.transaction(async (nested) => {
    await nested.insert(users).values({ email: 'b@example.com', name: 'B' });
    // En caso de error, rollback hasta este savepoint
  });
});

// Rollback explicito
await db.transaction(async (tx) => {
  const [user] = await tx.insert(users).values({ ... }).returning();

  if (someCondition) {
    tx.rollback(); // Rollback de toda la transaccion
  }
});

Migraciones

# Generar archivo de migracion
npx drizzle-kit generate

# Ejecutar migracion
npx drizzle-kit migrate

# Push directo del schema a DB (para desarrollo)
npx drizzle-kit push

# Verificar diferencias de schema
npx drizzle-kit check

# Ver datos con Drizzle Studio
npx drizzle-kit studio

flowchart TB
    subgraph DevFlow["Flujo de desarrollo"]
        direction LR
        Schema1["Cambio de Schema"] --> Push["push<br/>(directo)"] --> DB1["Actualizacion de Database"]
    end

    subgraph ProdFlow["Flujo de produccion"]
        direction TB
        Schema2["Cambio de Schema"] --> Generate["generate<br/>(genera SQL)"] --> Migrate["migrate<br/>(aplicar)"]
        Generate --> SQLFile["drizzle/0001_xxx.sql<br/>(SQL revisable)"]
    end

Funciones helper type-safe

// src/db/helpers.ts
import { db } from './index';
import { posts, users } from './schema';
import { eq, and, desc, sql, type SQL } from 'drizzle-orm';

// Paginacion type-safe
export async function getPaginatedPosts(options: {
  page: number;
  limit: number;
  status?: 'draft' | 'published' | 'archived';
}) {
  const { page, limit, status } = options;
  const offset = (page - 1) * limit;

  const conditions: SQL[] = [];
  if (status) {
    conditions.push(eq(posts.status, status));
  }

  const [data, countResult] = await Promise.all([
    db
      .select()
      .from(posts)
      .where(conditions.length > 0 ? and(...conditions) : undefined)
      .orderBy(desc(posts.createdAt))
      .limit(limit)
      .offset(offset),
    db
      .select({ count: sql<number>`count(*)` })
      .from(posts)
      .where(conditions.length > 0 ? and(...conditions) : undefined),
  ]);

  return {
    data,
    pagination: {
      page,
      limit,
      total: countResult[0].count,
      totalPages: Math.ceil(countResult[0].count / limit),
    },
  };
}

// Funciones genericas aprovechando inferencia de tipos
export type User = typeof users.$inferSelect;
export type NewUser = typeof users.$inferInsert;
export type Post = typeof posts.$inferSelect;
export type NewPost = typeof posts.$inferInsert;

Comparacion con Prisma

Enlaces de referencia

• Documentacion oficial de Drizzle ORM
• Drizzle Kit
• GitHub: drizzle-team/drizzle-orm