SQLite

npm install sqlite3

const sqlite3 = require('sqlite3')
const path = require('path')

const DB_PATH = path.join(__dirname, 'data.db')

const db = new sqlite3.Database(DB_PATH, (err) => {
  if (err) throw err
  console.log('SQLite connected:', DB_PATH)
})

const CREATE_TABLE_SQL = `
  CREATE TABLE IF NOT EXISTS todo (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    title       TEXT NOT NULL,
    description TEXT DEFAULT '',
    completed   INTEGER DEFAULT 0,
    created_at  TEXT DEFAULT (datetime('now', 'localtime')),
    updated_at  TEXT DEFAULT (datetime('now', 'localtime'))
  )
`

db.run(CREATE_TABLE_SQL, (err) => {
  if (err) throw err
  console.log('Table created')
})

function createTodo(title, description = '') {
  return new Promise((resolve, reject) => {
    db.run(
      'INSERT INTO todo (title, description) VALUES (?, ?)',
      [title, description],
      function (err) {
        if (err) reject(err)
        else resolve({ id: this.lastID, title, description })
      }
    )
  })
}

// 查询全部
function listTodos() {
  return new Promise((resolve, reject) => {
    db.all('SELECT * FROM todo ORDER BY created_at DESC', (err, rows) => {
      if (err) reject(err)
      else resolve(rows)
    })
  })
}

// 条件查询
function findTodo(id) {
  return new Promise((resolve, reject) => {
    db.get('SELECT * FROM todo WHERE id = ?', [id], (err, row) => {
      if (err) reject(err)
      else resolve(row)
    })
  })
}

function updateTodo(id, { title, description, completed }) {
  const fields = []
  const values = []

  if (title !== undefined) { fields.push('title = ?'); values.push(title) }
  if (description !== undefined) { fields.push('description = ?'); values.push(description) }
  if (completed !== undefined) { fields.push('completed = ?'); values.push(completed ? 1 : 0) }

  fields.push("updated_at = datetime('now', 'localtime')")
  values.push(id)

  return new Promise((resolve, reject) => {
    db.run(
      `UPDATE todo SET ${fields.join(', ')} WHERE id = ?`,
      values,
      function (err) {
        if (err) reject(err)
        else resolve({ changes: this.changes })
      }
    )
  })
}

function deleteTodo(id) {
  return new Promise((resolve, reject) => {
    db.run('DELETE FROM todo WHERE id = ?', [id], function (err) {
      if (err) reject(err)
      else resolve({ deleted: this.changes > 0 })
    })
  })
}

const http = require('http')
const url = require('url')

const server = http.createServer(async (req, res) => {
  const { pathname } = url.parse(req.url)
  const method = req.method

  res.setHeader('Content-Type', 'application/json; charset=utf-8')

  try {
    // GET /todos
    if (method === 'GET' && pathname === '/todos') {
      const todos = await listTodos()
      return respond(res, 200, todos)
    }

    // POST /todos
    if (method === 'POST' && pathname === '/todos') {
      const body = await parseBody(req)
      const todo = await createTodo(body.title, body.description)
      return respond(res, 201, todo)
    }

    // DELETE /todos/:id
    if (method === 'DELETE' && pathname.startsWith('/todos/')) {
      const id = pathname.split('/').pop()
      const result = await deleteTodo(id)
      return respond(res, 200, result)
    }

    respond(res, 404, { error: 'Not Found' })
  } catch (err) {
    respond(res, 500, { error: err.message })
  }
})

function respond(res, status, data) {
  res.writeHead(status)
  res.end(JSON.stringify(data))
}

function parseBody(req) {
  return new Promise((resolve) => {
    let body = ''
    req.on('data', (chunk) => { body += chunk })
    req.on('end', () => {
      try { resolve(JSON.parse(body)) }
      catch { resolve({}) }
    })
  })
}

server.listen(3000)

function parseBody(req) {
  const contentType = req.headers['content-type'] || ''

  return new Promise((resolve) => {
    let body = ''
    req.on('data', (chunk) => { body += chunk })
    req.on('end', () => {
      if (contentType.includes('application/json')) {
        try { resolve(JSON.parse(body)) }
        catch { resolve({}) }
      } else if (contentType.includes('application/x-www-form-urlencoded')) {
        resolve(Object.fromEntries(new URLSearchParams(body)))
      } else {
        resolve({ raw: body })
      }
    })
  })
}

db.serialize(() => {
  db.run('BEGIN TRANSACTION')

  db.run('INSERT INTO todo (title) VALUES (?)', ['Task 1'])
  db.run('INSERT INTO todo (title) VALUES (?)', ['Task 2'])

  // 出错自动回滚
  db.run('COMMIT', (err) => {
    if (err) {
      db.run('ROLLBACK')
      console.error('Transaction failed:', err.message)
    } else {
      console.log('Transaction committed')
    }
  })
})

// 应用退出时关闭连接
process.on('SIGINT', () => {
  db.close((err) => {
    if (err) console.error('Close error:', err.message)
    process.exit(err ? 1 : 0)
  })
})

// 使用 Promise 封装
const dbAsync = {
  run(sql, params = []) {
    return new Promise((resolve, reject) => {
      db.run(sql, params, function (err) {
        if (err) reject(err)
        else resolve({ lastID: this.lastID, changes: this.changes })
      })
    })
  },
  get(sql, params = []) {
    return new Promise((resolve, reject) => {
      db.get(sql, params, (err, row) => {
        if (err) reject(err)
        else resolve(row)
      })
    })
  },
  all(sql, params = []) {
    return new Promise((resolve, reject) => {
      db.all(sql, params, (err, rows) => {
        if (err) reject(err)
        else resolve(rows)
      })
    })
  },
}