Connect a database

A SQL database source turns the rows of a SELECT you write into documents — one document per row — and keeps them in step as rows are added, changed and deleted. PostgreSQL and MySQL (and MariaDB) are supported.

Everything Connect a source says about schedules, Sync now, the run log and what happens when a document is edited, removed or deleted applies unchanged. This page covers what is specific to a database.

Before you start: a read-only role

Give the platform a database role that can only read. The platform opens every connection read-only and only ever runs your query as a subquery, so it cannot write by accident — but those are guards, not a permission boundary. The role is.

-- PostgreSQL
CREATE ROLE kovac_reader LOGIN PASSWORD '…';
GRANT CONNECT ON DATABASE crm TO kovac_reader;
GRANT USAGE ON SCHEMA public TO kovac_reader;
GRANT SELECT ON tickets TO kovac_reader;

-- MySQL
CREATE USER 'kovac_reader'@'%' IDENTIFIED BY '…';
GRANT SELECT ON crm.tickets TO 'kovac_reader'@'%';

The platform must be able to reach the database host and port over the network. Use a password of at least eight characters; shorter ones cannot be reliably scrubbed from error messages.

Add the source

Open a collection’s Sources tab, choose Add source, pick SQL database, and fill in:

• Database — PostgreSQL or MySQL / MariaDB.
• Host, Port (blank for the default), Database, Username, Password.
• SELECT query — one SELECT (or WITH … SELECT) statement. It is only ever run inside SELECT … FROM (your query) …, so it must not end with anything but the query itself, and it cannot contain a ;.
• ID column — a column that is unique per row and does not change. It becomes the document’s identity across syncs.
• Content column — the document body. A row whose content is empty is not a document.
• Updated-at column — strongly recommended. Drives incremental sync and is the row’s version. Without it, every row is fetched every run and the content itself decides what changed.
• Title column — optional. Becomes the document’s title and its first heading.
• URL template — optional. Where a citation links, with column names in braces: https://crm.example.com/tickets/{id}. Blank gives a plain sql://database/id reference.
• Under Advanced: Metadata columns (blank keeps every other column) and, for PostgreSQL, Require TLS.

Test connection runs your query with LIMIT 1 and checks that the columns you named are among its results.

What becomes a document

Each row with content. The body is the content column, headed by the title column when you set one. Every other column becomes metadata on the document, filterable in search: a column customer becomes the metadata key customer, Created On becomes created_on. Values are kept as text unless you declare the field in the collection’s metadata schema — declare priority as a number and created_on as a date, and you get range filters on them.

Some names are reserved for the platform’s own metadata (title, author, created, modified, language and a few more). A column with one of those names is refused by Test connection with an alias to use, for example created AS created_on.

How changes are detected

Each run lists the rows in pages, ordered by the ID column, and compares each row’s updated-at value with the one it saw last time; an unchanged row is not fetched. After the first run, only rows with an updated-at later than the previous run are listed at all (on MySQL that comparison assumes the column is in UTC). A row that disappears from the query’s results — deleted, or filtered out by your WHERE — is paused, then deleted after the grace period. A row whose content becomes empty is treated the same way.

A run ingests at most 5,000 rows before it yields and comes straight back, so a large first load completes over several runs; the run log shows the progress.

Test-connection messages

• query must start with SELECT or WITH / must be a single statement — only a query is accepted.
• the query does not return the declared column(s): … — the names you typed are not among the query’s columns; alias them in the SELECT.
• column(s) … would be dropped: reserved metadata name(s) — alias as suggested.
• the username or password is wrong, the database does not exist on that server, the database host is unreachable from the platform — as they say.
• the role is not allowed to read one of the tables the query uses — grant SELECT on it.
• the query failed: … — the database’s own message follows.