FeaturesORM

Database ORM

ShipAny uses Supabase as the default database dependency. You can refer to the Database chapter to configure database connections.

If you need to use other databases like PostgreSQL / MySQL / SQLite, you can follow this chapter to configure ORM connections.

New Project Initialization

ShipAny provides code supporting drizzle ORM in the drizzle branch. When developing a new project, you can choose to initialize the project using this branch.

This branch does not support edge-runtime and cannot be deployed to Cloudflare Pages. It’s suitable for deployment to Vercel or self-hosted servers.

This branch has significant code changes. For existing projects, it’s not recommended to sync with this branch for upgrades.

  1. Clone the code
Terminal
git clone -b drizzle [email protected]:shipanyai/shipany-template-one.git my-shipany-project
  1. Create configuration file
Terminal
cp .env.example .env.development
  1. Configure database connection

ShipAny supports SQLite / MySQL / PostgreSQL databases by default. Fill in the connection information for your chosen database.

.env.development
SQLITE_URL = "file:data/shipany.db"
MYSQL_URL = "mysql://aigc:[email protected]:3306/shipany"
POSTGRES_URL = "postgresql://aigc:[email protected]:5432/shipany"

Using PostgreSQL

If you’re using PostgreSQL or PostgreSQL-compatible databases like Supabase / Neon, follow these steps to configure ORM connection.

  1. Define data structure

You can modify the default data structure definition as needed:

./drizzle/postgres/schema.ts
import {
  index,
  integer,
  pgTable,
  text,
  uniqueIndex,
  varchar,
} from "drizzle-orm/pg-core";
 
export const users = pgTable(
  "users",
  {
    id: integer().primaryKey().generatedAlwaysAsIdentity(),
    uuid: varchar({ length: 255 }).unique().notNull(),
    email: varchar({ length: 255 }).notNull(),
    created_at: varchar({ length: 255 }),
    nickname: varchar({ length: 255 }),
    avatar_url: varchar({ length: 255 }),
    locale: varchar({ length: 50 }),
    signin_type: varchar({ length: 50 }),
    signin_ip: varchar({ length: 255 }),
    signin_provider: varchar({ length: 50 }),
    signin_openid: varchar({ length: 255 }),
  },
  (table) => [
    uniqueIndex("email_provider_unique_idx").on(
      table.email,
      table.signin_provider
    ),
  ]
);
 
export const orders = pgTable("orders", {
  id: integer().primaryKey().generatedAlwaysAsIdentity(),
  order_no: varchar({ length: 255 }).unique().notNull(),
  created_at: varchar({ length: 255 }),
  user_uuid: varchar({ length: 255 }).notNull().default(""),
  user_email: varchar({ length: 255 }).notNull().default(""),
  amount: integer().notNull(),
  interval: varchar({ length: 50 }),
  expired_at: varchar({ length: 255 }),
  status: varchar({ length: 50 }).notNull(),
  stripe_session_id: varchar({ length: 255 }),
  credits: integer().notNull(),
  currency: varchar({ length: 50 }),
  sub_id: varchar({ length: 255 }),
  sub_interval_count: integer(),
  sub_cycle_anchor: integer(),
  sub_period_end: integer(),
  sub_period_start: integer(),
  sub_times: integer(),
  product_id: varchar({ length: 255 }),
  product_name: varchar({ length: 255 }),
  valid_months: integer(),
  order_detail: text(),
  paid_at: varchar({ length: 255 }),
  paid_email: varchar({ length: 255 }),
  paid_detail: text(),
});
 
export const apikeys = pgTable("apikeys", {
  id: integer().primaryKey().generatedAlwaysAsIdentity(),
  api_key: varchar({ length: 255 }).unique().notNull(),
  title: varchar({ length: 100 }),
  user_uuid: varchar({ length: 255 }).notNull(),
  created_at: varchar({ length: 255 }),
  status: varchar({ length: 50 }),
});
 
export const credits = pgTable("credits", {
  id: integer().primaryKey().generatedAlwaysAsIdentity(),
  trans_no: varchar({ length: 255 }).unique().notNull(),
  created_at: varchar({ length: 255 }),
  user_uuid: varchar({ length: 255 }).notNull(),
  trans_type: varchar({ length: 50 }).notNull(),
  credits: integer().notNull(),
  order_no: varchar({ length: 255 }),
  expired_at: varchar({ length: 255 }),
});
 
export const posts = pgTable(
  "posts",
  {
    id: integer().primaryKey().generatedAlwaysAsIdentity(),
    uuid: varchar({ length: 255 }).unique().notNull(),
    slug: varchar({ length: 255 }),
    title: varchar({ length: 255 }),
    description: text(),
    content: text(),
    created_at: varchar({ length: 255 }),
    updated_at: varchar({ length: 255 }),
    status: varchar({ length: 50 }),
    cover_url: varchar({ length: 255 }),
    author_name: varchar({ length: 255 }),
    author_avatar_url: varchar({ length: 255 }),
    locale: varchar({ length: 50 }),
  },
  (table) => [index("slug_locale_idx").on(table.slug, table.locale)]
);
  1. Export table structure

When performing data operations, the table structure is imported from ./drizzle/schema.ts by default. You need to export the table structure according to your needs.

./drizzle/schema.ts
export * from "./postgres/schema";
  1. Modify database client

The default PostgreSQL database client is defined in ./drizzle/postgres/db.ts. You can modify it as needed:

./drizzle/postgres/db.ts
import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";
 
let globalPool: postgres.Sql | null = null;
 
export async function getDb() {
  if (!process.env.POSTGRES_URL) {
    throw new Error("POSTGRES_URL is not set");
  }
 
  if (!globalPool) {
    globalPool = postgres(process.env.POSTGRES_URL, {
      max: 10,
      idle_timeout: 20,
      connect_timeout: 10,
    });
  }
 
  const db = drizzle(globalPool);
 
  return db;
}
  1. Export database client

You need to export the actual database client for model operations.

./drizzle/db.ts
export * from "./postgres/db";
  1. Export ORM configuration

Modify the ORM configuration as needed, adjusting according to your chosen database.

./drizzle/config.ts
import { config } from "dotenv";
import { defineConfig } from "drizzle-kit";
 
config({ path: ".env" });
config({ path: ".env.development" });
config({ path: ".env.local" });
 
export default defineConfig({
  out: "./drizzle/postgres/migration",
  schema: "./drizzle/postgres/schema.ts",
  dialect: "postgresql",
  dbCredentials: {
    url: process.env.POSTGRES_URL!,
  },
});
  1. Create tables

Run the following commands in terminal:

Terminal
pnpm db:generate
pnpm db:migrate

This will generate migration files based on the table structure defined in ./drizzle/postgres/schema.ts. It will automatically execute SQL statements in migration files to create tables.

  1. Data operation examples

Use const db = await getDb() to get the database client, then perform data operations according to your needs. Table types are exported from ./drizzle/postgres/schema.ts.

./models/post.ts
import { and, desc, eq } from "drizzle-orm";
 
import { getDb } from "@/drizzle/db";
import { posts } from "@/drizzle/schema";
 
// insert data
export async function insertPost(post: typeof posts.$inferInsert) {
  const db = await getDb();
  await db.insert(posts).values(post);
 
  return post;
}
 
// update data
export async function updatePost(
  uuid: string,
  post: Partial<typeof posts.$inferInsert>
) {
  const db = await getDb();
  await db.update(posts).set(post).where(eq(posts.uuid, uuid));
 
  return post;
}
 
// select data
export async function findPostByUuid(
  uuid: string
): Promise<typeof posts.$inferSelect | undefined> {
  const db = await getDb();
  const data = await db
    .select()
    .from(posts)
    .where(eq(posts.uuid, uuid))
    .limit(1);
 
  return data ? data[0] : undefined;
}

Using MySQL

If you’re using MySQL or other MySQL-compatible databases, follow these steps to modify the ORM configuration.

  1. Configure MySQL database connection
.env.development
MYSQL_URL = "mysql://aigc:[email protected]:3306/shipany"
  1. Export MySQL table structure
./drizzle/schema.ts
export * from "./mysql/schema";
  1. Export MySQL database client
./drizzle/db.ts
export * from "./mysql/db";
  1. Export MySQL ORM configuration
./drizzle/config.ts
import { config } from "dotenv";
import { defineConfig } from "drizzle-kit";
 
config({ path: ".env" });
config({ path: ".env.development" });
config({ path: ".env.local" });
 
export default defineConfig({
  out: "./drizzle/mysql/migration",
  schema: "./drizzle/mysql/schema.ts",
  dialect: "mysql",
  dbCredentials: {
    url: process.env.MYSQL_URL!,
  },
});
  1. Create tables

Run the following commands in terminal:

Terminal
pnpm db:generate
pnpm db:migrate

This will generate migration files based on the table structure defined in ./drizzle/mysql/schema.ts. It will automatically execute SQL statements in migration files to create tables.

  1. Customization

Table structure is defined in ./drizzle/mysql/schema.ts. You can modify it as needed.

Database connection client is defined in ./drizzle/mysql/db.ts. You can modify it as needed.

Data operations are similar to PostgreSQL syntax.

Using SQLite

If you’re using SQLite database, follow these steps to modify the ORM configuration.

  1. Configure SQLite database connection
.env.development
SQLITE_URL = "file:data/shipany.db"

data/shipany.db is your local file. You can use relative or absolute paths.

  1. Export SQLite table structure
./drizzle/schema.ts
export * from "./sqlite/schema";
  1. Export SQLite database client
./drizzle/db.ts
export * from "./sqlite/db";
  1. Export SQLite ORM configuration
./drizzle/config.ts
import { config } from "dotenv";
import { defineConfig } from "drizzle-kit";
 
config({ path: ".env" });
config({ path: ".env.development" });
config({ path: ".env.local" });
 
export default defineConfig({
  out: "./drizzle/sqlite/migration",
  schema: "./drizzle/sqlite/schema.ts",
  dialect: "sqlite",
  dbCredentials: {
    url: process.env.SQLITE_URL!,
  },
});
  1. Create tables

Run the following commands in terminal:

Terminal
pnpm db:generate
pnpm db:migrate

This will generate migration files based on the table structure defined in ./drizzle/sqlite/schema.ts. It will automatically execute SQL statements in migration files to create tables.

  1. Customization

Table structure is defined in ./drizzle/sqlite/schema.ts. You can modify it as needed.

Database connection client is defined in ./drizzle/sqlite/db.ts. You can modify it as needed.

Data operations are similar to PostgreSQL syntax.

Visual Database Management

Run the command in terminal:

Terminal
pnpm db:studio

Open your browser and visit https://local.drizzle.studio/ to manage your database visually.

orm-studio

References

DatabaseOAuth