title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/engines/database-engines/sqlite.md)

Allows to connect to [SQLite](https://www.sqlite.org/index.html) database and perform `INSERT` and `SELECT` queries to exchange data between ClickHouse and SQLite.

## Creating a database[​](#creating-a-database "Direct link to Creating a database")

```
    CREATE DATABASE sqlite_database
    ENGINE = SQLite('db_path')
```

**Engine Parameters**

- `db_path` — Path to a file with SQLite database.

## Data types support[​](#data_types-support "Direct link to Data types support")

The table below shows the default type mapping when ClickHouse automatically infers schema from SQLite:

|  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| SQLite ClickHouse|  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | INTEGER [Int32](/docs/sql-reference/data-types/int-uint)| REAL [Float32](/docs/sql-reference/data-types/float)| TEXT [String](/docs/sql-reference/data-types/string)| TEXT [UUID](/docs/sql-reference/data-types/uuid)| BLOB [String](/docs/sql-reference/data-types/string) | | | | | | | | | | | |

When you explicitly define a table with specific ClickHouse types using the [SQLite table engine](/docs/engines/table-engines/integrations/sqlite), the following ClickHouse types can be parsed from SQLite TEXT columns:

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

SQLite has dynamic typing, and its type access functions perform automatic type coercion. For example, reading a TEXT column as an integer will return 0 if the text cannot be parsed as a number. This means that if a ClickHouse table is defined with a different type than the underlying SQLite column, values may be silently coerced rather than causing an error.

## Specifics and recommendations[​](#specifics-and-recommendations "Direct link to Specifics and recommendations")

SQLite stores the entire database (definitions, tables, indices, and the data itself) as a single cross-platform file on a host machine. During writing SQLite locks the entire database file, therefore write operations are performed sequentially. Read operations can be multi-tasked.
SQLite does not require service management (such as startup scripts) or access control based on `GRANT` and passwords. Access control is handled by means of file-system permissions given to the database file itself.

## Usage example[​](#usage-example "Direct link to Usage example")

Database in ClickHouse, connected to the SQLite:

```
CREATE DATABASE sqlite_db ENGINE = SQLite('sqlite.db');
SHOW TABLES FROM sqlite_db;
```

```
┌──name───┐
│ table1  │
│ table2  │
└─────────┘
```

Shows the tables:

```
SELECT * FROM sqlite_db.table1;
```

```
┌─col1──┬─col2─┐
│ line1 │    1 │
│ line2 │    2 │
│ line3 │    3 │
└───────┴──────┘
```

Inserting data into SQLite table from ClickHouse table:

```
CREATE TABLE clickhouse_table(`col1` String,`col2` Int16) ENGINE = MergeTree() ORDER BY col2;
INSERT INTO clickhouse_table VALUES ('text',10);
INSERT INTO sqlite_db.table1 SELECT * FROM clickhouse_table;
SELECT * FROM sqlite_db.table1;
```

```
┌─col1──┬─col2─┐
│ line1 │    1 │
│ line2 │    2 │
│ line3 │    3 │
│ text  │   10 │
└───────┴──────┘
```

- [Creating a database](#creating-a-database)- [Data types support](#data_types-support)- [Specifics and recommendations](#specifics-and-recommendations)- [Usage example](#usage-example)

Was this page helpful?