> For the complete documentation index, see [llms.txt](https://docs.boston.gov/digital/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.boston.gov/digital/guides/amazon-web-services/services/microservices-2021/sql-proxy-2021.md). # 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](https://docs.google.com/document/d/1jgzdX7X7V1SbOmna0Uo5TXbVPJnUKTV7NNdHKcNj4so/edit?usp=sharing) ## API User Guide The `DBConnector` micro-service is available at: | Environment | API Base URL | | ----------- | ---------------------------------------------------- | | test | **** | | prod | **** | ### Flow #### 1. Authenticate (get or refresh an authToken) The first step is to post to the [**/v1/auth**](/digital/guides/amazon-web-services/services/microservices-2021/sql-proxy-2021.md#authenticate-user) 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](/digital/guides/amazon-web-services/services/microservices-2021/sql-proxy-2021.md#user-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" ``` {% hint style="info" %} 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.* {% endhint %} #### 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](/digital/guides/amazon-web-services/services/microservices-2021/sql-proxy-2021.md#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.

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

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

| | **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. {% tabs %} {% tab title="200: OK Expected response" %} ```javascript {} ``` {% endtab %} {% endtabs %} ### 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. | {% tabs %} {% tab title="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"} ``` {% endtab %} {% tab title="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"} ``` {% endtab %} {% tab title="401 This will be returned if the user is disabled." %} ``` {"error": "User Disabled"} ``` {% endtab %} {% tab title="403 A 403 may be returned if the request came from an IPAddress not listed for this username, an empty JSON object is returned." %} ``` {} ``` {% endtab %} {% tab title="404 If the username/password does not validate, then an empty JSON object is returned." %} ``` {} ``` {% endtab %} {% tab title="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." %} ``` {} ``` {% endtab %} {% endtabs %} ## 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 | {% tabs %} {% tab title="200 Successfully generates a new authToken (and also a new refreshToken)." %} ``` *** Normal response { "userid": 6, "authToken": "XXXXXX", "refreshToken": "XXXXX" } ``` {% endtab %} {% tab title="400 Other errors that may be returned:" %} ``` *** General Error. {"error": } *** If no refreshToken was supplied in the body payload. {"error": "Missing refresh token"} *** If the refreshToken is not valid. {"error": "Invalid refresh token"} ``` {% endtab %} {% tab title="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"} ``` {% endtab %} {% tab title="403 Usually returned because the callers originating IPAddress does not appear to match the whitelist provided in the user Account." %} ``` {} ``` {% endtab %} {% endtabs %} ### 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": } ``` **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](/digital/guides/amazon-web-services/services/microservices-2021/sql-proxy-2021.md#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 | {% tabs %} {% tab title="200 Returns a JSON Array with user details. Note: The password field is obfuscated. (see user permissions for roles)" %} ``` [ { "ID": 1, "Username": "david.upton@boston.gov", "Password": "*****", "IPAddresses": "", "Enabled": true, "Role": 4096, "TTL": "" }, {...}, {...} ] ``` {% endtab %} {% endtabs %} ## 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. | {% tabs %} {% tab title="200 Returns a JSON Array with user details. Note: The password field is not obfuscated. (see user permissions for roles)" %} ``` [ { "ID": 1, "Username": "david.upton@boston.gov", "Password": "the-password", "IPAddresses": "", "Enabled": true, "Role": 4096, "Session": null, "TTL": "" } ] ``` {% endtab %} {% endtabs %} ## 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. | {% tabs %} {% tab title="200 Returns an array of connection string records which the requested user is permissioned to use." %} ``` [ { "Username":"david.upton@boston.gov", "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" } ] ``` {% endtab %} {% endtabs %} ## 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. "") 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.

| {% tabs %} {% tab title="201 The ID of the newly created user." %} ``` { "id": 16 } ``` {% endtab %} {% tab title="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." } ``` {% endtab %} {% endtabs %} ## 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 | {% tabs %} {% tab title="204 If the record was updated." %} ``` {} ``` {% endtab %} {% tab title="400 If no record for the userid is found:" %} ``` { "error": "Not Found" } *** Tries to rename username with duplicate { "error": "Username already exists." } ``` {% endtab %} {% endtabs %} ## 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 | {% tabs %} {% tab title="204 If the record was found and "deleted" (actually will just be disabled)" %} ``` {} ``` {% endtab %} {% tab title="400 If the userid was not found." %} ``` { "error": "Not Found" } ``` {% endtab %} {% endtabs %} ### 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. {% hint style="info" %} The connToken is used to refer to the remote system in [execution endpoints](/digital/guides/amazon-web-services/services/microservices-2021/sql-proxy-2021.md#execute-commands-on-remote-system) so that connectivity details do not need to be stored in and passed from the calling system. {% endhint %} {% hint style="success" %} The connToken (UUID) should never change once the connections string record is created, and therefore can be safely stored in the calling system. {% endhint %} ## 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. | {% tabs %} {% tab title="200 " %} ``` ``` {% endtab %} {% endtabs %} ## 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 | {% tabs %} {% tab title="200 " %} ``` ``` {% endtab %} {% tab title="400 If the requested connToken is not found in the database." %} ``` { "error": "connToken not found" } { "error": "malformed connToken" } ``` {% endtab %} {% endtabs %} ## 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 | {% tabs %} {% tab title="200 " %} ``` ``` {% endtab %} {% endtabs %} ## 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 | {% tabs %} {% tab title="200 " %} ``` ``` {% endtab %} {% endtabs %} ## 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)

| {% tabs %} {% tab title="201 Returns a message with the newly created connToken." %} ``` { "connToken": "12342343412341" } ``` {% endtab %} {% tab title="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)." } ``` {% endtab %} {% endtabs %} ## 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 | {% tabs %} {% tab title="201 If the permission is granted." %} ``` {} ``` {% endtab %} {% tab title="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)." } ``` {% endtab %} {% endtabs %} ## 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 | | {% tabs %} {% tab title="204 If the connection is successfully updated." %} ``` {} ``` {% endtab %} {% endtabs %} ## 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 | {% tabs %} {% tab title="204 The connection string is disabled." %} ``` {} ``` {% endtab %} {% tab title="400 If the connToken provided cannot be found." %} ``` *** Connection string was not found in system. { "error": "Not Found" } ``` {% endtab %} {% endtabs %} ## 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 | {% tabs %} {% tab title="204 If the permission was removed." %} ``` {} ``` {% endtab %} {% tab title="400 If the connToken provided was not found." %} ``` { "error": "Not Found" } ``` {% endtab %} {% endtabs %} ### 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. | {% tabs %} {% tab title="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!" } ], ... ] ``` {% endtab %} {% tab title="400 Separate errors are reported if fields are missing from the payload." %} ``` { "error": "Missing connection string token" } { "error": "Missing payload" } { "error": "Missing statement" } ``` {% endtab %} {% endtabs %} #### 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: ```javascript statement: 'SELECT {a} FROM {c} ORDER BY {b};', args: '{"a": "ID", "b": "CreateDate", "c": "dbo.users"}' ``` Expands out to: ```sql SELECT ID FROM dbo.users ORDER BY CreateDate; ``` {% hint style="success" %} **Calling Views** You can call views using the `/query` endpoint (and also the `/select` endpoint).\ Treat a view like a table. {% endhint %} ## 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.

| {% tabs %} {% tab title="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", ... } } ``` {% endtab %} {% tab title="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" } ``` {% endtab %} {% endtabs %} ## 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.

| {% tabs %} {% tab title="200 Returns a JSON array with each record in the table expressed as a row." %} ``` [ { "ID": 1, "Name": "david", ... }, { "ID": 2, "Name": "Jim", ... }, ... ] ``` {% endtab %} {% tab title="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" } ``` {% endtab %} {% endtabs %} ## 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" ] ]

| {% tabs %} {% tab title="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" } ``` {% endtab %} {% tab title="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" } ``` {% endtab %} {% endtabs %} ## 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"} ]

| {% tabs %} {% tab title="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 } ``` {% endtab %} {% tab title="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" } ``` {% endtab %} {% endtabs %} ## 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"} ]

| {% tabs %} {% tab title="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 } ``` {% endtab %} {% tab title="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" } ``` {% endtab %} {% endtabs %} ### 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 } ] ```