Postgres

The postgres connector runs a SQL query you write against a Postgres database and sends the rows it returns. Use it for internal systems that store their data in Postgres, or for a reporting replica.

#What it pulls

Exactly the rows your query returns, nothing else. Each row is recorded under one object name, rows by default. Dates come out as ISO timestamps, big integers as strings, and binary columns are dropped.

#Options

$1 is the start of the run window: the time of the last successful run, or 1970-01-01 on the first run. $2 is the end of the window, the time the run started. Filter on a created or updated timestamp with both:

SELECT id, status, total, region, created_at, updated_at
FROM orders
WHERE updated_at >= $1 AND updated_at < $2

The connector refuses a query that does not use $1, because without it every run would resend the whole table.

#Connect

datayield connect postgres \
  -o query='SELECT id, status, total, region, created_at, updated_at FROM orders WHERE updated_at >= $1 AND updated_at < $2' \
  -o object=orders
# asks for url, which is stored in ~/.datayield/credentials

Secret options passed directly are saved to ~/.datayield/credentials. Values written as env:NAME are read from the environment at run time, which suits CI. See CLI reference.

#The database user

Create a user that can only read the tables in your query. The connector never writes, but a read-only user makes that a property of the database rather than of the SDK.

CREATE ROLE datayield_reader LOGIN PASSWORD '...';
GRANT CONNECT ON DATABASE app TO datayield_reader;
GRANT USAGE ON SCHEMA public TO datayield_reader;
GRANT SELECT ON orders TO datayield_reader;

Select only the columns you mean to sell. Columns you leave out of the query never leave the database.

#Checks

datayield connect postgres connects and runs select 1. A wrong password, an unknown user or an unknown database fails with a credentials error that says which part of the URL to check.