SQLite Queries

SQLite Queries

Certified

Execute multiple SQL statements against SQLite

Executes multiple SQL statements sequentially against a SQLite database, optionally within a single transaction.

The database can be:

  • reused from a previous task via sqliteFile,
  • referenced directly through the JDBC URL,
  • or created and persisted when outputDbFile is enabled.

When outputDbFile is set to true, the database used during execution is uploaded to Kestra internal storage and exposed as outputs.<taskId>.databaseUri, enabling database reuse across tasks.

yaml
type: io.kestra.plugin.jdbc.sqlite.Queries

Execute multiple queries, using existing SQLite file, and pass the results to another task.

yaml
id: sqlite_query_using_file
namespace: company.team

tasks:
  - id: init_db
    type: io.kestra.plugin.jdbc.sqlite.Queries
    url: jdbc:sqlite:myfile.db
    outputDbFile: true
    sql: |
      CREATE TABLE IF NOT EXISTS pgsql_types (
        play_time TEXT,
        concert_id INTEGER,
        timestamp_type TEXT
      );
      INSERT INTO pgsql_types (play_time, concert_id, timestamp_type) VALUES ('2024-01', 1, '2024-01-01T12:00:00');

  - id: select
    type: io.kestra.plugin.jdbc.sqlite.Queries
    url: jdbc:sqlite:myfile.db
    sqliteFile: "{{ outputs.init_db.databaseUri }}"
    outputDbFile: true
    sql: SELECT * FROM pgsql_types
    fetchType: FETCH

  - id: use_fetched_data
    type: io.kestra.plugin.jdbc.sqlite.Queries
    url: jdbc:sqlite:myfile.db
    sqliteFile: "{{ outputs.select.databaseUri }}"
    sql: |
        CREATE TABLE IF NOT EXISTS pl_store_distribute (
          year_month TEXT,
          store_code INTEGER,
          update_date TEXT
        );
        {% for row in outputs.select.outputs[0].rows %}
            INSERT INTO pl_store_distribute (year_month, store_code, update_date)
            VALUES ('{{row.play_time}}', {{row.concert_id}}, '{{row.timestamp_type}}');
        {% endfor %}
Properties

SQL statement(s) to execute

Runs one or more SQL statements rendered with flow variables. Query tasks accept a single statement; Queries tasks can execute multiple statements separated by semicolons

SQL to execute after main query in same transaction

Optional SQL executed in the same transaction after the main statement. Useful for marking rows as processed to avoid duplicates; only a single statement is allowed. Commit covers both sql and afterSQL

Assets this task consumes as inputs or produces as outputs, for lineage tracking and the asset graph (Enterprise Edition). A flow declaring this property on a task is rejected in the open-source edition.

Definitions
assetFailureBehaviorstring
Possible Values
IGNOREFAILWARN

Asset failure behavior

Behavior applied to the task state when a declared asset fails to render, emit, or be persisted (e.g. a lock conflict): FAIL escalates it to FAILED, WARN (default) warns it if it would otherwise succeed, IGNORE leaves the state untouched.

enableAutobooleanstring

Whether to auto-register assets referenced dynamically at runtime that are not statically declared in inputs or outputs.

inputsarray

The assets consumed as inputs.

id*string
Min length1
typestring
outputs

The assets produced as outputs.

id*string
Min length1
Max length150
type*object
descriptionstring
displayNamestring
metadataobject
Default{}
namespacestring
Min length1
Max length150
id*string
Min length1
Max length150
type*object
descriptionstring
displayNamestring
metadataobject
Default{}
namespacestring
Min length1
Max length150
id*string
Min length1
Max length150
type*object
descriptionstring
displayNamestring
metadataobject
Default{}
namespacestring
Min length1
Max length150
id*string
Min length1
Max length150
type*object
descriptionstring
displayNamestring
metadataobject
Default{}
namespacestring
Min length1
Max length150
id*string
Min length1
Max length150
type*object
descriptionstring
displayNamestring
metadataobject
Default{}
namespacestring
Min length1
Max length150
id*string
Min length1
Max length150
type*string
Min length1

Custom asset type

descriptionstring
displayNamestring
metadataobject
Default{}
namespacestring
Min length1
Max length150
Default10000

Number of rows to fetch per database round trip

Controls JDBC fetch size for STORE mode. Default: 10,000 rows; use Integer.MIN_VALUE for MySQL streaming. Ignored for FETCH and FETCH_ONE

DefaultNONE
Possible Values
STOREFETCHFETCH_ONENONE

Result fetching mode

FETCH returns all rows, FETCH_ONE returns the first row only, STORE streams rows to internal storage (ION), NONE returns no data. Default: NONE

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.

SubTypestring

Output file names to capture after SQL execution

Creates named temporary files in the task working directory before the SQL runs, making their absolute paths available as {{ outputFiles.name }} Pebble variables in the SQL template. Only supported by embedded, in-process drivers (DuckDB, SQLite) where the database engine writes to the same filesystem as the Kestra worker. Remote database drivers (Postgres, MySQL, etc.) do not support this — they execute SQL on a separate server that cannot write to the Kestra worker filesystem.

Named parameter bindings for SQL query

Map of parameter names to values. Use : name placeholders rendered then bound as prepared-statement parameters; supports nulls and typed values

The database user's password

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.

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

Defaulttrue

Transaction

If one query failed, rollback transactions.

Defaultjdbc:sqlite:

The JDBC URL to connect to the database

Example: jdbc: sqlite: mydb.sqlite

The database user

SubTypeinteger

The number of affected rows for each query that doesn't return a result set

One entry per INSERT, UPDATE, DELETE or DDL statement, in execution order, as reported by the JDBC driver (DDL statements usually report 0). Queries returning a result set are not included here; their results are in outputs.

The list of per-query outputs

Definitions
rowobject

First row of fetched data

Only populated when fetchType is FETCH_ONE

rowsarray
SubTypeobject

List of all fetched rows

Only populated when fetchType is FETCH

sizeinteger

Number of rows fetched

Only populated when fetchType is FETCH or STORE

uristring
Formaturi

URI of stored results in internal storage

Only populated when fetchType is STORE; file is stored in internal storage using ION format

Unitrows

The number of fetched rows.

More Plugins in this Category

Ashby
Ashby

Ashby plugin for Kestra

Data

1 tasks

1 blueprints

Dremio
Dremio

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

Data

3 tasks

3 blueprints