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

Copy the database connection information from the popup, similar to this string:
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:
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:
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.
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.
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
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
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
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
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
Last updated on January 5, 2025