Drizzle ORM

export async function findActiveUsers(orgId: string) {
  return db
    .select()
    .from(users)
    .where(and(eq(users.orgId, orgId), isNull(users.deletedAt)))
    .orderBy(desc(users.createdAt))
    .limit(50);
}

To find active users (async), given org ID:
  Give back the rows of users:
    • where all of these are true:
      • users' org ID is org ID
      • users' deleted at is empty
    • sorted by users' created at (newest first)
    • at most 50 rows

const matches = await db
  .select()
  .from(posts)
  .where(or(ilike(posts.title, `%${term}%`), between(posts.views, 100, 1000)));

Let matches be the rows of posts:
  • where any of these is true:
    • posts' title contains term (ignoring case)
    • posts' views is between 100 and 1000

await db
  .insert(subscriptions)
  .values({ userId, plan: "pro" })
  .onConflictDoUpdate({ target: subscriptions.userId, set: { plan: "pro" } });

Insert into subscriptions:
  • the values: an object with user ID and plan: "pro"
  • updating rows that already exist (matched on subscriptions' user ID):
    • setting an object with plan: "pro"

const recent = await db.query.posts.findMany({
  where: eq(posts.published, true),
  with: { author: true, comments: { limit: 3 } },
  orderBy: [desc(posts.createdAt)],
  limit: 10,
});

Let recent be the rows of posts:
  • where posts' published is true
  • including author
  • including comments:
    • at most 3 rows
  • sorted by posts' created at (newest first)
  • at most 10 rows

await db.transaction(async (tx) => {
  const [account] = await tx.select().from(accounts).where(eq(accounts.id, fromId));
  if (account.balance < amount) tx.rollback();
  await tx.update(accounts).set({ balance: account.balance - amount }).where(eq(accounts.id, fromId));
});

Do this all-or-nothing, in one transaction:
  Look up rows from accounts, and take the first result as account:
    • where accounts' ID is from ID
  If account's balance is less than amount, cancel the transaction, undoing everything it changed.
  Update accounts:
    • setting an object with balance: account's balance minus amount
    • where accounts' ID is from ID

export const users = pgTable("users", {
  id: uuid("id").primaryKey().defaultRandom(),
  email: text("email").notNull().unique(),
  orgId: uuid("org_id").references(() => orgs.id, { onDelete: "cascade" }),
  createdAt: timestamp("created_at").defaultNow().notNull(),
});

Let users be a Postgres table "users":
  • ID: UUID, primary key, defaults to a random UUID
  • email: text, required, unique
  • org ID: UUID, points to orgs' ID, deleted along with it
  • created at: timestamp, defaults to the current time, required