Database

Comprehensive guide to database management in ShipKit

Database Management

This document provides a comprehensive guide to database management in ShipKit, including schema design, queries, migrations, and best practices.

Schema Design

Core Models

// src/server/db/schema.ts
import { relations, sql } from "drizzle-orm";
import {
  boolean,
  index,
  integer,
  pgEnum,
  pgTableCreator,
  primaryKey,
  serial,
  text,
  timestamp,
  varchar,
} from "drizzle-orm/pg-core";

const createTable = pgTableCreator((name) => `${env.DB_PREFIX}_${name}`);

export const users = createTable("user", {
  id: varchar("id", { length: 255 })
    .notNull()
    .primaryKey()
    .$defaultFn(() => crypto.randomUUID()),
  name: varchar("name", { length: 255 }),
  email: varchar("email", { length: 255 }).notNull(),
  emailVerified: timestamp("email_verified", {
    mode: "date",
    withTimezone: true,
  }).default(sql`CURRENT_TIMESTAMP`),
  image: varchar("image", { length: 255 }),
  password: varchar("password", { length: 255 }),
  role: varchar("role", { length: 50 }).default("user").notNull(),
  createdAt: timestamp("created_at", { withTimezone: true })
    .default(sql`CURRENT_TIMESTAMP`)
    .notNull(),
  updatedAt: timestamp("updated_at", { withTimezone: true }).$onUpdate(
    () => new Date(),
  ),
});

export const accounts = createTable(
  "account",
  {
    userId: text("userId")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    type: text("type").notNull(),
    provider: text("provider").notNull(),
    providerAccountId: text("providerAccountId").notNull(),
    refresh_token: text("refresh_token"),
    access_token: text("access_token"),
    expires_at: integer("expires_at"),
    token_type: text("token_type"),
    scope: text("scope"),
    id_token: text("id_token"),
    session_state: text("session_state"),
  },
  (account) => ({
    compoundKey: primaryKey({
      columns: [account.provider, account.providerAccountId],
    }),
    userIdIdx: index("account_user_id_idx").on(account.userId),
  }),
);

export const sessions = createTable("session", {
  sessionToken: text("sessionToken").primaryKey(),
  userId: text("userId")
    .notNull()
    .references(() => users.id, { onDelete: "cascade" }),
  expires: timestamp("expires", { mode: "date" }).notNull(),
});

export const posts = createTable(
  "post",
  {
    id: serial("id").primaryKey(),
    name: varchar("name", { length: 256 }),
    createdById: varchar("created_by", { length: 255 })
      .notNull()
      .references(() => users.id),
    createdAt: timestamp("created_at", { withTimezone: true })
      .default(sql`CURRENT_TIMESTAMP`)
      .notNull(),
    updatedAt: timestamp("updated_at", { withTimezone: true }).$onUpdate(
      () => new Date(),
    ),
  },
  (example) => ({
    createdByIdIdx: index("created_by_idx").on(example.createdById),
    nameIndex: index("name_idx").on(example.name),
  }),
);

Database Client

Configuration

// src/server/db/index.ts
import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";
import { env } from "@/env";
import * as schema from "./schema";

const globalForDb = globalThis as unknown as {
  conn: postgres.Sql | undefined;
};

const conn = globalForDb.conn ?? postgres(env.DATABASE_URL);
if (env.NODE_ENV !== "production") globalForDb.conn = conn;

export const db = drizzle(conn, { schema });

Query Patterns

// src/server/db/queries.ts
import { eq } from "drizzle-orm";
import { db } from "./index";
import { users, posts } from "./schema";

// Create
export async function createUser(data: {
  email: string;
  name?: string;
}) {
  return db.insert(users).values(data);
}

// Read
export async function getUserById(id: string) {
  return db.query.users.findFirst({
    where: eq(users.id, id),
    with: {
      posts: true,
    },
  });
}

// Update
export async function updateUser(id: string, data: {
  name?: string;
  email?: string;
}) {
  return db.update(users)
    .set(data)
    .where(eq(users.id, id));
}

// Delete
export async function deleteUser(id: string) {
  return db.delete(users)
    .where(eq(users.id, id));
}

Advanced Queries

// src/server/db/advanced-queries.ts
import { and, eq, desc, sql } from "drizzle-orm";
import { db } from "./index";
import { users, posts } from "./schema";

// Transaction
export async function createPostWithTags(data: {
  title: string;
  content: string;
  authorId: string;
  tags: string[];
}) {
  return db.transaction(async (tx) => {
    const post = await tx.insert(posts).values({
      title: data.title,
      content: data.content,
      authorId: data.authorId,
    }).returning();

    // Handle tags...
    return post;
  });
}

// Complex Filtering
export async function searchPosts(query: string) {
  return db.query.posts.findMany({
    where: sql`${posts.title} ILIKE ${`%${query}%`} OR ${posts.content} ILIKE ${`%${query}%`}`,
    with: {
      author: {
        columns: {
          name: true,
          image: true,
        },
      },
    },
    orderBy: desc(posts.createdAt),
  });
}

Best Practices

Query Optimization

  1. Use Indexes

    // Define indexes in schema
export const posts = createTable(
  "post",
  {
    // ... fields
  },
  (table) => ({
    titleIdx: index("title_idx").on(table.title),
    authorContentIdx: index("author_content_idx").on(
      table.authorId,
      table.content,
    ),
  }),
);

  2. Batch Operations

    // Use insert many
await db.insert(posts).values(postsData);

  3. Use Transactions

    await db.transaction(async (tx) => {
  await tx.insert(users).values(userData);
  await tx.insert(posts).values(postData);
});

Performance Tips

  1. Selective Columns

    const users = await db.select({
  id: users.id,
  name: users.name,
}).from(users);

  2. Pagination

    const posts = await db.query.posts.findMany({
  limit: 10,
  offset: page * 10,
  orderBy: desc(posts.createdAt),
});

  3. Efficient Joins

    const userPosts = await db.query.users.findMany({
  with: {
    posts: {
      limit: 5,
      orderBy: desc(posts.createdAt),
    },
  },
});

Error Handling

// src/server/db/error.ts
import { PostgresError } from "postgres";

export function handleDatabaseError(error: unknown) {
  if (error instanceof PostgresError) {
    switch (error.code) {
      case "23505": // unique_violation
        return new Error("Duplicate entry");
      case "23503": // foreign_key_violation
        return new Error("Referenced record not found");
      default:
        return new Error("Database error");
    }
  }

  return new Error("Unknown error occurred");
}

Testing

Unit Tests

// src/server/db/__tests__/user.test.ts
import { describe, expect, it, beforeEach } from "vitest";
import { db } from "../index";
import { createUser, getUserById } from "../queries";

describe("User queries", () => {
  beforeEach(async () => {
    await db.delete(users);
  });

  it("should create a user", async () => {
    const user = await createUser({
      email: "test@example.com",
      name: "Test User",
    });

    expect(user).toHaveProperty("id");
    expect(user.email).toBe("test@example.com");
  });

  it("should get user by id", async () => {
    const created = await createUser({
      email: "test@example.com",
      name: "Test User",
    });

    const user = await getUserById(created.id);
    expect(user).toMatchObject(created);
  });
});

Integration Tests

// src/server/db/__tests__/integration.test.ts
import { describe, expect, it, beforeAll, afterAll } from "vitest";
import { db } from "../index";
import { createPostWithTags } from "../advanced-queries";

describe("Database integration", () => {
  beforeAll(async () => {
    // Setup test database
  });

  afterAll(async () => {
    // Cleanup
  });

  it("should create post with tags", async () => {
    const user = await db.insert(users).values({
      email: "test@example.com",
      name: "Test User",
    }).returning();

    const post = await createPostWithTags({
      title: "Test Post",
      content: "Test Content",
      authorId: user[0].id,
      tags: ["test", "example"],
    });

    expect(post).toBeDefined();
    // Add more assertions
  });
});