External Datasources

DatAscend has the possibility of connecting various external datasources (databases) via PostgreSQL’s Foreign Data Wrappers.

Limitations

Although DatAscend allows for connecting external datasources, it has various limitations. First, all limitations for the FDWs used are also limitations for DatAscend:

• Postgres FDW
• postgres_fdw
• mysql_fdw

Internal Limitations

DatAscend can not:

• Change the metadata (Constraints, Relationships, Column names, add new columns, etc…) of external tables.
• It can not create new tables or views.
• It does not give access to the remote server. This means there could be unexpected errors on the side of the database (like the database not being structured correctly for use with FDWs) that DatAscend would not be able to catch.
• Currently it does not allow fetching for related entities from another entity. DatAscend does import the relation metadata into the json files, but it can not query for this related data.
• DatAscend does not migrate databases into its own. It is not an ETL system.

API

The API for external datasources allows for connecting and disconnecting datasources, and for CRUD operations with the entities of the datasource. Currently the API can not change external entities’ relationships or metadata (keys, columns, etc.) from within DatAscend.

Endpoints to interact with the Datasources themselves:

GET /api/v1/datasources: Gets all datasources POST /api/v1/datasources: Adds a new datasource:

{
  "name": "Datasource name",
  "type": "mysql",
  "host": "localhost",
  "port": 3306,
  "username": "Database username",
  "password": "Database password",
  "database": "Database name",
  "synchronize": false,
  "logging": true,
  "external": true
}

GET /api/v1/datasources/{datasourceName}: Get single datasource PUT /api/v1/datasources/{datasourceName}: Update a datasource DELETE /api/v1/datasources/{datasourceName}: Deletes a datasource GET /api/v1/datasources/{datasourceName}/{entity}/metadata: Gets the metadata for an specific entity from an specific datasource.

Entity Records CRUD

For getting the records of external entities, the endpoints are the same as the ones for internal entities. Specifically for external datasources, to avoid having two entities from different datasources with the same name, the entities are named: datasourceName~entityName. For example, an entity salary from a datasource named employees would be imported as employees~salary. This means that when querying for an entity’s records, instead of replacing {entity} with salary, the endpoint would expect employees~salary.

GET /api/v1/entities/{entity}: Gets all records for a single entity. Allows for pagination, sorting, key filtering and grouping by using the normal parameters. GET /api/v1/entitites/{entity}/{id}: Gets the record that matches the value in the id param for an entity.

For creating, updating and deleting records, The endpoints are instead:

POST /api/v1/datasources/{datasourceName}/{entity}: Adds a record in a datasource table/entity. (Body varies depending on the entity structure). PUT /api/v1/datasources/{datasourceName}/{entity}/{id}: Updates a record in a datasource table/entity. (Body varies depending on the entity structure) DELETE /api/v1/datasources/{datasourceName}/{entity}/{id}: Deletes a record in a datasource table/entity.

Queries between multiple datasources

DatAscend does not allows for direct relationship queries, but it does allow to combine queries using its multi-datasource query endpoint. Basically, DatAscend can query data from one entity (from any datasource) based on a query to another entity (either from the same datasource of another).

POST /api/v1/datasource/querybuilder/get: Returns external multi-datasource records by given query.

{
  "datasource": "datasource1",
  "entity": "entityName",
  "columns": [
    "columns"
  ],
  "combinator": "and",
  "not": false,
  "rules": [
    {
      "field": "columnName",
      "operator": "=",
      "value": {
        "datasource": "datasource2",
        "entity": "entityName2",
        "columns": [
          "column"
        ],
        "combinator": "and",
        "not": false,
        "rules": [
          {
            "field": "field",
            "operator": "=",
            "value": "10000"
          }
        ]
      }
    }
  ]
}

The body consists of two main sections: Everything inside rules and everything outside rules.

Outside rules we have the following properties:

• datasource: Name of the datasource.
• entity: Name of the entity.
• columns: All columns to be queried.
• combinator: logical operator that joins both queries.
• not: if true, operation is negated.

Inside rules we have the rules for the query, which can either be an object with the following properties:

• field: field for the query filter.
• operator: logical operator.
• value: value to compare for.

Or a recursive object where value is another object with the same properties as the body (as seen in the example above).