TentacleSQL
Query your HTTP endpoints data using SQL
Install
Using docker
docker run -p 8080 -ti factorialco/tentaclesql
As system command
npm i -g @factorialco/tentaclesql
Setup
To be able to start TentacleSQL you need to provide even SCHEMA_URL
or
SCHEMA_FILE
environment variable specifying the route to retrieve the schema
from.
Alternativelly you can provide your own schema into same POST request you run the query.
Examples:
SCHEMA_URL='https://example.com/api/schema'
SCHEMA_FILE='example.yaml' # Note that should be placed into `schemas` folder
This schema stucture is an array of all the table definitions available in your schema. Something like:
[
{
"name": "applications",
"url": "https://example.com/api/schema/tables/applications",
"fields": [
{
"key": "id",
"type": "number"
},
{
"key": "first_name",
"type": "text"
},
{
"key": "last_name",
"type": "text"
}
]
},
{
"name": "goals",
"url": "https://example.com/api/schema/tables/goals",
"fields": [
{
"key": "id",
"type": "number"
},
{
"key": "progress",
"type": "text"
},
{
"key": "application_id",
"type": "number",
"table": "applications",
"foreign_key": "id"
}
]
}
]
To see yaml example check schemas/example.yaml
.
Schema definition API
The schema definition needs to respond with an array of table definitions:
Table:
-
name
: Name of the table -
url
: URL to retrieve the data from -
autodiscover
: If you want to autodiscover fields from url response -
result_key
: Which key should be readed for array of results -
fields
: List of fields / foreign keys of this table
Each table will have an array of fields that can be raw fields or foreign key to denote relations between tables.
Field:
-
key
: Name of the column / field -
type
: Type of the field. Available types:text
,number
,data
andboolean
Foreign key:
-
key
: Name of the foreign key -
type
: Type of the field. Available types:text
,number
,data
andboolean
-
table
: Target table of the foreign key -
foreign_key
: Referenced column by the foreign key
Usage HTTP API
Once you have your tentaclesql server up and running you can use it by sending
POST requests against /
.
Example:
curl -H "Content-type: application/json" -X POST -d '{"query": "SELECT 1;"}' http://localhost:3000/
The expected payload contains the following parameters:
- query: The SQL query to be executed against the in-memory database.
- parameters: The parameters to be replaced in the query.
-
config: Configuration parameters.
- extensions: Array of extensions to enable. Check https://github.com/nalgeon/sqlean to see all the supported extensions and how to use them.
- schema: Manual schema definition
Usage CLI
Note that you must configure your schema first. You can pass an environment variable:
Example
SCHEMA_FILE=example.yaml tentaclesql interactive
Run interactive prompt
yarn cli interactive
# or installed global
tentaclesql interactive
Execute queries
yarn cli query "SELECT 1;"
# or installed global
tentaclesql query "SELECT 1;"
Bulk fetch
By default Tentacle sends one HTTP request for each table data, however you can change this and fetch all table data in one HTTP request. To enable this you need to pass following paramaters:
BULK_FETCH=true
BULK_FETCH_URL=url