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"]
 }'