Connect SQLite#

SQLite is a built-in relational connector for a file-backed SQLite database. It appears in the datasource catalog as SQLite, shares the connection editor and the table-or-SQL query modes of the SQL Server, OLE DB, and ODBC connectors, and needs no driver installed: the SQLite client is built into Reportworq and works on Windows and Linux servers alike. Use it for a staged extract, a lightweight local warehouse, or demo data that must travel with the repository.

Before you begin#

• You are an administrator, working in Settings > Integrations > DATASOURCES. See Connect a data source for the general add-and-test flow.
• The SQLite database file is on the Reportworq server (or inside its repository, see below), and the service account that runs Reportworq can read it. The path resolves on the server, never on your workstation.
• For a shared, multi-writer database, use a server database instead of a SQLite file. SQLite is designed for a single writer at a time.

Configure the connection#

1. Add the SQLite connector and open its editor.
2. Keep Enable datasource connection selected.
3. Enter a Datasource Name. Report authors reference the connection by this name, for example in the connectionName argument of =RWSQL.
4. Enter the Connection String, normally the file path alone in the form Data Source=<path>. See Connection string forms below.
5. A plain SQLite file needs no login, so leave the %USERNAME% Variable and %PASSWORD% Variable fields empty. If your file requires credentials, use the same %USERNAME% and %PASSWORD% placeholders and masked fields as the other SQL connectors; see Keep credentials out of the connection string.
6. Leave Validation Query at SELECT 1. Test connection runs it.
7. Leave Limit Query Type at Limit, the SQLite dialect. The connector defaults to it because SQLite uses LIMIT n rather than SELECT TOP n.
8. If another process writes to the database file while Reportworq reads it, expand Advanced Options and set Command Timeout to how long, in seconds, to wait when the file is locked by another writer (default 30; 0 waits indefinitely). For the SQLite client this is a lock wait only; a slow query is not stopped when it elapses. The SQLite connector has no separate Connection Timeout field.
9. Save, then select Test connection. A readable database file returns "Success."

Connection string forms#

A file on the server, given as an absolute path. Use the path convention of the server's operating system:

Data Source=C:\Reportworq\data\finance.sqlite

Data Source=/var/lib/reportworq/data/finance.sqlite

A file that ships inside the repository. The repository: prefix resolves, when you test or query the connection, to the repository's files folder (v6/fs/files under the repository), so the database travels with a copy of the repository folder and the connection stays valid wherever the repository is extracted, with no absolute path and no per-environment edit. A file-level copy of the repository carries it; the built-in Backup feature on the Web Server tab of Server configuration zips the content store only (v6/data) and does not include the files folder, so add v6/fs/files/ to your own backup routine:

Data Source=repository:finance.sqlite

The relative path may include subfolders (repository:data/finance.sqlite). A path that tries to leave the files folder, for example with .. or a rooted path, is rejected as a configuration error rather than resolved. If the value is quoted (Data Source="repository:finance.sqlite";), the token ends at the closing quote. The Excel connector supports the same repository: prefix for a workbook, with the same files folder and the same rules.

To protect the file from accidental writes through a SQL query, add the SQLite client's read-only mode:

Data Source=repository:finance.sqlite;Mode=ReadOnly

The other keywords the SQLite client accepts are listed in Microsoft's SQLite connection-string reference.

Query the source in a data model#

Once the connection tests, a model author builds views over it on the model's Connection tab, choosing Table or View or SQL Query, exactly as for the other SQL connectors; see Query the source in a data model. Two SQLite specifics:

• The table picker lists the user tables and views in the file and hides SQLite's internal sqlite_ objects.
• SQLite has no schema namespace, so a table name stands alone; there is no schema.table qualification.

Notes and limits#

• The file path resolves on the Reportworq server. A path that works on your workstation fails on the server unless the file is there too, under the service account's read permission.
• The repository: prefix reaches only the repository's files folder. Anything outside it must be given as an absolute server path.
• SQLite defaults Limit Query Type to Limit; changing it to another dialect makes row-capped previews fail.

Related pages#

• Connect SQL Server, OLE DB, and ODBC for the shared editor, the credential placeholders, and the timeout fields.
• Connect a data source for the general add-and-test flow.
• Connect Excel for an Excel workbook that can ship inside the repository the same way.
• Relational database functions for =RWSQL, which can read a SQLite connection like any other.

Going deeper. To curate columns into governed business fields, set filters, and publish a view for report authors, see Data models.