SQL

API Reference

插件为前端提供了一个接口，以便通过 sqlx 与 SQL 数据库进行通信。它支持 SQLite、MySQL 和 PostgreSQL 驱动程序，由 Cargo 功能启用。

¥Plugin providing an interface for the frontend to communicate with SQL databases through sqlx. It supports the SQLite, MySQL and PostgreSQL drivers, enabled by a Cargo feature.

支持的平台

¥Supported Platforms

This plugin requires a Rust version of at least 1.77.2

Platform	Level	Notes
windows
linux
macos
android
ios

设置

¥Setup

安装 SQL 插件以开始使用。

¥Install the SQL plugin to get started.

Automatic
Manual

使用项目的包管理器添加依赖：

¥Use your project’s package manager to add the dependency:

npm run tauri add sql

yarn run tauri add sql

pnpm tauri add sql

deno task tauri add sql

bun tauri add sql

cargo tauri add sql

Run the following command in the src-tauri folder to add the plugin to the project’s dependencies in Cargo.toml:
```
cargo add tauri-plugin-sql
```

Modify lib.rs to initialize the plugin:

#[cfg_attr(mobile, tauri::mobile_entry_point)]
    pub fn run() {
    tauri::Builder::default()
        .plugin(tauri_plugin_sql::Builder::default().build())
        .run(tauri::generate_context!())
        .expect("error while running tauri application");
}

Install the JavaScript Guest bindings using your preferred JavaScript package manager:

npm install @tauri-apps/plugin-sql

yarn add @tauri-apps/plugin-sql

pnpm add @tauri-apps/plugin-sql

deno add npm:@tauri-apps/plugin-sql

bun add @tauri-apps/plugin-sql

安装插件后，必须选择支持的数据库引擎。可用的引擎是 Sqlite、MySQL 和 PostgreSQL。在 src-tauri 文件夹中运行以下命令以启用你首选的引擎：

¥After installing the plugin, you must select the supported database engine. The available engines are Sqlite, MySQL and PostgreSQL. Run the following command in the src-tauri folder to enable your preferred engine:

cargo add tauri-plugin-sql --features sqlite

cargo add tauri-plugin-sql --features mysql

cargo add tauri-plugin-sql --features postgres

使用

¥Usage

所有插件的 API 都可通过 JavaScript 来宾绑定获得：

¥All the plugin’s APIs are available through the JavaScript guest bindings:

路径相对于 tauri::api::path::BaseDirectory::AppConfig。

¥The path is relative to tauri::api::path::BaseDirectory::AppConfig.

import Database from '@tauri-apps/plugin-sql';
// when using `"withGlobalTauri": true`, you may use
// const Database = window.__TAURI__.sql;

const db = await Database.load('sqlite:test.db');
await db.execute('INSERT INTO ...');

import Database from '@tauri-apps/plugin-sql';
// when using `"withGlobalTauri": true`, you may use
// const Database = window.__TAURI__.sql;

const db = await Database.load('mysql://user:password@host/test');
await db.execute('INSERT INTO ...');

import Database from '@tauri-apps/plugin-sql';
// when using `"withGlobalTauri": true`, you may use
// const Database = window.__TAURI__.sql;

const db = await Database.load('postgres://user:password@host/test');
await db.execute('INSERT INTO ...');

语法

¥Syntax

我们使用 sqlx 作为底层库并采用其查询语法。

¥We use sqlx as the underlying library and adopt their query syntax.

替换查询数据时使用 ”$#” 语法

¥Use the ”$#” syntax when substituting query data

const result = await db.execute(
  "INSERT into todos (id, title, status) VALUES ($1, $2, $3)",
  [todos.id, todos.title, todos.status],
);

const result = await db.execute(
"UPDATE todos SET title = $1, status = $2 WHERE id = $3",
[todos.title, todos.status, todos.id],
);

替换查询数据时使用 ”?”

¥Use ”?” when substituting query data

const result = await db.execute(
  "INSERT into todos (id, title, status) VALUES (?, ?, ?)",
  [todos.id, todos.title, todos.status],
);

const result = await db.execute(
  "UPDATE todos SET title = ?, status = ? WHERE id = ?",
  [todos.title, todos.status, todos.id],
);

替换查询数据时使用 ”$#” 语法

¥Use the ”$#” syntax when substituting query data

const result = await db.execute(
  "INSERT into todos (id, title, status) VALUES ($1, $2, $3)",
  [todos.id, todos.title, todos.status],
);

const result = await db.execute(
"UPDATE todos SET title = $1, status = $2 WHERE id = $3",
[todos.title, todos.status, todos.id],
);

迁移

¥Migrations

此插件支持数据库迁移，允许你管理数据库架构随时间的变化。

¥This plugin supports database migrations, allowing you to manage database schema evolution over time.

定义迁移

¥Defining Migrations

迁移在 Rust 中使用 Migration 结构定义。每个迁移都应包含一个唯一的版本号、一个描述、要执行的 SQL 以及迁移类型（Up 或 Down）。

¥Migrations are defined in Rust using the Migration struct. Each migration should include a unique version number, a description, the SQL to be executed, and the type of migration (Up or Down).

迁移示例：

¥Example of a migration:

use tauri_plugin_sql::{Migration, MigrationKind};

let migration = Migration {
    version: 1,
    description: "create_initial_tables",
    sql: "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);",
    kind: MigrationKind::Up,
};

添加迁移到插件生成器

¥Adding Migrations to the Plugin Builder

迁移使用插件提供的 Builder 结构注册。使用 add_migrations 方法将迁移添加到特定数据库连接的插件中。

¥Migrations are registered with the Builder struct provided by the plugin. Use the add_migrations method to add your migrations to the plugin for a specific database connection.

添加迁移示例：

¥Example of adding migrations:

use tauri_plugin_sql::{Builder, Migration, MigrationKind};

fn main() {
    let migrations = vec![
        // Define your migrations here
        Migration {
            version: 1,
            description: "create_initial_tables",
            sql: "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);",
            kind: MigrationKind::Up,
        }
    ];

    tauri::Builder::default()
        .plugin(
            tauri_plugin_sql::Builder::default()
                .add_migrations("sqlite:mydatabase.db", migrations)
                .build(),
        )
        ...
}

应用迁移

¥Applying Migrations

要在初始化插件时应用迁移，请将连接字符串添加到 tauri.conf.json 文件：

¥To apply the migrations when the plugin is initialized, add the connection string to the tauri.conf.json file:

{
  "plugins": {
    "sql": {
      "preload": ["sqlite:mydatabase.db"]
    }
  }
}

或者，客户端 load() 还会针对给定的连接字符串运行迁移：

¥Alternatively, the client side load() also runs the migrations for a given connection string:

import Database from '@tauri-apps/plugin-sql';
const db = await Database.load('sqlite:mydatabase.db');

确保迁移以正确的顺序定义，并且可以安全地多次运行。

¥Ensure that the migrations are defined in the correct order and are safe to run multiple times.

迁移管理

¥Migration Management

版本控制：每个迁移都必须有一个唯一的版本号。这对于确保以正确的顺序应用迁移至关重要。

¥Version Control: Each migration must have a unique version number. This is crucial for ensuring the migrations are applied in the correct order.
幂等性：以可以安全地重新运行而不会导致错误或意外后果的方式编写迁移。

¥Idempotency: Write migrations in a way that they can be safely re-run without causing errors or unintended consequences.
测试：彻底测试迁移以确保它们按预期工作并且不会损害数据库的完整性。

¥Testing: Thoroughly test migrations to ensure they work as expected and do not compromise the integrity of your database.

权限

¥Permissions

默认情况下，所有潜在危险的插件命令和范围都会被阻止，无法访问。你必须修改 capabilities 配置中的权限才能启用这些权限。

¥By default all potentially dangerous plugin commands and scopes are blocked and cannot be accessed. You must modify the permissions in your capabilities configuration to enable these.

有关更详细的说明，请参阅功能概述。

¥See the Capabilities Overview for more information and the step by step guide to use plugin permissions.

{
  "permissions": [
    ...,
    "sql:default",
    "sql:allow-execute",
  ]
}

Default Permission

Default Permissions

This permission set configures what kind of database operations are available from the sql plugin.

Granted Permissions

All reading related operations are enabled. Also allows to load or close a connection.

allow-close
allow-load
allow-select

Permission Table

Identifier	Description
`sql:allow-close`	Enables the close command without any pre-configured scope.
`sql:deny-close`	Denies the close command without any pre-configured scope.
`sql:allow-execute`	Enables the execute command without any pre-configured scope.
`sql:deny-execute`	Denies the execute command without any pre-configured scope.
`sql:allow-load`	Enables the load command without any pre-configured scope.
`sql:deny-load`	Denies the load command without any pre-configured scope.
`sql:allow-select`	Enables the select command without any pre-configured scope.
`sql:deny-select`	Denies the select command without any pre-configured scope.