Supabase + Node.js 技能

加载与：base.md + supabase.md + typescript.md

Express/Hono 模式与 Supabase 认证和 Drizzle ORM。

来源： Supabase JS 客户端 | Drizzle ORM

---

核心原则

Drizzle 用于查询，Supabase 用于认证/存储，中间件用于验证。

使用 Drizzle ORM 进行类型安全的数据库访问。使用 Supabase 客户端进行认证验证、存储和实时功能。Express 或 Hono 用于 API 层。

---

项目结构

project/
├── src/
│   ├── routes/
│   │   ├── index.ts             # 路由聚合器
│   │   ├── auth.ts
│   │   ├── posts.ts
│   │   └── users.ts
│   ├── middleware/
│   │   ├── auth.ts              # JWT 验证
│   │   ├── error.ts             # 错误处理
│   │   └── validate.ts          # 请求验证
│   ├── db/
│   │   ├── index.ts             # Drizzle 客户端
│   │   ├── schema.ts            # 模式定义
│   │   └── queries/             # 查询函数
│   ├── lib/
│   │   ├── supabase.ts          # Supabase 客户端
│   │   └── config.ts            # 环境配置
│   ├── types/
│   │   └── express.d.ts         # Express 类型扩展
│   └── index.ts                 # 应用入口点
├── supabase/
│   ├── migrations/
│   └── config.toml
├── drizzle.config.ts
├── package.json
├── tsconfig.json
└── .env

---

设置

安装依赖

npm install express cors helmet dotenv @supabase/supabase-js drizzle-orm postgres zod
npm install -D typescript @types/express @types/cors @types/node tsx drizzle-kit

package.json 脚本

{
  "scripts": {
    "dev": "tsx watch src/index.ts",
    "build": "tsc",
    "start": "node dist/index.js",
    "db:generate": "drizzle-kit generate",
    "db:push": "drizzle-kit push",
    "db:studio": "drizzle-kit studio"
  }
}

环境变量

# .env
PORT=3000
NODE_ENV=development

# Supabase
SUPABASE_URL=http://localhost:54321
SUPABASE_ANON_KEY=<从 supabase 开始>
SUPABASE_SERVICE_ROLE_KEY=<从 supabase 开始>

# 数据库
DATABASE_URL=postgresql://postgres:postgres@localhost:54322/postgres

---

配置

src/lib/config.ts

import { z } from 'zod';
import dotenv from 'dotenv';

dotenv.config();

const envSchema = z.object({
  PORT: z.string().default('3000'),
  NODE_ENV: z.enum(['development', 'production', 'test']).default('development'),
  SUPABASE_URL: z.string().url(),
  SUPABASE_ANON_KEY: z.string(),
  SUPABASE_SERVICE_ROLE_KEY: z.string(),
  DATABASE_URL: z.string(),
});

export const config = envSchema.parse(process.env);

---

数据库设置

drizzle.config.ts

import { defineConfig } from 'drizzle-kit';
import { config } from './src/lib/config';

export default defineConfig({
  schema: './src/db/schema.ts',
  out: './supabase/migrations',
  dialect: 'postgresql',
  dbCredentials: {
    url: config.DATABASE_URL,
  },
  schemaFilter: ['public'],
});

src/db/index.ts

import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';
import * as schema from './schema';
import { config } from '../lib/config';

const client = postgres(config.DATABASE_URL, {
  prepare: false, // 需要 Supabase 池化
});

export const db = drizzle(client, { schema });

src/db/schema.ts

import {
  pgTable,
  uuid,
  text,
  timestamp,
  boolean,
} from 'drizzle-orm/pg-core';

export const profiles = pgTable('profiles', {
  id: uuid('id').primaryKey(),
  email: text('email').notNull(),
  name: text('name'),
  avatarUrl: text('avatar_url'),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
});

export const posts = pgTable('posts', {
  id: uuid('id').primaryKey().defaultRandom(),
  authorId: uuid('author_id').references(() => profiles.id).notNull(),
  title: text('title').notNull(),
  content: text('content'),
  published: boolean('published').default(false),
  createdAt: timestamp('created_at').defaultNow().notNull(),
});

// 类型导出
export type Profile = typeof profiles.$inferSelect;
export type NewProfile = typeof profiles.$inferInsert;
export type Post = typeof posts.$inferSelect;
export type NewPost = typeof posts.$inferInsert;

---

Supabase 客户端

src/lib/supabase.ts

import { createClient, SupabaseClient, User } from '@supabase/supabase-js';
import { config } from './config';

// 客户端与匿名密钥（尊重 RLS）
export const supabase = createClient(
  config.SUPABASE_URL,
  config.SUPABASE_ANON_KEY
);

// 管理员客户端（绕过 RLS）
export const supabaseAdmin = createClient(
  config.SUPABASE_URL,
  config.SUPABASE_SERVICE_ROLE_KEY,
  {
    auth: {
      autoRefreshToken: false,
      persistSession: false,
    },
  }
);

// 验证 JWT 并获取用户
export async function verifyToken(token: string): Promise<User | null> {
  const { data: { user }, error } = await supabase.auth.getUser(token);

  if (error || !user) {
    return null;
  }

  return user;
}

---

类型扩展

src/types/express.d.ts

import { User } from '@supabase/supabase-js';

declare global {
  namespace Express {
    interface Request {
      user?: User;
    }
  }
}

export {};

---

中间件

src/middleware/auth.ts

import { Request, Response, NextFunction } from 'express';
import { verifyToken } from '../lib/supabase';

export async function requireAuth(
  req: Request,
  res: Response,
  next: NextFunction
) {
  const authHeader = req.headers.authorization;

  if (!authHeader?.startsWith('Bearer ')) {
    return res.status(401).json({ error: '缺少授权头' });
  }

  const token = authHeader.split(' ')[1];
  const user = await verifyToken(token);

  if (!user) {
    return res.status(401).json({ error: '无效的令牌' });
  }

  req.user = user;
  next();
}

// 可选认证 - 即使没有令牌也继续
export async function optionalAuth(
  req: Request,
  res: Response,
  next: NextFunction
) {
  const authHeader = req.headers.authorization;

  if (authHeader?.startsWith('Bearer ')) {
    const token = authHeader.split(' ')[1];
    req.user = await verifyToken(token) ?? undefined;
  }

  next();
}

src/middleware/error.ts

import { Request, Response, NextFunction } from 'express';

export class AppError extends Error {
  constructor(
    public statusCode: number,
    message: string
  ) {
    super(message);
    this.name = 'AppError';
  }
}

export function errorHandler(
  err: Error,
  req: Request,
  res: Response,
  next: NextFunction
) {
  console.error(err);

  if (err instanceof AppError) {
    return res.status(err.statusCode).json({ error: err.message });
  }

  return res.status(500).json({ error: '内部服务器错误' });
}

src/middleware/validate.ts

import { Request, Response, NextFunction } from 'express';
import { z, ZodSchema } from 'zod';

export function validate<T extends ZodSchema>(schema: T) {
  return (req: Request, res: Response, next: NextFunction) => {
    try {
      req.body = schema.parse(req.body);
      next();
    } catch (error) {
      if (error instanceof z.ZodError) {
        return res.status(400).json({
          error: '验证失败',
          details: error.errors,
        });
      }
      next(error);
    }
  };
}

---

路由

src/routes/auth.ts

import { Router } from 'express';
import { z } from 'zod';
import { supabase } from '../lib/supabase';
import { validate } from '../middleware/validate';

const router = Router();

const signUpSchema = z.object({
  email: z.string().email(),
  password: z.string().min(8),
});

const signInSchema = z.object({
  email: z.string().email(),
  password: z.string(),
});

router.post('/signup', validate(signUpSchema), async (req, res, next) => {
  try {
    const { email, password } = req.body;

    const { data, error } = await supabase.auth.signUp({
      email,
      password,
    });

    if (error) {
      return res.status(400).json({ error: error.message });
    }

    return res.status(201).json({
      user: data.user,
      session: data.session,
    });
  } catch (error) {
    next(error);
  }
});

router.post('/signin', validate(signInSchema), async (req, res, next) => {
  try {
    const { email, password } = req.body;

    const { data, error } = await supabase.auth.signInWithPassword({
      email,
      password,
    });

    if (error) {
      return res.status(401).json({ error: '无效的凭证' });
    }

    return res.json({
      user: data.user,
      session: data.session,
    });
  } catch (error) {
    next(error);
  }
});

router.post('/signout', async (req, res) => {
  await supabase.auth.signOut();
  return res.json({ message: '已注销' });
});

router.post('/refresh', async (req, res, next) => {
  try {
    const { refresh_token } = req.body;

    const { data, error } = await supabase.auth.refreshSession({
      refresh_token,
    });

    if (error) {
      return res.status(401).json({ error: '无效的刷新令牌' });
    }

    return res.json({
      session: data.session,
    });
  } catch (error) {
    next(error);
  }
});

export default router;

src/routes/posts.ts

import { Router } from 'express';
import { z } from 'zod';
import { eq, desc } from 'drizzle-orm';
import { db } from '../db';
import { posts, Post } from '../db/schema';
import { requireAuth, optionalAuth } from '../middleware/auth';
import { validate } from '../middleware/validate';
import { AppError } from '../middleware/error';

const router = Router();

const createPostSchema = z.object({
  title: z.string().min(1).max(200),
  content: z.string().optional(),
  published: z.boolean().default(false),
});

const updatePostSchema = createPostSchema.partial();

// 列出所有已发布的帖子
router.get('/', optionalAuth, async (req, res, next) => {
  try {
    const result = await db
      .select()
      .from(posts)
      .where(eq(posts.published, true))
      .orderBy(desc(posts.createdAt));

    return res.json(result);
  } catch (error) {
    next(error);
  }
});

// 列出用户的帖子
router.get('/me', requireAuth, async (req, res, next) => {
  try {
    const result = await db
      .select()
      .from(posts)
      .where(eq(posts.authorId, req.user!.id))
      .orderBy(desc(posts.createdAt));

    return res.json(result);
  } catch (error) {
    next(error);
  }
});

// 获取单个帖子
router.get('/:id', async (req, res, next) => {
  try {
    const [post] = await db
      .select()
      .from(posts)
      .where(eq(posts.id, req.params.id))
      .limit(1);

    if (!post) {
      throw new AppError(404, '帖子未找到');
    }

    return res.json(post);
  } catch (error) {
    next(error);
  }
});

// 创建帖子
router.post('/', requireAuth, validate(createPostSchema), async (req, res, next) => {
  try {
    const [post] = await db
      .insert(posts)
      .values({
        ...req.body,
        authorId: req.user!.id,
      })
      .returning();

    return res.status(201).json(post);
  } catch (error) {
    next(error);
  }
});

// 更新帖子
router.patch('/:id', requireAuth, validate(updatePostSchema), async (req, res, next) => {
  try {
    const [post] = await db
      .update(posts)
      .set(req.body)
      .where(eq(posts.id, req.params.id))
      .returning();

    if (!post) {
      throw new AppError(404, '帖子未找到');
    }

    return res.json(post);
  } catch (error) {
    next(error);
  }
});

// 删除帖子
router.delete('/:id', requireAuth, async (req, res, next) => {
  try {
    const [post] = await db
      .delete(posts)
      .where(eq(posts.id, req.params.id))
      .returning();

    if (!post) {
      throw new AppError(404, '帖子未找到');
    }

    return res.status(204).send();
  } catch (error) {
    next(error);
  }
});

export default router;

src/routes/index.ts

import { Router } from 'express';
import authRoutes from './auth';
import postRoutes from './posts';

const router = Router();

router.use('/auth', authRoutes);
router.use('/posts', postRoutes);

export default router;

---

主应用

src/index.ts

import express from 'express';
import cors from 'cors';
import helmet from 'helmet';
import routes from './routes';
import { errorHandler } from './middleware/error';
import { config } from './lib/config';

const app = express();

// 安全中间件
app.use(helmet());
app.use(cors());
app.use(express.json());

// 健康检查
app.get('/health', (req, res) => {
  res.json({ status: 'healthy' });
});

// API 路由
app.use('/api', routes);

// 错误处理（必须是最后一个）
app.use(errorHandler);

app.listen(config.PORT, () => {
  console.log(`服务器在端口 ${config.PORT} 上运行`);
});

export default app;

---

查询函数

src/db/queries/posts.ts

import { db } from '../index';
import { posts, profiles } from '../schema';
import { eq, desc, and } from 'drizzle-orm';

export async function getPublishedPosts(limit = 10) {
  return db
    .select({
      id: posts.id,
      title: posts.title,
      content: posts.content,
      author: profiles.name,
      createdAt: posts.createdAt,
    })
    .from(posts)
    .innerJoin(profiles, eq(posts.authorId, profiles.id))
    .where(eq(posts.published, true))
    .orderBy(desc(posts.createdAt))
    .limit(limit);
}

export async function getUserPosts(userId: string) {
  return db
    .select()
    .from(posts)
    .where(eq(posts.authorId, userId))
    .orderBy(desc(posts.createdAt));
}

export async function getPostById(id: string) {
  const [post] = await db
    .select()
    .from(posts)
    .where(eq(posts.id, id))
    .limit(1);

  return post ?? null;
}

export async function createPost(data: {
  title: string;
  content?: string;
  authorId: string;
  published?: boolean;
}) {
  const [post] = await db.insert(posts).values(data).returning();
  return post;
}

---

存储

上传端点

import multer from 'multer';
import { supabase } from '../lib/supabase';

const upload = multer({ storage: multer.memoryStorage() });

router.post(
  '/avatar',
  requireAuth,
  upload.single('file'),
  async (req, res, next) => {
    try {
      if (!req.file) {
        throw new AppError(400, '未上传文件');
      }

      const fileExt = req.file.originalname.split('.').pop();
      const filePath = `${req.user!.id}/avatar.${fileExt}`;

      const { error } = await supabase.storage
        .from('avatars')
        .upload(filePath, req.file.buffer, {
          contentType: req.file.mimetype,
          upsert: true,
        });

      if (error) {
        throw new AppError(500, '上传失败');
      }

      const { data } = supabase.storage
        .from('avatars')
        .getPublicUrl(filePath);

      return res.json({ url: data.publicUrl });
    } catch (error) {
      next(error);
    }
  }
);

---

Hono 替代方案

对于边缘部署或更轻量级：

src/index.ts (Hono)

import { Hono } from 'hono';
import { cors } from 'hono/cors';
import { jwt } from 'hono/jwt';
import { db } from './db';
import { posts } from './db/schema';
import { eq, desc } from 'drizzle-orm';
import { config } from './lib/config';

const app = new Hono();

app.use('/*', cors());

// 公共路由
app.get('/posts', async (c) => {
  const result = await db
    .select()
    .from(posts)
    .where(eq(posts.published, true))
    .orderBy(desc(posts.createdAt));

  return c.json(result);
});

// 受保护的路由
app.use('/api/*', async (c, next) => {
  const auth = c.req.header('Authorization');
  if (!auth?.startsWith('Bearer ')) {
    return c.json({ error: '未授权' }, 401);
  }
  // 用 Supabase 验证...
  await next();
});

app.post('/api/posts', async (c) => {
  const body = await c.req.json();
  const [post] = await db.insert(posts).values(body).returning();
  return c.json(post, 201);
});

export default app;

---

测试

tests/setup.ts

import { beforeAll, afterAll, beforeEach } from 'vitest';
import { db } from '../src/db';
import { posts, profiles } from '../src/db/schema';

beforeAll(async () => {
  // 设置测试数据库
});

beforeEach(async () => {
  // 清理表
  await db.delete(posts);
  await db.delete(profiles);
});

afterAll(async () => {
  // 清理
});

tests/posts.test.ts

import { describe, it, expect } from 'vitest';
import request from 'supertest';
import app from '../src/index';

describe('帖子 API', () => {
  it('应该列出已发布的帖子', async () => {
    const res = await request(app)
      .get('/api/posts')
      .expect(200);

    expect(Array.isArray(res.body)).toBe(true);
  });

  it('应该需要认证才能创建帖子', async () => {
    await request(app)
      .post('/api/posts')
      .send({ title: '测试' })
      .expect(401);
  });
});

---

反模式

• 使用 Supabase 客户端进行数据库查询 - 使用 Drizzle
• 同步 JWT 验证 - 保持异步
• 无输入验证 - 使用 Zod 中间件
• 缺少错误处理 - 使用集中式错误处理程序
• 硬编码机密 - 使用环境变量
• 无请求日志 - 添加 morgan 或 pino
• 阻塞事件循环 - 整个过程中使用异步
• 在响应中暴露服务密钥 - 永远不要暴露