Executing Queries

The Tauri SQL plugin provides two JavaScript methods for sending SQL to your database: execute for statements that modify data or structure, and select for queries that return rows. Both methods live on a Database instance you already opened via Database.load(). If you still need a handle, start with Database Connection.

Required Permissions

Before any query runs, the frontend must be allowed to call the underlying commands. Add at least these permissions to your capability file:

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

The sql:default set grants read-only operations and connection management. sql:allow-execute and sql:allow-select are required to run INSERT/UPDATE/DELETE and SELECT statements respectively. Without them the plugin will reject the calls.

The execute Method

execute runs any SQL statement that does not return rows: INSERT, UPDATE, DELETE, CREATE TABLE, DROP, and similar DDL. It returns a QueryResult with two fields:

• rowsAffected: number – how many rows were inserted, updated, or deleted.
• lastInsertId?: number – the ID of the last inserted row, if the database and driver support it. This is not set for PostgreSQL; use RETURNING id with select instead.

The method signature looks like this:

const result: QueryResult = await db.execute(query, bindValues?);

bindValues is an optional array of values that replace placeholders in the query string. Always use it – never concatenate user input directly into SQL.

Placeholder Syntax

The placeholder character depends on the database engine. SQLite and PostgreSQL use $1, $2, etc. MySQL uses ?.

await db.execute(
  "INSERT INTO todos (id, title, status) VALUES ($1, $2, $3)",
  [1, "Learn Tauri", "pending"]
);

Mixing the wrong placeholder style with your database will throw a runtime error.

INSERT Example

Assume a React component that adds a new todo when a button is clicked. The database was already loaded in a parent and passed via props or a hook.

import { useState } from "react";
import Database from "@tauri-apps/plugin-sql";
interface AddTodoProps {
  db: Database;
}
export default function AddTodo({ db }: AddTodoProps) {
  const [title, setTitle] = useState("");
  const handleAdd = async () => {
    try {
      const result = await db.execute(
        "INSERT INTO todos (title, status) VALUES ($1, 'pending')",
        [title]
      );
      console.log("Rows inserted:", result.rowsAffected);
      setTitle("");
    } catch (err) {
      console.error("Insert failed:", err);
    }
  };
  return (
    <div>
      <input
        type="text"
        value={title}
        onChange={(e) => setTitle(e.target.value)}
        placeholder="Todo title"
      />
      <button onClick={handleAdd}>Add</button>
    </div>
  );
}

The execute call uses $1 (assuming SQLite). On success, result.rowsAffected will be 1. The lastInsertId field would contain the generated id if your table uses an auto‑increment column – useful for further operations without an extra query.

UPDATE Example

An update changes existing rows. The number of affected rows tells you whether the targeted record existed.

import Database from "@tauri-apps/plugin-sql";
interface UpdateTodoProps {
  db: Database;
  id: number;
  newTitle: string;
}
export default function UpdateTodo({ db, id, newTitle }: UpdateTodoProps) {
  const handleUpdate = async () => {
    const result = await db.execute(
      "UPDATE todos SET title = $1 WHERE id = $2",
      [newTitle, id]
    );
    if (result.rowsAffected === 0) {
      console.warn("No todo found with id", id);
    }
  };
  return <button onClick={handleUpdate}>Save</button>;
}

Note the check for rowsAffected === 0. That's how you detect that the WHERE clause matched nothing – a common pattern to confirm the record exists before expecting side effects.

DELETE Example

Deleting rows follows the same pattern. Always bind identifiers to avoid accidental full‑table deletions.

async function deleteTodo(db: Database, id: number) {
  const result = await db.execute("DELETE FROM todos WHERE id = $1", [id]);
  console.log(`Removed ${result.rowsAffected} todo(s)`);
}

The select Method

For SELECT queries that return data, use select. It accepts the same (query, bindValues?) arguments and returns a Promise<T>, where T is an array of rows. Each row is a plain object whose keys match the column names.

const rows = await db.select("SELECT * FROM todos");
// rows is typed as unknown; cast or use a generic to get proper types

You can help TypeScript understand the shape by passing a generic:

interface Todo {
  id: number;
  title: string;
  status: string;
}
const todos = await db.select<Todo[]>("SELECT * FROM todos");
// todos is now Todo[]

The generic is only for type‑checking; the plugin still returns whatever the database sends.

Basic SELECT

import { useEffect, useState } from "react";
import Database from "@tauri-apps/plugin-sql";
interface Todo {
  id: number;
  title: string;
  status: string;
}
export default function TodoList({ db }: { db: Database }) {
  const [todos, setTodos] = useState<Todo[]>([]);
  const [loading, setLoading] = useState(true);
  useEffect(() => {
    const load = async () => {
      try {
        const rows = await db.select<Todo[]>("SELECT * FROM todos");
        setTodos(rows);
      } catch (err) {
        console.error("Failed to fetch todos:", err);
      } finally {
        setLoading(false);
      }
    };
    load();
  }, [db]);
  if (loading) return <p>Loading…</p>;
  return (
    <ul>
      {todos.map((todo) => (
        <li key={todo.id}>{todo.title} — {todo.status}</li>
      ))}
    </ul>
  );
}

The useEffect runs once when the component mounts (and whenever db changes). It calls select, sets the result into state, and lets React render the list.

SELECT with Parameters

Filtering and sorting use bound parameters just like execute.

const activeTodos = await db.select<Todo[]>(
  "SELECT * FROM todos WHERE status = $1 ORDER BY id DESC",
  ["pending"]
);

Here $1 gets replaced by "pending". The plugin maps each $N to the corresponding element in the array. For MySQL, replace $1 with ?.

const rows = await db.select<Todo[]>(
  "INSERT INTO todos (title, status) VALUES ($1, $2) RETURNING *",
  ["Read docs", "done"]
);
const newTodo = rows[0]; // contains id, title, status

A Complete CRUD Workflow

The following stepper walks through a realistic sequence: create a table, insert a row, query it, update it, and delete it – all from the frontend. This assumes you already have a loaded Database instance (e.g., const db = await Database.load("sqlite:test.db")).

await db.execute(
  `CREATE TABLE IF NOT EXISTS todos (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending'
  )`
);

const insertResult = await db.execute(
  "INSERT INTO todos (title, status) VALUES ($1, $2)",
  ["Buy groceries", "pending"]
);
console.log("Inserted ID:", insertResult.lastInsertId);

interface Todo { id: number; title: string; status: string; }
const todos = await db.select<Todo[]>("SELECT * FROM todos");
console.table(todos);

const updateResult = await db.execute(
  "UPDATE todos SET status = $1 WHERE id = $2",
  ["done", 1]  // assuming id = 1
);
console.log("Rows updated:", updateResult.rowsAffected);

const deleteResult = await db.execute(
  "DELETE FROM todos WHERE id = $1",
  [1]
);
console.log("Rows deleted:", deleteResult.rowsAffected);

Error Handling and Edge Cases

Both execute and select throw if the SQL is malformed, the database is locked, or the permission is missing. Always wrap calls in try/catch, especially for user‑facing mutations.

Common failure points:

• Mismatched number of bind values: You wrote three placeholders but supplied only two values. The error message will say something like “parameter index out of range.”
• Wrong database driver loaded: The connection string uses mysql:// but you compiled the plugin with the sqlite feature. You'll get a connection error.
• Empty result from select: Returns an empty array [], not null. Don't treat it as a missing row check – just check rows.length.
• Multiple statements: The underlying sqlx driver does not allow multiple SQL statements in one execute call (like INSERT; INSERT;). Execute them separately.

Best Practices

• Always bind values. Even for integers or booleans, pass them through the array. The plugin's parameter binding is the only reliable SQL injection defense.
• Keep database calls out of render. Call select inside useEffect or event handlers, never directly in the component body without a guard.
• Handle the unmount case. If the component unmounts before the promise resolves, state updates on an unmounted component will cause a React warning. Use an abort flag or a cleanup function.
• Use the same Database instance across components. Loading a new connection each time is wasteful. Pass the instance via React Context or props.
• Close when the app shuts down. Call db.close() when your app is about to exit (e.g., on a window close event) to release the connection pool cleanly. This is not strictly required but a good habit for resource management.
• Schema changes in code: The plugin does not provide DDL abstraction beyond raw execute. If you need migrations, prefer Rust‑side migration definitions as shown in SQL Plugin best practices, not ad‑hoc CREATE TABLE statements scattered in the frontend.

Permissions Troubleshooting

If queries silently fail or the plugin throws “command not allowed”, check two things:

1. The capability file includes sql:allow-execute and sql:allow-select.
2. For the sql:allow-execute permission, you are calling the frontend execute method, not using a custom Rust command that bypasses the plugin’s permission gate. The permissions apply to the JavaScript API; direct Rust calls from your own tauri::command functions are unrestricted.

Summary

Executing queries from a Tauri app boils down to choosing between execute and select depending on whether you need rows back, and then binding your values the way your database engine expects. The plugin handles the IPC, serialization, and connection management, so the frontend code stays simple: open a database, run parameterized SQL, and render the result.