Database

Platform~10 min

Provider-agnostic database access with Supabase, Prisma, and memory adapters.

Read this when you need consistent data access or want to switch providers.
Useful for developers building features that read or write to the database.

Overview

The database layer provides a provider-agnostic way to access data from anywhere in your app. It handles adapter selection (Supabase, Prisma, or memory), provides lightweight table helpers, and keeps data access consistent across modules.

All database code lives in lib/database/ with detailed documentation in lib/database/LIB-DATABASE.md.

Key Concepts

The database layer has three main parts:

Providers

Supabase, Prisma, or memory via a single config switch. Memory is the default when auth is not configured.

Table Helpers

Simple CRUD helpers via db.table()for list, create, update, and delete.

Supabase Utilities

Repository pattern and CRUD utils remain available in lib/supabase/ when using Supabase directly.

Provider Pattern

Supabase is the recommended default, with Prisma and memory as alternatives.

Catalyst uses a provider switch to keep your data layer consistent. You set DATABASE_PROVIDER once and the app chooses the right adapter for each request.

Supabase is the recommended default. Memory is used for unauthenticated usage or local demos. Prisma is available for teams that need custom database infrastructure.

Supabase (Recommended)

Managed Postgres. Auth, storage, and realtime with the best default experience in Catalyst.

Prisma (Server-only)

Bring your own database. Works with Postgres, MySQL, SQLite, and SQL Server. Use on the server only.

Memory

Fast demo mode. In-memory tables that reset on refresh. Great for unauthenticated previews.

Provider selection is automatic when DATABASE_PROVIDER is set to auto. Authenticated requests choose Supabase when configured; unauthenticated requests fall back to memory.

1) If unauthenticated, always use memory.

2) If authenticated and DATABASE_PROVIDER is prisma, use prisma.

3) If authenticated and Supabase is configured, use supabase.

4) If DATABASE_PROVIDER is explicit (supabase/prisma/memory), use it.

5) If DATABASE_PROVIDER is auto, fall back to memory.

Config snippet

DATABASE_PROVIDER=auto
NEXT_PUBLIC_SUPABASE_URL=https://YOUR_PROJECT.supabase.co
NEXT_PUBLIC_SUPABASE_PUBLISHABLE_KEY=your-publishable-key
# DATABASE_URL=postgresql://user:pass@host:5432/db

Supabase (Recommended)

Setup steps and why Supabase is the default.

Quick Start

The simplest way to access data:

// In a server component or server action
import { createDatabaseFromServer } from "@/lib/database/server"

export default async function Page() {
  const db = await createDatabaseFromServer()

  const posts = await db.table("posts").list({
    orderBy: { column: "created_at", order: "desc" },
  })

  return <PostList posts={posts} />
}

Factory Helpers

Use the right helper for your context:

Server Helper

Server components, server actions, API routes

import { createDatabaseFromServer } from "@/lib/database/server"

const db = await createDatabaseFromServer()

Client Helper

Client components (shared in-memory fallback)

import { createDatabaseFromClient } from "@/lib/database"

const db = createDatabaseFromClient()

Custom Client

Use an existing provider client instance

import { createDatabase } from "@/lib/database"

const db = createDatabase({ provider: "memory" })

Prefer server-side data access. Server components can fetch data directly without exposing queries to the browser. Use custom clients for Prisma or when you already have a Supabase client.

Common Operations

Fetch a list

const posts = await db.table("posts").list({
  orderBy: { column: "created_at", order: "desc" },
})

Fetch a single item

const post = await db.table("posts").getById(postId)

Create a record

const post = await db.table("posts").create({
  title: "New Post",
  status: "draft",
})

Update a record

const post = await db.table("posts").update(postId, {
  status: "published",
})

Delete a record

await db.table("posts").delete(postId)

Repository Pattern

For larger apps using Supabase directly, use repositories to encapsulate data access. This keeps your components clean and makes queries easier to test and maintain.

Example: Post Repository

// lib/data/posts/post-repository.ts
import { createCrudRepository } from "@/lib/supabase"
import { getServerPostCrudClient } from "./post-crud"

export const postRepository = createCrudRepository(
  getServerPostCrudClient,
  {
    listSelect: "id, title, status, created_at",
    listQuery: (query) => query.order("created_at", { ascending: false }),
  }
)

// Usage in a page
import { postRepository } from "@/lib/data/posts/post-repository"

const posts = await postRepository.list()
const post = await postRepository.getById(id)
await postRepository.create({ title: "New Post" })
await postRepository.update(id, { status: "published" })
await postRepository.delete(id)

See lib/supabase/LIB-SUPABASE.md for complete repository setup with models, DTOs, and CRUD clients. The provider-agnostic helpers live in lib/database/LIB-DATABASE.md.

Row Level Security (RLS)

RLS is enabled by default in Supabase. Your database tables need policies that define who can access what data. Without policies, queries will return empty results. This applies only when your provider is Supabase.

Common RLS Pattern

-- Users can only see their own records
CREATE POLICY "Users can view own records"
  ON posts FOR SELECT
  TO authenticated
  USING (user_id = auth.uid());

-- Users can only insert their own records
CREATE POLICY "Users can insert own records"
  ON posts FOR INSERT
  TO authenticated
  WITH CHECK (user_id = auth.uid());

Catalyst includes a global RBAC migration in supabase/migrations/ that sets up user roles and basic policies.

Setup

To use the database layer, choose a provider and add the right env vars:

# .env.local
DATABASE_PROVIDER=auto

# Supabase provider
NEXT_PUBLIC_SUPABASE_URL=https://YOUR_PROJECT.supabase.co
NEXT_PUBLIC_SUPABASE_PUBLISHABLE_KEY=your-publishable-key

# Prisma provider
# DATABASE_URL=postgresql://user:pass@host:5432/db

Pick a provider

Use auto to default to memory for unauthenticated requests and Supabase for authenticated users (when configured), or set supabase /prisma explicitly.

Configure your provider

Supabase needs URL + publishable key. Prisma needs DATABASE_URLand a generated Prisma client.

Run migrations (Supabase)

Run supabase db push to apply included migrations (RBAC, storage buckets).

For AI Agents

Key rules:

Use createDatabaseFromServer() for server-side usage
Memory provider is default when auth is not configured
Use db.table() helpers for list/create/update/delete
Always check for errors after Supabase operations when using the raw client
Remember RLS in Supabase — add policies for new tables
For complex apps, use the repository pattern
Read lib/database/LIB-DATABASE.md for full patterns