Skip to content
Manacore Docs

Database Patterns

Database Patterns

Manacore uses Drizzle ORM with PostgreSQL for type-safe database access.

Schema Design

Basic Table

import {
  pgTable,
  uuid,
  varchar,
  text,
  timestamp,
  boolean,
  integer,
} from 'drizzle-orm/pg-core';


export const users = pgTable('users', {
  // Primary key - always UUID
  id: uuid('id').primaryKey().defaultRandom(),


  // Required fields
  email: varchar('email', { length: 255 }).notNull().unique(),
  name: varchar('name', { length: 100 }).notNull(),


  // Optional fields
  bio: text('bio'),
  avatarUrl: varchar('avatar_url', { length: 500 }),


  // Soft delete pattern
  deletedAt: timestamp('deleted_at'),


  // Audit fields - always include
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
});

Foreign Keys

export const posts = pgTable('posts', {
  id: uuid('id').primaryKey().defaultRandom(),
  title: varchar('title', { length: 255 }).notNull(),
  content: text('content'),


  // Foreign key with cascade delete
  authorId: uuid('author_id')
    .notNull()
    .references(() => users.id, { onDelete: 'cascade' }),


  // Foreign key with set null
  categoryId: uuid('category_id')
    .references(() => categories.id, { onDelete: 'set null' }),


  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
});

Indexes

import { pgTable, uuid, varchar, index, uniqueIndex } from 'drizzle-orm/pg-core';


export const users = pgTable('users', {
  id: uuid('id').primaryKey().defaultRandom(),
  email: varchar('email', { length: 255 }).notNull(),
  organizationId: uuid('organization_id'),
  status: varchar('status', { length: 20 }),
}, (table) => ({
  // Single column index
  emailIdx: uniqueIndex('users_email_idx').on(table.email),


  // Composite index
  orgStatusIdx: index('users_org_status_idx').on(table.organizationId, table.status),
}));

Enums

import { pgEnum, pgTable, uuid } from 'drizzle-orm/pg-core';


// Define enum
export const userRoleEnum = pgEnum('user_role', ['admin', 'user', 'guest']);
export const statusEnum = pgEnum('status', ['active', 'inactive', 'pending']);


// Use in table
export const users = pgTable('users', {
  id: uuid('id').primaryKey().defaultRandom(),
  role: userRoleEnum('role').default('user').notNull(),
  status: statusEnum('status').default('pending').notNull(),
});

JSON Columns

import { pgTable, uuid, jsonb } from 'drizzle-orm/pg-core';


// Type for JSON structure
interface UserSettings {
  theme: 'light' | 'dark';
  notifications: boolean;
  language: string;
}


export const users = pgTable('users', {
  id: uuid('id').primaryKey().defaultRandom(),
  settings: jsonb('settings').$type<UserSettings>().default({
    theme: 'light',
    notifications: true,
    language: 'en',
  }),
});

Query Patterns

Basic Queries

import { eq, and, or, like, ilike, isNull, isNotNull } from 'drizzle-orm';


// Select all
const allUsers = await db.select().from(users);


// Select with condition
const activeUsers = await db
  .select()
  .from(users)
  .where(eq(users.status, 'active'));


// Select specific columns
const emails = await db
  .select({ email: users.email, name: users.name })
  .from(users);


// Multiple conditions
const result = await db
  .select()
  .from(users)
  .where(
    and(
      eq(users.status, 'active'),
      isNull(users.deletedAt),
      or(
        eq(users.role, 'admin'),
        eq(users.role, 'moderator')
      )
    )
  );


// Pattern matching
const matched = await db
  .select()
  .from(users)
  .where(ilike(users.name, '%john%'));

Joins

import { eq } from 'drizzle-orm';


// 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()
  .from(users)
  .leftJoin(posts, eq(users.id, posts.authorId));

Ordering and Pagination

import { desc, asc } from 'drizzle-orm';


// Order by
const sorted = await db
  .select()
  .from(users)
  .orderBy(desc(users.createdAt));


// Pagination
const page = 1;
const pageSize = 20;


const paginated = await db
  .select()
  .from(users)
  .orderBy(desc(users.createdAt))
  .limit(pageSize)
  .offset((page - 1) * pageSize);

Aggregations

import { count, sum, avg, min, max } from 'drizzle-orm';


// Count
const [{ total }] = await db
  .select({ total: count() })
  .from(users)
  .where(eq(users.status, 'active'));


// Group by
const countByRole = await db
  .select({
    role: users.role,
    count: count(),
  })
  .from(users)
  .groupBy(users.role);

Insert

// Single insert
const [newUser] = await db
  .insert(users)
  .values({
    email: 'user@example.com',
    name: 'John Doe',
  })
  .returning();


// Bulk insert
const newUsers = await db
  .insert(users)
  .values([
    { email: 'user1@example.com', name: 'User 1' },
    { email: 'user2@example.com', name: 'User 2' },
  ])
  .returning();


// Upsert (insert or update)
await db
  .insert(users)
  .values({ email: 'user@example.com', name: 'John' })
  .onConflictDoUpdate({
    target: users.email,
    set: { name: 'John Updated' },
  });

Update

// Update with returning
const [updated] = await db
  .update(users)
  .set({
    name: 'New Name',
    updatedAt: new Date(),
  })
  .where(eq(users.id, userId))
  .returning();


// Conditional update
await db
  .update(users)
  .set({ status: 'inactive' })
  .where(
    and(
      eq(users.status, 'active'),
      lt(users.lastLoginAt, thirtyDaysAgo)
    )
  );

Delete

// Hard delete
await db.delete(users).where(eq(users.id, userId));


// Soft delete (preferred)
await db
  .update(users)
  .set({ deletedAt: new Date() })
  .where(eq(users.id, userId));

Transactions

import { db } from './drizzle';


// Basic transaction
await db.transaction(async (tx) => {
  const [user] = await tx
    .insert(users)
    .values({ email: 'user@example.com', name: 'John' })
    .returning();


  await tx
    .insert(profiles)
    .values({ userId: user.id, bio: 'Hello!' });


  // If anything throws, entire transaction rolls back
});


// With savepoints
await db.transaction(async (tx) => {
  await tx.insert(users).values({ /* ... */ });


  try {
    await tx.insert(optionalData).values({ /* ... */ });
  } catch (e) {
    // This specific insert failed, but transaction continues
    console.log('Optional insert failed, continuing...');
  }


  await tx.insert(requiredData).values({ /* ... */ });
});

Service Pattern

@Injectable()
export class UsersService {
  constructor(@Inject(DRIZZLE) private db: DrizzleDB) {}


  async findAll(options?: { status?: string; page?: number; limit?: number }) {
    const { status, page = 1, limit = 20 } = options || {};


    const query = this.db
      .select()
      .from(users)
      .where(
        and(
          isNull(users.deletedAt),
          status ? eq(users.status, status) : undefined
        )
      )
      .orderBy(desc(users.createdAt))
      .limit(limit)
      .offset((page - 1) * limit);


    return query;
  }


  async findById(id: string) {
    const [user] = await this.db
      .select()
      .from(users)
      .where(and(eq(users.id, id), isNull(users.deletedAt)));


    return user || null;
  }


  async create(data: CreateUserDto) {
    const [user] = await this.db
      .insert(users)
      .values(data)
      .returning();


    return user;
  }


  async update(id: string, data: UpdateUserDto) {
    const [user] = await this.db
      .update(users)
      .set({ ...data, updatedAt: new Date() })
      .where(eq(users.id, id))
      .returning();


    return user;
  }


  async softDelete(id: string) {
    await this.db
      .update(users)
      .set({ deletedAt: new Date() })
      .where(eq(users.id, id));
  }
}

Best Practices