SQLite files
Load JSON rows and CSV files into SQLite tables, export a query to CSV, back up a file and check or compact it. Typical uses: build a SQLite file from an import, save a copy of a database file.
sqlite import_jsonsqlite import_jsonsqlite export_csvsqlite export_csvsqlite import_csvsqlite import_csvsqlite backupsqlite backupsqlite integrity_checksqlite integrity_checksqlite vacuumsqlite vacuum

sqlite import_json <path> <table> <json_rows>


Description

    Insert JSON rows into a table (created when missing). The columns are all the keys of the rows; a missing key is NULL. Result: {table, rows, columns, created}

Parameters

    path:   The SQLite file (same rights as file load). A plain path: no :memory:, no file: URI, no ? - String - required
    table:   The table - String - required
    json_rows:   A JSON array of objects - String - required
admin
sqlite import_json "tmp/doc_sqlite.db" "products_draft" "[{\"code\": \"A-100\", \"label\": \"Pump\", \"price\": 349.9, \"stock\": 4, \"tags\": [\"water\", \"garden\"]}, {\"code\": \"A-200\", \"label\": \"Hose 25 m\", \"price\": 39, \"stock\": 12}]"
mentdb
{"table": "products_draft", "rows": 2, "columns": ["code", "label", "price", "stock", "tags"], "created": true}

sqlite import_json <path> <table> <json_rows> <json_options>


Description

    Insert JSON rows with options: mode (append: add to the table; replace: drop and create it again; create: an error if it exists), create (true: create the table and the file when missing), types (the SQL type of some columns, ex: {"price": "NUMERIC"}), primary_key (a column or a list of columns, for a new table), foreign_keys (false: SQLite checks the foreign keys only when this is true), max_db_mb (2048, 1 to 1048576: the file cannot grow over this size; PRAGMA max_page_count is refused for this reason), timeout_s (stops the statement after this time, also a statement that only computes) (300), busy_timeout_ms (5000, 0 to 600000: how long to wait when another program holds a lock on the file)

Parameters

    path:   The SQLite file (same rights as file load). A plain path: no :memory:, no file: URI, no ? - String - required
    table:   The table - String - required
    json_rows:   The rows - String - required
    json_options:   The options, as a JSON object - String - required
admin
sqlite import_json "tmp/doc_sqlite.db" "products" "[{\"code\": \"A-100\", \"label\": \"Pump\", \"price\": 349.9, \"stock\": 4, \"tags\": [\"water\", \"garden\"]}, {\"code\": \"A-200\", \"label\": \"Hose 25 m\", \"price\": 39, \"stock\": 12}, {\"code\": \"B-010\", \"label\": \"Gloves\", \"price\": 7.5, \"stock\": null, \"photo\": {\"base64\": \"iVBORw==\"}}]" "{\"mode\": \"replace\", \"primary_key\": \"code\"}"
mentdb
{"table": "products", "rows": 3, "columns": ["code", "label", "price", "stock", "tags", "photo"], "created": true}
The guessed types and the stored values
admin
sqlite query "tmp/doc_sqlite.db" "SELECT code, typeof(price) AS price_type, stock, tags, photo FROM products ORDER BY code"
mentdb
[{"code": "A-100", "price_type": "real", "stock": 4, "tags": "[\"water\", \"garden\"]", "photo": null}, {"code": "A-200", "price_type": "real", "stock": 12, "tags": null, "photo": null}, {"code": "B-010", "price_type": "real", "stock": null, "tags": null, "photo": "iVBORw=="}]
A duplicate key: nothing is written
admin
sqlite import_json "tmp/doc_sqlite.db" "products" "[{\"code\": \"C-001\", \"label\": \"Rake\"}, {\"code\": \"A-100\", \"label\": \"Pump again\"}]"
mentdb
ERROR: Sorry, an error appears (row 2: UNIQUE constraint failed: products.code (SQLITE_CONSTRAINT_PRIMARYKEY) (nothing was written)).
Used in use cases (1 recipe)

    Integrator & ETL developer › Hand a product catalogue to another tool as a SQLite file, check it and keep a copy
Often used with

    file exist  ·  file mkdir  ·  sqlite backup  ·  sqlite integrity_check  ·  sqlite query  ·  sqlite tables

sqlite export_csv <path> <sql> <csv_path>


Description

    Write the rows of a query to a CSV file (comma, UTF-8, a header line). An existing file is refused. Result: {file, rows, columns}

Parameters

    path:   The SQLite file (same rights as file load). A plain path: no :memory:, no file: URI, no ? - String - required
    sql:   One SQL statement, with ? for the parameters - String - required
    csv_path:   The CSV file to write - String - required
admin
sqlite export_csv "tmp/doc_sqlite.db" "SELECT id, shop, day, amount FROM orders ORDER BY id" "tmp/doc_sqlite_new.csv"
mentdb
{"file": "tmp/doc_sqlite_new.csv", "rows": 4, "columns": ["id", "shop", "day", "amount"]}

sqlite export_csv <path> <sql> <csv_path> <json_options>


Description

    Write a query to a CSV file with options: params (the values of the ?), overwrite (false), csv (the parameters of csv write, ex: {"separator": ";", "bom": true}), max_rows (1000000, up to 5000000), max_result_mb, blob, timeout_s (600), busy_timeout_ms

Parameters

    path:   The SQLite file (same rights as file load). A plain path: no :memory:, no file: URI, no ? - String - required
    sql:   One SQL statement, with ? for the parameters - String - required
    csv_path:   The CSV file - String - required
    json_options:   The options, as a JSON object - String - required
admin
sqlite export_csv "tmp/doc_sqlite.db" "SELECT id, shop, day, amount FROM orders WHERE shop = ? ORDER BY id" "tmp/doc_sqlite_orders.csv" "{\"params\": [\"SD\"], \"overwrite\": true}"
mentdb
{"file": "tmp/doc_sqlite_orders.csv", "rows": 2, "columns": ["id", "shop", "day", "amount"]}
Used in use cases (1 recipe)

    SQL developer in MQL › Keep sensor readings in a SQLite file, sum them up by day and export the hot ones
Often used with

    file exist  ·  file mkdir  ·  sqlite execute  ·  sqlite query  ·  sqlite script

sqlite import_csv <path> <table> <csv_path>


Description

    Insert the rows of a CSV file into a table (created when missing). The CSV is read as csv read does: encoding, separator and types detected

Parameters

    path:   The SQLite file (same rights as file load). A plain path: no :memory:, no file: URI, no ? - String - required
    table:   The table - String - required
    csv_path:   The CSV file - String - required
admin
sqlite import_csv "tmp/doc_sqlite.db" "orders_csv" "tmp/doc_sqlite_orders.csv"
mentdb
{"table": "orders_csv", "rows": 2, "columns": ["id", "shop", "day", "amount"], "created": true}

sqlite import_csv <path> <table> <csv_path> <json_options>


Description

    Insert a CSV file with options: the options of sqlite import_json, and csv (the parameters of csv read, ex: {"separator": ";", "decimal": ","})

Parameters

    path:   The SQLite file (same rights as file load). A plain path: no :memory:, no file: URI, no ? - String - required
    table:   The table - String - required
    csv_path:   The CSV file - String - required
    json_options:   The options, as a JSON object - String - required
admin
sqlite import_csv "tmp/doc_sqlite.db" "orders_csv" "tmp/doc_sqlite_orders.csv" "{\"mode\": \"replace\", \"types\": {\"amount\": \"REAL\"}}"
mentdb
{"table": "orders_csv", "rows": 2, "columns": ["id", "shop", "day", "amount"], "created": true}

sqlite backup <path> <dest_path>


Description

    Copy a SQLite file with the online backup API, even while it is in use. An existing destination is refused. Result: {source, destination, bytes}

Parameters

    path:   The SQLite file (same rights as file load). A plain path: no :memory:, no file: URI, no ? - String - required
    dest_path:   The copy - String - required
admin
sqlite backup "tmp/doc_sqlite.db" "tmp/doc_sqlite_new_backup.db"
mentdb
{"source": "tmp/doc_sqlite.db", "destination": "tmp/doc_sqlite_new_backup.db", "bytes": 40960}

sqlite backup <path> <dest_path> <json_options>


Description

    Copy a file with options: overwrite (false), busy_timeout_ms (5000, 0 to 600000: how long to wait when another program holds a lock on the file)

Parameters

    path:   The SQLite file (same rights as file load). A plain path: no :memory:, no file: URI, no ? - String - required
    dest_path:   The copy - String - required
    json_options:   The options, as a JSON object - String - required
admin
sqlite backup "tmp/doc_sqlite.db" "tmp/doc_sqlite_backup.db" "{\"overwrite\": true}"
mentdb
{"source": "tmp/doc_sqlite.db", "destination": "tmp/doc_sqlite_backup.db", "bytes": 40960}
The copy holds the same rows
admin
sqlite query "tmp/doc_sqlite_backup.db" "SELECT count(*) AS orders FROM orders"
mentdb
[{"orders": 4}]
Used in use cases (1 recipe)

    Integrator & ETL developer › Hand a product catalogue to another tool as a SQLite file, check it and keep a copy
Often used with

    file exist  ·  file mkdir  ·  sqlite import_json  ·  sqlite integrity_check  ·  sqlite query  ·  sqlite tables

sqlite integrity_check <path>


Description

    Check the structure of the file (PRAGMA integrity_check) and the foreign keys (PRAGMA foreign_key_check). Result: {ok, check, errors, foreign_key_errors}

Parameters

    path:   The SQLite file (same rights as file load). A plain path: no :memory:, no file: URI, no ? - String - required
admin
sqlite integrity_check "tmp/doc_sqlite_backup.db"
mentdb
{"ok": true, "check": "integrity_check", "errors": [], "foreign_key_errors": []}

sqlite integrity_check <path> <json_options>


Description

    Check with options: quick (false: true runs quick_check, faster on big files, without the index checks), max_errors (100, 1 to 10000), timeout_s (600), busy_timeout_ms (5000, 0 to 600000: how long to wait when another program holds a lock on the file)

Parameters

    path:   The SQLite file (same rights as file load). A plain path: no :memory:, no file: URI, no ? - String - required
    json_options:   The options, as a JSON object - String - required
admin
sqlite integrity_check "tmp/doc_sqlite_backup.db" "{\"quick\": true}"
mentdb
{"ok": true, "check": "quick_check", "errors": [], "foreign_key_errors": []}
Used in use cases (1 recipe)

    Integrator & ETL developer › Hand a product catalogue to another tool as a SQLite file, check it and keep a copy
Often used with

    file exist  ·  file mkdir  ·  sqlite backup  ·  sqlite import_json  ·  sqlite query  ·  sqlite tables

sqlite vacuum <path>


Description

    Rebuild the file to give back the free pages to the disk (VACUUM). It needs free space for a copy of the file and no other program writing. Result: {file, size_before, size_after}

Parameters

    path:   The SQLite file (same rights as file load). A plain path: no :memory:, no file: URI, no ? - String - required
admin
sqlite vacuum "tmp/doc_sqlite_backup.db"
mentdb
{"file": "tmp/doc_sqlite_backup.db", "size_before": 40960, "size_after": 40960}

sqlite vacuum <path> <json_options>


Description

    VACUUM with options: timeout_s (3600), busy_timeout_ms (5000, 0 to 600000: how long to wait when another program holds a lock on the file)

Parameters

    path:   The SQLite file (same rights as file load). A plain path: no :memory:, no file: URI, no ? - String - required
    json_options:   The options, as a JSON object - String - required
admin
sqlite vacuum "tmp/doc_sqlite_backup.db" "{\"busy_timeout_ms\": 20000}"
mentdb
{"file": "tmp/doc_sqlite_backup.db", "size_before": 40960, "size_after": 40960}