Databases for SaaS: Postgres + Drizzle in Beginner Terms

Databases for SaaS: Postgres + Drizzle in Beginner Terms

When you build a SaaS app, you need a place to store data: users, orders, posts, sessions, you name it. This guide explains databases with PostgreSQL (Postgres) as the default choice, and shows how Drizzle ORM lets you define and query your schema in TypeScript.

You will learn:

• What tables, rows, columns, and relationships really mean.
• Why Postgres is a great default for SaaS.
• How Drizzle defines schemas and runs type-safe queries.
• Why indexes and constraints keep data fast and clean.
• How local dev differs from production.
• What migrations are and why they matter.

Tables, Rows, and Relationships (No Jargon)

Think of a database table like a spreadsheet:

• Table: a collection of data (e.g., users).
• Row: one entry in the table (one user account).
• Column: a piece of data for each row (email, created_at, plan).
• Primary key: a unique ID for each row.
• Relationship: a link between tables, usually via an ID.

Example: a sessions table might include user_id, which points to a row in users. That link is a relationship. This is why Postgres is called a relational database.

In plain English: tables hold types of things, rows hold individual things, and relationships connect things.

Why PostgreSQL Is a Great Default Database

Postgres is often recommended as a default choice [1] because it is:

• Reliable: decades of production use and full ACID transactions.
• General-purpose: works for SaaS, finance, analytics, content, and more.
• Scalable: start small, grow to large workloads [1].
• Well-supported: lots of managed hosting options and tools [2].
• Open source: no vendor lock-in or license fees.
• Compatible: many systems speak “Postgres” or are built on it [2].

Unless you have a very special use case, Postgres is a safe, flexible foundation for SaaS data.

Introducing Drizzle ORM: Typed Schema in TypeScript

An ORM (Object‑Relational Mapper) lets you talk to a database using your programming language instead of raw SQL. Drizzle is a modern TypeScript ORM that keeps SQL close but adds type safety.

Why Drizzle is beginner-friendly:

• Single source of truth: define your schema in TypeScript once and reuse it for queries and migrations [3].
• Type safety: if a column does not exist, TypeScript warns you before runtime.
• No magic strings: you build queries with functions instead of raw SQL strings.

Here is a Drizzle schema example for a sessions table:

import { pgTable, varchar, text, timestamp, index, uniqueIndex } from "drizzle-orm/pg-core";

export const sessions = pgTable(
  "sessions",
  {
    id: varchar({ length: 255 }).primaryKey(),          // Primary key column, a string ID
    user_id: varchar({ length: 255 }).notNull(),        // ID of the user who owns this session (must have a value)
    token: varchar({ length: 512 }).notNull(),          // A unique session token (must have a value)
    expires_at: timestamp({ withTimezone: true }).notNull(), // When the session expires
    ip_address: varchar({ length: 255 }),               // (Optional) IP address of the session
    user_agent: text(),                                 // (Optional) User agent string of the browser/device
    created_at: timestamp({ withTimezone: true }).notNull().defaultNow(),
    updated_at: timestamp({ withTimezone: true }).notNull().defaultNow(),
  },
  (table) => [
    // Indexes and constraints:
    uniqueIndex("sessions_token_unique_idx").on(table.token),  // Ensure no two sessions have the same token
    index("sessions_user_id_idx").on(table.user_id),           // Index to quickly look up sessions by user_id
  ]
);

What this means in plain terms:

• sessions is the table name.
• id, user_id, token, expires_at are required fields.
• ip_address and user_agent are optional.
• created_at and updated_at auto-fill timestamps.
• uniqueIndex on token prevents duplicate sessions.
• index on user_id makes user lookups fast.

Because this is TypeScript, your editor knows the schema and can catch mistakes early. Drizzle uses the schema as the source of truth for migrations, too.

Querying Data with Drizzle (Plus Indexes & Data Safety)

You define tables, then query them. Drizzle provides a fluent API for select/insert/update/delete that compiles to SQL safely.

SQL example:

SELECT *
FROM credits
WHERE user_uuid = 'some-user-id'
ORDER BY created_at DESC;

Drizzle example:

import { db } from "@/db";                      // our Drizzle database instance
import { credits as creditsTable } from "@/db/schema";  // the credits table schema we defined

const userId = "some-user-uuid";
const userCredits = await db.select().from(creditsTable)
  .where(creditsTable.user_uuid.eq(userId))
  .orderBy(creditsTable.created_at.desc());

Why this is safer:

• Type safety: column names are checked at compile time.
• Parameterized queries: values are passed safely, reducing SQL injection risk.
• Clear intent: reads like the query you want.

Indexes, constraints, and safety basics:

• Indexes: speed up frequent lookups (e.g., user_id). Without indexes, large tables slow down.
• Unique constraints: enforce rules like “one email per user.”
• NOT NULL: prevents missing required data.
• Data types: Postgres enforces types; Drizzle mirrors them in TypeScript.
• Transactions: group multiple writes so they succeed or fail together.
• Backups: always enable backups in production.

Local Development vs Production Databases

Local development:

• Runs on your laptop or Docker.
• Safe to reset or wipe; good for experimentation.
• Usually uses a local DATABASE_URL in .env.
• Data is fake/test data.

Production:

• Stores real user data; do not experiment here.
• Usually hosted on managed services (Neon, Supabase, RDS, etc.).
• Requires secure credentials, backups, and monitoring.
• Migrations must be applied carefully during deploys [5].

Key takeaway: keep dev and prod separate. Test migrations locally before applying them in production.

Common Beginner Questions

Q: Do I need to learn SQL to use Postgres and Drizzle?

A: Not immediately, but it helps long‑term. ORMs let you build without writing raw SQL, but SQL knowledge is valuable for debugging and performance [4].

Good SQL basics to learn:

• Data modeling: tables and relationships.
• Basic queries: SELECT, INSERT, UPDATE, DELETE, WHERE.
• Joins: combining related data.
• Aggregations: counts, sums, averages.

Drizzle does not hide SQL — it helps you use it safely. Think of ORMs as training wheels: great for speed, but SQL knowledge makes you stronger.

Q: What happens if I change a column or table later? (Why migrations matter)

When you update your schema in code, your database does not change automatically. Migrations keep your database in sync with your code.

How Drizzle migrations work:

1. Update schema in TypeScript.
2. Run drizzle-kit generate to create SQL migration files [5].
3. Review the migration (Drizzle can ask about renames vs new columns).
4. Apply migrations with drizzle-kit migrate.
5. Commit migration files so every environment stays consistent.

Example: changing credits from INT to BIGINT requires a migration so the real DB updates too. In production, always back up first and run migrations during deploys.

Next Steps: Try It Out with Drizzle Migrations

Follow your project’s README to generate and apply migrations. For example:

npm run db:generate   # generates a migration based on schema differences
npm run db:migrate    # applies the migration to the database

Then try:

• Insert a test row (e.g., create a user).
• Query it back via Drizzle.
• Add a new column in the schema and run migrations again.

This will help you see how schema code becomes SQL and how migrations keep your database aligned.

References

• [1] [2] Hashboard - PostgreSQL
• [3] Drizzle ORM - Schema
• [4] Do I need to know SQL to use Prisma · Discussion
• [5] Drizzle ORM - generate