tauri-plugin-sql

To use this plugin, you must have a Rust version of at least 1.80. Installation involves two steps: adding the core plugin to your Rust backend and adding the JavaScript guest bindings to your frontend.

1. Install Core Plugin (Rust)

Add the plugin to your src-tauri/Cargo.toml. You must enable a driver feature (sqlite, postgres, or mysql) to use that specific database type.

2. Install JavaScript Guest Bindings

Since the plugin is part of a monorepo, use the provided mirrors to install via your package manager:

pnpm add https://github.com/tauri-apps/tauri-plugin-sql#v1
# or
npm add https://github.com/tauri-apps/tauri-plugin-sql#v1
# or
yarn add https://github.com/tauri-apps/tauri-plugin-sql#v1

[dependencies.tauri-plugin-sql]
git = "https://github.com/tauri-apps/plugins-workspace"
branch = "v1"
features = ["sqlite"] # or "postgres", or "mysql"

You must register the core plugin in your Tauri application's entry point (usually src-tauri/src/main.rs) using the tauri_plugin_sql::Builder.

fn main() {
    tauri::Builder::default()
        .plugin(tauri_plugin_sql::Builder::default().build())
        .run(tauri::generate_context!())
        .expect("error while running tauri application");
}

The PluginConfig allows you to specify a list of databases to be preloaded when the plugin is initialized. Preloading a database automatically handles directory creation (for SQLite), database creation if it doesn't exist, and running any registered migrations.

Use the preload key in your configuration to provide an array of database connection strings.

{
  "preload": ["sqlite:my_db.db", "postgres://user:pass@localhost/db"]
}

The plugin returns errors that can be serialized to strings. Common error scenarios include:

• Sql: Errors originating from the underlying database driver (e.g., syntax errors).
• Migration: Errors occurring during the application of migrations.
• DatabaseNotLoaded: Attempting to execute a command against a database that hasn't been loaded via load() or preload.
• UnsupportedDatatype: Encountering a data type that cannot be processed.

The plugin provides manual control over database connection pools.

• load(db): Manually initializes a connection pool for the specified db connection string. If migrations are registered for this string, they will be applied.
• close(db?): Closes connection pools. If a db string is provided, only that pool is closed. If no string is provided, all active connection pools are shut down.

You can define schema migrations in Rust and attach them to specific database connection strings using the Builder.

Each migration requires a version (i64), a description, the sql statement, and a kind (MigrationKind::Up or MigrationKind::Down).

Use add_migrations on the Builder to map a database URL to a list of Migration objects.

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

let builder = Builder::default().add_migrations(
    "sqlite:app.db",
    vec![
        Migration {
            version: 1,
            description: "create table",
            sql: "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);",
            kind: MigrationKind::Up,
        }
    ],
);

// Then use builder.build() in your Tauri setup

Use the static load method to asynchronously connect to an underlying database. This method returns a Database instance once the connection is established.

For SQLite, the path must start with sqlite: and is relative to the tauri::api::path::BaseDirectory::App directory.

const db = await Database.load("sqlite:test.db");

To interact with a database, you must first obtain a Database instance. You can use two different initialization patterns:

1. Database.load(path): An asynchronous static method that connects to the underlying database and returns a Database instance once the connection is established.
2. Database.get(path): A synchronous static method that returns a Database instance immediately, deferring the actual connection until the first query is executed.

Sqlite Path Requirements: For SQLite, the path must start with sqlite: and is relative to tauri::api::path::BaseDirectory::App.

Example:

// Asynchronous connection
const db = await Database.load("sqlite:test.db");

// Synchronous instance retrieval (connection deferred)
const db = Database.get("sqlite:test.db");

Use select<T>(query, bindValues?) to run SELECT queries. It returns a Promise<T>, where T is the expected shape of the resulting rows.

Binding Syntax by Driver:

• Sqlite & Postgres: Use $1, $2, etc.
• MySQL: Use ?.

Example (Sqlite/Postgres):

const result = await db.select(
  "SELECT * from todos WHERE id = $1", id
);

Example (MySQL):

const result = await db.select(
  "SELECT * from todos WHERE id = ?", id
);

The execute command is used for running SQL statements that modify data (INSERT, UPDATE, DELETE, CREATE, etc.).

Parameters:

• db: The database connection string identifier.
• query: The SQL string to execute.
• values: An array of JSON values to bind to the query placeholders.

Returns: A tuple containing rows_affected (u64) and last_insert_id (the type of which depends on the driver: i64 for SQLite, u64 for others, or 0 for Postgres).

// Example: Inserting a row
// SQL: INSERT INTO users (name) VALUES (?)
const [rowsAffected, lastId] = await execute(
  "sqlite:app.db",
  "INSERT INTO users (name) VALUES (?)",
  ["Alice"]
);