Create a Database connection#
We currently support connections to the following data warehouses: databricks
, postgresql
, snowflake
, bigquery
and AWS Athena
. All sensitive connection data
is encrypted using the key you provide in your .env file before being stored to the application storage.
You can also specify the engine to connect to the Database through an SSH tunnel, as demonstrated in the second example below.
You can include the API key for the LLM in the request body as an optional parameter. If you do not include the API key, the LLM will use the API key specified in the .env file.
You can find additional details on how to connect to each of the supported data warehouses below.
You have the flexibility to configure your own file storage service credentials, such as AWS S3, to manage the storage of all CSV files generated by the /questions or /responses endpoints.
Once the database connection is established, it retrieves the table names and creates table-descriptions resources with a status of NOT_SCANNED.
Request this POST endpoint:
/api/v1/database-connections
Request body
{
"alias": "string",
"use_ssh": false,
"connection_uri": "string",
"schemas": [
"string"
],
"path_to_credentials_file": "string",
"llm_api_key": "string",
"ssh_settings": {
"host": "string",
"username": "string",
"password": "string",
"private_key_password": "string"
},
"file_storage": {
"name": "string",
"access_key_id": "string",
"secret_access_key": "string",
"region": "string",
"bucket": "string"
}
}
SSH Parameters
Set the use_ssh field to true
Set the connection_uri field using the following structure:
<db-driver>://<user>:<password>@<host>/<db-name>`
Set in path_to_credentials_file your SSH key file location, if you use s3 like this:
"s3://<bucket-name>/<file-name>"
Name |
Type |
Description |
---|---|---|
host |
string |
The hostname or IP address of the SSH server you need to access |
username |
string |
Your username for SSH authentication |
password |
string |
Your password for SSH authentication |
private_key_password |
string |
The password for the id_rsa private key file, if it is password-protected |
File Storage Parameters
Name |
Type |
Description |
---|---|---|
name |
string |
Set S3, it is required |
access_key_id |
string |
Your AWS access key, it is encrypted internally |
secret_access_key |
string |
Your AWS secret access key, it is encrypted internally |
region |
string |
Your bucket region |
bucket |
string |
Your bucket name |
Responses
HTTP 201 code response
{
"id": "64f251ce9614e0e94b0520bc",
"alias": "string_999",
dialect: "postgresql",
"use_ssh": true,
"connection_uri": "gAAAAABk8lHQNAUn5XARb94Q8H1OfHpVzOtzP3b2LCpwxUsNCe7LGkwkN8FX-IF3t65oI5mTzgDMR0BY2lzvx55gO0rxlQxRDA==",
"path_to_credentials_file": "string",
"llm_api_key": "gAAAAABlCz5TeU0ym4hW3bf9u21dz7B9tlnttOGLRDt8gq2ykkblNvpp70ZjT9FeFcoyMv-Csvp3GNQfw66eYvQBrcBEPsLokkLO2Jc2DD-Q8Aw6g_8UahdOTxJdT4izA6MsiQrf7GGmYBGZqbqsjTdNmcq661wF9Q==",
"ssh_settings": {
"host": "string",
"username": "string",
"password": "gAAAAABk8lHQAaaSuoUKxddkMHw7jerwFmUeiE3hL6si06geRt8CV-r43fbckZjI6LbIULWPZ4HlQUF9_YpfaYfM6FarQbhDUQ==",
"private_key_password": "gAAAAABk8lHQWilFpIbCADvunHGYFMqgoPKIml_WRXf5Yuowqng28DVsq6-sChl695y5D_mWrr1I3hcJCZqkmhDqpma6iz3PKA=="
},
"file_storage": {
"name": "S3",
"access_key_id": "gAAAAABk8lHQAaaSuoUKxddkMHw7jerwFmUeiE3hL6si06geRt8CV-r43fbckZjI6LbIULWPZ4HlQUF9_YpfaYfM6FarQbhDUQ==",
"secret_access_key": "gAAAAABk8lHQAaaSuoUKxddkMHw7jerwFmUeiE3hL6si06geRt8CV-r43fbckZjI6LbIULWPZ4HlQUF9_YpfaYfM6FarQbhDUQ==",
"region": "us-east-1",
"bucket": "my-bucket"
}
}
HTTP 400 code response (if the db connection fails it returns a 400 error), here you can find all the error codes
{
"error_code": "string",
"message": "string",
"description": "string",
"detail": {
"alias": "string",
"use_ssh": false,
"connection_uri": "string"
}
}
Example 1
Without a SSH connection
curl -X 'POST' \
'<host>/api/v1/database-connections' \
-H 'accept: application/json' \
-H 'Content-Type: application/json' \
-d '{
"alias": "my_db_alias_identifier",
"use_ssh": false,
"connection_uri": "sqlite:///mydb.db"
}'
Example 2
With a SSH connection
curl -X 'POST' \
'<host>/api/v1/database-connections' \
-H 'accept: application/json' \
-H 'Content-Type: application/json' \
-d '{
"alias": "my_db_alias",
"use_ssh": true,
"connection_uri": "postgresql+psycopg2://<user>:<password>@<host>/<db-name>"
"ssh_settings": {
"host": "string",
"username": "string",
"password": "string",
"private_key_password": "string",
}
}'
Example 3
With a SSH connection and LLM credentials
url -X 'POST' \
'<host>/api/v1/database-connections' \
-H 'accept: application/json' \
-H 'Content-Type: application/json' \
-d '{
"alias": "my_db_alias",
"use_ssh": true,
"connection_uri": "postgresql+psycopg2://<user>:<password>@<host>/<db-name>",
"llm_api_key": "api_key",
"ssh_settings": {
"host": "string",
"username": "string",
"password": "string",
"private_key_password": "string"
}
}'
Connections to supported Data warehouses#
The format of the connection_uri
parameter in the API call will depend on the data warehouse type you are connecting to.
You can find samples and how to generate them below.
Postgres#
Uri structure:
"connection_uri": postgresql+psycopg2://<user>:<password>@<host>:<port>/<db-name>
Example:
"connection_uri": postgresql+psycopg2://admin:123456@foo.rds.amazonaws.com:5432/my-database
Specify a schema (If it isn’t specified by default it uses public):
"connection_uri": postgresql+psycopg2://<user>:<password>@<host>:<port>/<db-name>?options=-csearch_path=<my-schema>
MySQL/MariaDB#
Uri structure:
"connection_uri": mysql+pymysql://<user>:<password>@<host>:<port>/<db-name>
Example:
"connection_uri": mysql+pymysql://admin:123456@foo.rds.amazonaws.com:3306/my-database
Microsoft SQL Server#
Uri structure:
"connection_uri": mssql+pymssql://<user>:<password>@<host>:<port>/<db-name>
Example:
"connection_uri": mssql+pymssql://admin:123456@foo.rds.amazonaws.com:1433/my-database
To specify a schema other than the default dbo, execute the following command:
ALTER USER <your_username> WITH DEFAULT_SCHEMA = <your_schema_name>;
Databricks#
Uri structure:
"connection_uri": databricks://token:<token>@<host>?http_path=<http-path>&catalog=<catalog>&schema=<schema-name>
Example:
"connection_uri": databricks://token:abcd1234abcd1234abcd1234abcd1234@foo-bar.cloud.databricks.com?http_path=sql/protocolv1/o/123456/123-1234-abcdabcd&catalog=foobar&schema=default
Snowflake#
Uri structure:
"connection_uri": snowflake://<user>:<password>@<organization>-<account-name>/<database>/<schema>
Example:
"connection_uri": snowflake://jon:123456@foo-bar/my-database/public
Redshift#
Uri structure:
"connection_uri": redshift+psycopg2://<user>:<password>@<host>:<port>/<database>
Example:
"connection_uri": redshift+psycopg2://jon:123456@host.amazonaws.com:5439/my-database
ClickHouse#
Uri structure:
"connection_uri": clickhouse+http://<user>:<password>@<host>:<port>/<db_name>?protocol=https
Example:
"connection_uri": clickhouse+http://jon:123456@foo-bar:8443/my-database?protocol=https
AWS Athena#
Uri structure:
"connection_uri": awsathena+rest://<aws_access_key_id>:<aws_secret_access_key>@athena.<region_name>.amazonaws.com:443/<schema_name>?s3_staging_dir=<s3_staging_dir>&work_group=primary
Example:
"connection_uri": awsathena+rest://foobar:foobar@athena.us-east-2.amazonaws.com:443/db_test?s3_staging_dir=s3://my-bucket/output/&work_group=primary
MotherDuck#
Uri structure:
"connection_uri": duckdb:///md:<db_name>?motherduck_token=<token>
Example:
"connection_uri": duckdb:///md:foobar?motherduck_token=abcd1234abcd1234abcd1234abcd1234abcd1234abcd1234abcd1234abcd1234abcd1234abcd1234abcd1234abcd1234abcd1234abcd1234abcd1234abcd1234abcd1234abcd1234abcd1234abcd1234abcd1234abcd1234abcd1234abcd1234abcd1234abcd1234abcd1234abcd1234abcd1234abcd1234abcd1234abcd1234abcd1234abcd1234abcd1234abcd1234
BigQuery#
To connect to BigQuery you should create a json credential file. Please follow Steps 1-3 under “Configure BigQuery Authentication in Google Cloud Platform” in this tutorial.
Please ensure the service account only has “Viewer” permissions.
Once you have your credential json file you can store it inside the project. For example given the credential file my-db-123456acbd.json in the folder private_credentials you should set in the endpoint param path_to_credentials_file the path, for example:
"path_to_credentials_file": "private_credentials/my-db-123456acbd.json"
and the connection_uri
will be:
Uri structure:
"connection_uri": bigquery://<project>/<database>
Example:
"connection_uri": bigquery://v2-real-estate/K2
Connecting multi-schemas
You can connect many schemas using one db connection if you want to create SQL joins between schemas. Currently only BigQuery, Snowflake, Databricks and Postgres support this feature. To use multi-schemas instead of sending the schema in the connection_uri set it in the schemas param, like this:
Example
curl -X 'POST' \
'<host>/api/v1/database-connections' \
-H 'accept: application/json' \
-H 'Content-Type: application/json' \
-d '{
"alias": "my_db_alias_identifier",
"use_ssh": false,
"connection_uri": "snowflake://<user>:<password>@<organization>-<account-name>/<database>",
"schemas": ["foo", "bar"]
}'