ORM

ORM (Object-Relational Mapping) is a programming technique that bridges the gap between object-oriented programming languages and relational databases like PostgreSQL. It provides an abstraction layer, enabling developers to interact with database tables and records using objects and methods rather than raw SQL queries.

Popular ORM options include Prisma and Drizzle. Other notable libraries include Hibernate for Java, SQLAlchemy for Python, and ActiveRecord for Ruby.

Using Drizzle

First, install the necessary packages:

bun add drizzle-orm pg
npm add --dev drizzle-kit @types/pg

Create Table

Create a schema.ts file:

import { integer, pgTable, varchar } from "drizzle-orm/pg-core"

export const usersTable = pgTable("users", {
  id: integer().primaryKey().generatedAlwaysAsIdentity(),
  name: varchar({ length: 255 }).notNull(),
  age: integer().notNull(),
  email: varchar({ length: 255 }).notNull().unique(),
})

Create a drizzle.config.ts file:

import { defineConfig } from 'drizzle-kit'

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

Add a .env file:

DATABASE_URL=postgres://postgres:my_password@localhost:5432/postgres

Apply the schema:

$ bunx drizzle-kit push
[✓] Pulling schema from database...
[✓] Changes applied

Query

Create an index.ts file:

import { drizzle } from 'drizzle-orm/connect'
import { eq } from 'drizzle-orm'
import { usersTable } from './schema'

const db = await drizzle("node-postgres", process.env.DATABASE_URL!)

await db.insert(usersTable).values({
  name: 'Alice',
  age: 7,
  email: 'alice@example.com',
})

await db
  .update(usersTable)
  .set({ age: 8 })
  .where(eq(usersTable.email, 'alice@example.com'))

const users = await db.select().from(usersTable)
console.log(users)

The syntax is actualy more like a SQL builder than traditional ORM.

Run the query:

$ bun index.ts
[
  {
    id: 1,
    name: "Alice",
    age: 8,
    email: "alice@example.com",
  }
]

Bonus

Drizzle has an admin UI baked in:

bunx drizzle-kit studio