4.2 数据库与 CRUD 操作

Table name: todos

Columns:
- id (bigint, primary key) - 自动创建
- created_at (timestamptz) - 自动创建
- task (text) - 手动添加，Required 打勾
- completed (bool) - 手动添加，Default value: false
- user_id (uuid) - 暂时不加（等学了认证再说）

task: 学习 API 调用
completed: false

task: 做一个 Todo App
completed: true

pnpm install @supabase/supabase-js

NEXT_PUBLIC_SUPABASE_URL=你的Project_URL
NEXT_PUBLIC_SUPABASE_ANON_KEY=你的anon_public_key

import { createClient } from '@supabase/supabase-js'
 
const supabaseUrl = process.env.NEXT_PUBLIC_SUPABASE_URL!
const supabaseKey = process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
 
export const supabase = createClient(supabaseUrl, supabaseKey)

'use client'
 
import { useEffect, useState } from 'react'
import { supabase } from '@/lib/supabase'
 
type Todo = {
  id: number
  task: string
  completed: boolean
  created_at: string
}
 
export default function TodosPage() {
  const [todos, setTodos] = useState<Todo[]>([])
  const [newTask, setNewTask] = useState('')
  const [loading, setLoading] = useState(false)
 
  // 读取（Read）- 获取所有 todos
  const fetchTodos = async () => {
    const { data, error } = await supabase
      .from('todos')
      .select('*')
      .order('created_at', { ascending: false })
 
    if (error) {
      console.error('获取失败：', error)
    } else {
      setTodos(data || [])
    }
  }
 
  // 增加（Create）- 添加新 todo
  const addTodo = async () => {
    if (!newTask.trim()) return
 
    setLoading(true)
    const { data, error } = await supabase
      .from('todos')
      .insert([{ task: newTask, completed: false }])
      .select()
 
    if (error) {
      console.error('添加失败：', error)
    } else {
      setTodos([...data, ...todos])
      setNewTask('')
    }
    setLoading(false)
  }
 
  // 更新（Update）- 切换完成状态
  const toggleTodo = async (id: number, completed: boolean) => {
    const { error } = await supabase
      .from('todos')
      .update({ completed: !completed })
      .eq('id', id)
 
    if (error) {
      console.error('更新失败：', error)
    } else {
      fetchTodos() // 重新获取数据
    }
  }
 
  // 删除（Delete）
  const deleteTodo = async (id: number) => {
    const { error } = await supabase
      .from('todos')
      .delete()
      .eq('id', id)
 
    if (error) {
      console.error('删除失败：', error)
    } else {
      setTodos(todos.filter(todo => todo.id !== id))
    }
  }
 
  // 页面加载时获取数据
  useEffect(() => {
    fetchTodos()
  }, [])
 
  return (
    <div className="max-w-2xl mx-auto p-8">
      <h1 className="text-3xl font-bold mb-6">我的 Todo List</h1>
 
      {/* 添加新任务 */}
      <div className="flex gap-2 mb-6">
        <input
          type="text"
          value={newTask}
          onChange={(e) => setNewTask(e.target.value)}
          onKeyPress={(e) => e.key === 'Enter' && addTodo()}
          placeholder="输入新任务..."
          className="flex-1 border p-2 rounded"
        />
        <button
          onClick={addTodo}
          disabled={loading}
          className="bg-blue-500 text-white px-4 py-2 rounded disabled:opacity-50"
        >
          {loading ? '添加中...' : '添加'}
        </button>
      </div>
 
      {/* Todo 列表 */}
      <div className="space-y-2">
        {todos.map((todo) => (
          <div
            key={todo.id}
            className="flex items-center gap-2 p-3 border rounded hover:bg-gray-50"
          >
            <input
              type="checkbox"
              checked={todo.completed}
              onChange={() => toggleTodo(todo.id, todo.completed)}
              className="w-5 h-5"
            />
            <span className={`flex-1 ${todo.completed ? 'line-through text-gray-500' : ''}`}>
              {todo.task}
            </span>
            <button
              onClick={() => deleteTodo(todo.id)}
              className="text-red-500 hover:text-red-700"
            >
              删除
            </button>
          </div>
        ))}
 
        {todos.length === 0 && (
          <p className="text-center text-gray-500 py-8">还没有任务，添加一个吧！</p>
        )}
      </div>
    </div>
  )
}

Table name: messages

Columns:
- id (bigint, primary key)
- created_at (timestamptz)
- author (text) - Required
- content (text) - Required
- likes (int4) - Default: 0

'use client'
 
import { useEffect, useState } from 'react'
import { supabase } from '@/lib/supabase'
 
type Message = {
  id: number
  author: string
  content: string
  likes: number
  created_at: string
}
 
export default function Guestbook() {
  const [messages, setMessages] = useState<Message[]>([])
  const [author, setAuthor] = useState('')
  const [content, setContent] = useState('')
 
  const fetchMessages = async () => {
    const { data } = await supabase
      .from('messages')
      .select('*')
      .order('created_at', { ascending: false })
 
    if (data) setMessages(data)
  }
 
  const addMessage = async () => {
    if (!author.trim() || !content.trim()) {
      alert('请填写完整信息')
      return
    }
 
    const { error } = await supabase
      .from('messages')
      .insert([{ author, content, likes: 0 }])
 
    if (error) {
      console.error('添加失败：', error)
    } else {
      setAuthor('')
      setContent('')
      fetchMessages()
    }
  }
 
  const addLike = async (id: number, currentLikes: number) => {
    await supabase
      .from('messages')
      .update({ likes: currentLikes + 1 })
      .eq('id', id)
 
    fetchMessages()
  }
 
  useEffect(() => {
    fetchMessages()
  }, [])
 
  return (
    <div className="max-w-2xl mx-auto p-8">
      <h1 className="text-3xl font-bold mb-6">留言板</h1>
 
      {/* 发表留言 */}
      <div className="border rounded p-4 mb-6">
        <input
          type="text"
          value={author}
          onChange={(e) => setAuthor(e.target.value)}
          placeholder="你的名字"
          className="w-full border p-2 rounded mb-2"
        />
        <textarea
          value={content}
          onChange={(e) => setContent(e.target.value)}
          placeholder="写点什么..."
          rows={3}
          className="w-full border p-2 rounded mb-2"
        />
        <button
          onClick={addMessage}
          className="bg-blue-500 text-white px-4 py-2 rounded"
        >
          发表留言
        </button>
      </div>
 
      {/* 留言列表 */}
      <div className="space-y-4">
        {messages.map((msg) => (
          <div key={msg.id} className="border rounded p-4">
            <div className="flex justify-between items-start mb-2">
              <div>
                <p className="font-bold">{msg.author}</p>
                <p className="text-sm text-gray-500">
                  {new Date(msg.created_at).toLocaleString('zh-CN')}
                </p>
              </div>
              <button
                onClick={() => addLike(msg.id, msg.likes)}
                className="flex items-center gap-1 text-red-500 hover:text-red-700"
              >
                ❤️ {msg.likes}
              </button>
            </div>
            <p className="text-gray-700">{msg.content}</p>
          </div>
        ))}
      </div>
    </div>
  )
}