如何在迁移表中添加Cloudinary图片并实现帖子时间戳？

1. 迁移表修改：添加时间戳与图片字段 #

先更新迁移表，补充自动时间戳字段和图片URL存储字段（Cloudinary只需要存返回的图片链接，无需存储二进制文件）：

import { Sql } from 'postgres';

export type Post = {
  id: number;
  userId: number;
  title: string;
  content: string;
  imageUrl: string | null; // 允许帖子无图片
  createdAt: Date;
  updatedAt: Date;
};

export async function up(sql: Sql) {
  await sql`
    CREATE TABLE posts (
      id serial PRIMARY KEY,
      user_id integer NOT NULL REFERENCES users (id) ON DELETE CASCADE,
      title varchar(80) NOT NULL UNIQUE,
      content text NOT NULL,
      image_url text, -- 存储Cloudinary图片URL
      created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
      updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP
    );
  `;
  // 新增触发器：更新帖子时自动同步updated_at时间
  await sql`
    CREATE OR REPLACE FUNCTION update_updated_at_column()
    RETURNS TRIGGER AS $$
    BEGIN
      NEW.updated_at = CURRENT_TIMESTAMP;
      RETURN NEW;
    END;
    $$ LANGUAGE plpgsql;

    CREATE TRIGGER update_posts_updated_at
    BEFORE UPDATE ON posts
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();
  `;
}

export async function down(sql: Sql) {
  await sql`DROP TRIGGER IF EXISTS update_posts_updated_at ON posts`;
  await sql`DROP FUNCTION IF EXISTS update_updated_at_column`;
  await sql`DROP TABLE posts`;
}

2. Cloudinary集成步骤 #

安装依赖 #

npm install cloudinary

配置Cloudinary凭证 #

在项目根目录的.env文件中添加你的Cloudinary控制台获取的信息：

CLOUDINARY_CLOUD_NAME=你的云名称
CLOUDINARY_API_KEY=你的API密钥
CLOUDINARY_API_SECRET=你的API密钥

编写图片上传工具函数 #

新建utils/cloudinary.ts：

import { v2 as cloudinary } from 'cloudinary';

cloudinary.config({
  cloud_name: process.env.CLOUDINARY_CLOUD_NAME,
  api_key: process.env.CLOUDINARY_API_KEY,
  api_secret: process.env.CLOUDINARY_API_SECRET,
});

// 上传图片到Cloudinary，返回安全URL
export async function uploadImage(filePath: string) {
  try {
    const result = await cloudinary.uploader.upload(filePath, {
      folder: 'travel-posts', // 分类存储到指定文件夹
      format: 'webp', // 优化图片格式，节省带宽
    });
    return result.secure_url;
  } catch (error) {
    console.error('Cloudinary上传失败:', error);
    throw new Error('图片上传失败');
  }
}

3. 更新数据库操作代码 #

修正原有代码的字段错误（比如createBlogPost中误用post字段，实际表字段是content），并新增图片和时间戳的处理逻辑：

import 'server-only';
import { cache } from 'react';
import { Post } from '../migrations/00002-createTablePosts';
import { sql } from './connect';
import { UserBlogPostWithoutUserId } from './users';

// 扩展返回类型，包含图片和时间戳信息
export type UserBlogPostWithDetails = {
  postId: number;
  title: string;
  content: string;
  imageUrl: string | null;
  username: string;
  createdAt: Date;
  updatedAt: Date;
};

export const getPosts = cache(async () => {
  const posts = await sql<Post[]>`
    SELECT * FROM posts
  `;
  return posts;
});

export const deletePostByUserId = cache(async (userId: number) => {
  const [post] = await sql<Post[]>`
    DELETE FROM
      posts
    WHERE
      user_id = ${userId}
    RETURNING *
  `;
  return post;
});

// 更新创建帖子函数，支持传入图片URL
export const createBlogPost = cache(
  async (userId: number, title: string, content: string, imageUrl: string | null = null) => {
    const [post] = await sql<Post[]>`
      INSERT INTO posts
      (user_id, title, content, image_url)
      VALUES
        (${userId}, ${title}, ${content}, ${imageUrl})
      RETURNING *
    `;
    return post;
  },
);

// 更新查询逻辑，返回图片和时间戳
export const getAllBlogPosts = cache(async () => {
  const posts = await sql<UserBlogPostWithDetails[]>`
    SELECT
      posts.id AS post_id,
      posts.title AS title,
      posts.content AS content,
      posts.image_url AS image_url,
      posts.created_at AS created_at,
      posts.updated_at AS updated_at,
      users.username AS username
    FROM
      posts
    INNER JOIN
      users ON posts.user_id = users.id
    ORDER BY posts.created_at DESC -- 按最新发布排序
  `;
  return posts;
});

export const getBlogPostsById = cache(async (id: number) => {
  const [post] = await sql<UserBlogPostWithDetails[]>`
    SELECT
      posts.id AS post_id,
      posts.title AS title,
      posts.content AS content,
      posts.image_url AS image_url,
      posts.created_at AS created_at,
      posts.updated_at AS updated_at,
      users.username AS username
    FROM
      posts
    INNER JOIN
      users ON posts.user_id = users.id
    WHERE
      posts.id = ${id}
  `;
  return post;
});

4. 前端上传流程提示 #

1. 创建包含文件输入框的表单，限制仅接受图片类型（accept="image/*"）
2. 用户选择图片后，用FormData封装文件，发送到后端API
3. 后端接收文件后，临时保存到服务器或直接读取Buffer，调用uploadImage函数获取Cloudinary URL
4. 调用createBlogPost将帖子内容和图片URL存入数据库

内容的提问来源于stack exchange，提问作者gitaddBella