Database

ShipAny uses drizzle-orm to support multiple types of databases.

Configure Database

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

Create Database

View Database Connection Information

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

In the popup box, copy the database connection information, which looks like this string:

postgresql://postgres.defqvdpquwyqqjlmurkg:[YOUR-PASSWORD]@aws-0-ap-southeast-1.pooler.supabase.com:6543/postgres

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

Configure Database

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

Set the database connection information:

DATABASE_URL="postgresql://postgres.defqvdpquwyqqjlmurkg:******@aws-0-ap-southeast-1.pooler.supabase.com:6543/postgres"

Initialize Database

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

pnpm db:migrate

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

If the database you configured via DATABASE_URL is not a newly created database, or if the database already contains ShipAny table information, please 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 the database tables.

Update Database

If at the beginning of your project, you used a new database that was initialized through the pnpm db:migrate command, and if you later pull the latest code, you can continue to execute the following command to update the database tables:

pnpm db:migrate

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

Manage Database

Execute the command in the project root directory: pnpm db:studio

This command will open a database management interface where you can view, edit, and delete database tables.

Operate Database

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:

For data table operation syntax, refer to the drizzle-orm documentation.

import { posts } from "@/db/schema";
import { db } from "@/db";
import { and, desc, eq } from "drizzle-orm";
 
export enum PostStatus {
  Created = "created",
  Deleted = "deleted",
  Online = "online",
  Offline = "offline",
}
 
export async function insertPost(
  data: typeof posts.$inferInsert
): Promise<typeof posts.$inferSelect | undefined> {
  const [post] = await db().insert(posts).values(data).returning();
 
  return post;
}
 
export async function updatePost(
  uuid: string,
  data: Partial<typeof posts.$inferInsert>
): Promise<typeof posts.$inferSelect | undefined> {
  const [post] = await db()
    .update(posts)
    .set(data)
    .where(eq(posts.uuid, uuid))
    .returning();
 
  return post;
}
 
export async function findPostByUuid(
  uuid: string
): Promise<typeof posts.$inferSelect | undefined> {
  const [post] = await db()
    .select()
    .from(posts)
    .where(eq(posts.uuid, uuid))
    .limit(1);
 
  return post;
}
 
export async function findPostBySlug(
  slug: string,
  locale: string
): Promise<typeof posts.$inferSelect | undefined> {
  const [post] = await db()
    .select()
    .from(posts)
    .where(and(eq(posts.slug, slug), eq(posts.locale, locale)))
    .limit(1);
 
  return post;
}
 
export async function getAllPosts(
  page: number = 1,
  limit: number = 50
): Promise<(typeof posts.$inferSelect)[] | undefined> {
  const offset = (page - 1) * limit;
 
  const data = await db()
    .select()
    .from(posts)
    .orderBy(desc(posts.created_at))
    .limit(limit)
    .offset(offset);
 
  return data;
}
 
export async function getPostsByLocale(
  locale: string,
  page: number = 1,
  limit: number = 50
): Promise<(typeof posts.$inferSelect)[] | undefined> {
  const offset = (page - 1) * limit;
 
  const data = await db()
    .select()
    .from(posts)
    .where(and(eq(posts.locale, locale), eq(posts.status, PostStatus.Online)))
    .orderBy(desc(posts.created_at))
    .limit(limit)
    .offset(offset);
 
  return data;
}
 
export async function getPostsTotal(): Promise<number> {
  const total = await db().$count(posts);
 
  return total;
}

Use Other Types of Databases

If you use a self-hosted Postgres database, or use other cloud databases compatible with postgres, such as: Neon. The steps to configure and connect to the database are the same as using Supabase. You just need to fill in the database’s DATABASE_URL.

If you need to use MySQL or SQLite and other databases, you can refer to the following steps for customization:

Use New Database Schema

Modify the src/db/schema.ts file to use a new database schema.

For field definitions of data tables in the schema, refer to the drizzle-orm documentation.

Modify Database Configuration

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

The default is Postgres database connection configuration. You can refer to the drizzle-orm documentation to modify as needed.

import "dotenv/config";
import { config } from "dotenv";
import { defineConfig } from "drizzle-kit";
 
config({ path: ".env" });
config({ path: ".env.development" });
config({ path: ".env.local" });
 
export default defineConfig({
  out: "./src/db/migrations",
  schema: "./src/db/schema.ts",
  dialect: "postgresql",
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
});

Modify Database Connection Instance

Modify the database connection instance according to the type of database you use.

import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";
 
// Detect if running in Cloudflare Workers environment
const isCloudflareWorker =
  typeof globalThis !== "undefined" && "Cloudflare" in globalThis;
 
// Database instance for Node.js environment
let dbInstance: ReturnType<typeof drizzle> | null = null;
 
export function db() {
  const databaseUrl = process.env.DATABASE_URL;
  if (!databaseUrl) {
    throw new Error("DATABASE_URL is not set");
  }
 
  // In Cloudflare Workers, create new connection each time
  if (isCloudflareWorker) {
    // Workers environment uses minimal configuration
    const client = postgres(databaseUrl, {
      prepare: false,
      max: 1, // Limit to 1 connection in Workers
      idle_timeout: 10, // Shorter timeout for Workers
      connect_timeout: 5,
    });
 
    return drizzle(client);
  }
 
  // In Node.js environment, use singleton pattern
  if (dbInstance) {
    return dbInstance;
  }
 
  // Node.js environment with connection pool configuration
  const client = postgres(databaseUrl, {
    prepare: false,
    max: 10, // Maximum connections in pool
    idle_timeout: 30, // Idle connection timeout (seconds)
    connect_timeout: 10, // Connection timeout (seconds)
  });
  dbInstance = drizzle({ client });
 
  return dbInstance;
}

Generate Database Migration Files

If you want to update database tables through migration files in the future. You can execute the following command in the project root directory after completing the above three steps to generate database migration files.

pnpm db:generate

Configuring different types of databases in ShipAny is very simple. You only need to complete the customization of the above four steps. The logic for operating the database under src/model does not need to be modified.

References

FAQ OAuth