Express 加 sqlite3 写一个简单博客

例图：

搭建命令：

前提已装好node.js

开始创建项目结构

npm init -y
package.json:

{
  "name": "ex01",
  "version": "1.0.0",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [],
  "author": "",
  "license": "ISC",
  "description": ""
}
安装必要的依赖

npm install express sqlite3 ejs express-session body-parser

代码：

app.js

const express = require('express');
const session = require('express-session');
const bodyParser = require('body-parser');
const path = require('path');
const db = require('./database');

const app = express();

// 配置中间件
app.set('view engine', 'ejs');
app.use(express.static(path.join(__dirname, 'public')));
app.use(bodyParser.urlencoded({ extended: false }));
app.use(session({
    secret: 'blog_secret_key',
    resave: false,
    saveUninitialized: true
}));

// 首页路由
app.get('/', async (req, res) => {
    try {
        const category_id = req.query.category;
        const search = req.query.search;
        let posts;
        let categories = await db.all('SELECT * FROM categories');
        
        if (search) {
            // 搜索标题和内容
            posts = await db.all(`
                SELECT posts.*, categories.name as category_name 
                FROM posts 
                LEFT JOIN categories ON posts.category_id = categories.id 
                WHERE title LIKE ? OR content LIKE ?
                ORDER BY created_at DESC`, 
                [`%${search}%`, `%${search}%`]
            );
        } else if (category_id) {
            posts = await db.all(`
                SELECT posts.*, categories.name as category_name 
                FROM posts 
                LEFT JOIN categories ON posts.category_id = categories.id 
                WHERE category_id = ? 
                ORDER BY created_at DESC`, [category_id]);
        } else {
            posts = await db.all(`
                SELECT posts.*, categories.name as category_name 
                FROM posts 
                LEFT JOIN categories ON posts.category_id = categories.id 
                ORDER BY created_at DESC`);
        }
        
        res.render('index', { 
            posts, 
            categories, 
            current_category: category_id,
            search_query: search || ''
        });
    } catch (err) {
        res.status(500).send('数据库错误');
    }
});

// 创建博文页面
app.get('/post/new', async (req, res) => {
    try {
        const categories = await db.all('SELECT * FROM categories');
        res.render('new', { categories });
    } catch (err) {
        res.status(500).send('获取分类失败');
    }
});

// 提交新博文
app.post('/post/new', async (req, res) => {
    const { title, content, category_id } = req.body;
    try {
        await db.run(
            'INSERT INTO posts (title, content, category_id, created_at) VALUES (?, ?, ?, ?)',
            [title, content, category_id, new Date().toISOString()]
        );
        res.redirect('/');
    } catch (err) {
        res.status(500).send('创建博文失败');
    }
});

// 查看单篇博文
app.get('/post/:id', async (req, res) => {
    try {
        const post = await db.get(`
            SELECT posts.*, categories.name as category_name 
            FROM posts 
            LEFT JOIN categories ON posts.category_id = categories.id 
            WHERE posts.id = ?`, [req.params.id]);
        if (post) {
            res.render('post', { post });
        } else {
            res.status(404).send('博文不存在');
        }
    } catch (err) {
        res.status(500).send('数据库错误');
    }
});

// 编辑博文页面
app.get('/post/:id/edit', async (req, res) => {
    try {
        const post = await db.get('SELECT * FROM posts WHERE id = ?', [req.params.id]);
        const categories = await db.all('SELECT * FROM categories');
        if (post) {
            res.render('edit', { post, categories });
        } else {
            res.status(404).send('博文不存在');
        }
    } catch (err) {
        res.status(500).send('数据库错误');
    }
});

// 更新博文
app.post('/post/:id/edit', async (req, res) => {
    const { title, content, category_id } = req.body;
    try {
        await db.run(
            'UPDATE posts SET title = ?, content = ?, category_id = ? WHERE id = ?',
            [title, content, category_id, req.params.id]
        );
        res.redirect(`/post/${req.params.id}`);
    } catch (err) {
        res.status(500).send('更新博文失败');
    }
});

// 删除博文
app.post('/post/:id/delete', async (req, res) => {
    try {
        await db.run('DELETE FROM posts WHERE id = ?', [req.params.id]);
        res.redirect('/');
    } catch (err) {
        res.status(500).send('删除博文失败');
    }
});

const PORT = process.env.PORT || 3000;
app.listen(PORT, () => {
    console.log(`服务器运行在 http://localhost:${PORT}`);
});

database.js

const sqlite3 = require('sqlite3').verbose();
const path = require('path');

// 创建数据库连接
const db = new sqlite3.Database(path.join(__dirname, 'blog.db'), (err) => {
    if (err) {
        console.error('数据库连接失败:', err);
    } else {
        console.log('成功连接到数据库');
        initDatabase().catch(err => {
            console.error('数据库初始化失败:', err);
        });
    }
});

// 初始化数据库表
async function initDatabase() {
    try {
        // 检查表是否存在
        const tablesExist = await get(`
            SELECT name FROM sqlite_master 
            WHERE type='table' AND (name='posts' OR name='categories')
        `);

        if (!tablesExist) {
            console.log('首次运行，创建数据库表...');
            
            // 创建分类表
            await run(`
                CREATE TABLE IF NOT EXISTS categories (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    name TEXT NOT NULL UNIQUE
                )
            `);
            console.log('分类表创建成功');

            // 创建文章表
            await run(`
                CREATE TABLE IF NOT EXISTS posts (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    title TEXT NOT NULL,
                    content TEXT NOT NULL,
                    category_id INTEGER,
                    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (category_id) REFERENCES categories(id)
                )
            `);
            console.log('文章表创建成功');

            // 插入默认分类
            await run(`
                INSERT INTO categories (name) VALUES 
                ('技术'),
                ('生活'),
                ('随笔')
            `);
            console.log('默认分类创建成功');
        } else {
            console.log('数据库表已存在，跳过初始化');
        }
    } catch (err) {
        console.error('数据库初始化错误:', err);
        throw err;
    }
}

// Promise 包装数据库操作
function run(sql, params = []) {
    return new Promise((resolve, reject) => {
        db.run(sql, params, function(err) {
            if (err) {
                console.error('SQL执行错误:', err);
                reject(err);
            } else {
                resolve(this);
            }
        });
    });
}

function get(sql, params = []) {
    return new Promise((resolve, reject) => {
        db.get(sql, params, (err, result) => {
            if (err) {
                console.error('SQL执行错误:', err);
                reject(err);
            } else {
                resolve(result);
            }
        });
    });
}

function all(sql, params = []) {
    return new Promise((resolve, reject) => {
        db.all(sql, params, (err, rows) => {
            if (err) {
                console.error('SQL执行错误:', err);
                reject(err);
            } else {
                resolve(rows);
            }
        });
    });
}

// 关闭数据库连接
process.on('SIGINT', () => {
    db.close((err) => {
        if (err) {
            console.error('关闭数据库时出错:', err);
        } else {
            console.log('数据库连接已关闭');
        }
        process.exit(0);
    });
});

module.exports = {
    run,
    get,
    all
};

views\index.ejs

<!DOCTYPE html>
<html>
<head>
    <title>我的博客</title>
    <meta charset="UTF-8">
    <style>
        body { 
            font-family: Arial, sans-serif;
            max-width: 800px;
            margin: 0 auto;
            padding: 20px;
        }
        .post {
            margin-bottom: 20px;
            padding: 15px;
            border: 1px solid #ddd;
            border-radius: 5px;
        }
        .post h2 {
            margin-top: 0;
        }
        .post-date {
            color: #666;
            font-size: 0.9em;
        }
        .new-post-btn {
            display: inline-block;
            padding: 10px 20px;
            background-color: #007bff;
            color: white;
            text-decoration: none;
            border-radius: 5px;
            margin-bottom: 20px;
        }
        .categories {
            margin: 20px 0;
            padding: 10px 0;
            border-bottom: 1px solid #eee;
        }
        .category-link {
            display: inline-block;
            padding: 5px 10px;
            margin-right: 10px;
            text-decoration: none;
            color: #666;
            border-radius: 3px;
        }
        .category-link.active {
            background-color: #007bff;
            color: white;
        }
        .post-category {
            display: inline-block;
            padding: 3px 8px;
            background-color: #e9ecef;
            border-radius: 3px;
            font-size: 0.9em;
            margin-right: 10px;
        }
        .search-box {
            margin: 20px 0;
            display: flex;
            gap: 10px;
        }
        .search-input {
            flex: 1;
            padding: 8px;
            border: 1px solid #ddd;
            border-radius: 4px;
            font-size: 1em;
        }
        .search-btn {
            padding: 8px 20px;
            background-color: #007bff;
            color: white;
            border: none;
            border-radius: 4px;
            cursor: pointer;
        }
        .search-btn:hover {
            background-color: #0056b3;
        }
        .search-results {
            margin-bottom: 20px;
            padding: 10px;
            background-color: #f8f9fa;
            border-radius: 4px;
        }
        .search-results-hidden {
            display: none;
        }
    </style>
</head>
<body>
    <h1>博客文章列表</h1>
    <a href="/post/new" class="new-post-btn">写新文章</a>
    
    <form class="search-box" action="/" method="GET">
        <input type="text" name="search" class="search-input" 
               placeholder="搜索文章标题或内容..." 
               value="<%= search_query %>">
        <button type="submit" class="search-btn">搜索</button>
    </form>

    <div class="search-results <%= !search_query ? 'search-results-hidden' : '' %>">
        搜索结果: "<%= search_query || '' %>" - 找到 <%= posts ? posts.length : 0 %> 篇文章
    </div>
    
    <div class="categories">
        <a href="/" class="category-link <%= !current_category ? 'active' : '' %>">全部</a>
        <% categories.forEach(function(category) { %>
            <a href="/?category=<%= category.id %>" 
               class="category-link <%= current_category == category.id ? 'active' : '' %>">
                <%= category.name %>
            </a>
        <% }); %>
    </div>
    
    <% if (posts && posts.length > 0) { %>
        <% posts.forEach(function(post) { %>
            <div class="post">
                <h2><a href="/post/<%= post.id %>"><%= post.title %></a></h2>
                <div class="post-meta">
                    <span class="post-category"><%= post.category_name || '未分类' %></span>
                    <span class="post-date">发布时间: <%= new Date(post.created_at).toLocaleString() %></span>
                </div>
                <p><%= post.content.substring(0, 200) %>...</p>
            </div>
        <% }); %>
    <% } else { %>
        <p>还没有任何博客文章。</p>
    <% } %>
</body>
</html>

views\post.ejs

<!DOCTYPE html>
<html>
<head>
    <title><%= post.title %> - 我的博客</title>
    <meta charset="UTF-8">
    <style>
        body {
            font-family: Arial, sans-serif;
            max-width: 800px;
            margin: 0 auto;
            padding: 20px;
        }
        .post-title {
            margin-bottom: 10px;
        }
        .post-meta {
            color: #666;
            margin-bottom: 20px;
        }
        .post-content {
            line-height: 1.6;
            white-space: pre-wrap;
        }
        .back-link {
            display: inline-block;
            margin-bottom: 20px;
            color: #007bff;
            text-decoration: none;
        }
        .post-category {
            display: inline-block;
            padding: 3px 8px;
            background-color: #e9ecef;
            border-radius: 3px;
            font-size: 0.9em;
            margin-right: 10px;
        }
        .action-buttons {
            margin: 20px 0;
            display: flex;
            gap: 10px;
        }
        .edit-btn {
            padding: 5px 15px;
            background-color: #28a745;
            color: white;
            text-decoration: none;
            border-radius: 3px;
            font-size: 0.9em;
        }
        .delete-btn {
            padding: 5px 15px;
            background-color: #dc3545;
            color: white;
            border: none;
            border-radius: 3px;
            cursor: pointer;
            font-size: 0.9em;
        }
        .delete-btn:hover {
            background-color: #c82333;
        }
    </style>
</head>
<body>
    <a href="/" class="back-link">← 返回首页</a>
    
    <article>
        <h1 class="post-title"><%= post.title %></h1>
        <div class="post-meta">
            <span class="post-category"><%= post.category_name || '未分类' %></span>
            <span class="post-date">发布时间: <%= new Date(post.created_at).toLocaleString() %></span>
        </div>
        
        <div class="action-buttons">
            <a href="/post/<%= post.id %>/edit" class="edit-btn">编辑文章</a>
            <form action="/post/<%= post.id %>/delete" method="POST" style="display: inline;" 
                  onsubmit="return confirm('确定要删除这篇文章吗？');">
                <button type="submit" class="delete-btn">删除文章</button>
            </form>
        </div>
        
        <div class="post-content">
            <%= post.content %>
        </div>
    </article>
</body>
</html>

views\new.ejs

<!DOCTYPE html>
<html>
<head>
    <title>写新文章 - 我的博客</title>
    <meta charset="UTF-8">
    <style>
        body {
            font-family: Arial, sans-serif;
            max-width: 800px;
            margin: 0 auto;
            padding: 20px;
        }
        form {
            display: flex;
            flex-direction: column;
        }
        input, textarea, select {
            margin: 10px 0;
            padding: 8px;
            border: 1px solid #ddd;
            border-radius: 4px;
        }
        textarea {
            height: 300px;
        }
        button {
            padding: 10px 20px;
            background-color: #007bff;
            color: white;
            border: none;
            border-radius: 5px;
            cursor: pointer;
        }
        button:hover {
            background-color: #0056b3;
        }
        .back-link {
            display: inline-block;
            margin-bottom: 20px;
            color: #007bff;
            text-decoration: none;
        }
        label {
            margin-top: 10px;
            color: #666;
        }
    </style>
</head>
<body>
    <a href="/" class="back-link">← 返回首页</a>
    <h1>写新文章</h1>
    
    <form action="/post/new" method="POST">
        <label for="title">文章标题</label>
        <input type="text" id="title" name="title" placeholder="文章标题" required>
        
        <label for="category">选择分类</label>
        <select id="category" name="category_id" required>
            <option value="">请选择分类</option>
            <% categories.forEach(function(category) { %>
                <option value="<%= category.id %>"><%= category.name %></option>
            <% }); %>
        </select>
        
        <label for="content">文章内容</label>
        <textarea id="content" name="content" placeholder="文章内容" required></textarea>
        
        <button type="submit">发布文章</button>
    </form>
</body>
</html>

views\edit.ejs

<!DOCTYPE html>
<html>
<head>
    <title>编辑文章 - 我的博客</title>
    <meta charset="UTF-8">
    <style>
        body {
            font-family: Arial, sans-serif;
            max-width: 800px;
            margin: 0 auto;
            padding: 20px;
        }
        form {
            display: flex;
            flex-direction: column;
        }
        input, textarea, select {
            margin: 10px 0;
            padding: 8px;
            border: 1px solid #ddd;
            border-radius: 4px;
        }
        textarea {
            height: 300px;
        }
        button {
            padding: 10px 20px;
            background-color: #007bff;
            color: white;
            border: none;
            border-radius: 5px;
            cursor: pointer;
        }
        button:hover {
            background-color: #0056b3;
        }
        .back-link {
            display: inline-block;
            margin-bottom: 20px;
            color: #007bff;
            text-decoration: none;
        }
        label {
            margin-top: 10px;
            color: #666;
        }
    </style>
</head>
<body>
    <a href="/post/<%= post.id %>" class="back-link">← 返回文章</a>
    <h1>编辑文章</h1>
    
    <form action="/post/<%= post.id %>/edit" method="POST">
        <label for="title">文章标题</label>
        <input type="text" id="title" name="title" value="<%= post.title %>" required>
        
        <label for="category">选择分类</label>
        <select id="category" name="category_id" required>
            <option value="">请选择分类</option>
            <% categories.forEach(function(category) { %>
                <option value="<%= category.id %>" <%= post.category_id == category.id ? 'selected' : '' %>>
                    <%= category.name %>
                </option>
            <% }); %>
        </select>
        
        <label for="content">文章内容</label>
        <textarea id="content" name="content" required><%= post.content %></textarea>
        
        <button type="submit">更新文章</button>
    </form>
</body>
</html>