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

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

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

在 src-tauri 文件夹中运行以下命令，将插件添加到 Cargo.toml 中的项目依赖：
```
cargo add tauri-plugin-sql
```

修改 lib.rs 以初始化插件：

#[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");
}

使用你偏好的 JavaScript 包管理器安装 JavaScript Guest 绑定：

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.

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

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]
);

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

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]
);

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

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 以及迁移类型（向上或向下）。

🌐 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,
};

或者如果你想从文件中使用 SQL，你可以通过使用 include_str! 来包含它：

🌐 Or if you want to use SQL from a file, you can include it by using include_str!:

use tauri_plugin_sql::{Migration, MigrationKind};

let migration = Migration {
    version: 1,
    description: "create_initial_tables",
    sql: include_str!("../drizzle/0000_graceful_boomer.sql"),
    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

版本控制：每次迁移必须有一个唯一的版本号。这对于确保迁移按正确顺序应用至关重要。
幂等性：以一种可以安全地重新运行而不会导致错误或意外后果的方式编写迁移。
测试：彻底测试迁移，以确保它们按预期工作，并且不会损害数据库的完整性。

权限

🌐 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.

This default permission set includes the following:

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.