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}
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
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)
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
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
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
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}
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
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
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
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
SQL developer in MQL › Keep sensor readings in a SQLite file, sum them up by day and export the hot ones
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
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
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": ","})
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
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}
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
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)
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
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
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
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}
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
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)
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
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
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
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}
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
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)
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
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}
