SQL Proxy API (DBConnector)

Requirement

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.

Service Specification

Specification May 2021

API User Guide

The DBConnector micro-service is available at:

Environment	API Base URL
test	https://dbconnector.digital-staging.boston.gov
prod	https://dbconnector.boston.gov

Flow

1. Authenticate (get or refresh an authToken)

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

HEADER "Authorization: Bearer xxxx-xxxxx-xxxxx"

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.

2. Fetch or verify connToken (optional)

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.

3. Perform database/remoteAPI operation

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.

Terminology

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.

Heartbeat

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.

Heartbeat required by ECS

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.

200: OK Expected response

{}

Authentication

Authenticate User

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).

Request Body

Name	Type	Description
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.

200 Returns an Authentication Token and a Refresh Token, and also the ID of the user, which can/should be used for other endpoint calls.

{
  "userid": 1,
  "authToken": "xxx-xxxx-xxxx",
  "refreshToken": "xxx-xxxx"
}

** If the user is found to be over-using the endpoint, (flooding) then 
   the following will be returned:
{"error": "No Data"}

400 Errors determined while authenticating will return one of the following:

** No body
{"error": "Missing authentication payload"}
** No username in body
{"error": "Missing username/email field"}
** No password in body
{"error": "Missing password field"}

** Username not found
{"error": "Invalid username or password"}
** Password does not match
{"error": "Invalid username or password"}

** Other un expected errors
{"error": "error message text"}

401 This will be returned if the user is disabled.

{"error": "User Disabled"}

403 A 403 may be returned if the request came from an IPAddress not listed for this username, an empty JSON object is returned.

{}

404 If the username/password does not validate, then an empty JSON object is returned.

{}

500 If there is an error generating the token, or any other server-side error a 500 status will be returned with an empty JSON string.

{}

Refresh Authentication Token

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.

Headers

Name	Type	Description
Authorization	string	Bearer: A valid authToken. An expired autToken may be used provided it matches the refreshToken and the refreshToken is not expired.

Request Body

Name	Type	Description
refresh_token	string	A valid refreshToken

200 Successfully generates a new authToken (and also a new refreshToken).

*** Normal response
{
    "userid": 6,
    "authToken": "XXXXXX",
    "refreshToken": "XXXXX"
}

400 Other errors that may be returned:

*** General Error.
{"error": <explanation>}

*** If no refreshToken was supplied in the body payload.
{"error": "Missing refresh token"}

*** If the refreshToken is not valid.
{"error": "Invalid refresh token"}

401 If there are issues with the authToken, these are the expected messages:

*** If no authToken was supplied.
{"error": "Missing Authentication Token"}
*** If the AuthToken supplied has expired.
{"error": "Expired Token"}
*** If the token is unknown or badly formatted.
{"error": "Bad Token"}

403 Usually returned because the callers originating IPAddress does not appear to match the whitelist provided in the user Account.

{}

Common Endpoint Error Messages

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.

{
    "error": <description>
}

401: Unauthorised

Generally, 401 errors are returned when there is an issue with the AuthToken provided in he header.

*** If no authToken was supplied
{"error": "Missing Authentication Token"}

*** If the authToken supplied has expired.
{"error": "Expired Token"}

*** If the authToken is unknown or badly formatted
{"error": "Bad Token"}

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.

*** Response if abuse detected
{
    "error": "No Data"
}

User Management

List all Users (paged)

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)

Query Parameters

Name	Type	Description
page	number	The page number to return. Note: Page numbering starts at zero.
limit	number	The number of users to return for each page.

Headers

Name	Type	Description
Authorization	string	Bearer - A valid connToken

200 Returns a JSON Array with user details. Note: The password field is obfuscated. (see user permissions for roles)

[
    {
        "ID": 1,
        "Username": "[e-mail]",
        "Password": "*****",
        "IPAddresses": "",
        "Enabled": true,
        "Role": 4096,
        "TTL": ""
    },
    {...},
    {...}
]

List a single User

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.

Path Parameters

Name	Type	Description
useridentifier	integer	The useridentifier may be either of: - userID (numeric): unique user number - username (string): unique username

Headers

Name	Type	Description
Authorization	string	Bearer: A valid connToken.

200 Returns a JSON Array with user details. Note: The password field is not obfuscated. (see user permissions for roles)

[
    {
        "ID": 1,
        "Username": "[e-mail]",
        "Password": "the-password",
        "IPAddresses": "",
        "Enabled": true,
        "Role": 4096,
        "Session": null,
        "TTL": ""
    }
]

List Connections available to a User

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.

Path Parameters

Name	Type	Description
useridentifier	string	The useridentifier may be either of: - userID (numeric): unique user number - username (string): unique username

Headers

Name	Type	Description
Authorization	string	Bearer: A valid authToken.

200 Returns an array of connection string records which the requested user is permissioned to use.

[
 {
   "Username":"[e-mail]",
   "userid":1,
   "connid":1,
   "Token":"xx-xx-xx-xx-xx",
   "ConnectionString":"{\"host\":\"MSSQL_CMDB\", \"port\":\"1433\", \"schema\":\"dbo\", \"database\":\"CMDB\", \"user\":\"myUser\", \"password\":\"myPassword\"}",
   "Description":"Updated Dummy",
   "Enabled":true,
   "Count":0,
   "LastUse":
   "2021-08-10T19:30:20.160Z"
  }
]

Add a new User

POST https://dbconnector.digital-staging.boston.gov/v1/users

Adds a new user. Permission: ADMIN or OWNER.

Headers

Name	Type	Description
Authorization	string	Bearer: A valid authToken.

Request Body

Name	Type	Description
username	string	Any unique string to identify this user. Recommended to use email addresses for human users (e.g. "[e-mail]") 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.

201 The ID of the newly created user.

{
    "id": 16
}

400 As well as the common error messages, an error will be thrown if: - A duplicate username is attempted to be created. - The username is more than 100 chars long.

{
    "error": "Username already exists."
}


{
    "error": "Username is more than 100chars or IPAddress is more than 150 chars or ttl is more than 10 chars."
}

Update an existing User

PATCH https://dbconnector.digital-staging.boston.gov/v1/users/:useridentifier

Updates the specified user with information provided in the payload. Permission: ADMIN or OWNER

Path Parameters

Name	Type	Description
useridentifier	number	The userid. Userid is returned from the /v1/auth request.

Headers

Name	Type	Description
Authroization	string	Bearer: A valid authToken

204 If the record was updated.

{}

400 If no record for the userid is found:

{
    "error": "Not Found"
}

*** Tries to rename username with duplicate
 {
     "error": "Username already exists."
 }

Delete an existing User

DELETE https://dbconnector.digital-staging.boston.gov/v1/users/:userid

Deletes the specified user. Permission: ADMIN or OWNER

Path Parameters

Name	Type	Description
userid*	number	The userid (number). Userid is returned from the /v1/auth request.

Headers

Name	Type	Description
Authorization*	string	Bearer: A valid authToken

204 If the record was found and "deleted" (actually will just be disabled)

{}

400 If the userid was not found.

{
    "error": "Not Found"
}

Connection Strings

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.

List all Connections (paged)

GET https://dbconnector.digital-staging.boston.gov/v1/connections

Returns a list of all active remote system connection strings. Permission: ADMIN OR OWNER

Headers

Name	Type	Description
Authorization	string	Bearer: A valid authToken.

200

List single Connection

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.

Path Parameters

Name	Type	Description
token	string	Bearer: A valid authToken.

Headers

Name	Type	Description
Authorization	string	Bearer: A valid authToken

200

400 If the requested connToken is not found in the database.

{ "error": "connToken not found" }
{ "error": "malformed connToken" }

Find connToken

GET https://dbconnector.digital-staging.boston.gov/v1/connections/find/:name

Fetch a connection token using the tokens name. Permission: All authenticated users.

Path Parameters

Name	Type	Description
:name	string	The name of a token

Headers

Name	Type	Description
Authorization	string	Bearer: A valid authToken

200

List Users who may use a Connection

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

Path Parameters

Name	Type	Description
token	string	Bearer: A valid connToken.

Headers

Name	Type	Description
Authorization	string	Bearer: A valid authToken

200

Add a new Connection

POST https://dbconnector.digital-staging.boston.gov/v1/connection

Saves a connection string. Permission: ADMIN or OWNER.

Headers

Name	Type	Description
Authorization	string	Bearer: A valid authToken.

Request Body

Name	Type	Description
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)

201 Returns a message with the newly created connToken.

{
    "connToken": "12342343412341"
}

400 Errors related to the parameters passed, or caused by insert action.

{
    error: "Missing connectionString in payload"
}
{
    error: "Missing name in payload"
}

*** If the provided Name already exists.
{
    error: "Cannot insert duplicate key row in object 'dbo.connTokens' with unique index 'XXXX'. The duplicate key value is (xxx)."
}

Grant User Permission to Connection

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.

Path Parameters

Name	Type	Description
token	string	A valid connToken
userid	integer	A userid (number). (The userid is returned from the /v1/auth request)

Headers

Name	Type	Description
Authorization	string	Bearer: a valid authToken

201 If the permission is granted.

{}

400 Errors related to the payload or actual insertion of the permission

*** Required fields missing
{
    error: "Missing connectionString in payload"
}
{
    error: "Missing name in payload"
}

*** Token with that Name already exists
{
    error: "Cannot insert duplicate key row in object 'dbo.connTokens' with unique index 'UK_Name'. The duplicate key value is (XXX)."
}

Update an existing Connection

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..

Path Parameters

Name	Type	Description
token	string	A valid connToken.

Headers

Name	Type	Description
Authorization	string	Bearer: A valid authToken

Request Body

Name	Type	Description
connectionString	string
name	string
description	string
enabled	integer

204 If the connection is successfully updated.

{}

Delete an existing Connection

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

Path Parameters

Name	Type	Description
token	string	A valid connToken

Headers

Name	Type	Description
Authorization	string	Bearer: A valid authToken

204 The connection string is disabled.

{}

400 If the connToken provided cannot be found.

*** Connection string was not found in system.
{
    "error": "Not Found"
}

Revoke User Permission for Connection

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

Path Parameters

Name	Type	Description
token	string	A valid connToken.
userid	integer	A userid. UserIds are returned from the /v1/auth request.

Headers

Name	Type	Description
Authorization	string	Bearer: A valid authToken

204 If the permission was removed.

{}

400 If the connToken provided was not found.

{
    "error": "Not Found"
}

Execution Endpoints

Running data commands on a Remote System.

General SQL Errors

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:

{
    "error": "cleaned error message from host system"
}

Execute SQL Statement

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.

Path Parameters

Name	Type	Description
:driver	string	The driver to use to execute the statement on the remote system. At this time, we only have mssql.

Headers

Name	Type	Description
Authorization	string	A valid Authentication Token in format: "Bearer xxxxx-xxxxxx-xxxxxx"

Request Body

Name	Type	Description
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.

200 Returns a JSON array of arrays of objects. Because the statement parameter may be comprised of multiple valid SQL Statements, there is an array returned for each SQL Statement found. Statements that do not return recordsets (e.g. delete statements) will return an empty array. Statements that do return recordsets will return an array of objects where each object is a row from the recordset.

*** If the statement provided contains a single sql statement.
[
    {
        "ID": 1,
        "Name": "david",
        ...
    },
    {    
        "ID": 2,
        "Name": "Jim",
        ...
    },
    ...
]

*** If the statement provided contains multiple sql statements.
[
    [],
    [
        {
            "ID": 1,
            "Name": "david",
            ...
        },
        {    
            "ID": 2,
            "Name": "Jim",
            ...
        },
        ...
    ],
    [
        {
            "MyVar": "Good Job!"
        }
    ],
    ...
]

400 Separate errors are reported if fields are missing from the payload.

{ "error": "Missing connection string token" }
{ "error": "Missing payload" }
{ "error": "Missing statement" }

Note on statement and args (parameters)

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:

statement: 'SELECT {a} FROM {c} ORDER BY {b};',
args: '{"a": "ID", "b": "CreateDate", "c": "dbo.users"}'

Expands out to:

SELECT ID FROM dbo.users ORDER BY CreateDate;

Calling Views

You can call views using the /query endpoint (and also the /select endpoint). Treat a view like a table.

Execute Stored Procedure

POST https://dbconnector.digital-staging.boston.gov/v1/exec/:driver

Execute a stored procedure on the remote system. Permission: ALTER, FULL, ADMIN or OWNER

Path Parameters

Name	Type	Description
:driver	string	The driver to use to execute the statement on the remote system. At this time, we only have mssql

Headers

Name	Type	Description
Authorization	string	A valid Authentication Token in format: "Bearer xxxxx-xxxxxx-xxxxxx"

Request Body

Name	Type	Description
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.

200 Return recordset or recordsets from the stored procedure - if any.

*** If the stored procedure contains a single sql statement.
[
    {
        "ID": 1,
        "Name": "david",
        ...
    },
    {    
        "ID": 2,
        "Name": "Jim",
        ...
    },
    ...
]
*** If the stored procedure contains multiple statements
[
    [],
    [
        {
            "ID": 1,
            "Name": "david",
            ...
        },
        {    
            "ID": 2,
            "Name": "Jim",
            ...
        },
        ...
    ],
    [
        {
            "MyVar": "Good Job!"
        }
    ],
    ...
]

*** If the stored procedure executed contains a single sql statement 
    and output variables.
{
    result: [
        {
            "ID": 1,
            "Name": "david",
            ...
        },
        {    
            "ID": 2,
            "Name": "Jim",
            ...
        },
        ...
    ],
    output: {
        param1: "value",
        param2: "value",
        ...
    }
}
*** If the stored procedure executed contains multiple sql statements 
    and output variables.
{
    result: [
        [],
        [
            {
                "ID": 1,
                "Name": "david",
                ...
            },
            {    
                "ID": 2,
                "Name": "Jim",
                ...
            },
            ...
        ],
        [
            {
                "MyVar": "Good Job!"
            }
        ],
        ...
    ],
    output: {
        param1: "value",
        param2: "value",
        ...
    }
}

400 Separate errors are reported if fields are missing from the payload.

{ "error": "Missing connection string token" }
{ "error": "connToken not found" }
{ "error": "Missing payload" }
{ "error": "Missing table in payload" }
{ "error": "Missing procname in payload" }

Run Select Query

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.

Path Parameters

Name	Type	Description
:driver	string	The driver to use to execute the statement on the remote system. At this time, we only have mssql.

Headers

Name	Type	Description
Authorization	string	A valid Authentication Token in format: "Bearer xxxxx-xxxxxx-xxxxxx"

Request Body

Name	Type	Description
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.

200 Returns a JSON array with each record in the table expressed as a row.

[
    {
        "ID": 1,
        "Name": "david",
        ...
    },
    {    
        "ID": 2,
        "Name": "Jim",
        ...
    },
    ...
]      

400 Separate errors are reported if fields are missing from the payload.

{ "error": "Missing connection string token" }
{ "error": "connToken not found" }
{ "error": "Missing payload" }
{ "error": "Missing table in payload" }
{ "error": "Paged query must have sort/order" }
{ "error": "Must have limit and sort if page defined" }

Run Insert Query

POST https://dbconnector.digital-staging.boston.gov/v1/insert/:driver

This endpoint creates a new record (or records) in the specified table.

Path Parameters

Name	Type	Description
:driver	string	The driver to use to execute the statement on the remote system. At this time, we only have mssql.

Headers

Name	Type	Description
Authorization	string	A valid Authentication Token in format: "Bearer xxxxx-xxxxxx-xxxxxx"

Request Body

Name	Type	Description
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" ] ]

200 Returns a confirmation or else the @@IDENTITY value of the record added.

If a single new record is defined in the values array:
{
    "Identity": N
}
If multiple records defined in the values array:
{
    "result": "success"
}

400 Separate errors are reported if fields are missing from the payload.

{ "error": "Missing connection string token" }
{ "error": "connToken not found" }
{ "error": "Missing payload" }
{ "error": "Missing table in payload" }
{ "error": "Insert must have fields defined" }
{ "error": "Insert must have field values defined" }

Run Update Query

POST https://dbconnector.digital-staging.boston.gov/v1/update/:driver

This endpoint will update existing records in a table.

Path Parameters

Name	Type	Description
:driver	string	The driver to use to execute the statement on the remote system. At this time, we only have mssql.

Headers

Name	Type	Description
Authorization	string	A valid authToken in the format: "Bearer xxxxx-xxxxxx-xxxxxx

Request Body

Name	Type	Description
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"} ]

200 If the command executes successfully. The Updated value (N) indicates how many records were actually updated. Note: The returned value for Updated should be checked, because if no records match the filter provided then no records will be updated - but a 200 code will still be returned.

{
    "Updated": N
}

400 Separate errors are reported if fields are missing from the payload.

{ "error": "Missing connection string token" }
{ "error": "connToken not found" }
{ "error": "Missing payload" }
{ "error": "Missing table in payload" }
{ "error": "Update must have a filter defined" }
{ "error": "Update must have field values defined" }

Run Delete Query

POST https://dbconnector.digital-staging.boston.gov/vi/delete/:driver

Path Parameters

Name	Type	Description
:driver	string	The driver to use to execute the statement on the remote system. At this time, we only have mssql.

Headers

Name	Type	Description
Authorization	string	A valid authToken in the format: "Bearer xxxxx-xxxxxx-xxxxxx"

Request Body

Name	Type	Description
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"} ]

200 If the command executes successfully. The Deleted value (N) indicates how many records were actually deleted. Note: The returned value for Deleted should be checked, because if no records match the filter provided then no records will be deleted - but a 200 code will still be returned.

{
    "Deleted": N
}

400 Separate errors are reported if fields are missing from the payload.

{ "error": "Missing connection string token" }
{ "error": "connToken not found" }
{ "error": "Missing payload" }
{ "error": "Missing table in payload" }
{ "error": "Delete must have a filter defined" }

User Permissions

Each user account defined in DBConnector has an assigned role.

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

Generally, the roles are hierarchical, so for example: a FULL-QUERY-USER can perform all the tasks a READ-USER can perform.

Where Arrays

In the filter fields for the /select, /update and /delete endpoints, the following directives can be used:

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.

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.

Test Data

The endpoint can be tested using some test data pre-loaded into the application.

The credentials to use are:

username: devuser
password: Boston2021

These credentials can be used with the connToken

45826BE6-1E29-CC64-B49E-550B9610C2EA

Data contained in testTable available from connStr #4

  {
    "ID": 1,
    "Name": "David Upton",
    "Address": "1 Nevermind St, Nowhere",
    "Sex": "Male",
    "Age": 21
  },
  {
    "ID": 2,
    "Name": "Matt McGowan",
    "Address": "14 Glochester Park",
    "Sex": "Male",
    "Age": 21
  },
  {
    "ID": 3,
    "Name": "Phiilip Kelly",
    "Address": "2 Redline, Boston",
    "Sex": "Male",
    "Age": 21
  },
  {
    "ID": 4,
    "Name": "Stella Ubana",
    "Address": "3 Kids is too many, Boston",
    "Sex": "Female",
    "Age": 21
  },
  {
    "ID": 5,
    "Name": "Caroline Stjarnborg",
    "Address": "1 City Hall Plaza, Boston",
    "Sex": "Female",
    "Age": 21
  },
  {
    "ID": 6,
    "Name": "James Duffy",
    "Address": "1 Boston",
    "Sex": "Male",
    "Age": 21
  },
  {
    "ID": 7,
    "Name": "Sebastian Ebard",
    "Address": "2 Nice Street, Rosendale",
    "Sex": "Male",
    "Age": 21
  },
  {
    "ID": 8,
    "Name": "Jeanethe Falvey",
    "Address": "3 Nice Street, Rosendale",
    "Sex": "Female",
    "Age": 21
  }
]