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. If the database is uploaded into a workspace rather than placed on the server, see Reference a database uploaded into a workspace.
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.

Reference a database uploaded into a workspace#

A third option points the connection at a database that was uploaded into a workspace's own file tree, rather than placed on the server's disk. It uses the workspace: prefix, a sibling of repository: that reaches a specific workspace's uploaded files instead of the shared files folder:

Data Source=workspace:Finance/finance.sqlite

The SQLite connector's editor has no file-browse picker of its own (only the Excel connector offers a Browse… button next to its file setting), so the way to reach a workspace: reference is to start from the file itself:

1. In the workspace browser, find the SQLite database, open its ⋮ menu, and choose Configure as data source. This action is available to system administrators only, and only appears for a .db, .sqlite, or .sqlite3 file.
2. Reportworq creates a new SQLite connection, named after the file, already enabled, with Connection String pre-filled to Data Source=workspace:<workspace name>/<folder path>/<file name> and Availability limited to that file's own workspace - then opens it in the editor for you to review and save.

Because the connection is now a normal, named Integrations connection, widening its Availability afterward makes that database's data readable from the other workspaces you add.

workspace: names its workspace by name, the way Configure as data source writes it. A workspace id (the 16-character hex value) is still accepted and always resolves; Reportworq falls back to writing the id, instead of the name, only when the name can't be carried faithfully - it is empty, has leading or trailing spaces, contains /, \, or ;, is not unique among workspace names, or happens to equal another workspace's id. At use time Reportworq tries an existing workspace id first, then a case-insensitive name match; a name shared by two or more workspaces is a configuration error - rename one of the workspaces, or run Configure as data source on the file again, which writes a reference that can't be confused. Renaming a workspace breaks a reference written with its old name - update the reference to the workspace's current name, or run Configure as data source again; the id form does not break on a rename because it never carried the name. If that old name is later reused by a different workspace, the reference silently switches to the new workspace's file - with no error - avoid reusing a renamed workspace's name. Reportworq logs the resolved workspace for each name-based reference, so an administrator can trace which workspace was actually read.

A workspace: connection is always opened read-only, regardless of any Mode= you add - a workspace: reference silently upgrades or ignores an explicit Mode= other than ReadOnly, rather than honoring it. The same folder/file-name rules as repository: apply: a name containing /, \, or ;, or named . or .., cannot be referenced, and a path that does not resolve to a real file in that workspace, or tries to leave it with .., is rejected as a configuration error naming the reference. The reference names the file's live folder path, not an internal id, so a renamed or moved file breaks it - repoint the connection by running Configure as data source again, or by editing the Connection String by hand.

Only a system administrator can create or change this connection, and at run time the only control over who can use it is the connection's Availability - the file's own workspace and folder permissions are not applied. Anyone with access to any workspace the connection is available to can read the database's data, even when Availability does not include the file's own workspace. Configure as data source starts the new connection's Availability limited to the file's own workspace, so widening it afterward is a deliberate choice. This is a real difference from a workspace-file data model (Build a data model from a workspace file), which instead follows the model author's own folder permissions on the file, checked on every use.

If the database belongs to one workspace and does not need to be a shared connection at all, skip Integrations entirely and see Build a data model from a workspace file - a data model can read it directly.

Query the source in a data model#

Once the connection tests, a model author builds views over it on each view's Design tab, starting from a table or writing SQL, exactly as for the other SQL connectors; see Build a view with the designer. Three SQLite specifics:

• The table picker lists the user tables and views in the file and hides SQLite's internal sqlite_ objects.
• The foreign keys the database declares are offered as related tables on the designer canvas and listed as Declared by the driver on the model's Source screen, so a database with declared keys needs no manual relationships. Only single-column keys are offered, and database views declare none. See Table relationships.
• 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.
• A workspace:-uploaded database must be an allowed upload type in that workspace. A fresh install permits .db / .sqlite / .sqlite3 by default; an existing install keeps its saved upload allow-list, so an administrator may need to add the Databases category first, in Settings > Security > Workspaces > the workspace > Workspace Files > Allowed File Types (the + Databases quick-add chip).
• A path that does not exist fails Test connection (SQLite error 14, unable to open the database file); Reportworq does not create an empty database for you. If you really want SQLite to create the file, add Mode=ReadWriteCreate to the connection string.
• 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 or a workspace the same way.
• Build a data model from a workspace file for reading the same kind of database straight into a data model, with no connection here at all.
• 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.