# REST API v1 - Database API endpoints

> For the complete documentation index, see [llms.txt](https://developers.thoughtspot.com/docs/llms.txt)

Source: https://developers.thoughtspot.com/docs/database-api-v1

# REST API v1 - Database API endpoints

> **IMPORTANT:** The REST API v1 endpoints are deprecated and replaced with the REST API v2 endpoints. Your existing integrations with REST API v1 endpoints will continue to function without disruption until further notice. ThoughtSpot strongly recommends migrating your integrations to the REST API v2 endpoints.

The Database API endpoints allow you to get details of your databases and their schemas, create tables within a database, and run TQL commands in Falcon-based ThoughtSpot deployments.

To modify the schema of a database or add a new table to a database, you must have admin access to the database and its objects.

The `/tspublic/v1/database` API endpoints are applicable to ThoughtSpot Software deployments only.

## Supported operations

 
| API endpoint | Available from |
| --- | --- |
| 
`[**GET** /tspublic/v1/database/tables]({{navprefix}}/{{database-api}}#list-tables)`  
Gets a list of all tables in a database and schema in Falcon.

 | 

ThoughtSpot Cloud Not applicable  
ThoughtSpot Software 6.3.1

 |
| 

`[**GET** /tspublic/v1/database/schemas]({{navprefix}}/{{database-api}}#list-schemas)`  
Gets a list of all schemas in a particular database in Falcon.

 | 

ThoughtSpot Cloud Not applicable  
ThoughtSpot Software 6.3.1

 |
| 

`[**GET** /tspublic/v1/database/databases]({{navprefix}}/{{database-api}}#list-database)`  
Gets a list of all databases in Falcon.

 | 

ThoughtSpot Cloud Not applicable  
ThoughtSpot Software 6.3.1

 |
| 

`[**GET** /tspublic/v1/database/getTableDetail]({{navprefix}}/{{database-api}}#table-detail)`  
Gets details of a particular table.

 | 

ThoughtSpot Cloud Not applicable  
ThoughtSpot Software 6.3.1

 |
| 

`[**POST** /tspublic/v1/database/createtable]({{navprefix}}/{{database-api}}#create-table)`  
Creates tables in a database.

 | 

ThoughtSpot Cloud Not applicable  
ThoughtSpot Software 6.3.1

 |
| 

`[**POST** /tspublic/v1/database/run]({{navprefix}}/{{database-api}}#run-tql)`  
Runs TQL commands with SQL statements.

 | 

ThoughtSpot Cloud Not applicable  
ThoughtSpot Software 6.3.1

 |

## Get a list of tables in a database

To get a list of all tables in a database and schema, send a `GET` request to the `/tspublic/v1/database/tables` endpoint.

### Resource URL

GET /tspublic/v1/database/tables

### Request parameters

 
| Query parameter | Description |
| --- | --- |
| 
`database`

 | 

_String_. Name or GUID of the database from which you want to fetch the tables.

 |
| 

`schema`

 | 

_String_. Name of the database schema.

 |

### Example request

cURL

```cURL
curl -X GET \
--header 'Accept: application/json' \
--header 'X-Requested-By: ThoughtSpot' \
'https://ThoughtSpot-host>/callosum/v1/tspublic/v1/database/tables?database=usage_stats&schema=falcon_default_schema'
```

Request URL

https://{ThoughtSpot-Host}/callosum/v1/tspublic/v1/database/tables?database=usage\_stats&schema=falcon\_default\_schema

### Example response

If the request is successful, the API returns the tables for the specified database and schema:

```JSON
[
  "table_usage",
  "use_case"
]
```

### Response codes

 
| HTTP status code | Description |
| --- | --- |
| 
200

 | 

Successful operation.

 |
|  |  |

## Get a list of schemas in a database

To get a list of schemas in a database, send a `GET` request to the `/tspublic/v1/database/schemas` endpoint.

### Resource URL

GET /tspublic/v1/database/schemas

### Request parameters

 
| Query parameter | Description |
| --- | --- |
| 
`database`

 | 

_String_. Name or GUID of the database for which you want to fetch the schemas.

 |

### Example request

cURL

```cURL
curl -X GET \
--header 'Accept: application/json' \
--header 'X-Requested-By: ThoughtSpot' \
'https://{ThoughtSpot-Host}/callosum/v1/tspublic/v1/database/schemas?database=b87a99b8-4a1b-4d94-bd57-e1c9971722c8'
```

Request URL

https://{ThoughtSpot-Host}/callosum/v1/tspublic/v1/database/schemas?database=b87a99b8-4a1b-4d94-bd57-e1c9971722c8

### Example response

If the request is successful, the API returns the schema for the specified database.

```JSON
[
  "NPATH_PUBLIC_MuHAOPj"
]
```

### Response codes

 
| HTTP status code | Description |
| --- | --- |
| 
200

 | 

Successful operation

 |

## Get a list of databases

To get a list of all databases associated with your ThoughtSpot application instance, send a `GET` request to the `/tspublic/v1/database/databases` endpoint.

### Resource URL

GET /tspublic/v1/database/databases

### Request parameters

None

### Example request

cURL

curl -X GET \\
--header 'Accept: application/json' \\
--header 'X-Requested-By: ThoughtSpot' \\
'https://{ThoughtSpot-Host}/callosum/v1/tspublic/v1/database/databases'

Request URL

https://{ThoughtSpot-Host}/callosum/v1/tspublic/v1/database/databases

### Example response

If the request is successful, the API returns a list of databases associated with your ThoughtSpot instances.

```JSON
[
  "b7d11389-a8e1-4f8c-851b-816148fd6508",
  "usage_stats",
  "log_replayer",
  "analyze_scenarios",
  "a8bb3bcc-a53f-43bf-a192-a877e88c8196",
  "623617f2-ba5a-45a8-aa18-27a951939ece",
  "test123456y",
  "ac55d819-bd12-4a00-bec8-a6e0bcdf2843",
  "049f092f-5893-469f-a899-7f7ac732d417",
  "43fc17b4-e97c-4a7d-a19f-fd03cbc18d00",
  "DataConnect",
  "2b7c4240-159c-4cdd-95df-ce80ccc553f0",
  "599c4f73-940f-4cd8-9276-5f64089453aa",
  "MarketSpot",
  "53cd18f9-b0af-4223-9e93-d50657e77fda"
 ]
```

### Response codes

 
| HTTP status code | Description |
| --- | --- |
| 
200

 | 

Successful operation

 |

## Get details of a table

To get details of a particular table, send a `GET` request to the `/tspublic/v1/database/databases` endpoint.

### Resource URL

GET /tspublic/v1/database/getTableDetail

### Request parameters

 
| Query parameter | Description |
| --- | --- |
| 
`database`

 | 

_String_. Name or GUID of the database.

 |
| 

`schema`

 | 

_String_. Name of the schema.

 |
| 

`table`

 | 

_String_. Name of the table.

 |

### Example request

cURL

curl -X GET \\
--header 'Accept: application/json' \\
--header 'X-Requested-By: ThoughtSpot' \\
'https://{ThoughtSpot-Host}/callosum/v1/tspublic/v1/database/getTableDetail?database=usage\_stats&schema=falcon\_default\_schema&table=table\_usage'

Request URL

https://{ThoughtSpot-Host}/callosum/v1/tspublic/v1/database/getTableDetail?database=usage\_stats&schema=falcon\_default\_schema&table=table\_usage

### Example response

If the request is successful, the API returns the table details such as columns and joins.

```JSON
{
  "column": [
    {
      "data_type": "TYPE_VAR_CHAR",
      "size": 0,
      "id": {
        "guid": "0197db8b-b2d8-4df8-b18d-3832624e8bdd",
        "name": "incident_id"
      },
      "datetime": "TYPE_NOT_DATE",
      "compression_scheme": "AUTO"
    },
    {
      "data_type": "TYPE_VAR_CHAR",
      "size": 0,
      "id": {
        "guid": "25b86811-3afb-4822-9d77-4b20fe6fdbd1",
        "name": "table_name"
      },
      "datetime": "TYPE_NOT_DATE",
      "compression_scheme": "AUTO"
    },
    {
      "data_type": "TYPE_VAR_CHAR",
      "size": 0,
      "id": {
        "guid": "a8b96637-a027-40ca-b55e-6a9e14233606",
        "name": "column_name"
      },
      "datetime": "TYPE_NOT_DATE",
      "compression_scheme": "AUTO"
    },
    {
      "data_type": "TYPE_VAR_CHAR",
      "size": 0,
      "id": {
        "guid": "f90407d8-c566-4c73-895a-646eb436db11",
        "name": "column_guid"
      },
      "datetime": "TYPE_NOT_DATE",
      "compression_scheme": "AUTO"
    },
    {
      "data_type": "TYPE_INT64",
      "size": 0,
      "parsing_hint": {
        "date_format": "%Y%m%d %H:%M:%S"
      },
      "id": {
        "guid": "7b1bdfd6-597e-41cb-8410-4dce9c1ad3c1",
        "name": "upload_ts"
      },
      "datetime": "TYPE_DATE_TIME",
      "compression_scheme": "AUTO"
    }
  ],
  "id": {
    "guid": "f5431116-a86c-40c1-9f4b-3e52374b0a82",
    "name": "table_usage"
  },
  "version": 0,
  "primary_key": {
    "column": [
      {
        "guid": "0197db8b-b2d8-4df8-b18d-3832624e8bdd",
        "name": "incident_id"
      },
      {
        "guid": "f90407d8-c566-4c73-895a-646eb436db11",
        "name": "column_guid"
      }
    ]
  },
  "relation": [
    {
      "type": "FOREIGNKEY",
      "column": [
        {
          "source": {
            "guid": "25b86811-3afb-4822-9d77-4b20fe6fdbd1",
            "name": "table_name"
          },
          "target": {
            "guid": "ad402f95-d112-4d5a-a0eb-d7615adf9463",
            "name": "table_name"
          },
          "target_table": {
            "name": "use_case"
          },
          "target_schema": {
            "name": "falcon_default_schema"
          },
          "target_database": {
            "name": "usage_stats"
          },
          "source_table": {
            "name": "table_usage"
          },
          "source_schema": {
            "name": "falcon_default_schema"
          },
          "source_database": {
            "name": "usage_stats"
          }
        }
      ],
      "id": {
        "guid": "d61220ae-b150-49a1-98e0-d6d4780db8df"
      }
    }
  ]
}
```

### Response codes

 
| HTTP status code | Description |
| --- | --- |
| 
200

 | 

Successful operation

 |

## Create tables

To create tables in a database, send a `POST` request to the `/tspublic/v1/database/createtable/` API endpoint.

### Resource URL

POST /tspublic/v1/database/createtable/

### Request parameters

 
| Form parameter | Description |
| --- | --- |
| 
`createdb`

 | 

_Boolean_. Set this flag to `true` to create tables with a database and schema. If you are adding a table to an existing database and schema, you can set `createdb` to false.

 |
| 

`ddl_schema`

 | 

_String_. The schema of the request that needs to be generated.

 |

### Example request

cURL

```cURL
curl -X POST \
--header 'Content-Type: application/x-www-form-urlencoded' \
--header 'Accept: application/json' --header 'X-Requested-By: ThoughtSpot' -d 'create_db=true&ddl_schema={"database":{"name":"DataFlow_DB"},"schema":{"name":"falcon_default_schema"},"table":{"id":{"name":"test_table1"},"primary_key":[{"name":"test_pk"}],"column":[{"id":{"name":"test_pk"},"size":0,"data_type":"TYPE_INT32"},{"id":{"name":"test_col1"},"size":0,"data_type":"TYPE_FLOAT"},{"id":{"name":"test_col2"},"data_type":"TYPE_INT64","datetime":"TYPE_DATE"}{"id":{"name":"test_col3"},"size":10,"data_type":"TYPE_VAR_CHAR"}]}}'
'https://{ThoughtSpot-Host}/callosum/v1/tspublic/v1/database/createtable'
```

Request URL

https://{ThoughtSpot-Host}/callosum/v1/tspublic/v1/database/createtable

#### Example DDL schema for table creation

```JSON
{
   "database":{
      "name":"DataFlow_DB"
   },
   "schema":{
      "name":"falcon_default_schema"
   },
   "table":{
      "id":{
         "name":"test_table1"
      },
      "primary_key":[
         {
            "name":"test_pk"
         }
      ],
      "column":[
         {
            "id":{
               "name":"test_pk"
            },
            "size":0,
            "data_type":"TYPE_INT32"
         },
         {
            "id":{
               "name":"test_col1"
            },
            "size":0,
            "data_type":"TYPE_FLOAT"
         },
         {
            "id":{
               "name":"test_col2"
            },
            "data_type":"TYPE_INT64",
            "datetime":"TYPE_DATE"
         }{
            "id":{
               "name":"test_col3"
            },
            "size":10,
            "data_type":"TYPE_VAR_CHAR"
         }
      ]
   }
}
```

### Example response

If the request is successful, the API returns the following response:

```JSON
{
"logical_table_header": {
"id_guid": "322d9ff5-3d2a-4f40-b2cc-3e2c23a1ac55",
"name": "test_table1",
"author_guid": "59481331-ee53-42be-a548-bd87be6ddd4a",
"author_name": "tsadmin",
"author_display_name": "Administrator",
"created": 1638538990887,
"modified": 1638538991877,
"modified_by": "59481331-ee53-42be-a548-bd87be6ddd4a",
"generation_num": 500,
"owner_guid": "322d9ff5-3d2a-4f40-b2cc-3e2c23a1ac55",
"deleted": false,
"hidden": false,
"database_stripe": "DataFlow_DB",
"schema_stripe": "falcon_default_schema",
"type": "ONE_TO_ONE_LOGICAL",
"metadata_type": "LOGICAL_TABLE"
},
"physical_table_id": "a97af543-b122-4e1a-b425-bffc4ad3dbe4"
}
```

### Response codes

 
| HTTP status code | Description |
| --- | --- |
| 
200

 | 

Successful operation

 |
| 

400

 | 

Invalid parameter values

 |

## Run a given TQL command

To perform administrative tasks directly in the database, you can use the ThoughtSpot SQL Command Line (TQL). TQL supports many, but not all, common SQL commands.

The `/tspublic/v1/database/run` allows you to run TQL commands to create or update a database schema.

### Resource URL

POST /tspublic/v1/database/run

### Request parameters

 
| Form parameter | Description |
| --- | --- |
| 
`sqlstatements`

 | 

_String_. A JSON array of SQL statements separated by a semicolon (;).

 |

### Example request

cURL

```cURL
curl -X POST \
--header 'Content-Type: application/x-www-form-urlencoded' \
--header 'Accept: application/json' \
--header 'X-Requested-By: ThoughtSpot' \
-d 'sqlstatements=["alter table geo.falcon_default_schema.test_table434 add column test_col17 varchar(10) DEFAULT '';";"alter table geo.falcon_default_schema.test_table434 add column test_col18 varchar(10) DEFAULT '';"]' \
'http://{ThoughtSpot-Host}/callosum/v1/tspublic/v1/database/run'
```

Request URL

https://{ThoughtSpot-Host}/callosum/v1/tspublic/v1/database/run

### Example response

If the request is successful, the API returns the SQL statements in the response body.

```JSON
[
  {
    "sqlStatement": "alter table geo.falcon_default_schema.test_table434 add column test_col17 varchar(10) DEFAULT '';",
    "cmdStatus": "FAILED",
    "errorMsg": "Error Code: INVALID_ARGUMENT Incident Id: 8e9c61bc-0261-4690-a28e-daf621803355\nError Message: Error while getting server schema",
    "errorCode": {
      "name": "INVALID_ARGUMENT",
      "ordinal": 9
    }
  },
  {
    "sqlStatement": "alter table geo.falcon_default_schema.test_table434 add column test_col18 varchar(10) DEFAULT '';",
    "cmdStatus": "NOT_EXECUTED"
  }
]
```

### Response codes

 
| HTTP status code | Description |
| --- | --- |
| 
200

 | 

Successful operation

 |
| 

500

 | 

Invalid input or server error

 |