Performance

SQLite chooses a plan for finding the rows each query needs. Without a useful index, it may have to examine many rows. An index can make filters and joins faster, but it takes storage and adds work to writes. EXPLAIN QUERY PLAN shows how SQLite intends to run a query, so inspect it before adding indexes blindly.

In NitroSQLite, start with the query and the amount of data it reads. Add indexes for columns you filter or join on, inspect query plans with SQLite's EXPLAIN QUERY PLAN, and select only the columns and rows your screen needs. Their effect depends on your schema and data.

Keep long work off the JavaScript thread

execute() and the other synchronous methods block their JavaScript caller. Use executeAsync(), executeBatchAsync(), and loadFileAsync() for work that may take longer. The native async implementations run database work on background threads. A connection runs native operations one at a time in submission order. See multiple connections when reads need a separate handle.

const { rows } = await db.executeAsync<{ id: number; title: string }>(
  'SELECT id, title FROM articles ORDER BY id DESC LIMIT ?',
  [50],
)
console.log(rows._array)

An async call still consumes device CPU and database I/O. Paginate large result sets instead of bringing every row into JavaScript at once.

Reuse SQL text

Before SQLite runs a statement, it compiles the SQL text into a program for its virtual machine. Compiling one statement is quick, but the cost adds up when the same statement runs many times, such as an INSERT in a loop.

Each connection keeps the compiled programs of its 32 most recently used queries and data changes: statements that start with SELECT, INSERT, UPDATE, DELETE, REPLACE, WITH, or VALUES. When execute() or executeAsync() receives the same SQL text again, it reuses the program with the new parameters. Pass values as parameters rather than formatting them into the SQL so the text stays identical between calls. Other statements, such as PRAGMA, ATTACH, and BEGIN, compile on every call because SQLite can apply their effect while compiling them. Use prepared statements when you want to manage a compiled statement's lifetime yourself.

for (const metric of metrics) {
  db.execute('INSERT INTO metrics (key, value) VALUES (?, ?)', [
    metric.key,
    metric.value,
  ])
}

Group related writes

One executeBatchAsync() call executes a fixed set of statements inside a native transaction. Use nested parameter arrays for repeated SQL. Use db.transaction() when application logic must inspect a result between writes. Both keep a transaction open while they work; keep the scope short and await each operation inside a transaction callback.

await db.executeBatchAsync([
  {
    query: 'INSERT INTO metrics (key, value) VALUES (?, ?)',
    params: [
      ['a', 1],
      ['b', 2],
    ],
  },
])

Binding values avoids building a new SQL string from user data. The batch implementation executes each expanded command separately. For repeated calls on one connection, prepared statements keep one SQL command compiled between executions. Measure on your target devices and data sizes before choosing a batch size, prepared statement, or index strategy.

See sync and async, batch operations, and transactions for the exact ordering rules.