<img src="https://d5nxst8fruw4z.cloudfront.net/atrk.gif?account=lYCzn1QolK10N8" style="display:none" height="1" width="1" alt="">

Database Connections API

Overview

GreenArrow can connect to external MySQL, Microsoft SQL Server, and PostgreSQL databases to support remote mailing lists in Studio.

Engine’s Database Connections API is used to manage the connections to these external databases.

Attributes

The following attributes are defined for Database Connections:

database_connection

hash

/

required


id

integer

/

read-only

A unique automatically generated identifier.

name

string

/

required

A descriptive name for this Database Connection.

  • Must be unique (case-insensitive)
  • Must be 1-100 characters long
type

string

/

required

The type of database to connect to.

  • Must be one of: mssql (Microsoft SQL Server), mysql (MySQL), or postgres (PostgreSQL)
database

string

/

required

The database name to connect to.

  • Must be 1-100 characters long
username

string

/

default: ""

The username to connect to the database with.

  • Must be 1-100 characters long
password

string

/

default: ""

The password to connect to the database with. May be a blank string, or set to null if there is no password.

  • May be up to 100-characters long
remote_connection

hash

/

default: null



Specifies information about remote database connections. When this is null, GreenArrow attempts to log into a database running on the local server.

hostname

string

The hostname or IP address to connect to the database server on.

port

integer

The TCP port to connect to the database server on. Set to 0 to use the default for the database type. The defaults are:

  • Microsoft SQL Server - port 1433
  • MySQL - port 3386
  • PostgreSQL - port 5432

The port must be between 0 and 65535

ssl

boolean

/

default: false

Specifies whether to connect to the database server using an encrypted (SSL) connection.

extra

string

Optional string that’s appended to the DSN used to connect to the database. All connections are made using Perl’s DBI interface.

  • May be up to 255-characters long
notes

text

Optional notes about this database connection.

Get a List of Database Connections

GET /ga/api/v3/eng/database_connections

Parameters

The following parameters are valid for the above endpoint.

page

integer

/

optional

The page number from which to retrieve. Page numbering starts at 0.

page_token

string

/

optional

The page_token to retrieve the next page based on the prior query results.

name

string

/

optional

Filter the results to just a single Database Connection with this name (case-insensitive).

Examples:

GET /ga/api/v3/eng/database_connections?page={page}
GET /ga/api/v3/eng/database_connections?page_token={page_token}
GET /ga/api/v3/eng/database_connections?name={name}

Response

The response will contain a list of Database Connections in the following format.

database_connections

array of hashes


id

integer

An automatically generated identifier.

name

string

A descriptive name for this Database Connection.

pagination

hash


page

integer

The page number of this result set. Page numbers start at 0.

per_page

integer

The number of records returned on each page.

num_pages

integer

The total number of pages in the result set.

num_records

integer

The total number of records that are in the result set.

next_page_token

string

A unique identifier that can be used to retrieve the next result set.

null is returned if this is the last page.

Example 1: Get the First Page of Database Connections

GET /ga/api/v3/eng/database_connections

HTTP/1.1 200 OK

{
  "success": true,
  "data": {
    "database_connections": [
      {
        "id": 1,
        "name": "my_db_conn1"
      },
      {
        "id": 2,
        "name": "my_db_conn2"
      },
      {
        "id": 3,
        "name": "my_db_conn3"
      }
    ],
    "pagination": {
      "page": 0,
      "per_page": 100,
      "num_pages": 1,
      "num_records": 3,
      "next_page_token": null
    }
  },
  "error_code": null,
  "error_messages": null
}

Example 2: Get a Specific Page of Database Connections with Per-Page Limits

GET /ga/api/v3/eng/database_connections?page=1&per_page=2

HTTP/1.1 200 OK

{
  "success": true,
  "data": {
    "database_connections": [
      {
        "id": 3,
        "name": "my_db_conn3"
      }
    ],
    "pagination": {
      "page": 1,
      "per_page": 2,
      "num_pages": 2,
      "num_records": 3,
      "next_page_token": null
    }
  },
  "error_code": null,
  "error_messages": null
}

Example 3: Find a Database Connection by Name

GET /ga/api/v3/eng/database_connections?name=my_db_conn2

HTTP/1.1 200 OK

{
  "success": true,
  "data": {
    "database_connections": [
      {
        "id": 2,
        "name": "my_db_conn2"
      }
    ],
    "pagination": {
      "page": 0,
      "per_page": 100,
      "num_pages": 1,
      "num_records": 1,
      "next_page_token": null
    }
  },
  "error_code": null,
  "error_messages": null
}

Get Details on a Single Database Connection

GET /ga/api/v3/eng/database_connections/{id}

Response

The response will contain details on the requested record:

database_connection

hash

The attributes for this hash are defined in the Attributes section of this document.

Example

GET /ga/api/v3/eng/database_connections/1

HTTP/1.1 200 OK

{
  "success": true,
  "data": {
    "database_connection": {
      "id": 1,
      "name": "my_db_conn1",
      "type": "mysql",
      "database": "database_name_01",
      "username": "root",
      "password": "",
      "remote_connection": null,
      "extra": null,
      "notes": null
    }
  },
  "error_code": null,
  "error_messages": null
}

Create a Database Connection

POST /ga/api/v3/eng/database_connections

Payload

database_connection

hash

/

required

The content of this hash is described in the Attributes section of this page.

The data may contain a mixture of required and optional fields. Do not specify read-only fields.

Response

The response will include a database_connection key containing the full Database Connection record as defined in the Database Connection Attributes.

Example

POST /ga/api/v3/eng/database_connections

{
  "database_connection": {
    "name": "a_new_db_conn",
    "type": "mysql",
    "username": "root",
    "database": "db_name",
    "remote_connection": {
      "port": 123,
      "ssl": false,
      "hostname": "example.com"
    }
  }
}

HTTP/1.1 200 OK

{
  "success": true,
  "data": {
    "database_connection": {
      "id": 4,
      "name": "a_new_db_conn",
      "type": "mysql",
      "database": "db_name",
      "username": "root",
      "password": "",
      "remote_connection": {
        "hostname": "example.com",
        "port": 123,
        "ssl": false
      },
      "extra": null,
      "notes": null
    }
  },
  "error_code": null,
  "error_messages": null
}

Update a Database Connection

PUT /ga/api/v3/eng/database_connections/{id}

Payload

database_connection

hash

/

required

The content of this hash is described in the Attributes section of this page.

The data may contain a mixture of required and optional fields. Do not specify read-only fields.

Response

The response will include a data key containing the full Database Connection record as defined in the Database Connection Attributes.

Example

PUT /ga/api/v3/eng/database_connections/1

{
  "database_connection": {
    "name": "updated_name"
  }
}

HTTP/1.1 200 OK

{
  "success": true,
  "data": {
    "database_connection": {
      "id": 1,
      "name": "updated_name",
      "type": "mysql",
      "database": "database_name_01",
      "username": "root",
      "password": "",
      "remote_connection": null,
      "extra": null,
      "notes": null
    }
  },
  "error_code": null,
  "error_messages": null
}

Delete a Database Connection

DELETE /ga/api/v3/eng/database_connections/{id}

Response

The response is a standard success or error response.

Example

DELETE /ga/api/v3/eng/database_connections/2

HTTP/1.1 200 OK

{
  "success": true,
  "data": {
  },
  "error_code": null,
  "error_messages": null
}