Logo
RavenSaaS Docs

Database

Database Overview

RavenSaaS uses drizzle-orm to support multiple database types.

Configure Database

Using Supabase database as an example, the process to configure database in RavenSaaS is:

1. Create Database

Login to Supabase console and create a database

2. View Database Connection Information

In the Supabase console, enter your created database and click Connect at the top

Supabase Connect

Copy the database connection information from the popup, similar to this string:

Terminal
1postgresql://postgres.vbpzjovjpcezduozlkkh:[YOUR-PASSWORD]@aws-0-us-east-1.pooler.supabase.com:6543/postgres?pgbouncer=true

[YOUR-PASSWORD] needs to be replaced with the password you set when creating the database.

3. Configure Database

Modify project configuration files: .env.development and .env.production

Set database connection information:

Terminal
1DATABASE_URL="postgresql://postgres.vbpzjovjpcezduozlkkh:[YOUR-PASSWORD]@aws-0-us-east-1.pooler.supabase.com:6543/postgres?pgbouncer=true"

Initialize Database

After configuring DATABASE_URL, run the following command in the project root directory to initialize the database:

Terminal
1pnpm db:migrate

This command will execute all migration files in the src/db/migrations directory to create database tables

Note: If the database you configured through DATABASE_URL is not a newly created database, or if the database already contains RavenSaaS table information, do not execute the above command. Instead, compare the SQL content of all migration files in the src/db/migrations directory and manually execute SQL statements to update database tables.

2. Modify Database Configuration (No modification by default)

Modify the ./drizzle.config.ts file to use new database connection configuration.

Default is Postgres database connection configuration, you can refer to drizzle-orm documentation to modify as needed.

./drizzle.config.ts
1import { defineConfig } from "drizzle-kit";
2import * as dotenv from "dotenv";
3
4dotenv.config({ path: '.env.development' });
5if (!process.env.DATABASE_URL) {
6  dotenv.config({ path: '.env' });
7}
8
9export default defineConfig({
10  schema: "./src/db/schema.ts",
11  out: "./src/db/migrations",
12  dialect: "postgresql",
13  dbCredentials: {
14    url: process.env.DATABASE_URL!,
15  },
16  verbose: true,
17  strict: true,
18});
19

3. Modify Database Connection Instance (No modification by default)

Modify the database connection instance according to the database type you are using.

Terminal
1import { drizzle } from "drizzle-orm/postgres-js";
2import postgres from "postgres";
3
4const isCloudflareWorker =
5  typeof globalThis !== "undefined" && "Cloudflare" in globalThis;
6
7// DB Instance
8let dbInstance: ReturnType<typeof drizzle> | null = null;
9
10export function db() {
11  const databaseUrl = process.env.DATABASE_URL;
12  if (!databaseUrl) {
13    throw new Error("DATABASE_URL is not set");
14  }
15
16  if (isCloudflareWorker) {
17    const client = postgres(databaseUrl, {
18      prepare: false,
19      max: 1,
20      idle_timeout: 10,
21      connect_timeout: 5,
22    });
23 
24    return drizzle(client);
25  }
26
27  if (dbInstance) {
28    return dbInstance;
29  }
30  
31  const client = postgres(databaseUrl, {
32    prepare: false,
33    max: 10,
34    idle_timeout: 30,
35    connect_timeout: 10,
36  });
37  dbInstance = drizzle({ client });
38
39  return dbInstance;
40}

Update Database

4. Generate Database Migration Files

If after project creation, you are using a new database and initialized the database through the pnpm db:migrate command, if you later pull the latest code, you can continue to execute the following command to update database tables

Terminal
1pnpm db:generate

This command will incrementally update database tables based on the SQL content of all migration files in the src/db/migrations directory.

Database Operations

In the src/models directory, write database operation files to implement CRUD operations on data tables. You can refer to the following example of operating the posts table:

Database operation syntax can refer to drizzle-orm documentation.

Create Data

./src/models/posts.ts
1export async function insertPost(
2  data: typeof posts.$inferInsert
3): Promise<typeof posts.$inferSelect | undefined> {
4  const [post] = await db().insert(posts).values(data).returning();
5
6  return post;
7}

Update Data

./src/models/posts.ts
1export async function updatePost(
2  id: string,
3  data: Partial<typeof posts.$inferInsert>
4): Promise<typeof posts.$inferSelect | undefined> {
5  const [post] = await db()
6    .update(posts)
7    .set(data)
8    .where(eq(posts.id, parseInt(id)))
9    .returning();
10 
11  return post;
12}

Query Data

./src/models/posts.ts
1export async function getPostById(
2  id: string
3): Promise<typeof posts.$inferSelect | undefined> {
4  const [post] = await db()
5      .select()
6      .from(posts)
7      .where(eq(posts.id, parseInt(id)))
8      .limit(1);
9
10    return post;
11}
12
13export async function getPostBySlug(
14  slug: string
15): Promise<typeof posts.$inferSelect | undefined> {
16  const [post] = await db()
17      .select()
18      .from(posts)
19      .where(eq(posts.slug, slug))
20      .limit(1);
21
22    return post;
23}
24
25export async function getAllPosts(
26  page: number = 1,
27  limit: number = 10
28): Promise<(typeof posts.$inferSelect)[] | undefined> {
29  const offset = (page - 1) * limit;
30
31  const data = await db()
32    .select()
33    .from(posts)
34    .limit(limit)
35    .offset(offset)
36    .orderBy(desc(posts.created_at));
37
38  return data;
39}

Using Other Database Types

If you want to use other types of databases, you can refer to the drizzle-orm official documentation for configuration.

Reference

drizzle-orm

drizzle-orm official documentation

View Docs →

supabase

supabase official documentation

View Docs →

Last updated on January 5, 2025

Deploy to Cloudflare
Authentication