数据库集成 #
一、数据库方案概述 #
1.1 数据库选择 #
| 数据库 | 特点 | 使用场景 |
|---|---|---|
| SQLite | 轻量级、嵌入式 | 本地数据存储 |
| tauri-plugin-sql | 官方插件 | 简化数据库操作 |
| sled | 纯 Rust KV 存储 | 高性能键值存储 |
| redb | 纯 Rust 数据库 | 简单数据存储 |
1.2 架构设计 #
text
┌─────────────────────────────────────────────────────────────┐
│ 数据库架构 │
├─────────────────────────────────────────────────────────────┤
│ │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ 前端 API │ │
│ │ @tauri-apps/plugin-sql │ │
│ └─────────────────────────────────────────────────────┘ │
│ │ │
│ │ IPC │
│ ▼ │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ Rust 后端 │ │
│ │ tauri-plugin-sql / rusqlite │ │
│ └─────────────────────────────────────────────────────┘ │
│ │ │
│ ▼ │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ 数据库文件 │ │
│ │ SQLite / sled / redb │ │
│ └─────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────┘
二、tauri-plugin-sql #
2.1 安装插件 #
bash
pnpm add @tauri-apps/plugin-sql
toml
# Cargo.toml
[dependencies]
tauri-plugin-sql = { version = "2", features = ["sqlite"] }
2.2 注册插件 #
rust
// src-tauri/src/lib.rs
tauri::Builder::default()
.plugin(tauri_plugin_sql::Builder::default().build())
.run(tauri::generate_context!())
.expect("error while running tauri application");
2.3 权限配置 #
json
// src-tauri/capabilities/default.json
{
"permissions": [
"sql:default",
"sql:allow-load",
"sql:allow-execute",
"sql:allow-select"
]
}
三、SQLite 基础 #
3.1 创建数据库连接 #
typescript
import Database from '@tauri-apps/plugin-sql';
const db = await Database.load('sqlite:app.db');
3.2 创建表 #
typescript
await db.execute(`
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT UNIQUE NOT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
)
`);
3.3 插入数据 #
typescript
// 插入单条
await db.execute(
'INSERT INTO users (name, email) VALUES ($1, $2)',
['Alice', 'alice@example.com']
);
// 获取插入的 ID
const result = await db.execute(
'INSERT INTO users (name, email) VALUES ($1, $2)',
['Bob', 'bob@example.com']
);
console.log('Last insert ID:', result.lastInsertId);
3.4 查询数据 #
typescript
// 查询多条
const users = await db.select<User[]>(
'SELECT * FROM users ORDER BY created_at DESC'
);
// 查询单条
const user = await db.select<User[]>(
'SELECT * FROM users WHERE id = $1',
[1]
);
// 条件查询
const activeUsers = await db.select<User[]>(
'SELECT * FROM users WHERE email LIKE $1',
['%@example.com']
);
3.5 更新数据 #
typescript
await db.execute(
'UPDATE users SET name = $1 WHERE id = $2',
['Alice Updated', 1]
);
3.6 删除数据 #
typescript
await db.execute(
'DELETE FROM users WHERE id = $1',
[1]
);
四、数据库封装 #
4.1 数据库管理类 #
typescript
// db/database.ts
import Database from '@tauri-apps/plugin-sql';
export class DatabaseManager {
private db: Database | null = null;
private dbName: string;
constructor(dbName: string = 'app.db') {
this.dbName = dbName;
}
async init(): Promise<void> {
this.db = await Database.load(`sqlite:${this.dbName}`);
await this.createTables();
}
private async createTables(): Promise<void> {
await this.db!.execute(`
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT UNIQUE NOT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS posts (
id INTEGER PRIMARY KEY AUTOINCREMENT,
title TEXT NOT NULL,
content TEXT,
author_id INTEGER NOT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (author_id) REFERENCES users(id)
);
`);
}
async query<T>(sql: string, params: unknown[] = []): Promise<T[]> {
return this.db!.select<T[]>(sql, params);
}
async execute(sql: string, params: unknown[] = []): Promise<void> {
await this.db!.execute(sql, params);
}
async transaction<T>(callback: () => Promise<T>): Promise<T> {
await this.db!.execute('BEGIN TRANSACTION');
try {
const result = await callback();
await this.db!.execute('COMMIT');
return result;
} catch (error) {
await this.db!.execute('ROLLBACK');
throw error;
}
}
async close(): Promise<void> {
if (this.db) {
await this.db.close();
this.db = null;
}
}
}
export const db = new DatabaseManager();
4.2 Repository 模式 #
typescript
// db/repositories/userRepository.ts
import { db } from '../database';
export interface User {
id: number;
name: string;
email: string;
createdAt: string;
}
export class UserRepository {
static async findAll(): Promise<User[]> {
return db.query<User>('SELECT * FROM users ORDER BY created_at DESC');
}
static async findById(id: number): Promise<User | null> {
const users = await db.query<User>(
'SELECT * FROM users WHERE id = $1',
[id]
);
return users[0] || null;
}
static async findByEmail(email: string): Promise<User | null> {
const users = await db.query<User>(
'SELECT * FROM users WHERE email = $1',
[email]
);
return users[0] || null;
}
static async create(name: string, email: string): Promise<number> {
await db.execute(
'INSERT INTO users (name, email) VALUES ($1, $2)',
[name, email]
);
const result = await db.query<{ id: number }>(
'SELECT last_insert_rowid() as id'
);
return result[0].id;
}
static async update(id: number, data: Partial<User>): Promise<void> {
const fields: string[] = [];
const values: unknown[] = [];
if (data.name) {
fields.push('name = $' + (fields.length + 1));
values.push(data.name);
}
if (data.email) {
fields.push('email = $' + (fields.length + 1));
values.push(data.email);
}
if (fields.length > 0) {
values.push(id);
await db.execute(
`UPDATE users SET ${fields.join(', ')} WHERE id = $${fields.length + 1}`,
values
);
}
}
static async delete(id: number): Promise<void> {
await db.execute('DELETE FROM users WHERE id = $1', [id]);
}
}
五、事务处理 #
5.1 基本事务 #
typescript
await db.transaction(async () => {
// 创建用户
await db.execute(
'INSERT INTO users (name, email) VALUES ($1, $2)',
['Alice', 'alice@example.com']
);
// 创建关联文章
await db.execute(
'INSERT INTO posts (title, author_id) VALUES ($1, $2)',
['First Post', 1]
);
});
5.2 批量操作 #
typescript
async function batchInsert(users: { name: string; email: string }[]) {
await db.transaction(async () => {
for (const user of users) {
await db.execute(
'INSERT INTO users (name, email) VALUES ($1, $2)',
[user.name, user.email]
);
}
});
}
六、数据迁移 #
6.1 迁移系统 #
typescript
// db/migrations.ts
import { db } from './database';
interface Migration {
version: number;
name: string;
up: string;
down: string;
}
const migrations: Migration[] = [
{
version: 1,
name: 'create_users_table',
up: `
CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT UNIQUE NOT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
)
`,
down: 'DROP TABLE users',
},
{
version: 2,
name: 'add_user_avatar',
up: 'ALTER TABLE users ADD COLUMN avatar TEXT',
down: 'ALTER TABLE users DROP COLUMN avatar',
},
];
export async function runMigrations(): Promise<void> {
await db.execute(`
CREATE TABLE IF NOT EXISTS migrations (
version INTEGER PRIMARY KEY,
name TEXT NOT NULL,
applied_at DATETIME DEFAULT CURRENT_TIMESTAMP
)
`);
const applied = await db.query<{ version: number }>(
'SELECT version FROM migrations ORDER BY version'
);
const appliedVersions = new Set(applied.map((m) => m.version));
for (const migration of migrations) {
if (!appliedVersions.has(migration.version)) {
await db.transaction(async () => {
await db.execute(migration.up);
await db.execute(
'INSERT INTO migrations (version, name) VALUES ($1, $2)',
[migration.version, migration.name]
);
});
console.log(`Applied migration: ${migration.name}`);
}
}
}
七、Rust 后端数据库 #
7.1 使用 rusqlite #
toml
# Cargo.toml
[dependencies]
rusqlite = { version = "0.31", features = ["bundled"] }
rust
use rusqlite::{Connection, Result};
#[tauri::command]
fn get_users() -> Result<Vec<User>, String> {
let conn = Connection::open("app.db").map_err(|e| e.to_string())?;
let mut stmt = conn
.prepare("SELECT id, name, email FROM users")
.map_err(|e| e.to_string())?;
let users = stmt
.query_map([], |row| {
Ok(User {
id: row.get(0)?,
name: row.get(1)?,
email: row.get(2)?,
})
})
.map_err(|e| e.to_string())?
.collect::<Result<Vec<_>, _>>()
.map_err(|e| e.to_string())?;
Ok(users)
}
#[derive(serde::Serialize)]
struct User {
id: i32,
name: String,
email: String,
}
7.2 使用状态管理连接 #
rust
use std::sync::Mutex;
use rusqlite::Connection;
struct DbState {
conn: Mutex<Connection>,
}
#[tauri::command]
fn query_users(state: tauri::State<DbState>) -> Result<Vec<User>, String> {
let conn = state.conn.lock().unwrap();
let mut stmt = conn
.prepare("SELECT id, name, email FROM users")
.map_err(|e| e.to_string())?;
// ...
}
fn main() {
let conn = Connection::open("app.db").expect("Failed to open database");
tauri::Builder::default()
.manage(DbState {
conn: Mutex::new(conn),
})
.run(tauri::generate_context!())
.expect("error while running tauri application");
}
八、最佳实践 #
8.1 索引优化 #
sql
-- 创建索引
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_posts_author ON posts(author_id);
-- 复合索引
CREATE INDEX idx_posts_author_date ON posts(author_id, created_at);
8.2 查询优化 #
typescript
// 使用 LIMIT 限制结果
const recentPosts = await db.query<Post>(
'SELECT * FROM posts ORDER BY created_at DESC LIMIT 10'
);
// 只选择需要的字段
const userEmails = await db.query<{ email: string }>(
'SELECT email FROM users'
);
8.3 数据备份 #
typescript
import { copyFile } from '@tauri-apps/plugin-fs';
import { appDataDir, join } from '@tauri-apps/api/path';
async function backupDatabase(): Promise<string> {
const dbPath = await join(await appDataDir(), 'app.db');
const backupPath = await join(
await appDataDir(),
`backups/app_${Date.now()}.db`
);
await copyFile(dbPath, backupPath);
return backupPath;
}
九、总结 #
9.1 核心要点 #
| 要点 | 说明 |
|---|---|
| 插件使用 | tauri-plugin-sql |
| 数据库选择 | SQLite 适合本地存储 |
| Repository | 封装数据访问逻辑 |
| 事务处理 | 确保数据一致性 |
| 数据迁移 | 版本化管理数据库 |
9.2 下一步 #
现在你已经掌握了数据库集成，接下来让我们学习 数据持久化，了解数据持久化的最佳实践！
最后更新：2026-03-28