Best Practices for the SQL Plugin

Using a database in a Tauri app is straightforward, but writing database code that survives real-world use requires a handful of deliberate habits. This guide covers the patterns that prevent data corruption, security holes, and confusing bugs. Every recommendation is grounded in how the SQL plugin actually works under the hood, with complete code examples you can drop into a project.

Always Use Parameterized Queries

SQL injection is not a theoretical threat in desktop apps. Any time a user types into a form, pastes from a clipboard, or imports a file, that data can contain SQL fragments that break or hijack your query. The defense is always the same: never concatenate user input into a SQL string.

The Tauri SQL plugin uses sqlx internally, which expects parameters passed as a separate array alongside the query. The placeholder syntax differs by database engine.

import Database from '@tauri-apps/plugin-sql';
const db = await Database.load('sqlite:test.db');
// Correct: parameters are bound separately
await db.execute(
  'INSERT INTO users (name, email) VALUES ($1, $2)',
  ['Alice', 'alice@example.com']
);

The same pattern applies to select queries. When you need to fetch a user by ID, you write:

const rows = await db.select<{ id: number; name: string }[]>(
  'SELECT id, name FROM users WHERE id = $1',
  [userId]
);

sqlx sanitizes the values before they reach the database engine. The database never sees the parameter values as part of the SQL text — it sees placeholders, then the actual values are sent through a separate, safe channel. This means no amount of special characters in userId can break out of the string context.

The same parameterized approach works everywhere — execute, select, and any query you run through the plugin. There is no scenario where concatenation is acceptable.

Group Related Operations in Transactions

A transaction bundles multiple database operations into a single atomic unit. Either all of them succeed and are committed permanently, or none of them take effect. This prevents partial updates that leave the database in an inconsistent state.

The SQL plugin does not expose a separate beginTransaction / commit API on the JavaScript side, but you can manage transactions directly with SQL statements. The following example transfers points between two users: deduct from one and add to the other. If either step fails, the entire change is rolled back.

async function transferPoints(
  db: Database,
  fromUserId: number,
  toUserId: number,
  amount: number
): Promise<void> {
  await db.execute('BEGIN');
  try {
    const deduct = await db.execute(
      'UPDATE users SET points = points - $1 WHERE id = $2 AND points >= $1',
      [amount, fromUserId]
    );
    if (deduct.rowsAffected === 0) {
      throw new Error('Insufficient points or user not found');
    }
    await db.execute(
      'UPDATE users SET points = points + $1 WHERE id = $2',
      [amount, toUserId]
    );
    await db.execute('COMMIT');
  } catch (error) {
    await db.execute('ROLLBACK');
    throw error;
  }
}

The logic checks rowsAffected after the first update to confirm that the source user actually had enough points. If that check fails, the entire transaction is rolled back so no money disappears or appears from nowhere.

Transactions are especially important when you insert data into multiple related tables — for example, creating an order with multiple line items. If the order insert succeeds but one of the line item inserts fails, you want the entire order gone, not a broken orphan.

Handle Errors Gracefully

Database operations can fail for many reasons: syntax errors, constraint violations, network interruptions (for MySQL/PostgreSQL), missing tables, or permission denials. Every database call should be wrapped in error handling that provides clear feedback to the user and logs enough detail for debugging.

import Database from '@tauri-apps/plugin-sql';
async function safeInsertUser(
  db: Database,
  name: string,
  email: string
): Promise<{ success: boolean; message: string }> {
  try {
    const result = await db.execute(
      'INSERT INTO users (name, email) VALUES ($1, $2)',
      [name, email]
    );
    return {
      success: true,
      message: `User added. Rows affected: ${result.rowsAffected}`,
    };
  } catch (error) {
    console.error('Database insert failed:', error);
    return {
      success: false,
      message: 'Could not save user. Please try again.',
    };
  }
}

The frontend can then decide whether to show a toast notification or keep the form open. The key principle: the user never sees raw SQL error messages, but the developer sees a detailed stack trace in the console.

For select queries, note that an empty result set is not an error — the query simply resolves to an empty array. Distinguish between "no rows found" (expected, maybe show an empty state) and a genuine query failure (unexpected, needs error handling).

Common error types to anticipate:

• Unique constraint violation: inserting a duplicate value into a column with a UNIQUE constraint.
• Foreign key constraint failure: referencing a row that does not exist.
• Syntax error: malformed SQL (should be caught during development, not in production).
• Permission denied: the Tauri capability blocks the execute or select command.

Manage Connections Efficiently

The Database.load() function establishes a connection pool (backed by sqlx on the Rust side). Creating a new connection pool for every query is wasteful and adds latency. Instead, create the connection once when your app starts and reuse it.

// db.ts — shared module
import Database from '@tauri-apps/plugin-sql';
let db: Database | null = null;
export async function getDb(): Promise<Database> {
  if (!db) {
    db = await Database.load('sqlite:app.db');
  }
  return db;
}

Every component or service that needs the database imports getDb and calls it. The first call initializes the connection; subsequent calls return the already-initialized instance.

If you prefer lazy initialization — connecting only when the first query actually runs — use Database.get() instead:

const db = Database.get('sqlite:app.db');
// No connection is established yet.
// The first db.execute() or db.select() triggers the actual connection.

This is useful when the database is not needed on the very first screen of your app.

Close the database when your app is shutting down to allow the pool to release resources. Tauri does not automatically close connections on window close, so you should call db.close() in an appropriate cleanup hook:

import { onWindowUnload } from 'react';
function App() {
  useEffect(() => {
    return () => {
      // cleanup when component unmounts or app closes
      db?.close();
    };
  }, []);
}

Secure Your Permissions

Tauri v2 uses a capability-based permission system. By default, the SQL plugin cannot do anything — you must explicitly grant each operation your app needs. This is defined in src-tauri/capabilities/default.json (or any capability file you create).

{
  "identifier": "default",
  "description": "Capability for the main window",
  "windows": ["main"],
  "permissions": [
    "core:default",
    "sql:allow-load",
    "sql:allow-select",
    "sql:allow-execute"
  ]
}

Each permission enables exactly one command:

• sql:allow-load — required to open a database connection
• sql:allow-select — required to run SELECT queries
• sql:allow-execute — required to run INSERT, UPDATE, DELETE, or any other modifying statement
• sql:allow-close — required to explicitly close the connection pool

There is also a convenience permission sql:default, which bundles allow-load, allow-close, and allow-select but intentionally excludes allow-execute. You still need to add sql:allow-execute separately if your app modifies data. This is a good default because it forces you to consciously opt into mutation.

Use Migrations for Schema Evolution

Hardcoding CREATE TABLE statements in application code works for tiny projects, but it becomes unmanageable as soon as you need to add a column or change a constraint without losing existing data. Migrations version your database schema so that every change is recorded, applied in order, and safe to run multiple times.

The Tauri SQL plugin supports migrations defined in Rust and applied automatically when a connection is opened.

use tauri_plugin_sql::{Migration, MigrationKind};
let create_users = Migration {
    version: 1,
    description: "create users table",
    sql: "CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        email TEXT UNIQUE NOT NULL
    );",
    kind: MigrationKind::Up,
};

let create_users = Migration {
    version: 1,
    description: "create users table",
    sql: include_str!("../migrations/001_create_users.sql"),
    kind: MigrationKind::Up,
};

use tauri_plugin_sql::{Builder, Migration, MigrationKind};
#[cfg_attr(mobile, tauri::mobile_entry_point)]
pub fn run() {
    let migrations = vec![
        Migration {
            version: 1,
            description: "create users table",
            sql: "CREATE TABLE IF NOT EXISTS users (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                email TEXT UNIQUE NOT NULL
            );",
            kind: MigrationKind::Up,
        },
        Migration {
            version: 2,
            description: "add avatar column",
            sql: "ALTER TABLE users ADD COLUMN avatar TEXT;",
            kind: MigrationKind::Up,
        },
    ];
    tauri::Builder::default()
        .plugin(
            Builder::default()
                .add_migrations("sqlite:app.db", migrations)
                .build(),
        )
        .run(tauri::generate_context!())
        .expect("error while running tauri application");
}

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

Version numbers must be unique and strictly increasing. If two migrations share the same version, the plugin will error. If you need to roll back a change, add a new migration that undoes it — never edit an existing migration file after it has been applied in any environment.

Validate User Input Before Hitting the Database

Parameterized queries prevent SQL injection, but they do not validate the shape of the data. A name field that is 10,000 characters long or an email field that contains random gibberish should be caught before it reaches the database.

import { useState } from 'react';
import { getDb } from './db';
function AddUserForm() {
  const [name, setName] = useState('');
  const [email, setEmail] = useState('');
  const [error, setError] = useState('');
  const handleSubmit = async (e: React.FormEvent) => {
    e.preventDefault();
    setError('');
    // Frontend validation
    if (name.trim().length === 0 || name.length > 100) {
      setError('Name must be between 1 and 100 characters.');
      return;
    }
    if (!email.includes('@') || email.length > 255) {
      setError('Please enter a valid email address (max 255 characters).');
      return;
    }
    const db = await getDb();
    try {
      await db.execute(
        'INSERT INTO users (name, email) VALUES ($1, $2)',
        [name.trim(), email.trim().toLowerCase()]
      );
      setName('');
      setEmail('');
    } catch (err) {
      setError('Could not save user. Email might already be taken.');
    }
  };
  return (
    <form onSubmit={handleSubmit}>
      {error && <p className="error">{error}</p>}
      <input
        type="text"
        placeholder="Name"
        value={name}
        onChange={(e) => setName(e.target.value)}
      />
      <input
        type="email"
        placeholder="Email"
        value={email}
        onChange={(e) => setEmail(e.target.value)}
      />
      <button type="submit">Add User</button>
    </form>
  );
}

The validation happens in the React component before the database call. This catches obvious problems immediately and reduces unnecessary database errors. The UNIQUE constraint on the email column in the database serves as the last line of defense — if a duplicate slips through validation, the catch block handles it.

Performance Tips

• Batch inserts with a single transaction. Wrapping 100 INSERT statements in a BEGIN/COMMIT block is dramatically faster than issuing 100 individual transactions. Each individual commit writes to disk; a single transaction flushes once.
• Select only the columns you need. SELECT * is convenient during development but pulls unnecessary data over the IPC bridge and into the renderer. Specify columns explicitly in production code.
• Use WHERE clauses to narrow result sets. Fetching every row from a table and filtering in JavaScript defeats the purpose of a database. Let the SQL engine do the filtering.
• Avoid opening and closing connections repeatedly. The connection pool manages resources efficiently. Keep one connection alive for the lifetime of the app.

Common Pitfalls

• Using the wrong placeholder syntax for your database engine. SQLite and PostgreSQL use $1, $2; MySQL uses ?. Mixing these causes a runtime error. Always check which database you are targeting.
• Forgetting to enable permissions in capabilities. The error message is usually a vague "command not allowed." If a query that works in development suddenly breaks after a refactor, check capabilities/default.json first.
• Assuming rowsAffected is always defined. For PostgreSQL, lastInsertId is not set on the QueryResult. Use RETURNING id in your INSERT statement and a select call to get the generated ID instead.
• Running migrations that are not idempotent. Dropping and recreating a table in a migration will destroy data if that migration is applied more than once. Always use IF NOT EXISTS or ALTER TABLE add-column patterns.
• Leaving transactions open. A forgotten COMMIT or ROLLBACK after an exception leaves the database in a locked state. Always use try/catch/finally or an explicit rollback in the catch block.

Summary

All of these practices rest on a single foundation: the SQL plugin is a thin but faithful bridge to sqlx, and sqlx expects you to use parameterized queries, manage transactions explicitly, and respect permissions. Internalizing these patterns now means you will not have to untangle subtle data bugs later.

When you are ready to add network requests to your app — fetching data from an API, posting form submissions — the HTTP Plugin gives you a similarly safe, Rust-backed way to make HTTP calls from your frontend.