Datastore
The Datastore allows you to create custom tables on DroneDeploy. There are two parts to creating a Datastore table:
- 1.Creating the table
- 2.Creating columns
We highly recommend that you use the CLI for App development instead of building out tables via the API.
Creating a Datastore table is straightforward. You use a GraphQL mutation query as below. Then you enter in the following input values:
- 1.applicationId: This is the Id of your App.
- 2.name: This will be the name of your table.
- 3.description: This is a description you give about the table.
mutation CreateTable($input: CreateTableInput!) {
createTable(input: $input) {
table {
id
application {
id
}
name
description
}
}
}
{
"input": {
"applicationId": "Application:lonvecnbfyvovfqsvbxz",
"name": "Table Name",
"description": "Table Description"
}
}
The results should look something like this:
{
"data": {
"createTable": {
"table": {
"id": "Table:5b6bd03d0461f4000108c777",
"application": {
"id": "Application:lonvecnbfyvovfqsvbxz"
},
"name": "Table Name",
"description": "Table Description"
}
}
}
}
Your table now needs columns.
Creating a column is very similar to creating the table itself. You use a GraphQL mutation with the following input values:
- 1.tableId: This is the Id of the table you created above.
- 2.
- 3.name: This will be the name of your table column.
- 4.description: Give your column a description for easier reference.
Note that certain column types have specific inputs. For example, for the TEXT column type, you can specify
textLength
and even whether or not you want that column to be textEncrypted
.mutation CreateTableColumn($input: CreateTableColumnInput!) {
createTableColumn(input: $input) {
tableColumn {
id
name
description
... on NumberColumn {
type
}
... on TextColumn {
length
}
}
}
}
{
"input": {
"columnType": "TEXT",
"name": "name",
"textLength": 255,
"textEncrypted": false,
"tableId": "Table:5b6bd03d0461f4000108c777",
"description": "Users name"
}
}
The results should look something like this:
{
"data": {
"createTableColumn": {
"tableColumn": {
"id": "TextColumn:5ade569b2a3c590001231bbb",
"name": "name",
"description": "Users name",
"length": 255
}
}
}
}
Now that you have a Datastore table, you can store your own custom data! Similar to creating a Datastore table and creating a Datastore column, you will create a GraphQL query for creating Datastore data.
The inputs to this query are as follows: 1. externalId: This is the Id of the data that you can use to reference and retrieve it later. NOTE: There is a 36 character limit to this field. 1. tableId: This is the Id of the table you created above. 1. data: This is the data you want to store in stringified JSON format. Note that each Datastore table column is a JSON key. In this example, that would be the
name
column.mutation CreateTableData($input: CreateTableDataInput!) {
createTableData(input: $input) {
tableData {
id
application {
id
}
data
externalKey
table {
id
}
}
}
}
{
"input": {
"externalId": "[email protected]",
"tableId": "Table:5b6bd03d0461f4000108c777",
"data": "{ \"name\": \"DroneDeploy Developer\" }"
}
}
The results should look something like this.
{
"data": {
"createTableData": {
"tableData": {
"id": "TableData:5b5141ca09867c000116a15e",
"application": {
"id": "Application:lonvecnbfyvovfqsvbxz"
},
"data": "{\"name\": \"DroneDeploy Developer\"}",
"externalKey": "[email protected]",
"table": {
"id": "Table:5b6bd03d0461f4000108c777"
}
}
}
}
}
Now let's retrieve the data that we created. There are 3 different approaches to retrieving data from the table:
This query takes two inputs: 1. externalKey: This is the externalId that you passed into the Datastore data creation query. In this example, this was [email protected] 1. tableId: This is the Id of the table you created above.
query ($tableId: ID!, $externalKey: String!) {
node(id: $tableId) {
... on Table {
tableDatum(externalKey: $externalKey) {
data
}
}
}
}
The results should look something like this.
{
"data": {
"node": {
"tableDatum": {
"data": "{\"name\": \"DroneDeploy Developer\"}"
}
}
}
}
You can retreve all of the data from the table by using the
TableDataConnection
object. Like any standard Relay Connection, you can supply different paging parameters to retrieve a slice of the data (i.e. for displaying rows in a grid).This query takes three inputs: 1. tableId: The ID of the table 1. first: The first N records 1. after: The ID of the record to start returning
query($table_id:ID!,$first:Int!,$after:String!) {
node(id:$table_id) {
... on Table {
rows(first: $first, after: $cursor) {
edges {
cursor
node {
externalKey
data
dateCreation
dateModified
}
}
pageInfo {
hasNextPage
}
}
}
}
}
{
"tableId": "Table:5b6bd03d0461f4000108c777",
"first": 2,
"after": "YXJyYXljb25uZWN0aW9uOjA="
}
The results should look something like this.
{
"data": {
"node": {
"rows": {
"edges": [
{
"cursor": "YXJyYXljb25uZWN0aW9uOjA=",
"node": {
"data": "{\"name\": \"Jane Doe\", \"age\": 32}",
"dateCreation": "2018-01-01T20:01:23",
"dateModified": "2018-01-01T20:11:23",
"externalKey": "[email protected]"
}
},
{
"cursor": "YXJyYXljbSUEj3N0aW9uOjA=",
"node": {
"data": "{\"name\": \"John Doe\", \"age\": 29}",
"dateCreation": "2018-01-01T20:01:23",
"dateModified": "2018-01-01T20:11:23",
"externalKey": "[email protected]"
}
},
],
"pageInfo": {
"hasNextPage": false
}
}
}
}
}
Sometimes you need to select data from your tables by querying columns other than external keys. Datastore provides the ability to use a subset of standard ANSI-92 SQL to query your tables. Let's assume that we've created two columns on our table - name (a string field) and age (a number field that is an integer). To query that field, you would populate the
query
parameter of the TableDataConnection
object:query($table_id:ID!,$query:String!) {
node(id:$table_id) {
... on Table {
rows(query: $query) {
edges {
cursor
node {
externalKey
data
dateCreation
dateModified
}
}
pageInfo {
hasNextPage
}
}
}
}
}
{
"tableId": "Table:5b6bd03d0461f4000108c777",
"query": "select name, age where name = 'Jane Doe' and age > 22"
}
The results should look something like this:
{
"data": {
"node": {
"rows": {
"edges": [
{
"cursor": "YXJyYXljb25uZWN0aW9uOjA=",
"node": {
"data": "{\"name\": \"Jane Doe\", \"age\": 32}",
"dateCreation": "2018-01-01T20:01:23",
"dateModified": "2018-01-01T20:11:23",
"externalKey": "[email protected]"
}
}],
"pageInfo": {
"hasNextPage": false
}
}
}
}
}
You'll notice that in our SQL query, we did not define a
from
clause. That is because the query is limited ot the scope of the table that belongs to the connection.As mentioned above, a subset of standard SQL is allowed. The following describes the supported operators:
Comparison Operators
Operator | Supported |
= | Yes |
!= | Yes |
<> | Yes |
> | Yes |
< | Yes |
=< | Yes |
>= | Yes |
!< | No |
>! | No |
Logical Operators
Operator | Supported |
ALL | No |
AND | Yes |
ANY | No |
BETWEEN | Yes |
EXISTS | No |
IN | Yes |
LIKE | Yes |
NOT | Yes |
OR | Yes |
IS NULL | Yes |
UNIQUE | No |
Arithmetic Operators
Operator | Supported |
+ | No |
- | No |
* | No |
/ | No |
- Subselects, joins, and aggregate functions are not currently supported.
- You cannot run a query against an encrypted column
Last modified 6mo ago