Data Sources
Evidence supports connecting to a database, or local data files.
Connect your data
To connect your local development environment to a database:
- Run your evidence project with
npm run dev
- Navigate to localhost:3000/settings
- 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:
- BigQuery
- Snowflake
- Redshift
- PostgreSQL
- Trino
- Microsoft SQL Server
- MySQL
- SQLite
- DuckDB
- CSV and Parquet files
- & More
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
- Go to the Service Account Page and click on your project
- Add a name for your service account, then click Create
- Assign your service account a role for BigQuery (scroll down the role dropdown to find BigQuery roles).
- BigQuery User should work for most use cases.
- BigQuery Data Viewer may be required (depending on your organization's permissions settings in Google Cloud).
- Reach out to us if you run into issues or need help with BigQuery permissions.
- Click Continue, then click Done. You should see a table of users.
- Click on the email address for the service account you just created, then click the Keys tab
- Click Add Key, then Create New Key, then Create
- 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'
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.