Tools: SQLite Query

The sqlite tool gives your LLM the ability to query SQLite databases directly. This is a powerful way to provide access to structured data, allowing the LLM to perform data analysis, answer questions from a knowledge base, or check the state of an application.

NoteWhy not just use exec: sqlite3?

You could give the LLM a shell command like exec: sqlite3 ./data.db, but the built-in sqlite tool does more:

  • Schema introspection: Lectic reads the database schema and includes it in the tool description, so the LLM knows what tables and columns exist without you having to explain them.
  • Result limiting: Large query results can overwhelm the context window. The limit parameter caps response size and returns an error if exceeded, prompting the LLM to write a more selective query.
  • YAML output: Results are formatted as YAML, which LLMs tend to parse more reliably than raw SQL output.
  • Atomic transactions: Each tool call runs in a transaction. If anything fails, changes are rolled back.
  • No external binary: Lectic uses Bun’s built-in SQLite support, so you don’t need sqlite3 installed.

Configuration

The snippets below show only the tool definition. They assume you have an interlocutor with a valid prompt and model configuration. See Getting Started for a full header example.

To configure the tool, you must provide the path to the SQLite database file. The database schema is automatically introspected and provided to the LLM, so it knows what tables and columns are available.

tools:
  - sqlite: ./products.db
    name: db_query
    limit: 10000
    readonly: true
    details: >
      Contains the full product catalog and inventory levels. Use this to
      answer questions about what is in stock.
    init_sql: file:local:./schema.sql
    extensions:
      - ./lib/vector0
      - ./lib/math

The path can include environment variables (for example, $DATA_DIR/main.db), which Lectic expands.

Configuration parameters

  • sqlite: (Required) Path to the SQLite database file.
  • name: A custom tool name.
  • readonly: To set the database as read only.
  • limit: Maximum size of the serialized response in bytes. Large results raise an error instead of flooding the model.
  • details: Extra high‑level context for the model. String, file:, or exec: are accepted. See External Prompts.
  • extensions: A list of SQLite extension libraries to load before queries.
  • init_sql: Optional SQL script used to initialize a missing database file. Supports plain text, file:, or exec: sources.

Example conversation

Configuration:

tools:
  - sqlite: ./chinook.db
    name: chinook

Conversation:

Who are the top 5 artists by number of tracks?

:::Assistant

I will query the database to find out.

<tool-call with="chinook">
<arguments>
<query>
┆SELECT
┆ar.Name,
┆COUNT(t.TrackId) AS TrackCount
┆FROM Artists ar
┆JOIN Albums al ON ar.ArtistId = al.ArtistId
┆JOIN Tracks t ON al.AlbumId = t.AlbumId
┆GROUP BY ar.Name
┆ORDER BY TrackCount DESC
┆LIMIT 5;
</query>
</arguments>
<results>
<result type="text">
┆- Name: Iron Maiden
┆TrackCount: 213
┆- Name: Led Zeppelin
┆TrackCount: 114
┆- Name: Metallica
┆TrackCount: 112
┆- Name: U2
┆TrackCount: 110
┆- Name: Deep Purple
┆TrackCount: 92
</result>
</results>
</tool-call>

Based on the data, the top artists by track count are Iron Maiden, Led
Zeppelin, Metallica, U2, and Deep Purple.

:::

Database initialization (init_sql)

If init_sql is set and the SQLite file does not exist yet, Lectic runs that SQL script once at tool initialization. This is useful for shipping a plugin with a predefined schema.

Example:

tools:
  - sqlite: $LECTIC_DATA/my_plugin.sqlite
    init_sql: file:local:./schema.sql

Notes:

  • Initialization runs in a transaction.
  • If initialization fails, tool setup fails and the database changes are rolled back.
  • If readonly: true and the database file is missing, initialization is not possible and Lectic reports an error.

Writes and transactions

Writes are allowed by default. Each tool call runs inside a transaction and is atomic. If any statement in the call fails, Lectic rolls back the entire call, so the database is unchanged.

Limits and large results

The limit parameter caps the size of the serialized YAML that Lectic returns. If a result exceeds the cap, the tool raises an error. Tighten your query (for example, add LIMIT or select fewer columns) to stay under the cap.

Extensions

You can load extensions by path before queries run. On macOS, note that the system SQLite build may restrict loading extensions. Consult the Bun SQLite extension documentation if you hit issues.