Data as a Service
Without writing a single line of code, using Trillo Workbench, you can create databases and tables in minutes and expose them as secure APIs that can be used by a rest or SDK client.
The following diagram shows how you can create or discover database schemas using UI and expose them as APIs. Without writing a single line of code, you will be able to access Google Cloud SQL (MySQL, Postgres, Microsoft SQL) as a service in a few minutes.
Data as a Service - Publish Database as Secure APIs
Trillo data layer is designed to be generic and independent of the type of database such as relational, document, graph, etc. Therefore, some of the API use generic terms from UML (Unified Modelling Language). In the context of particular database technology, a UML concept may not support all the features for simplicity. For example, a relational DB 'table' can also be referred to as 'class', but it does not support inheritance. The following table describes the common terms (they may be used interchangeably).
Equivalent Terms | Concept |
table, class, collection | Represents a structure with attributes. In a relational DB, it corresponds to a table. |
column, attribute | A column of a table, an attribute of a class, a property of an object, etc. |
association, relation | Relationship between tables or association between classes. |
object, record | An instance of a class or a database record. |
Trillo Workbench by default provisions Cloud SQL in Google Cloud (MySQL). It uses it for storing data, metadata. It is also used for the new tables. By default it creates the following two databases:
- cloud_vault: this is used as the user, tenant store. Its tables can be customized (while retaining a few important attributes as they are). You can't add new tables to this database using the workbench UI.
- cloud_common: this is used for several system tables that can be configured (while retaining a few important attributes as they are). Any new table specified in the application is created inside this database.
Trillo Core platform permits the creation of several databases and integration with other DB technologies such as Mongo. But this functionality is not available through Trillo Workbench UI.
Trillo Core platform permits the creation of several databases and integration with other DB technologies such as Mongo. But this functionality is not available through Trillo Workbench UI.
Trillo Workbench UI uses the term class as an alias of a table. Similarly, the term attribute is used for a column. The reason for using class and attribute it to have generic terms for other databases such a MongoDB.
Selecting the Database menu option, you can add new tables, edit exiting tables schema, or remove it from the system.
- The UI is intuitive.
- It provides a grid to create columns of tables, an extended pane to specify the detailed meta property of each column (discussed below).
- You can also use the JSON editor to edit the schema as a JSON file.
This feature is coming soon.
At this step, you may seed your database by importing it from the CSV file. Simply select the Import Data menu option.
The platform makes all database tables and their metadata available as an SDK toolkit for use inside a serverless function.
An example API to save an object is shown below (for Java)
public static Object getObject(String className, String id)
parameters:
className: the name of table (a DB table is also generically
referred as class.
id: primary key (identifier of the record)
returns:
If the record is found then it returns a Map (HashMap).
Else, it returns Result object.
Result object has following properties:
status: success | failed | unknown (in this case failed)
message: the error message
code: HTTP code
data: Any application specific object that is returned to the invoker (it is
generally null if there is an error).
The platform also automatically publishes restful API for each table. You can view it by selecting API Tab (as shown in the above screenshot).
Each restful API has the following format:
ds/<operation>/shared/common/<table name>
In the above URL, the meaning of each part is explained below:
ds: it means that this is an API for the data service.
<operation>: it is the name of the operation such as 'save',
'getObject', 'page', etc.
shared: it means it is a 'shared' resource by Trillo provided
applications and customization. (You can ignore it for now).
common: it is the name of the default database (in the DB you will
see it as 'cloud_common'.
<table name>: name of the table.
By selecting the API tab, you can exercise an API. For example, you can add few records using save API and retrieve them using get API. See the diagram below.
API tab for its specification and testing
SQL templates (notice SQL Templates tab above) are used to write complex queries that become available as APIs. Any parameters that need to be substituted are specified by enclosing within 3 curly braces as {{{parameter}}}.
An example SQL template is shown below:
select * from cloud_vault.user_tbl as u where u.email like '{{{prefix}}}%'
------
Here, prefix is a parameter.
when this template is invoked as API by passing the following parameters
as JSON:
{
"prefix" : "a"
}
THIS IS NOT THE BODY OF THE API. The body of the API with URL is shown below.
Say, this template is given the name "UserByEmailPrefix". It will be invoked as API as follows:
URL: /ds/sqltemplate/shared/common/User
Method: POST (although the API does not update the data, in order to keep
it readable, Trillo uses POST method).
The complete POST body will be a JSON as follows.
{
"sqlTemplate": "UsersByEmailPrefix",
"size": 10,
"start": 1,
"params" : {
"prefix" : "a"
}
}
"start" and "size" are pagination parameters.
Database queries are similar to the templates above except that they are specified within the scope of a table (therefore they are not bound by table access control roles). Queries can be specified by selecting the "Queries" menu from the left navigation bar.
A query can be created for the database table or Google Cloud's BigQuery table.
Prefer using queries over SQL templates. They provide a singular view of all queries without having to step through all classes.
Trillo Workbench provides several tables to support its out-of-the-box functionality. For example, there are tables for user management, task management, audit and log messages, files, folders, etc. There may be instances when you want to extend these tables with additional columns. For example, you may need to add columns to the user's table. Trillo Workbench supports such customization. You will notice a red color button Customize on the toolbar for system tables. Once a table is customized, it is no longer treated as a system table.
We recommend that you don't delete or change the type of existing columns of a system table. It may break the system. You can change the length of the string type attributes. You can change other meta properties such as validation expression, indexed or not, unique or not, etc.
- Create a complete relational model for your application using UI.
- Customize the Trillo-provided system tables.
- Write complex queries and make them available as APIs.
- CRUD APIs are automatic.
- Test APIs.
The data model is a significant part of an application. But this is just a starting. CRUD APIs are directly not enough. For example, an application often requires programmatic data validations before data is written to the database. Due to one call, the multiple tables may be updated. In the following section we will how the application behavior is implemented.
Last modified 1yr ago