Mutliple Table Insert Using a Single POST - Common Coding Examples

This is part of a series of blogs from Espresso Logic’s Application Engineering team on common coding tasks and how they can be accomplished more efficiently using Espresso Logic. The purpose of these blogs is two fold:

• Provide code examples of how Espresso system can be used to solve certain use cases
• Provide code samples that you can use within your programs while developing apps

This blog deals with a design pattern all web and mobile transaction applications have to deal with – how to create a multiple table self registration using a single POST. This is both a performance issue, reducing the number of calls to and from the server, as well as a demonstration of how Espresso Logic advanced design handles complex transactions without coding.  In the prior blog we dealt with rules used to validate credit cards.

Self Registration Design Pattern

User self registration is a standard design pattern for some web and mobile applications. This becomes a bit more challenging when the database model is using multiple tables.  Using Espresso Logic , you can create a multiple table insert using a single POST.  The back-end server can be run in the cloud or on-premise to connect to your database and quickly expose RESTful endpoints for each of your SQL tables, views, and stored procedures.  Creating a compound nested document (called a Resource) will allow us to design an API that will  join related tables into a single REST endpoint.

Developers can design a Mobile front-end using a drag-and-drop tools that bind each of the text fields with the JSON nested document fields. Using a single POST,  this data is sent to the Espresso Logic REST server to handle the details.

Self Registration Account Setup

Data Model

The model below shows tables that hold each of the relevant parts of the self-registration entry. Each of these tables has an auto increment primary key and a foreign key relationship to the Person table  (e.g. Password, PersonPhone, Address, EmailAddress) to support the one-to-many relationship cardinality. So how can we create a single POST to insert into multiple tables and propagate the primary key?  Connecting to any SQL database, Espresso Logic  will instantly create REST API definitions for every table, view, and stored procedure.

Multiple table data model

Create new Resource

Next, we us the Espresso Logic Design Studio to select one or more tables from a point-and-click interface to create this new compound document endpoint (see Resource documentation), which is a combination of each of the child tables. The ‘Join’ for each child is be automatically completed using the existing relationships defined in the schema.  The unused Attributes for the POST can be excluded including the primary key (auto increment) and the foreign parent key PersonID (these are handled by the server) in each child table.

Multiple Table Resource Setup

POST JSON Sample Data

The REST Lab in Espresso Logic Design Studio can be used to test this new resource.  We enter all the values of the JSON self registration (shown below) and use the POST command.

{
    "Title": "Mr",
    "FirstName": "Test",
    "MiddleName": "M",
    "LastName": "Record5",
    "Phone": [
      {
        "PhoneNumber": "(407) 555-1216",
        "PhoneNumberTypeID": 1
      }
    ],
    "Address": [
      {
       
        "AddressTypeID": 2,
        "AddressLine1": "555 Main St",
        "AddressLine2": "Apt 6",
        "City": "Maitland",
        "StateProvinceID": 15,
        "PostalCode": "32751"
      },
      {
        "AddressTypeID": 1,
        "AddressLine1": "555 Main St",
        "AddressLine2": "6",
        "City": "Maitland",
        "StateProvinceID": 15,
        "PostalCode": "32751"
       
      }
    ],
    "Email": [
      {
        "EmailAddress": "tes6t@gmail.com"
      }
    ],
    "Password": [
      {
        "PasswordHash": " password6",
        "Username": "user"
      }
    ]
  }

How it works

The Espresso Logic REST Server will take this JSON and perform the necessary validations, derivations, and event processing (using Reactive Logic Programming) and then insert the Person first (returning the primary key) and then propagate this down to each of the related children in a single transaction.  All the logic, validations, and events must succeed or the entire transaction is rolled back.  This is not an easy trick to perform for any REST API – so do not try this at home without a deep understanding of SQL, transactions,  the data model and relationships between parent and child. Espresso Logic does this out-of-the-box with no code required.

 Live Browser

Espresso Logic offers  Live Browser  which is an Instant HTML5/Angular view of your data using the active schema. This will  lets us view the one-to-many relationships of the self registration process.  Try it yourself and see the power of Espresso Logic.  View the samples on GitHub. Espresso Logic ‘Mutliple Table insert using a Single POST’ examples are part of the extended demo.

Summary

The mobile and web developer want a simple and single REST endpoint to populate and update user information.  The server should be able to handle the complexity and hide the underlying data model.  Using the Espresso Logic back-end server eliminates all the code required to connect to the database, create a nested document endpoint, and propagate the primary key to all the related child tables.  Live Browser gives you an instant view to see and test your results – again, no code.  This is the fastest way to build and deliver mobile solutions on the market today.