Connecting to the database

Napkin works with a single DB backend and connection string a time. Database connection string has to be configured in YAML spec and it has to match the selected backend. Connection string can be later overridden with --uri (-u) flag when spec is executed with napkin run command.

BigQuery

backend: BigQuery
db_url: bigquery://bigquery.googleapis.com/PROJECT-NAME?dataset=DATASET-NAME

Database connection string (db_url in YAML or --uri) should be formatted as follows: bigquery://bigquery.googleapis.com/PROJECT-NAME. Furthermore, one can set default dataset by appending ?dataset=DATASET-NAME. Note that authentication credentials are cached for particular Spec YAML path, database connection string and app. If any of them changes you will need to authenticate again. The credentials are cached in ~/.napkin/oauthdb by default, this path can be changed with --credentials-db argument.

Alternatively, one may pass credentials JSON using --credentials-file (-C) option. This option can be useful when running napkin in unattended configuration.

Authenticating locally

Run napkin auth create command and follow the instructions in the web browser.

Authenticating for unattended runs

First, authenticate locally. Then run napkin auth show and save value of OAuth2-JsonToken in a JSON file. The credentials JSON can be later passed to napkin run using --credentials-file (-C) option.

It may be then stored in a secret store of CI/CD platform of choice. Please refer to GitHub Actions example for further reference.

Postgresql and Redshift

backend: Postgres
db_url: postgresql://user@db/napkin_db

Connection for Postgres and Redshift, which uses Postgres protocol, can be configured with connection string and/or environment variables. Connection strings use standard syntax from libpq and can be summarized with the following examples:

postgresql://
postgresql://localhost
postgresql://localhost:5433
postgresql://localhost/mydb
postgresql://user@localhost
postgresql://user:secret@localhost

Values that are not provided in the connection string will be sourced from environment variables if available. If postgresql:// connection string is used whole database connection configuration will be sourced from environment variables. This can be useful in unattended environments. For more examples and further reference on connection string and environment variables please visit libpq manual.

We do not recommend storing any passwords in the YAML spec. Instead, one can:

• store the password in a text file, then pass the file location with --credentials-file (-C) option,
• store the password in a text file, then pass the file location with PGPASSFILE environment variable,
• provide the password with the PGPASSWORD environment variable.

Sqlite

backend: Sqlite
db_url: sqlite:some-folder/dbfile.db

Connection string is a path to the database file (with sqlite: prefix).