4. Application with In-Memory Database

How to create an application connected to a in-memory database?

The Goal

The basic principle of all database applications is to create, read, update and delete records in persistent data storage. We will prepare an order application based on an advanced table editable through a side-bar panel with in-memory database integration.

CRUD application

The Description

We will prepare a basic layout following the in-memory database integration serving as a storage for data. Data variables and button events will bind actions for creating, reading, updating, and deleting table records. Creating or editing an order will trigger the sidebar panel checking data validation and the difference between required actions.

The Knowledge You Will Get

1. Use InMemory Database integration

2. Create actions both for reading and writing data

3. Create a master/detail View

4. Display a View in a sidebar

5. Use events to handle button actions

The Project Structure

View[MainView]

• View[MainView]

  • Data from Action[orderList]

  • Container[cntRoot]

    • Container[cntHeader]

      • Label[title]

      • Button[btnNewOrder]

    • Advanced Table[orderTable]

      • Column1

        • Text[txtDate]

      • Column2

        • Text[txtCustomer]

      • Column3

        • Text[txtDescription]

      • Column4

        • Text[txtAmount]

      • Column5

        • Container[cntItemActions]

          • Button[btnItemEdit]

          • Button[btnItemDelete]

View[OrderForm]

• View[OrderForm]

  • Data Variable[isFormValid]

  • Data from Action[orderEntity]

  • Container[cntRoot]

    • Text Input[fldCustomer]

    • Text Input[fldDescription]

    • Number[fldAmount]

    • Date and time[fldDate]

    • Container[cntButtons]

      • Button[btnSave]

      • Button[btnCreate]

      • Button[btnCancel]

  • Parameters[id]

Actions

• Action[CreateOrder]

• Action[DeleteOrder]

• Action[GetAllOrders]

• Action[GetOrders]

• Action[UpdateOrders]

Integrations

• inMemory DB[OrdersDB]

1. Create a Basic Layouts

Let's prepare a basic layout structure for the Main content and Overlay sidebar.

Create the View[MainView] showing actual orders with a Header containing a Button to Create New Order

View[MainView]

1. Container [cntRoot] -> Flow -> Under each other; Horizontal Alignment -> Stretch; Padding -> Wide; Spacing -> Medium

   1. Container [cntHeader] -> Flow -> Into row; Horizontal Alignment -> Stretch; Vertical Alignment -> Middle;

      1. Label [title] -> Value -> Orders; Font Size -> Large; Icon Name -> mdi/package-variant; Icon Size -> Large; Horizontal Alignment -> Left; Vertical Alignment -> Middle; Item Flex -> Stretch

      2. Button [btnNewOrder] -> Value -> Create New Order;

   2. Advanced Table [orderTable] -> Spacing -> Medium;

      1. Column [Date] -> Width -> 15%

         1. Text [txtDate]

      2. Column [Customer]

         1. Text [txtCustomer]

      3. Column [Description]

         1. Text [txtDescription]

      4. Column [Amount] -> Width -> 10%

         1. Text [txtAmount]

      5. Column [#5] -> Horizontal Alignment -> Right

         1. Container [cntItemActions] -> Flow -> Into row; Spacing -> Narrow

            1. Button [btnItemEdit] -> Icon Name -> mdi/pencil-outline; Size -> Medium; Button Style -> Custom; Style Background -> LIGHT_GRAY; Style Border Radius -> 100px

            2. Button [btnItemDelete] -> Icon Name -> mdi/trash-can-outline; Size -> Medium; Button Style -> Custom; Style Background -> LIGHT_GRAY; Style Border Radius -> 100px

The following View[OrderForm] serves as the content of an Overlay sidebar, containing editable fields of existing or new orders.

OrderForm

1. Container [cntRoot] -> Flow -> Under each other; Horizontal Alignment -> Stretch; Role -> Form; Padding -> Narrow; Spacing -> Medium

   1. Text Input [fldCustomer] -> Placeholder -> John Doe; Label Value -> Customer; Required -> true

   2. Text Input [fldDescription] -> Placeholder -> Some Order; Label Value -> Customer; Required -> true

   3. Number [fldAmount] -> Placeholder -> Amount; Label Value -> Amount $; Required -> true

   4. Date and time [fldDate] -> Label Value -> Date; Required -> true

   5. Container [cntButtons] -> Flow -> Into row; Horizontal Alignment -> Right; Spacing -> Narrow;

      1. Button [btnSave] -> Value -> Save; Type Role -> Submit; Width -> 90px;

      2. Button [btnCreate] -> Value -> Create; Type Role -> Submit; Width -> 90px;

      3. Button [btnCancel] -> Value -> Cancel; Type Role -> None; Background -> Secondary; Width -> 90px;

View[MainView]

View[OrderForm]

2. Connect inMemory Database

Connecting in-memory Database will be immediately available after creating an Integration.

1. inMemory DB[OrdersDB]

   1. Primary Key -> id; Auto generate -> UUID

inMemory DB[OrdersDB]

3. Create an Order Action and Bind it

First, bind the Create New Order Button with an action sending data into the in-memory database. Creating a new unique UUID record in-memory Database based on the fourth required input parameter.

1. Create a CreateOrder Action, saving new records into the inMemory Database

2. Input Parameters:

   1. Name -> customer -> String; Required -> true

   2. Name -> amount -> Float; Required -> true

   3. Name -> description -> String; Required -> true

   4. Name -> date -> String; Required -> true

3. inMemory DB / Insert Parameters:

   1. Document ->ƒx(/* inMemory DB / Insert)

ƒx(inMemory DB / Insert):

{
    customer: params.customer,
    description: params.description,
    amount: params.amount,
    date: params.date
}

Flow Action[Create Order]

Outline[Create Order]

Bind the CreateOrder with the Button btnCreate on Click Event in OrderForm.

Because Creating New Order would be a part of an Overlay sidebar, it's essential to close it after sending data to the in-memory Database. Even a success toast message would be in place.

1. On Event -> Call Action -> Select -> CreateOrder; Parameters:

   1. customer -> fldCustomer.value

   2. amount -> fldAmount.value

   3. description -> fldDescription.value

   4. date -> fldDate.value

2. On Success:

   1. Close Overlay -> Overlay ID -> orderForm; Button ID -> submit

   2. Show Toast Message -> Type -> Success; Message -> "Order has been created."; Duration(sec) -> 3

Flow Event[btnCreate]

btnCreate[Call Action]

4. Read All Existing Data and Bind Items with Table

Reading all existing data from the in-memory Database is passed by Action Variable. In our case, it's named orderList, which data is referenced by Advanced Table. You can seek its data structure in the Data Explorer.

1. Create a new GetAllOrders Action is returning all existing data from the in-memory database.

2. On Start -> inMemory DB / Get All

Flow Action[GetAllOrders]

The orderList Variable provides the connection between the in-memory Database and MainView. Keep in mind that any changes have to be reflected by reloading the Variable.

1. Add Data from Action -> ID -> orderList; Action Name -> GetAllOrders

2. Associate existing orderTable -> Items -> orderList.data

Outline[MainView]

orderList[Parameters]

orderTable[Items]

5. Associate Table Items with orderList Datasource

Making our Table life means associating required Item elements Values with orderList Variable. Remember that the data Array is now part of the orderTable, which is visible under the name items in the Data Explorer. But, if you will select or associate any of the existing Table components, its map structure will appear item in the Data Explorer.

1. txtDate -> Value -> DATE_FORMAT(item.date, "yyyy-MM-dd")

2. txtCustomer -> Value -> item.customer

3. txtDescription -> Value -> item.description

4. txtAmount -> Value -> "$" + FORMAT_NUMBER(item.amount, 2, ".", ",")

Canvas[orderTable]

6. Open&Close Sidebar Panel on Create New Order

Both of our Views are separated until we bind them by putting OrderForm into an Overlay sidebar panel.

Clicking on the Create New Order, Button will trigger the Overlay sidebar panel; closing it a force to reload the orderList Variable to refresh the content of the Table.

1. Create an on Click Event in btnNewOrder Button.

2. On Event -> Open View in a Sidebar -> Overlay ID -> orderForm; Size -> Medium; Header Title Value -> "Create Order"; Header Font Size -> Medium; Header Icon Name -> "mdi/asterisk"; Icon Size -> Medium; View -> OrderForm

3. On Close -> Update State -> Method -> orderList.reload

Flow Event[btnNewOrder]

Clicking on the Cancel Order Button will simply close the Overlay sidebar panel.

1. On Event -> Close Overlay -> Overlay ID -> orderForm; Button ID -> btnCancel

Flow Event[btnCancel]

7. Delete Record on btnItemDelete Action

Any single of Items orderTable has its own icon for Deleting records. First, let's make DeleteOrder Action and bind it with the Delete Button.

Deleting required data is associated with the Input id parameter.

1. Create an DeleteOrder Action.

2. Input Parameter:

   1. Name -> id -> String; Required -> true

3. On Start -> inMemory DB / Delete -> Key -> params.id

Flow Action[DeleteOrder]

Over-clicking on the Delete Item Button is secured by an Open Confirmation Dialog message.

1. Create an btnItemDelete on Click Event.

2. On Event -> Open Confirmation Dialog:

   1. Overlay Header Title -> Value -> "Confirm Delete"

   2. Overlay Header Icon -> Name -> "mdi/delete"

   3. Text -> Value -> "Do you really want to delete order ${item.description}?"

   4. Confirm Button -> Text Value -> "Delete"; Background Color -> ERROR

   5. Cancel Button -> Text Value -> "Cancel"; Background Color -> SECONDARY

3. On Confirm -> Call Action -> DeleteOrder; id -> id

4. On Success -> Update State -> Method ->orderList.reload

Canvas[btnItemDelete[Click]]

Event[btnItemDelete]

8. Editing Existing Records

For editing the existing records, we will use the same OrderForm View. That means we have to add an Input parameter containing a record id, create an action reading exact data, and handle the difference between save and create buttons.

1. Add Parameter -> Name -> id -> String

2. Create an GetOrder Action.

3. Input Parameter:

   1. Name -> id; -> String; Required -> true

4. On Start -> inMemory DB / Get -> Key -> params.id

5. Add Action Variable in OrderForm View:

   1. ID -> orderEntity; Action Name -> GetOrder; Parameters id -> params.id

   2. Enabled -> params.id != null

It's good to notice that GetOrder Action would be enabled just if the input id parameter won't be empty.

Flow Action[GetOrder]

orderEntity[Parameters]

OrderForm[Parameters]

If we are editing a record, the input parameter is not empty. It's also a logic parameter switching between creating and saving buttons.

1. btnSave -> Visibility Render -> params.id != null

2. btnCreate -> Visibility Render -> params.id == null

Fields Values are associated just if in the orderEntity existing loaded data.

1. fldCustomer -> Value -> orderEntity.data.customer

2. fldDescription -> Value -> orderEntity.data.description

3. fldAmount -> Value -> orderEntity.data.amount

4. fldDate -> Value -> orderEntity.data.date || ""

9. Updating Existing Records

The last Action we will prepare in this tutorial named UpdateOrder saving the specified id record by clicking on the btnSave Button.

1. Create an UpdateOrder Action.

2. Input Parameters:

   1. Name -> customer -> String; Required -> true

   2. Name -> amount -> Float; Required -> true

   3. Name -> description -> String; Required -> true

   4. Name -> date -> String; Required -> true

3. On Start -> inMemory DB / Update -> Key -> params.id

   1. Document ->ƒx(/* inMemory DB / Update)

ƒx(inMemory DB / Update):

{
  customer: params.customer,
  description: params.description,
  amount: params.amount,
  date: params.date
}

Flow Action[UpdateOrder]

1. Create and on Click Event of btnSave.

2. On Event -> Call Action -> Select -> UpdateOrder; Parameters:

   1. customer -> fldCustomer.value

   2. amount -> fldAmount.value

   3. description -> fldDescription.value

   4. date -> fldDate.value

3. On Success:

   1. Close Overlay -> Overlay ID -> "orderForm"; Button ID -> "submit"

   2. Show Toast Message -> Type -> Success; Message -> "Order has been saved."; Duration(sec) -> 3

Flow Event[btnSave]

btnSave[Call Action]

10. Validating The Order Form

The last step of our tutorial would be to set up the OrderForm Validation, checking up on missing input Values. Until the Form isn't valid, the save or create Button won't be enabled.

1. Create a new State Variable isFormValid:

   1. Initial Value -> fldCustomer.valid && fldDescription.valid && fldAmount.valid && fldDate.valid

2. btnSave -> State Enabled -> isFormValid.value

3. btnCreate -> State Enabled -> isFormValid.value

The Conclusion

Making the basic nice-looking CRUD application using the in-memory Database cannot be easier. We have done a great job in the ten steps.