This page contains information for developers wishing to update or maintain the SQL Proxy endpoint on the dbconnector service.
Developer Setup
The code for the dbconnector is stored in the AWS codecommit cob_dbconnector repository (CoB Digital account).
Typically the code is cloned from that repository onto the developers local machine. In order do this, AWS credentials must set up correctly. See these notes. The AWS-CLI is required, so it must be installed locally, and credentials must be set in the ~/.aws/credentials
file.
This command will clone the develop
branch ofcob_dbconnector
application into a folder at ~/sources/cob_dbconnector
. The file may be cloned anywhere on the local computer, however this document assumes it will cloned into ~/sources/cob_dbconnector
and if it is not, then commands given here will need to be modified for the new location.
Once the repo is cloned, then the following command will build the app, build its container locally and tag the container:
The script will create a docker container, build the necessary files from the local cloned folders and then save and tag the docker image.
The developer can then use docker-compose to start the container as follows:
This will build the docker container locally, start it and name it dbconnector
.
Once the steps in "Developer Setup" above have been completed, the developer can go ahead and change the code as needed in the cloned repository.
In this example the code is found in ~/sources/cob_dbconnector/src
.
Best practice is to create a working branch off the develop
branch and then make commit changes to the working branch as needed. Once ready to push and merge code back to the repo in CodeCommit, the working branch can be pushed, and then a PR made and committed. For example, to add a period to the end of the README.md file:
Now login to AWS and find the cob_dbconnector
repo in CodeCommit. Create a PR from the working_branch
into develop
. Use approval workflows as needed.
Unlike the Drupal deploy process (where the deploy is triggered by commits to the GutHub repo) there are no triggers fired by committing, pushing, tagging or merging to the cob_dbconnector CodeCommit repository.
Deployment is triggered by updates to the ECR repository.
Once changes have been made and saved in the src
folder, the application needs to be re-built and the container re-deployed in order to verify the changes work as expected.
The repository files on the host are NOT mounted into the container. Therefore changes to code on the host machine are not automatically replicated to the container, even if a local build is forced (e.g. running npm run dev
inside the container) . In order to "load" changed code into the container, it must be rebuilt.
To re-build and re-deploy the container:
Or more concisely (ensure you are in the correct folder):
Each time you build the container using build.sh
, a new local image is created and tagged. The old image is not automatically deleted, so over time you will get a lot of images saved on your local computer, taking up disk space.
Which this is nice if you want to manually create a new container from a previous image, mostly its a nuisance and just takes up local disk space.
You can delete all DBConnector images which do not have a tag.
Because the dbconnector is an API designed to be used via an REST endpoint, the best tool for testing is either a custom written testbed which calls the container, or else a tool like postman.
To connect to the container for testing, you will need to know the containers IPAddress. This can be found as follows:
The IPAddress will typically be something like 172.x.0.x
, depending on your docker setup. Locally the service runs on port 3000, and the container has port 3000 exposed.
https://<IPAddress>:3000/ should be used as the endpoint for testing locally: For example In postman run a test GET request on the URL :
This should return an empty json message {}
with an HTTP Code of 200. If you get anything else there is an issue.
Using docker you can attach to the running console for the dbconnector container and get a tail of the stdout and stderr in real-time. This is useful because you can add console.log()
commands to the code and then see them appear in the attached console.
Use this command to attach to the console:
Using docker you can open a new bash shell in the container and run commands in the console for that session. You could use this to verify the existence of files, or to check if dependencies are installed etc. This command creates an interactive shell:
If you dont need an interactive shell, you just want to run a single command, you can use this:
There are a suite of automated tests created for the dbconnector. These tests are run locally by the developer, and the tests can be extended.
The stock tests adequately test all the endpoints for the service, and as new functionality or features are added, the tests should be expanded to ensure they are up to date.
The tests are run using this command:
The tests are defined in the file src/tests/tests.config.js
.
There are 2 remote environments an AWS, stage
and production
.
To deploy to either, its a simple matter of creating a stage or production build and then pushing the image to the ECR registry. A watch process monitors the ECR repository and changes to the container images (uploads or re-tags) automatically prompts deploys to the ECR environment.
For the DBConnector there is a helper script to build, tag and push container images:
This is all that is needed to initiate a deploy.
The might be the way a developer works:
follow setup instructions here
create a local working branch from develop
branch
commit code locally to working branch
... iterate these steps as necessary
commit code and push to AWS Code Commit
create PR on CodeCommit (follow peer review and other related project steps) and then merge PR (on CodeCommit)
build and deploy stage version (stage)
advise external testers
... iterate back through development stage steps as needed.
build and depoy production (production)
Add solutions to issues as you encounter them here.
This page contains information for developers wishing to update or maintain the pdf endpoint on the dbconnector service.
Also see SQLProxy setup notes
Also see SQLProxy testing notes
Also see SQLProxy deployment notes
The PDF Chain tool for Ubuntu has a nice interface which utilizes the features of the PDF Toolkit which is used by the DBConnector. This is particularly useful for extracting fdf data files from fillable forms, which can then be manually edited and fed back into the form for in-dev-testing.
The non-fillable PDF generator has a maximum PDF version of 1.5. Many PDF generators, including fillable PDF forms save in version 1.6.
Version 1.6 works fine for the fillable PDF processes but using a v1.6 PDF docs cause the non-fillable PDF process to fail for the PDF generator.
To downgrade a PDF from v1.6 to v1.5, use the following gostscript command on Ubuntu:
You can determine the version using this command:
The City have a number of Data services (e.g. SQL Servers) that reside on the city network. There is no mechanism by which data can be accessed from these services from processes that are not resident on the Cityhall network. This includes boston.gov and other cloud based services.
The DBConnector
micro-service is available at:
The first step is to post to the /v1/auth endpoint, providing username and password credentials.
If authenticated, an Authentication Token, and Refresh Token will be returned.
By default, the Authentication Token (authToken) is valid for 180 seconds, and the Refresh Token for 900 seconds.
When your account was setup, a different lifetime may have been specified for authTokens issued using your credentials.
The authToken inherits role-based permissions that have been assigned to your credentials.
The authToken is then passed as a "bearer token" in the "Authroization" header - and can be used multiple times until it expires.
Example request header
When the Authentication Token expires, the Refresh Token can be passed to the /v1/auth/refresh endpoint and a new Authentication Token will be returned with a 180 second lifetime. A new Refresh Token will also be generated and returned with a 900 second lifetime. You could also just re-authenticate on the /v1/auth endpoint, but using the refresh token is faster and more efficient in the back-end because the user is not re-validated (using the DBConnector database) -saving database connection overhead.
If you have not saved or been given a connToken (which is a uuid representing a connection string) then the connToken can be obtained from the /v1/connections/:name endpoint.
Note: You will need to pass the authToken in header.
Depending on the role attached to the authToken (see User Permissions), you can use the /select, /insert, /update, /delete and /query endpoints to interact with data on the remote host defined by a connToken.
Notes: 1. You will need to pass the authToken in the "Authorization" header of your request. 2. You will need to pass the connToken (and other parameters) in the body/payload of your request
Results from all endpoints will be returned in JSON format.
The ECS requires that an application have an endpoint /admin/ok
which returns an HTTP Status Code of 200 when queried. The ECS container management functions use this to determine if the container is healthy or not. After some time, if that endpoint does not respond or return a 200, the task is stopped and restarted.
GET
This endpoint must return a 200 code when the task (instance of the dbconnector service) is running. A non 200 code will cause the task to be stopped.
POST
https://dbconnector.digital-staging.boston.gov/v1/auth
This endpoint is used to initially authenticate the user, and returns an Authentication Token which must be used in the header of all subsequent endpoint calls. - The Auth Token has a default lifetime of 180 seconds (3 min) (can be set per username), - The Refresh Token has an additional validity of 180 seconds (3 min).
POST
https://dbconnector.digital-staging.boston.gov/v1/auth/refresh
Using a valid Refresh Token (provided from /v1/auth endpoint), this endpoint will refresh a current (or expired) Authentication Token. Also generates a new Refresh Token.
The following errors can be raised from calls to the various endpoints.
400: Bad Request
400 errors usually indicate some issue with the body or querystring submitted to the endpoint.
A JSON string is returned with an error node in it. The error description is a short explanation of the issue encountered.
401: Unauthorised
Generally, 401 errors are returned when there is an issue with the AuthToken provided in he header.
403: Forbidden
When a user attempts to perform a task with insufficient permissions, a 403 error is generated. The 403 Errors raised by DBConnector
typically do not provide much information, but errors are logged. Typical 403 errors are:
Authenticating account or using token from an unregistered IPAddress
Account has insufficient permissions to perform requested task (see Permission description for each endpoint in this guide and also User Permissions section)
200:OK - Flooding
If too many requests have been made by a user in a given time period, then the user will be blocked and a 200 response code will be returned with an error message.
GET
https://dbconnector.digital-staging.boston.gov/v1/users
Returns a list of current users.
Permission: ADMIN or OWNER.
If the optional page
& list
parameters are provided, then a paged output will be returned (sorted by UserID)
GET
https://dbconnector.digital-staging.boston.gov/v1/users/:useridentifier
Returns a single user.
Permission: ADMIN, OWNER or the user specified in useridentifier
.
i.e. You can only read your own user record unless you are an ADMIN or OWNER.
GET
https://dbconnector.digital-staging.boston.gov/v1/users/:useridentifier/connections
Provides a list of connection strings that the user has been granted permission to use.
Permission: ADMIN, OWNER or user defined by useridentifier
.
i.e. You can only read your own user record unless you are an ADMIN or OWNER.
POST
https://dbconnector.digital-staging.boston.gov/v1/users
Adds a new user. Permission: ADMIN or OWNER.
PATCH
https://dbconnector.digital-staging.boston.gov/v1/users/:useridentifier
Updates the specified user with information provided in the payload. Permission: ADMIN or OWNER
DELETE
https://dbconnector.digital-staging.boston.gov/v1/users/:userid
Deletes the specified user. Permission: ADMIN or OWNER
A connection string record contains all the information required for a suitable driver to connect to a remote system.
Each connection string record is defined by a unique UUID (the connToken) -and also a unique name.
The connToken is used to refer to the remote system in execution endpoints so that connectivity details do not need to be stored in and passed from the calling system.
The connToken (UUID) should never change once the connections string record is created, and therefore can be safely stored in the calling system.
GET
https://dbconnector.digital-staging.boston.gov/v1/connections
Returns a list of all active remote system connection strings. Permission: ADMIN OR OWNER
GET
https://dbconnector.digital-staging.boston.gov/v1/connections/:token
Returns the remote system connection string defined by the specified Connection Token. Permission: ADMIN, OWNER or by a user who has permission to use the connection.
GET
https://dbconnector.digital-staging.boston.gov/v1/connections/find/:name
Fetch a connection token using the tokens name. Permission: All authenticated users.
GET
https://dbconnector.digital-staging.boston.gov/v1/connection/:token/users
Provides a list of users who have been granted permission to use a connection string. Permission: ADMIN or OWNER
POST
https://dbconnector.digital-staging.boston.gov/v1/connection
Saves a connection string. Permission: ADMIN or OWNER.
POST
https://dbconnector.digital-staging.boston.gov/v1/connection/:token/user/:userid
Grants a user permission to use a connection string. Permission: FULL/SUPER, ADMIN or OWNER.
PATCH
https://dbconnector.digital-staging.boston.gov/v1/connections/:token
Updates the remote system connection string defined by the specified Connection Token. Permission: ADMIN or OWNER..
DELETE
https://dbconnector.digital-staging.boston.gov/v1/connections/:token
Deletes the remote system connection string defined by the specified Connection Token. Permission: ADMIN or OWNER
DELETE
https://dbconnector.digital-staging.boston.gov/v1/connection/:token/user/:userid
Revoke an existing permission for a user to use a connection string. Permission: ADMIN or OWNER
Running data commands on a Remote System.
Errors which occur whilst executing an data command on the host server are passed back as cleanly as possible. Except for the /query endpoint, syntax errors should not occur.
Errors which might occur include:
connection string errors (credentials, host DNS/IPAddress etc),
incorrectly named tables and/or incorrectly named fields,
insufficient permissions to perform task on host,
creating duplicate records,
table locks,
foreign key constraints,
... etc ...
Actual error messages depend upon the drivers being used, and the wording of error reporting from the host. Generally a 400 error will be generated with a "cleaned" error message in this general JSON format:
POST
https://dbconnector.digital-staging.boston.gov/v1/query/:driver
Runs a command (or commands) on the remote system. Depending on the command/s, returns a JSON Array (of Arrays) of Objects.
To allow statements to be dynamic/re-used, the statement field may contain "named tokens" which will be substituted into the statement prior to execution. This creates a kind of stored procedure.
For Example:
Expands out to:
Calling Views
You can call views using the /query
endpoint (and also the /select
endpoint).
Treat a view like a table.
POST
https://dbconnector.digital-staging.boston.gov/v1/exec/:driver
Execute a stored procedure on the remote system. Permission: ALTER, FULL, ADMIN or OWNER
POST
https://dbconnector.digital-staging.boston.gov/v1/select/:driver
This runs a command on the remote system which is expected to return a paged data set in a JSON Array of Objects format.
POST
https://dbconnector.digital-staging.boston.gov/v1/insert/:driver
This endpoint creates a new record (or records) in the specified table.
POST
https://dbconnector.digital-staging.boston.gov/v1/update/:driver
This endpoint will update existing records in a table.
POST
https://dbconnector.digital-staging.boston.gov/vi/delete/:driver
Each user account defined in DBConnector has an assigned role.
Generally, the roles are hierarchical, so for example: a FULL-QUERY-USER can perform all the tasks a READ-USER can perform.
In the filter fields for the /select
, /update
and /delete
endpoints, the following directives can be used:
Why use an array of objects and not an object?
Because some filters might define the same field twice, which would not make sense in an object.
e.g. Suppose we wanted to run this: SELECT * FROM MyTable WHERE name ='a' OR name = 'b' or name ='c';
To accommodate the filter as an object we would have
{ "name": "a", "^name": "b", "^name": "c"}
which is not a valid structure.
So we use an array of objects thus:
[ {"name": "a"}, {"^name": "b"}, {"^name": "c"} ]
which is a valid structure.
The endpoint can be tested using some test data pre-loaded into the application.
The credentials to use are:
These credentials can be used with the connToken
Data contained in testTable available from connStr #4
Microservices are (usually API style) services or applications which perform a specific task that other web services or applications can leverage. They are typically middleware type components that enable:
Simplification of a web application (or service) by separating out specific utility tasks into a separate component, and/or
Standardization and provision of the same process that can be used by multiple web applications or other services, and/or
Predefined information or processes to bridge (or be shared between) organizations without direct network connections.
The requirement was to:
Complete a form PDF inserting data into the fields (rather than just stamping un-editable text on the form), and
Add a barcode and text to an existing PDF form, and output the resultant PDF as a form (the v1 PDFManager using a PHP solution always output a flat non-form PDF, even if a form PDF is used as the input).
This could not be achieved (in 2022) using an opensource PHP module, but there is a well established and proven Linux CLI app which can be utilized, and provided a couple of additional features to the requirement.
The main Drupal site (served by an Acquia webserver), while running on Linux is not managed by City of Boston and the pdftk libraries are not loaded on that server. Given the short time constraints, the pdftk was deployed within the same container as the DBConnector, leveraging the existing endpoint services (node/javascript/express) and some shellscripting.
The dbconnector service was extended to provide the following endpoints:
GET
/v1/pdf/heartbeat
GET
/v1/pdf/test
Internally calls the pdftk and captures the version of the cli.
POST
/v1/pdf/fill
A PDF and data file must be provided. The PDF must be a fillable form PDF and the data file must be a file in an FDF format.
The /v1/pdf/generate_fdf endpoint can be used to generate a blank FDF data file.
POST
/v1/pdf/overlay
POST
/v1/pdf/metadata
GET
/v1/pdf/decompress
This is a useful utility to use the PDFManager cannot manipulate a PDF because its compression is later than PDF1.5.
The endpoint first checks to see if it already has a file with the filename specified in the pdf_file
query parameter. If it does, then it just returns that file.
NOTE: restarting the dbconnector task(s) on AWS will empty this cache.
If the del
parameter is "true" then the file is deleted after decompression and downloading. To reduce load on the endpoint, set to "false" if the pdf_file
does not change often and if you expect to call the function frequently.
Returns the decompressed document as an attachment.
The expected headers are:
GET
/v1/pdf/fetch
Returns the document as an attachment.
When show=D, expected headers are:
The when show=I, expected headers are:
Name | Type | Description |
---|---|---|
Name | Type | Description |
---|---|---|
Name | Type | Description |
---|---|---|
Name | Type | Description |
---|---|---|
Name | Type | Description |
---|---|---|
Name | Type | Description |
---|---|---|
Name | Type | Description |
---|---|---|
Name | Type | Description |
---|---|---|
Name | Type | Description |
---|---|---|
Name | Type | Description |
---|---|---|
Name | Type | Description |
---|---|---|
Name | Type | Description |
---|---|---|
Name | Type | Description |
---|---|---|
Name | Type | Description |
---|---|---|
Name | Type | Description |
---|---|---|
Name | Type | Description |
---|---|---|
Name | Type | Description |
---|---|---|
Name | Type | Description |
---|---|---|
Name | Type | Description |
---|---|---|
Name | Type | Description |
---|---|---|
Name | Type | Description |
---|---|---|
Name | Type | Description |
---|---|---|
Name | Type | Description |
---|---|---|
Name | Type | Description |
---|---|---|
Name | Type | Description |
---|---|---|
Name | Type | Description |
---|---|---|
Name | Type | Description |
---|---|---|
Name | Type | Description |
---|---|---|
Name | Type | Description |
---|---|---|
Name | Type | Description |
---|---|---|
Name | Type | Description |
---|---|---|
Name | Type | Description |
---|---|---|
Name | Type | Description |
---|---|---|
Name | Type | Description |
---|---|---|
Name | Type | Description |
---|---|---|
Name | Type | Description |
---|---|---|
Name | Type | Description |
---|---|---|
Name | Type | Description |
---|---|---|
Name | Type | Description |
---|---|---|
Name | Type | Description |
---|---|---|
Name | Type | Description |
---|---|---|
Name | Type | Description |
---|---|---|
Name | Type | Description |
---|---|---|
Name | Type | Description |
---|---|---|
Name | Type | Description |
---|---|---|
Name | Type | Description |
---|---|---|
Name | Type | Description |
---|---|---|
Name | Type | Description |
---|---|---|
Name | Type | Description |
---|---|---|
Name | Type | Description |
---|---|---|
Name | Type | Description |
---|---|---|
As part of the creation, a method was required that would allow the manipultaion of fillable (form) PDF's.
Name | Type | Description |
---|
Name | Type | Description |
---|
Name | Type | Description |
---|
Name | Type | Description |
---|
Name | Type | Description |
---|
Environment
API Base URL
test
https://dbconnector.digital-staging.boston.gov
prod
https://dbconnector.boston.gov
Term
Meaning
AuthToken
A token which is generated when a user successfully authenticates against the /v1/auth endpoint and starts a session.
This token is used for all subsequent calls to the endpoint during a session.
The AuthToken has a lifetime which is typically 180s. After that the AuthToken expires and needs to be refreshed.
RefreshToken
A token which can be used to generate a new AuthToken without re-authenticating. A new AuthToken with a new lifetime of 180s can be generated at /v1/auth/refresh.
The RefreshToken is generated at the same time as the AuthToken and has a lifetime of 900s.
After the RefreshToken expires, the only way to generate an AuthToken is to authenticate against the /v1/auth endpoint.
The benefit of the RefreshToken is to ease load on the database server as AuthToken regeneration at /v1/auth/refresh endpoint does not require a database request,
User Account
A user account is required to authenticate, and is identified by a username and password. A user account may be only allow connections from a specified IPAddress, and will only be allowed to use certain ConnTokens.
username/userid
Each user account has a unique string and numeric identifier. The username is the string identifier, it must be unique and may be an email address. The userid is a system-generated number which can be used to identify the user in some endpoint operations.
ConnectionString
The DBConnector
connects to remote (database) environments which are either publicly available, or are housed within the City of Boston network. To connect to an environment, a connection string is required. Typically the connection string contains the following information about the target: Host, Port, Driver, Credentials.
ConnToken / ConnectionToken
Each ConnectionString defined within the DBConnector
is issued a unique ConnToken when it is saved. Any query requests made via the DBConnector
/v1/query or /v1/select endpoints provide the ConnToken (rather than a Connection String).
No Host or Credentials information needs to be stored in the caller system, nor passed across the network by the caller.
No Host or Credentials are passed across the internet from the caller system.
If Credentials need to be changed, the change is done once in the DBConnector
and all callers will use the new credentials without having to update their ConnTokens.
Session
A session begins when a user authenticates and receives an AuthToken, and ends when the AuthToken expires.
Calling System
The originating application which calls endpoints in this API.
username
string
A username (either a name or an email) which is registered in the DBConnector
(see /v1/users)
password
string
The password set for the username provided.
Authorization
string
Bearer: A valid authToken. An expired autToken may be used provided it matches the refreshToken and the refreshToken is not expired.
refresh_token
string
A valid refreshToken
page
number
The page number to return. Note: Page numbering starts at zero.
limit
number
The number of users to return for each page.
Authorization
string
Bearer - A valid connToken
useridentifier
integer
The useridentifier may be either of: - userID (numeric): unique user number - username (string): unique username
Authorization
string
Bearer: A valid connToken.
useridentifier
string
The useridentifier may be either of: - userID (numeric): unique user number - username (string): unique username
Authorization
string
Bearer: A valid authToken.
Authorization
string
Bearer: A valid authToken.
username
string
Any unique string to identify this user. Recommended to use email addresses for human users (e.g. "someone@boston.gov") or a meaningful name built around the calling service name (e.g. "cmdb_nightly_update"). Maximum 100 chars.
password
string
A complex password. The longer and more complex the better.
role
number
See User Permissions
enabled
number
1 or 0. Is this account to be created enabled or disabled. (0=disabled).
ipaddresses
string
A comma separated list of IPAddresses the user can make requests from. If this is left blank, then requests are accepted from all IPAddresses. Maximum 150 chars.
ttl
string
The lifetime of authTokens generated for this user. If this is left blank, then 180s will be used. Format is "xxxm/s" (e.g. "90s" for 90 seconds, or "3m" for 3 minutes) Note: Shorter key lifetimes provide better security. Maxmum 10m or 600s.
useridentifier
number
The userid. Userid is returned from the /v1/auth request.
Authroization
string
Bearer: A valid authToken
userid*
number
The userid (number). Userid is returned from the /v1/auth request.
Authorization*
string
Bearer: A valid authToken
Authorization
string
Bearer: A valid authToken.
token
string
Bearer: A valid authToken.
Authorization
string
Bearer: A valid authToken
:name
string
The name of a token
Authorization
string
Bearer: A valid authToken
token
string
Bearer: A valid connToken.
Authorization
string
Bearer: A valid authToken
Authorization
string
Bearer: A valid authToken.
connectionString
string
The connection string, usually as a JSON string.
"{
\"host\":\"somewhere.com\",
\"username\":\"sa\",
\"password\": \"asdfasd\"
}"
name
string
A name by which this connection string can be easily referred to.
description
string
The purpose of the connection string. Tip: Include the driver and/or type of connection defined.
enabled
number
1 (enabled) or 0 (disabled). Defaults to 1 (enabled)
token
string
A valid connToken
userid
integer
A userid (number). (The userid is returned from the /v1/auth request)
Authorization
string
Bearer: a valid authToken
token
string
A valid connToken.
Authorization
string
Bearer: A valid authToken
connectionString
string
name
string
description
string
enabled
integer
token
string
A valid connToken
Authorization
string
Bearer: A valid authToken
token
string
A valid connToken.
userid
integer
A userid. UserIds are returned from the /v1/auth request.
Authorization
string
Bearer: A valid authToken
:driver
string
The driver to use to execute the statement on the remote system. At this time, we only have mssql.
Authorization
string
A valid Authentication Token in format: "Bearer xxxxx-xxxxxx-xxxxxx"
token
string
statement
string
A single statement or command that can be executed on the remote system. Multiple statements may be included and should be separated by semi-colons.
args
string
A JSON string containing parameters to be substituted into the statement parameter.
:driver
string
The driver to use to execute the statement on the remote system. At this time, we only have mssql
Authorization
string
A valid Authentication Token in format: "Bearer xxxxx-xxxxxx-xxxxxx"
token
String
A valid connection string token
procname
string
The name of the procedure to execute
params
object
An object containing key:value pairs for parameters to be passed into the stored procedure. Note: Input parameters can be declared in any order
output
object
An object containing name:type pairs for output parameters to be passed into the stored procedure. The type must be one of the following strings: "number" or "varchar" Note: Ouput parameters can be declared in any order.
:driver
string
The driver to use to execute the statement on the remote system. At this time, we only have mssql.
Authorization
string
A valid Authentication Token in format: "Bearer xxxxx-xxxxxx-xxxxxx"
token
string
A valid connection string Token
table
string
The table to select data from. Note: Can also be a view name
fields
array
Fields to return. If omitted then all fields will be returned.
e.g. [ "ID", "name", "enabled" ]
filter
array
A JSON array of key/value pair objects containing filtering options for the data to be extracted from the table. (see where arrays)
e.g. [ {"ID": 1}, {"enabled": "false"} ]
sort
array
A JSON string array of fields to sort by. Required if limit parameter is provided.
e.g. [ "ID DESC", "name" ]
limit
string
Number of results to return in a page. If omitted, then defaults to 100 if the sort parameter is provided - else all records are returned.
page
string
Page to be returned. If omitted then defaults to page 0 (first page). Note: Page numbering starts at zero.
:driver
string
The driver to use to execute the statement on the remote system. At this time, we only have mssql.
Authorization
string
A valid Authentication Token in format: "Bearer xxxxx-xxxxxx-xxxxxx"
token
string
A valid connection string token (connToken)
table
string
The table to insert data into
fields
array
An array of fields to add values to. Each array element is a separate record to be added to the table.
e.g. [ "ID", "Name" ]
values
array
An array of arrays. Each array is a record. Each field in the record array is a value to add.
Note: The order of values must match the order of fields.
e.g. [ [ 1, "david" ], [ 2, "mike" ] ]
:driver
string
The driver to use to execute the statement on the remote system. At this time, we only have mssql.
Authorization
string
A valid authToken in the format: "Bearer xxxxx-xxxxxx-xxxxxx
token
string
A valid connection string token (connToken)
table
string
The table in which to update data.
values
object
Object containing key:value pairs where the key is the fieldname and the value is the fields value.
e.g. { "name":"david", "address": "my house" }
filter
array
A JSON array of key/value pair objects containing filtering options for the data to be extracted from the table. (see where arrays)
e.g. [ {"ID": 1}, {"enabled": "false"} ]
:driver
string
The driver to use to execute the statement on the remote system. At this time, we only have mssql.
Authorization
string
A valid authToken in the format: "Bearer xxxxx-xxxxxx-xxxxxx"
token
string
A valid connToken
table
string
The table to delete data from.
filter
array
A JSON array of key/value pair objects containing filtering options for the data to be extracted from the table. (see where arrays)
e.g. [ {"ID": 1}, {"enabled": "false"} ]
Role
Role#
Description
READ USER (NORMAL)
1
Can authenticate and then use the /select endpoint
ALTER USER (SUPER)
2
Can authenticate and then use the /select, /update and /insert endpoints
FULL QUERY USER
4
Can authenticate and then use the /select, /update, /insert, /delete and /query endpoints
ADMIN
2048
Can use all query endpoints and can CRUD users and connections and grant user rights to connections
OWNER
4096
Can use all endpoints
Filter field shorthand
Meaning
{"username": "david"}
return records where the username is exactly equal to "david".
{"!username": "david"}
return records where the username is not equal to "david". (the '!' must be the first char of the string.)
{"username": "david%"}
return records where "david" is at the start of the username field.
{"username": "%david"}
return records where "david" at the end of the username field.
{"username": "%david%"}
return records where "david" is contained in the username field.
{"username": ["david", "michael"]
return records where the username is "david" or "michael".
{"^username": "david"}
return records using an OR join for this filter. Care as the AND/OR predicates are applied in order they occur in the filter array.
formfile* | String | Url to a form PDF |
datafile* | String | Url to a form data file in FDF format |
basefile* | String | A PDF document - can be a URL or a file-reference returned from another endpoint. |
overlayfile* | String | URL to a PDF document |
overwrite | String | Defaults to "true" |
pdf_file* | String | A PDF document - can be a URL or a file-reference returned from another endpoint. |
meta_data* | String | A file in a the following format:
|
pdf_file* | String | Url to a PDF document |
del | String | Should the file be deleted after it is downloaded. Defaults to "true". |
file* | String | A file-reference from one of the endpoints |
del | String | Delete the file after downloading. defaults to false |
show | String | Download method: D (default) downloads attachment, I download and display in browser (if supported) |