> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-parallel-read-in-order-multi-part.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

> The engine allows to import and export data to SQLite and supports queries to SQLite tables directly from ClickHouse.

# SQLite table engine

export const CloudNotSupportedBadge = () => {
  return <a href="https://clickhouse.com/docs/products/cloud/guides/cloud-compatibility#list-of-unsupported-features" className="cloudNotSupportedBadge">
            <div className="cloudNotSupportedIcon">
            <svg width="16" height="16" viewBox="0 0 16 16" fill="none" xmlns="http://www.w3.org/2000/svg">
                <path strokeWidth="1.5" d="M6.33366 12.6666L12.3739 12.6667C13.6593 12.6667 14.7073 11.6187 14.7073 10.3334C14.7073 9.04804 13.6593 8.00003 12.3739 8.00003C12.3739 8.00003 12.3337 7.66659 12.0003 7.33325M10.667 5.33322C8.00033 2.33325 4.45395 4.78537 4.14195 6.68203C2.55728 6.7627 1.29395 8.06203 1.29395 9.6667C1.29395 11.3234 2.66699 12.6666 4.00033 12.6666" stroke="currentColor" strokeLinecap="round" strokeLinejoin="round" />
                <path strokeWidth="1.5" d="M2.66699 14L12.0003 4.66663" stroke="currentColor" strokeLinecap="round" strokeLinejoin="round" />
            </svg>

        </div>
            Not supported in ClickHouse Cloud
        </a>;
};

<CloudNotSupportedBadge />

The engine allows to import and export data to SQLite and supports queries to SQLite tables directly from ClickHouse.

<h2 id="creating-a-table">
  Creating a table
</h2>

```sql theme={null}
    CREATE TABLE [IF NOT EXISTS] [db.]table_name
    (
        name1 [type1],
        name2 [type2], ...
    ) ENGINE = SQLite('db_path', 'table')
```

**Engine Parameters**

* `db_path` — Path to SQLite file with a database.
* `table` — Name of a table in the SQLite database, or a query passed to SQLite as is (see [Passing a query instead of a table name](#passing-a-query)).

<h2 id="passing-a-query">
  Passing a query instead of a table name
</h2>

Instead of a table name, the `table` argument can be a `SELECT` query that is passed to SQLite as is. The structure of the table is inferred from the query result. SQLite reports a declared type only for a result column that is a direct column of a table; for an expression, a literal or an aggregate it reports nothing. A declared type that maps to `String` (see the [type mapping](/reference/engines/database-engines/sqlite#data_types-support)) is used as is. A column without a declared type is typed from the storage class of its value in the first row of the query result: an `INTEGER` value gives `Int64`, a `REAL` value gives `Float64`, and any other value (`TEXT`, `BLOB`, `NULL`), as well as an empty result, gives `String`. A numeric declared type is checked against that first row as well, because SQLite reports a declared type for a compound `SELECT` too, taken from one of its arms while the rows come from all of them. It is kept when the storage class of the value agrees with it (`INTEGER` for an integer type, `INTEGER` or `REAL` for a floating-point type), and also when the value is `NULL` or the result is empty, since neither carries any type information that could contradict it; otherwise the column is typed from the storage class, like an undeclared one. The inferred type is therefore only ever widened, never narrowed. Inferring such a column starts the query in SQLite. The first row does not speak for the rest: SQLite is free to return a different storage class in every row (for example, `CASE WHEN id = 1 THEN 1 ELSE 1.5 END`), so a query-backed read is fail-closed for every column read through a numeric type - not only for the ones without a declared type. A value whose storage class does not match that type, or which is not exactly representable in it (an `INTEGER` cell of `300` in a `UInt8` column, a `REAL` cell of `16777217` in a `Float32` column), fails the read instead of being silently coerced. To read a column with values of mixed storage classes, declare it as `String` or cast it to text in the SQLite query. Every inferred column is `Nullable`. The query can be written either as a subquery, or wrapped into the `query` function:

```sql theme={null}
CREATE TABLE sqlite_table ENGINE = SQLite('sqlite.db', (SELECT col1, col2 FROM table1 WHERE col2 > 1));
CREATE TABLE sqlite_table ENGINE = SQLite('sqlite.db', query('SELECT col1, col2 FROM table1 WHERE col2 > 1'));
```

Passing a query is supported starting from version 26.7. ClickHouse wraps the query into `SELECT ... FROM (<query>)` before sending it to SQLite, so it must not end with a semicolon.

Such a table is read-only: `INSERT` into it is not allowed. The same syntax is supported by the [`sqlite`](/reference/functions/table-functions/sqlite) table function.

<Note>
  The subquery form `(SELECT ...)` is parsed by ClickHouse and re-serialized before being sent to SQLite. It must therefore be valid ClickHouse SQL. To pass SQLite-specific syntax that ClickHouse does not parse, use the `query('...')` form, whose text is sent to SQLite verbatim.

  Any outer `WHERE`, `LIMIT`, aggregation, etc. of the surrounding ClickHouse query is **not** pushed down into the passed query — it is applied in ClickHouse after the full query result is fetched. To restrict the data read from SQLite, put the filter inside the passed query. With [`external_table_strict_query = 1`](/reference/settings/session-settings/external-table#external_table_strict_query) an outer filter on the columns of the table is rejected with an exception instead of being applied locally, because it cannot be pushed into the passed query. The check covers the top-level `WHERE` predicate and each conjunct of a top-level `AND`. A `PREWHERE` on the columns of this table is not a case for this setting: this table engine do not support `PREWHERE`, and such a query is rejected with `ILLEGAL_PREWHERE` regardless of the setting. The check runs only where a filter could be pushed down at all: when this table is the only table of the query, on either side of an `INNER JOIN`, or on the preserving side of an outer join (the left side of a `LEFT JOIN`, the right side of a `RIGHT JOIN`). On the non-preserving side of a `LEFT`/`RIGHT JOIN` and on either side of a `FULL JOIN` nothing is pushed down and nothing is checked, so a filter on the columns of this table is applied locally after the join even in strict mode. Where the check runs, a predicate that references other tables joined in the surrounding query is not pushed down and is excluded from the check, whether it references only the joined side or mixes it with this table inside one non-`AND` expression (for example an `OR`); such a predicate keeps its usual ClickHouse evaluation point (`WHERE` after the join, `PREWHERE` before it) and is not rejected.
</Note>

<h2 id="data-types-support">
  Data types support
</h2>

When you explicitly specify ClickHouse column types in the table definition, the following ClickHouse types can be parsed from SQLite TEXT columns:

* [Date](/reference/data-types/date), [Date32](/reference/data-types/date32)
* [DateTime](/reference/data-types/datetime), [DateTime64](/reference/data-types/datetime64)
* [UUID](/reference/data-types/uuid)
* [Enum8, Enum16](/reference/data-types/enum)
* [Decimal32, Decimal64, Decimal128, Decimal256](/reference/data-types/decimal)
* [FixedString](/reference/data-types/fixedstring)
* All integer types ([UInt8, UInt16, UInt32, UInt64, UInt128, UInt256, Int8, Int16, Int32, Int64, Int128, Int256](/reference/data-types/int-uint))
* [Float32, Float64](/reference/data-types/float)

See [SQLite database engine](/reference/engines/database-engines/sqlite#data_types-support) for the default type mapping.

A SQLite `NULL` read into an explicitly non-`Nullable` ClickHouse column raises the `CANNOT_INSERT_NULL_IN_ORDINARY_COLUMN` exception. Declare the column [Nullable](/reference/data-types/nullable) to read `NULL` values. An inferred schema follows the SQLite metadata: a column is `Nullable` unless it is declared `NOT NULL`.

<h2 id="usage-example">
  Usage example
</h2>

Shows a query creating the SQLite table:

```sql theme={null}
SHOW CREATE TABLE sqlite_db.table2;
```

```text theme={null}
CREATE TABLE SQLite.table2
(
    `col1` Nullable(Int64),
    `col2` Nullable(String)
)
ENGINE = SQLite('sqlite.db','table2');
```

Returns the data from the table:

```sql theme={null}
SELECT * FROM sqlite_db.table2 ORDER BY col1;
```

```text theme={null}
┌─col1─┬─col2──┐
│    1 │ text1 │
│    2 │ text2 │
│    3 │ text3 │
└──────┴───────┘
```

**See Also**

* [SQLite](/reference/engines/database-engines/sqlite) engine
* [sqlite](/reference/functions/table-functions/sqlite) table function