External Datasources

What are external data sources?

DatAscend allows for users to connect databases external to their system and manage them from inside DatAscend. For example, an user could have an old SQL databases whose records they want to keep, but do not want to spend time migrating, that is where external datasources come by.

Definition and purpose of external datasources in the system.

The purpose of external datasources is to allow users to connect and manage their external databases from inside DatAscend, using DatAscend’s entities. This means that, interacting with both external entities and internal entities should look and feel almost the same.

Explanation of how they are integrated and used (e.g., connecting to external databases for dynamic data access).

To integrate external datasources, the first step is to import the external datasource using the specific endpoint. After importing, a datasource object is stored on DatAscend, with CRUD operations available to manage the connection.

Importing the datasource automatically imports the tables of the external database as external entities into DatAscend.

How to perform CRUD operations on datasources (not entities)?

Get all datasources

GET /api/v1/datasources/

Response:

{
  "error": false,
  "status": 200,
  "message": "string",
  "errors": [{}],
  "data": [
    {
      "id": "05f44d27-4450-4f96-886d-684cd290827a",
      "name": "DatasourceName",
      "type": "mysql",
      "host": "localhost",
      "port": 3306,
      "username": "DatabaseUsername",
      "password": "DatabasePassword",
      "database": "DatabaseName",
      "external": true
    }
  ],
  "pagination": {
    "hasPreviousPage": true,
    "hasNextPage": true,
    "previousPage": 0,
    "nextPage": 0,
    "recordsTotal": 0,
    "currentPage": 0,
    "startIndex": 0,
    "numPages": 0,
    "limit": 0
  }
}

Add a new datasource

POST /api/v1/datasources/

Request body:

{
  "name": "DatasourceName",
  "type": "mysql",
  "host": "localhost",
  "port": 3306,
  "username": "DatabaseUsername",
  "password": "DatabasePassword",
  "database": "DatabaseName",
  "external": true
}

name: Name that will be used by DatAscend
type: The database’s type. Currently, only mysql and postgres are supported.
host: Address where the external database is hosted.
port: Port of the previously mentioned host where the database is accepting connections.
username: Username required to access the external database (This is not DatAscend’s username).
password: Password required to authenticate to the external database (This is not DatAscend’s username).
database: Name of the external database to connect to.
external: If the datasource is external, always should be true

Response:

{
  "error": true,
  "status": 0,
  "message": "string",
  "data": [
    {
      "id": "05f44d27-4450-4f96-886d-684cd290827a",
      "name": "DatasourceName",
      "type": "mysql",
      "host": "localhost",
      "port": 3306,
      "username": "DatabaseUsername",
      "password": "DatabasePassword",
      "database": "DatabaseName",
      "external": true
    }
  ],
  "entityName": "string"
}

Get an specific datasource

GET /api/v1/datasources/{datasourceName}

parameters:

Name	Type	From	Description
`datasourceName`	string	path	name of the datasource

Response:

{
  "error": false,
  "status": 200,
  "message": "string",
  "errors": [{}],
  "data": [
    {
      "id": "05f44d27-4450-4f96-886d-684cd290827a",
      "name": "DatasourceName",
      "type": "mysql",
      "host": "localhost",
      "port": 3306,
      "username": "DatabaseUsername",
      "password": "DatabasePassword",
      "database": "DatabaseName",
      "external": true
    }
  ],
  "pagination": {
    "hasPreviousPage": true,
    "hasNextPage": true,
    "previousPage": 0,
    "nextPage": 0,
    "recordsTotal": 0,
    "currentPage": 0,
    "startIndex": 0,
    "numPages": 0,
    "limit": 0
  }
}

Delete a datasource

DELETE /api/v1/datasources/{datasourceName}

parameters:

Name	Type	From	Description
`datasourceName`	string	path	name of the datasource

Response:

{
  "error": false,
  "status": 200,
  "message": "string",
  "errors": [{}],
  "data": [
    {
      "id": "05f44d27-4450-4f96-886d-684cd290827a",
      "name": "DatasourceName",
      "type": "mysql",
      "host": "localhost",
      "port": 3306,
      "username": "DatabaseUsername",
      "password": "DatabasePassword",
      "database": "DatabaseName",
      "external": true
    }
  ],
  "pagination": {
    "hasPreviousPage": true,
    "hasNextPage": true,
    "previousPage": 0,
    "nextPage": 0,
    "recordsTotal": 0,
    "currentPage": 0,
    "startIndex": 0,
    "numPages": 0,
    "limit": 0
  }
}

Update a datasource

PUT /api/v1/datasources/{UUID}

Parameters:

Name	Type	From	Description
`UUID`	string	path	universally unique identifier of the datasource

Request body:

{
  "host": "localhost",
  "port": 3306,
  "username": "DatabaseUsername",
  "password": "DatabasePassword",
  "database": "DatabaseName"
}

host: Address where the external database is hosted.
port: Port of the previously mentioned host where the database is accepting connections.
username: Username required to access the external database (This is not DatAscend’s username).
password: Password required to authenticate to the external database (This is not DatAscend’s username).
database: Name of the external database to connect to.

Response:

{
  "error": false,
  "status": 200,
  "message": "string",
  "errors": [{}],
  "data": [
    {
      "id": "05f44d27-4450-4f96-886d-684cd290827a",
      "name": "DatasourceName",
      "type": "mysql",
      "host": "localhost",
      "port": 3306,
      "username": "DatabaseUsername",
      "password": "DatabasePassword",
      "database": "DatabaseName",
      "external": true
    }
  ],
  "pagination": {
    "hasPreviousPage": true,
    "hasNextPage": true,
    "previousPage": 0,
    "nextPage": 0,
    "recordsTotal": 0,
    "currentPage": 0,
    "startIndex": 0,
    "numPages": 0,
    "limit": 0
  }
}

Update all external datasources metadata structure

POST /api/v1/datasources/metadata/update

Parameters:

Name	Type	From	Description
`version`	string	query	version of the application

Response:

{
  "error": false,
  "status": 200,
  "message": "string",
  "errors": [{}],
  "data": [],
  "pagination": {
    "hasPreviousPage": true,
    "hasNextPage": true,
    "previousPage": 0,
    "nextPage": 0,
    "recordsTotal": 0,
    "currentPage": 0,
    "startIndex": 0,
    "numPages": 0,
    "limit": 0
  }
}

Security considerations.

Both the password and username are database encrypted
Security considerations for the external database should be handled on the original database.

How to manage imported entities?

Get an entity record

To get an entity record from an specific external entity the same endpoint as internal entities is used:

GET /api/v1/entities/{entity}

Get the metadata for an specific entity in a specific datasource

GET /api/v1/datasources/{datasourceName}/{entity}/metadata

Parameters:

Name	Type	From	Description
`datasourceName`	string	path	name of the datasource
`entityName`	string	path	Name of the entity
`version`	string	query	version of the application

Response:

{
  "error": true,
  "status": 0,
  "message": "string",
  "data": [
    {
      "id": "05f44d27-4450-4f96-886d-684cd290827a",
      "type": "internal",
      "objectLabel": "breed",
      "objectName": "breed",
      "datasource": "",
      "showOnMenu": true,
      "importable": true,
      "readOnly": false,
      "audited": true,
      "listAs": ["name"],
      "relationships": [
        {
          "name": "RS_cattle_breed",
          "label": "Relationship between cattle and breed",
          "relationName": "RS_cattle_breed",
          "relationshipType": "one-to-one",
          "lEntity": "cattle",
          "rEntity": "breed",
          "lKey": "id",
          "rKey": "id",
          "key": "K_id_id",
          "joinEntity": "J_cattle_breed",
          "joinLKey": "cattle_id",
          "joinRKey": "breed_id",
          "dependency": "breed",
          "dependencyRelation": "breed",
          "required": true
        }
      ],
      "fields": [
        {
          "type": "text",
          "inlineEdit": true,
          "reportable": true,
          "searchable": true,
          "importable": false,
          "indexable": false,
          "editable": true,
          "readonly": false,
          "required": true,
          "audited": true,
          "visible": true,
          "unique": false,
          "defaultValue": "",
          "defaultExp": "",
          "comments": "",
          "group": "basicInfo",
          "label": "Name",
          "regex": "",
          "name": "name",
          "help": "",
          "precision": 0,
          "size": 0,
          "len": 0,
          "options": [],
          "optionsExp": "",
          "schema": ""
        }
      ],
      "schema": "dasec"
    }
  ],
  "entityName": "string"
}

Return external multi-datasource records by given query

POST /api/v1/datasources/querybuilder/get

Parameters:

Name	Type	From	Description
`page`	number	query	Page for pagination
`limit`	number	query	limit for pagination
`version`	string	query	version of the application

Request Body:

{
  "datasource": "employees",
  "entity": "departments",
  "columns": ["dept_name"],
  "combinator": "and",
  "not": false,
  "rules": [
    {
      "field": "dept_no",
      "operator": "=",
      "value": "10004"
    }
  ]
}

datasource: datasource to query from
entity: entity to query
columns: list of columns to query
combinator: combinator of the query (and or or)
not: if the combinator is negated
rules: list of clauses to compare
field: Field to compare to
operator: Comparison operator
value: Either the value to compare to, or a nested subquery

Response:

{
  "error": false,
  "status": 200,
  "message": "string",
  "errors": [{}],
  "data": [
    {
      "queriedColumn": "0000"
    }
  ],
  "pagination": {
    "hasPreviousPage": true,
    "hasNextPage": true,
    "previousPage": 0,
    "nextPage": 0,
    "recordsTotal": 0,
    "currentPage": 0,
    "startIndex": 0,
    "numPages": 0,
    "limit": 0
  }
}

Add a record in a datasource table

POST /api/v1/datasources/{datasourceName}/{entity}

Parameters:

Name	Type	From	Description
`datasourceName`	string	path	name of the datasource
`entity`	string	path	external entity name
`version`	string	query	version of the application

Request Body:

{
  "column": ""
}

column: Placeholder. The body of the request will vary depending on the structure of the external entity.

Response:

{
  "error": true,
  "status": 0,
  "message": "string",
  "data": [
    {
      "column": ""
    }
  ],
  "entityName": "string",
  "datasource": "employees"
}

Updates a record in a datasource table

PUT /api/v1/datasources/{datasourceName}/{entity}/{id}

Parameters:

Name	Type	From	Description
`datasourceName`	string	path	name of the datasource
`entity`	string	path	external entity name
`id`	string	path	id of the record
`version`	string	query	version of the application
`column`	string	query	Column to reference primary keys

Request Body:

{
  "column": ""
}

column: Placeholder. The body of the request will vary depending on the structure of the external entity.

Response:

{
  "error": true,
  "status": 0,
  "message": "string",
  "data": [
    {
      "column": ""
    }
  ],
  "entityName": "string",
  "datasource": "employees"
}

Deletes a record in a datasource table

DELETE /api/v1/datasources/{datasourceName}/{entity}/{id}

Parameters:

Name	Type	From	Description
`datasourceName`	string	path	name of the datasource
`entity`	string	path	external entity name
`id`	string	path	id of the record
`version`	string	query	version of the application
`column`	string	query	Column to reference primary keys

Response:

{
  "error": true,
  "status": 0,
  "message": "string",
  "data": [
    {
      "column": ""
    }
  ],
  "entityName": "string",
  "datasource": "employees"
}

column: Placeholder. The body of the request will vary depending on the structure of the external entity.

How imported entities are synced or refreshed?

To allow for syncing between the external datasource imported and changes on the original database, the system automatically refreshed and reimported every 5 minutes.

Limitations

Only records can be managed by using external datasources. External datasources structure or metadata (ej: creating new tables, adding or modyfing columns, changing primary keys or constraints) can not be updated by using DatAscend and should be handled on the original database.
Only PostgreSQL and MySQL databases are currently supported.
External Datasources should all have their own unique name when imported into the system (you can import two databases called “employees”, but when importing each one should have its own unique name like “employees1” and “employees2”)
The original databases need to be configured to accept http connections.
MySQL datasources import need to be configured to use the mysql_native_password authentication plugin.
Performance of external datasources is dependent on the internet connection.

Supported database types

PostgreSQL
MySQL