query
Query a SQLite database.

Query a SQLite database.

Executes a single SQL query against a SQLite database.

The database can be:

referenced directly via the JDBC URL,
loaded from an existing SQLite file using sqliteFile,
or created on the fly when outputDbFile is enabled.

When outputDbFile is set to true, the database file effectively used during execution is persisted to Kestra internal storage and exposed as outputs.<taskId>.databaseUri, allowing it to be reused by subsequent tasks.

yaml
type: "io.kestra.plugin.jdbc.sqlite.query"

Examples

Execute a query and pass the results to another task.

yaml
id: sqlite_query
namespace: company.team

tasks:
  - id: create_table
    type: io.kestra.plugin.jdbc.sqlite.Query
    url: jdbc:sqlite:myfile.db
    outputDbFile: true
    sql: |
      CREATE TABLE IF NOT EXISTS pgsql_types (
          concert_id INTEGER,
          available INTEGER,
          a TEXT,
          b TEXT,
          c TEXT,
          d TEXT,
          play_time TEXT,
          library_record TEXT,
          floatn_test REAL,
          double_test REAL,
          real_test REAL,
          numeric_test NUMERIC,
          date_type DATE,
          time_type TIME,
          timez_type DATETIME,
          timestamp_type DATETIME,
          timestampz_type DATETIME,
          interval_type TEXT,
          pay_by_quarter TEXT,
          schedule TEXT,
          json_type TEXT,
          blob_type BLOB
      );
    fetchType: NONE

  - id: select
    type: io.kestra.plugin.jdbc.sqlite.Query
    url: jdbc:sqlite:myfile.db
    sqliteFile: "{{ outputs.create_table.databaseUri }}"
    outputDbFile: true
    sql: |
      SELECT concert_id, available, a, b, c, d, play_time, library_record, floatn_test, double_test, real_test, numeric_test, date_type, time_type, timez_type, timestamp_type, timestampz_type, interval_type, pay_by_quarter, schedule, json_type, blob_type FROM pgsql_types;
    fetchType: FETCH

  - id: iterate_and_insert
    type: io.kestra.plugin.core.flow.ForEach
    values: "{{ outputs.select.rows }}"
    tasks:
      - id: insert_row
        type: io.kestra.plugin.jdbc.sqlite.Query
        url: jdbc:sqlite:myfile.db
        sqliteFile: "{{ outputs.select.databaseUri }}"
        sql: |
          INSERT INTO pl_store_distribute (year_month, store_code, update_date)
          VALUES ('{{ taskrun.value.play_time }}', {{ taskrun.value.concert_id }}, '{{ taskrun.value.timestamp_type }}');
        fetchType: NONE

Properties

sql*string

SQL statement(s) to execute.

Runs one or more SQL statements depending on the task type. Query tasks support a single SQL statement, while Queries tasks can run multiple statements separated by semicolons.

afterSQLstring

SQL to execute atomically after trigger query.

Optional SQL executed in the same transaction as the main trigger query. Typically updates processing flags to prevent duplicate processing. Both sql and afterSQL queries commit together, ensuring consistency.

fetchSizeintegerstring

Default10000

Number of rows that should be fetched.

Gives the JDBC driver a hint as to the number of rows that should be fetched from the database when more rows are needed for this ResultSet object. If the fetch size specified is zero, the JDBC driver ignores the value and is free to make its own best guess as to what the fetch size should be. Ignored if autoCommit is false.

fetchTypestring

DefaultNONE

Possible Values

STOREFETCHFETCH_ONENONE

The way you want to store data.

FETCH_ONE - output the first row. FETCH - output all rows as output variable. STORE - store all rows to a file. NONE - do nothing.

outputDbFilebooleanstring

Defaultfalse

Output the SQLite database file

When set to true, the SQLite database file used during execution is uploaded to Kestra internal storage and exposed as outputs.<taskId>.databaseUri.

parametersobject

Parameters

A map of parameters to bind to the SQL queries. The keys should match the parameter placeholders in the SQL string, e.g., : parameterName.

passwordstring

The database user's password.

sqliteFilestring

SQLite database file (optional)

Optional URI to an existing SQLite database file stored in Kestra internal storage.

When provided, the file is downloaded into the task working directory and used as the SQLite database for the query execution.

timeZoneIdstring

The time zone id to use for date/time manipulation. Default value is the worker's default time zone id.

urlstring

Defaultjdbc:sqlite:

The JDBC URL to connect to the database.

Example: jdbc: sqlite: mydb.sqlite

usernamestring

The database user.

Outputs

rowobject

Map containing the first row of fetched data.

Only populated if fetchOne parameter is set to true.

rowsarray

SubTypeobject

List of map containing rows of fetched data.

Only populated if fetch parameter is set to true.

sizeinteger

The number of rows fetched.

Only populated if store or fetch parameter is set to true.

uristring

Formaturi

The URI of the result file on Kestra's internal storage (.ion file / Amazon Ion formatted text file).

Only populated if store is set to true.

Metrics

fetch.sizecounter

Unitrows

The number of fetched rows.

Snowflake

Tasks that connect to Snowflake via JDBC to run queries and loads.

Database

Opensearch

Enterprise

This sub-group of plugins contains tasks for using Opensearch EE. Opensearch is a distributed, RESTful search and analytics engine capable of addressing a growing number of use cases.

Database