Postgrest

Overview

Framely also supports the backend component in form of postgrest provider. The backend component can be declaratively defined in two steps: First, create database tables needed by service by adding storage annotation to frames, secondly, provide function implementation using SQL to express business logic. Using backoffice annotation, you can specify the operation team can access these tables via back office.

There are a couple advantage of using backend component approach to build service provider.

  1. In addition to declaratively building database as content management system, Framely backend component allows you to implement the service declaratively too using SQL. This make it possible for the business analyst to build backend.
  2. Since tables are created based on the data type in the API schema, so no explicit conversion code is needed, rows in the table or view in the database are automatically converted into an object in the Framely.
  3. The admin interface or backoffice can also be automatically created based on annotation so that the operation team can use backoffice to provide service.
  4. Backoffice components can be reused by cloning for Framely hosted solution.

data-management How Framely, PostgreSQL and Backoffice Work Together

Annotations

Features

Provider annotations include storage annotations and back office annotations.

  • Storage annotations defines the database schema needed by the backend component, which can then be used to create the database for provider.
    • Column information for table
    • Set a default value of a column
    • Set not-null constraints
    • Set unique constraints
  • Back office annotations are used to define the user experience of the back office for the backend.
    • Upload a picture to a cell, and it will be stored as a URL
    • Select a value from a dropdown list

How To Use

Before starting, turn on Storage Enabled in Frames field to enable storing frames as tables in the database. There are two levels of provider annotations: slot level and frame level.

  • At a slot level, you can configure the column properties and back office annotations in the Schema - Slots field.
  • At a frame level, you can configure table constraints in the Annotation field.

provider-annotation

SQL Data Type

To create tables to store the frame instance, we need to map each slot of frame to a column in the database. SQL data type is a slot annotation, it defines the SQL data type for the corresponding column for the given slot. Normally, we will automatically decide the SQL data type for each slot but if the slot type is kotlin.String or customized entity(e.g. like Demo.test.City in the below picture), you need to specify the database type of the column.

Supported formats are char(n), varchar(n), text. Replace "n" with a number between 1 and 10485760, e.g. char(16). To learn more about Character Types, click here.

sql-data-type

Default Value

Default value is a slot annotation. You can use a constant or an expression as a default value. When there is no value specified in the column, the column will be filled with its default value.

default-value

For example, if the type of slot is java.time.LocalDate, you can set its default value as '2022-6-15' or now()::date. For details about default value in official documentation, click here.

Allow Null

allow-null

Allow null is a slot annotation that is turned on by default. Allow null is a column constraint and it means the column can be null. If you turn off allow null, it indicates that the column can't be null. To learn more about it, see Not-Null Constraints.

Unique

Unique is a frame annotation. If there is only one slot in a group of unique constraints, unique constraints ensure the data contained in the corresponding column is unique among all the rows in the table. If not, unique constraints make sure that the combination of values in the indicated columns is unique across the table.

To add one group of unique constraints, in the Annotation field, click Add and select unique keys. If there are 3 columns which should be unique individually, be sure to add 3 groups of unique constraints.

unique

To learn more about it, see Unique Constraints.

Tips

For now, among table constraints, we only support unique constraints. If you need more table constraints, please let us know.

URL

When sending messages to users, compared to plain text, rich cards can carry more information, like pictures, titles, descriptions, etc. To add a picture to a rich card, you need the picture URL.

Back office supports uploading pictures and storing them as URLs. You can upload pictures in back office and get the picture URLs using PostgreSQL Function.

process Process of Getting Picuture URL

url

To enable URL, select kotlin.String when choosing Type and set data type as text. Once done, there will be a switch: URL. Turn on URL so that you can upload pictures in that column.

For example, there is a slot called catPicture in a storage-enabled frame, and URL is enabled in this slot. Here are the steps to uploading pictures in back office.

  1. Go to back office, find the corresponding table, click Create.
  2. Upload a picture in the column called catPiture and fill another column called catName.
  3. Back to the page displaying the table, you can view the picture that you just uploaded.

back-office

Input Type

There are two input types: text and dropdown. By default, input type is text which means operators can type raw input directly. If you want to make it easy for operators to input legit and compatible value, you can switch input type to dropdown.

dropdown

Dropdown List is a JSON array, which should return a list of values that are at least legit and potentially compatible so that operator can pick the correct value. The content of dropdown list includes two columns (id, name): where name will be displayed, and id will be assigned as value.

For example, if a column is used to store a city, its dropdown List is like:

[ { id: 'NY', name: 'NewYork' },
  { id: 'LA', name: 'Los Angeles' },]

Connection

Once you deploy a chatbot which integrates with a postgrest provider, in the Connection field, you can get the URL of backoffice along with Admin Email and Admin Password to log in.

connection

Note

Learn how to integrate with a postgrest provider, check out Step 3: Import Component.

Function Implementation

As mentioned in Implement Functions, there are two kinds of ways to implement a function:

  • In Provider Dependent functions, use PL/pgSQL language to implement.
    • ❗ Provider-dependent functions should always return a frame in which the slot's type is compatible with the return column's type in the same index.

      For example, if the slots in a frame are [id, name] of which types are [kotlin.Int, kotlin.String], the types of return columns should be [bigint, text] instead of [text, bigint].

    • The name of a slot and of a column in the same index can be different.

      In the above example, the return columns can be [userId, userName].

  • In Kotlin functions, write function bodies in Kotlin.
    • Kotlin functions can be used to convert the value returning from a provider-dependent function to a desirable format.

      For example, if a provider-dependent function returns a multi-value frame with only one slot, you could use a Kotlin function to convert the multi-value frame into a multi-value slot so that you can use the return value directly in Value Recommendation.

    • Learn how to implement more Kotlin functions, check out Kotlin Function.

/* 
  Suppose a provider-dependent is getFoodCategory() which returns a list of frame. 
  There is one slot called returnValue in the frame. 
*/
return getFoodCategory()!!.map{it -> it.returnValue!!} 

Types Conversion

  • When you call the Provider Dependent function, you pass the entities (or frames) and we will convert their types to SQL data types (or composite types) automatically, so you can use these parameters in your function body.
  • When the function returns a set of values of composite type, we convert the composite type back to a frame, so you can display or use these values in the Framely environment.

conversion Type Conversion Between Framely and Postgrest

  • Here is the conversion between entities and SQL data types:
EntitySQL Data Type
kotlin.Int / java.time.Yearbigint
kotlin.Floatdouble precision
Customized entity (Builder-created Entity) / kotlin.Stringtext
kotlin.Unit, kotlin.Any / kotlin.DayOfWeek / kotlin.ZoneIdtext
kotlin.Booleanboolean
java.time.LocalDate / java.time.YearMonthdate
java.time.LocalTimetime without time zone
java.time.LocalDateTimetimestamp without time zone

Postgres Functions

If you are familiar with SQL, writing SQL commands within a PL/pgSQL function will be easy. For example, if you've stored your customers' information in your database and you want to get a customer's name by their user ID, which is an input parameter named userId_parameter, the code will be like this:

BEGIN
    RETURN QUERY 
    SELECT name FROM "Info" WHERE "userId" = "userId_parameter";
END

Besides, PL/pgSQL language also supports simple loops and Conditionals.

Last Updated: