we0-cms-supabase-api

we0-cms-supabase-api

A powerful CMS API package for Next.js applications with dynamic table management and Supabase support.

Features

• 🚀 Dynamic table creation and management
• 📊 RESTful API for models and data operations
• 🔧 TypeScript support with full type definitions
• 💾 Supabase backend with real-time capabilities
• 🎯 Easy integration with Next.js API routes
• 📝 Comprehensive CRUD operations
• 🔐 Built-in authentication and security with Supabase
• ⚡ Real-time data synchronization

Installation

npm install we0-cms-supabase-api
# or
yarn add we0-cms-supabase-api
# or
pnpm add we0-cms-supabase-api

Peer Dependencies

Make sure you have the following peer dependencies installed:

npm install next @supabase/supabase-js

Quick Start

1. Supabase Configuration

First, initialize the Supabase connection in your Next.js app:

// lib/cms-config.ts
import {
  executeSupabaseSetup,
  getSupabaseSetupSQL,
  initializeSupabase,
  SupabaseConfig,
} from "we0-cms-supabase-api"

const supabaseConfig: SupabaseConfig = {
  url: process.env.NEXT_PUBLIC_SUPABASE_URL!,
  key: process.env.SUPABASE_SERVICE_ROLE_KEY!, // Use service role key for server-side operations
  schema: "public", // Optional, defaults to 'public'
}

// Initialize Supabase connection
initializeSupabase(supabaseConfig)

// 🆕 自动设置数据库（推荐）
async function setupDatabase() {
  const setupSuccess = await executeSupabaseSetup()

  if (!setupSuccess) {
    // 如果自动设置失败，获取 SQL 脚本手动执行
    const setupSQL = getSupabaseSetupSQL()
    console.log("请在 Supabase SQL 编辑器中执行以下 SQL:")
    console.log(setupSQL)
  }
}

// 在应用启动时调用
setupDatabase()

2. Supabase 数据库设置

🆕 自动设置（推荐）

现在你可以通过代码自动检查和设置 Supabase 数据库：

import { executeSupabaseSetup, getSupabaseSetupSQL } from "we0-cms-supabase-api"

// 自动设置
const success = await executeSupabaseSetup()

if (!success) {
  // 获取完整的 SQL 脚本
  const sql = getSupabaseSetupSQL()
  // 复制到 Supabase SQL 编辑器执行
}

手动设置（备选方案）

如果自动设置失败，你需要在 Supabase SQL 编辑器中手动执行以下 SQL 函数：

You need to create the following SQL functions in your Supabase database for the CMS to work properly:

-- Function to execute SQL queries
CREATE OR REPLACE FUNCTION execute_sql(sql_query text)
RETURNS json
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  result json;
BEGIN
  EXECUTE sql_query;
  GET DIAGNOSTICS result = ROW_COUNT;
  RETURN json_build_object('success', true, 'rows_affected', result);
EXCEPTION
  WHEN OTHERS THEN
    RETURN json_build_object('success', false, 'error', SQLERRM);
END;
$$;

-- Function to execute SQL with parameters
CREATE OR REPLACE FUNCTION execute_sql_with_params(sql_query text, params json)
RETURNS json
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  result json;
BEGIN
  -- This is a simplified version - in production you'd want proper parameter binding
  EXECUTE sql_query;
  GET DIAGNOSTICS result = ROW_COUNT;
  RETURN json_build_object('success', true, 'rows_affected', result);
EXCEPTION
  WHEN OTHERS THEN
    RETURN json_build_object('success', false, 'error', SQLERRM);
END;
$$;

-- Function to check if table exists
CREATE OR REPLACE FUNCTION check_table_exists(table_name text)
RETURNS boolean
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
  RETURN EXISTS (
    SELECT FROM information_schema.tables
    WHERE table_schema = 'public'
    AND table_name = $1
  );
END;
$$;

-- Function to get table structure
CREATE OR REPLACE FUNCTION get_table_structure(table_name text)
RETURNS json
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  result json;
BEGIN
  SELECT json_agg(
    json_build_object(
      'column_name', column_name,
      'data_type', data_type,
      'is_nullable', is_nullable,
      'column_default', column_default
    )
  ) INTO result
  FROM information_schema.columns
  WHERE table_schema = 'public' AND table_name = $1;

  RETURN result;
END;
$$;

3. Create API Routes

Models Management API

Create app/api/cms/models/route.ts:

import { createModelRoute } from "we0-cms-supabase-api"

export const { GET, POST, PUT, DELETE } = createModelRoute()

Dynamic Data Management API

Create app/api/cms/data/[tableName]/route.ts:

import { createDynamicDataRoute } from "we0-cms-supabase-api"

export const { GET, POST, PUT, DELETE } = createDynamicDataRoute()

Alternative: One-liner Setup

Use the convenience function to create all routes at once:

// app/api/cms/models/route.ts
import { createCmsRoutes } from "we0-cms-supabase-api"

const routes = createCmsRoutes()
export const { GET, POST, PUT, DELETE } = routes.models

// app/api/cms/data/[tableName]/route.ts
import { createCmsRoutes } from "we0-cms-supabase-api"

const routes = createCmsRoutes()
export const { GET, POST, PUT, DELETE } = routes.data

API Usage

Models API

GET /api/cms/models - Get all models

curl "http://localhost:3000/api/cms/models?page=1&limit=10&name=user"

POST /api/cms/models - Create a new model

curl -X POST "http://localhost:3000/api/cms/models" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "用户模型",
    "table_name": "users",
    "json_schema": {
      "fields": [
        {
          "name": "name",
          "type": "string",
          "required": true,
          "maxLength": 100,
          "comment": "用户姓名"
        },
        {
          "name": "email",
          "type": "email",
          "unique": true,
          "required": true,
          "comment": "用户邮箱"
        },
        {
          "name": "age",
          "type": "integer",
          "comment": "年龄"
        }
      ]
    }
  }'

PUT /api/cms/models - Update a model

curl -X PUT "http://localhost:3000/api/cms/models" \
  -H "Content-Type: application/json" \
  -d '{
    "id": 1,
    "name": "更新的用户模型"
  }'

DELETE /api/cms/models?id=1 - Delete a model

curl -X DELETE "http://localhost:3000/api/cms/models?id=1"

Data API

GET /api/cms/data/users - Get table data

curl "http://localhost:3000/api/cms/data/users?page=1&limit=10&search=john"

POST /api/cms/data/users - Create new data

curl -X POST "http://localhost:3000/api/cms/data/users" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "John Doe",
    "email": "[e-mail]",
    "age": 30
  }'

PUT /api/cms/data/users - Update data

curl -X PUT "http://localhost:3000/api/cms/data/users" \
  -H "Content-Type: application/json" \
  -d '{
    "id": 1,
    "name": "John Smith",
    "age": 31
  }'

DELETE /api/cms/data/users?id=1 - Delete data

curl -X DELETE "http://localhost:3000/api/cms/data/users?id=1"

Advanced Usage

Custom Service Usage

import {
  getCmsModelService,
  getDynamicTableService,
  initializeSupabase,
  SupabaseConfig,
} from "we0-cms-supabase-api"

// Initialize Supabase
const supabaseConfig: SupabaseConfig = {
  url: process.env.NEXT_PUBLIC_SUPABASE_URL!,
  key: process.env.SUPABASE_SERVICE_ROLE_KEY!,
}
initializeSupabase(supabaseConfig)

// Use the dynamic table service directly
const tableService = getDynamicTableService()

// Create a custom table
await tableService.createTable("custom_table", {
  fields: [
    { name: "title", type: "string", required: true },
    { name: "content", type: "text" },
  ],
})

// Use the CMS model service directly
const cmsModelService = getCmsModelService()
const models = await cmsModelService.findAll()

Type Definitions

import type {
  ApiResponse,
  CmsModelAttributes,
  JsonSchema,
  PaginatedResponse,
  SchemaField,
  SupabaseConfig,
} from "we0-cms-supabase-api"

// Define your schema
const userSchema: JsonSchema = {
  fields: [
    {
      name: "username",
      type: "string",
      required: true,
      unique: true,
      maxLength: 50,
    },
    {
      name: "profile",
      type: "json",
    },
  ],
}

Environment Variables

Create a .env.local file in your project root:

# Supabase Configuration
NEXT_PUBLIC_SUPABASE_URL=your_supabase_project_url
SUPABASE_SERVICE_ROLE_KEY=your_supabase_service_role_key

# Optional: Custom schema (defaults to 'public')
SUPABASE_SCHEMA=public

You can find these values in your Supabase project dashboard:

• NEXT_PUBLIC_SUPABASE_URL: Project Settings > API > Project URL
• SUPABASE_SERVICE_ROLE_KEY: Project Settings > API > service_role key (keep this secret!)

Field Types

Supported field types for JSON schema:

• string - Variable length string
• text - Long text
• integer - Integer number
• float - Floating point number
• boolean - True/false value
• date - Date only
• datetime - Date and time
• json - JSON object
• email - Email string (validated as string)

Error Handling

All API responses follow a consistent format:

interface ApiResponse<T = any> {
  success: boolean
  message?: string
  data?: T
  error?: string
}

Contributing

• Fork the repository
• Create your feature branch (git checkout -b feature/amazing-feature)
• Commit your changes (git commit -m 'Add some amazing feature')
• Push to the branch (git push origin feature/amazing-feature)
• Open a Pull Request

License

MIT

Support

For support, please open an issue on the GitHub repository or contact the maintainers.