Database

ShipAny currently uses Supabase as its database for storing user login data, payment orders, and other data.

Other databases like MySQL / PostgreSQL will be supported gradually in the future.

To fully run ShipAny’s functionality, please follow these steps to configure the database.

Database Configuration

Log in to Supabase official website and create a Supabase database instance
Enter the created Supabase database instance and obtain the API access credentials

supabase-api

Fill in the database configuration information in the environment configuration file

You can create different Supabase database instances for development/production environments and fill them into .env.development and .env.production files respectively.

.env.development

SUPABASE_URL = "https://xxx.supabase.co"
SUPABASE_ANON_KEY = "eyxxx.eyxxx.qhxxx"

SUPABASE_ANON_KEY and SUPABASE_SERVICE_ROLE_KEY are optional. For data security, it is recommended to use SUPABASE_SERVICE_ROLE_KEY in production environments.

.env.production

SUPABASE_URL = "https://xxx.supabase.co"
SUPABASE_SERVICE_ROLE_KEY = "eyxxx.eyxxx.qhxxx"

Creating Database Tables

Copy the SQL statements from data/install.sql in the ShipAny template code

data/install.sql

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    uuid VARCHAR(255) UNIQUE NOT NULL,
    email VARCHAR(255) NOT NULL,
    created_at timestamptz,
    nickname VARCHAR(255),
    avatar_url VARCHAR(255),
    locale VARCHAR(50),
    signin_type VARCHAR(50),
    signin_ip VARCHAR(255),
    signin_provider VARCHAR(50),
    signin_openid VARCHAR(255),
    UNIQUE (email, signin_provider)
);
 
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    order_no VARCHAR(255) UNIQUE NOT NULL,
    created_at timestamptz,
    user_uuid VARCHAR(255) NOT NULL DEFAULT '',
    user_email VARCHAR(255) NOT NULL DEFAULT '',
    amount INT NOT NULL,
    interval VARCHAR(50),
    expired_at timestamptz,
    status VARCHAR(50) NOT NULL,
    stripe_session_id VARCHAR(255),
    credits INT NOT NULL,
    currency VARCHAR(50),
    sub_id VARCHAR(255),
    sub_interval_count int,
    sub_cycle_anchor int,
    sub_period_end int,
    sub_period_start int,
    sub_times int,
    product_id VARCHAR(255),
    product_name VARCHAR(255),
    valid_months int,
    order_detail TEXT,
    paid_at timestamptz,
    paid_email VARCHAR(255),
    paid_detail TEXT
);

Run the SQL statements in your Supabase instance to create the database tables

supabase-run

After configuring user login and payment functions, user data and payment data will be automatically written to the users and orders tables.

You can manage related data in the Supabase instance console.

Data CRUD

You can refer to the data operation logic in user.ts and order.ts under the models directory in the ShipAny template.

Use @supabase/supabase-js SDK to implement data Create, Read, Update, and Delete operations.

models/user.ts

import { User } from "@/types/user";
import { getSupabaseClient } from "./db";
 
export async function insertUser(user: User) {
  const supabase = getSupabaseClient();
  const { data, error } = await supabase.from("users").insert(user);
 
  if (error) {
    throw error;
  }
 
  return data;
}
 
export async function findUserByEmail(
  email: string,
  signin_provider: string
): Promise<User | undefined> {
  const supabase = getSupabaseClient();
  const { data, error } = await supabase
    .from("users")
    .select("*")
    .eq("email", email)
    .eq("signin_provider", signin_provider)
    .single();
 
  if (error) {
    return undefined;
  }
 
  return data;
}

References

Deploy to Cloudflare OAuth