Drizzle PostgreSQL Query Builder Joins

import { pgTable, serial, text, integer, timestamp } from 'drizzle-orm/pg-core';
import { drizzle } from 'drizzle-orm/node-postgres';
import { eq, gte, desc, and, sql } from 'drizzle-orm';
import { Pool } from 'pg';

const customers = pgTable('customers', {
  id: serial('id').primaryKey(),
  email: text('email').notNull(),
});

const orders = pgTable('orders', {
  id: serial('id').primaryKey(),
  customerId: integer('customer_id').notNull(),
  total: integer('total').notNull(),
  createdAt: timestamp('created_at').defaultNow(),
});

const pool = new Pool({ connectionString: process.env.DATABASE_URL });
const db = drizzle(pool);

async function recentBigOrders(minTotal: number) {
  const rows = await db
    .select({
      orderId: orders.id,
      email: customers.email,
      total: orders.total,
    })
    .from(orders)
    .innerJoin(customers, eq(orders.customerId, customers.id))
    .where(and(gte(orders.total, minTotal)))
    .orderBy(desc(orders.createdAt))
    .limit(50);

  return rows;
}

const qualified = await recentBigOrders(1000);
console.log('rows typed:', qualified[0]?.email.length ?? 0);
await pool.end();