Skip to main content

Data Sources

Evidence supports connecting to a database, or local data files.

Connect your data​

To connect your local development environment to a database:

  1. Run your evidence project with npm run dev
  2. Navigate to localhost:3000/settings
  3. Select your database and enter your credentials

Evidence will save your credentials locally, and run a test query to confirm that it can connect.

Connections to databases in production are managed via environment variables

Supported data sources​

Evidence supports:

We're adding new connectors regularly. Create a GitHub issue or send us a message in Slack if you'd like to use Evidence with a database that isn't currently supported.

The source code for Evidence's connectors is available on GitHub

Data source specific info​

All databases can be connected via the UI settings page as described above. Where relevant, additional information is provided below.

BigQuery​

Evidence supports connecting to Google BigQuery by using the gcloud CLI, a service account and a JSON key, or an OAuth access token.

Logging in with the gcloud CLI​

If you have the gcloud CLI installed, you can log in to BigQuery using the following command:

gcloud auth application-default login

Evidence will use the credentials stored by the gcloud CLI to connect to BigQuery.

Note: Since gcloud requires browser access, this method is only available when developing locally.

Create a Service Account Key​

  1. Go to the Service Account Page and click on your project
  2. Add a name for your service account, then click Create
  3. Assign your service account a role for BigQuery (scroll down the role dropdown to find BigQuery roles).
    1. BigQuery User should work for most use cases.
    2. BigQuery Data Viewer may be required (depending on your organization's permissions settings in Google Cloud).
    3. Reach out to us if you run into issues or need help with BigQuery permissions.
  4. Click Continue, then click Done. You should see a table of users.
  5. Click on the email address for the service account you just created, then click the Keys tab
  6. Click Add Key, then Create New Key, then Create
  7. Google will download a JSON Key File to your computer

Logging in with an OAuth access token​

If you have an access token but can't download the gcloud CLI on the device you're deploying on and don't want to use a service account, you can use an OAuth access token.

An OAuth access token can be generated by running the following command on a device with the gcloud CLI installed:

gcloud auth application-default print-access-token

Note: This token will expire after 1 hour.

Now you can copy the access token and use it in your Evidence project.

Snowflake​

Evidence supports connecting to Snowflake using a Snowflake Account, Key-Pair Authentication, Browser-Based SSO, or Native SSO through Okta.

Snowflake Account​

The Snowflake Account authentication method uses your Snowflake username and password to authenticate. If you don't have access to these, you will need to use one of the other authentication methods.

Key-Pair Authentication​

The Key-Pair Authentication method uses a public/private key pair to authenticate. To use this method, you will need to generate a public/private key pair and upload the public key to Snowflake.

Browser-Based SSO​

The Browser-Based SSO method uses a browser-based SSO flow to authenticate. To use this method, you will need to connect an SSO provider to your Snowflake account.

Native SSO through Okta​

The Native SSO through Okta method uses Okta to authenticate. To use this method, you will need to have an Okta account with MFA disabled connected to your Snowflake account.

Redshift​

The Redshift connector uses the Postgres connector under the hood, so configuration options are similar.

PostgreSQL​

SSL​

To connect to a Postgres database using SSL, you may need to modify the SSL settings used. Once you have selected a PostgreSQL data connection type, you can set the SSL value as follows:

  • false: Don't connect using SSL (default)
  • true: Connect using SSL, validating the SSL certificates. Self-signed certificates will fail using this approach.
  • no-verify: Connect using SSL, but don't validate the certificates.

Other SSL options will require the use of a custom connection string. Evidence uses the node-postgres package to manage these connections, and the details of additional SSL options via the connection string can be found at the package documentation.

One scenario might be a Postgres platform that issues a self-signed certificate for the database connection, but provides a CA certificate to validate that self-signed certificate. In this scenario you could use a CONNECTION STRING value as follows: 

postgresql://{user}:{password}@{host}:{port}/{database}?sslmode=require&sslrootcert=/path/to/file/ca-certificate.crt

Replace the various {properties} as needed, and replace /path/to/file/ca-certificate.crt with the path and filename of your certificate.

Trino​

Supported Authentication Types​

While Trino supports multiple authentication types, the connector does currently only support the password based ones. Behind the scenes, the connector is using Basic access authentication for communicating with Trino.

HTTPS​

To connect to a Trino installation that is accessible via HTTPS, you need to set the SSL option to true and the port to 443/8443 (unless you are using a non standard port for HTTPS, in which case you should use that instead).

Starburst Quickstart​

Starburst, the company behind Trino, offers a SAAS solution where they run Trino for you. Once you have signed up and created a Trino cluster, you should be able to connect Evidence with the following configuration:

Host: <YOUR_DOMAIN>-<YOUR_CLUSTER_NAME>.galaxy.starburst.io

Port: 443

User: <YOUR_EMAIL>/accountadmin

SSL: true

Password: The password you use to login to your Starburst account

Alternatively, you can also create a service account at https://<YOUR_DOMAIN>.galaxy.starburst.io/service-accounts and use this to connect.

Microsoft SQL Server​

Trust Server Certificate​

The trustServerCertificate option indicates whether the channel will be encrypted while bypassing walking the certificate chain to validate trust. This option is disabled by default.

Encrypt​

The encrypt option indicates whether SQL Server uses SSL encryption for all data sent between the client and server if the server has a certificate installed. Necessary for Azure databases.

MySQL​

SSL​

SSL options are:

  • false (default)
  • true
  • Amazon RDS
  • A credentials object

SQLite​

SQLite is a local file-based database. It should be stored in the root of your Evidence project.

DuckDB​

DuckDB is a local file-based database. It should be stored in the root of your Evidence project.

See the DuckDB docs for more information.

MotherDuck​

To connect to MotherDuck, you will need a service token.

In the filename field, enter md:?motherduck_token=[YOUR_SERVICE_TOKEN], and select No extension from the dropdown.

CSV and Parquet files​

In Evidence, you can query local CSV or Parquet files directly in SQL.

Get started by selecting the CSV connector on the Settings page in your project.

How to Query a CSV File​

Inside your Evidence Project​

Evidence looks for CSV files stored in a sources folder in the root of your Evidence project. You can then query them using this syntax:

select * from 'sources/myfile.csv'
Absolute Filepaths​

You can pass in an absolute filepath:

select * from 'Users/myname/Downloads/myfile.csv'
Relative Filepaths​

Paths are relative to two files deep in your Evidence project. For example, to query a CSV in the root of an Evidence project, you would use this syntax:

select * from '../../myfile.csv'
info

The ../../ prefix is needed because Evidence creates and runs inside the hidden folder .evidence/template/. The pages and the sources folders are special, and are copied into this hidden folder by default.

SQL Syntax for Querying CSVs​

Evidence uses DuckDB to run SQL against a CSVs. For query syntax, see the DuckDB docs.

Parsing Headers​

When parsing headers in CSV files, the read_csv_auto helper function provided by DuckDB can be helpful.

select * from read_csv_auto('source/myfile.csv', HEADER=TRUE);

In addition to the HEADER argument, this function can also accept changes to the delimiter (DELIM), quotes (QUOTE), and more.

Additional information about CSV helper functions can be found in the DuckDB docs.

Troubleshooting​

If you need help with connecting to your data, please feel free to send us a message in Slack.