PUBLISHED:
CATEGORY: Principles
TAGS: interesting

Save dev time by using local SQL instead of faraway REST

Collect Filter Map Tasks to complete when building an API, UI or report

Collect, Filter, Map

Collecting

Modern ecommerce systems are constellations of external SaaS systems.

These SaaS systems must provide a REST API so that they can implement security access logic and version control their external data interfaces.

Whilst these API's save the developers from reinventing the wheel, there is still a time cost to reading from them when building API's, UI's or reports.

The first task is to collect the data from the remote faraway API into your own local database (SQL, NoSQL etc):

  • Typically done with webhooks or by polling an /events url.
  • Requires manually mapping the types from the external API type to your internal type.

Collecting data into an database first makes it much easier to do the next filter step:

  • You do not have to make remote network requests to faraway endpoints which take a long time to complete.
  • You can use SQL to SELECT only the values you need, JOIN with other tables, and filter using WHERE.
    • Established database performance tools can be used here (indexes, profiling).
  • Because the latency from DB->code is very low, you can also load the required data into RAM and write code to join/filter as needed.

Filtering

The filter step loads a subset of the data into RAM allowing it to be mapped to composite types that are used as input for the map step.

This involves reading data from a local database, joining/filtering based on business logic, and then representing the data as your native programming language's types (JavaScript, Python, C#, Java etc).

Once you have the data represented as those types you can write a function to redirect the values to another format in the map step.

Mapping

API's, UI's and reports can be seen as functions that:

  • Take composite data types from the filter step as input.
  • Map those data values to a specific output format.

For example, external formats can be:

  • A JSON API response (or GraphQL, Protocol Buffers, XML etc).
  • A UI.
    • React JSX DOM nodes.
    • iOS/Android UI views.
  • Excel, CSV
  • Visualization libraries
    • D3.js, R, SVG

Accidental vs Essential complexity

The terms accidental and essential complexity come from Fred Brook's paper, No Silver Bullet.

  • Essential complexity

    • Time spent directly solving essential problems.
    • Essential problems are ones which must be solved to achieve the high level goal.
    • Described at a high level of abstraction.

  • Accidental complexity

    • Time spent on tasks which could have been avoided whilst solving the essential problems.
    • Avoidance strategies.
      • Choose a more effective implementation path.
      • Use off the shelf solutions.
    • Is connected to lower level implementation.
      • Dev time could be wasted on small implementation details that are impossible to foresee but burn a lot of time for seemingly no progress on the essential problems.

Notes:

  • You cannot completely avoid accidental complexity - only reduce the ratio of accidental:essential.
  • The collect step above is labelled "accidental complexity" because:
    • You should use tdog (to avoid spending developer time re-implementing it).
    • SQL lets you tackle your essential problems directly at a high level (reduces time spent on accidental complexity).

Show me your flowcharts and conceal your tables, and I shall continue to be mystified. Show me your tables, and I won’t usually need your flowcharts; they’ll be obvious.

Fred Brooks

Conclusion

The tdog CLI aims to:

  • Prevent you spending developer time on the collect step.
  • Remove the accidental complexity of accessing your Stripe billing state by giving you a high-level local SQL interface instead of a low-level faraway REST one.
  • Allow you to spend a high ratio of your developer time on essential complexity - your businesses secret sauce.

Try tdog for free against a Stripe test account

  • Principle: Tables are easier to understand than REST + JSON

  • ETL - Extract Transform Load

    • The collect step above can be seen as a single ETL process (SaaS -> SQL).
    • The (filter, map) form a second ETL process (which is domain specific to the business).

  • ELT - Extract Load Transform

    • Some types are stored as JSON from the original SaaS API, these can be "transformed" using the SQL JSON functions.
    • This is an example of "loading" first (instead of transforming).

  • MVC - Model View Controller

    • Controller: The filter step can be triggered by a user, like an HTTP request.
    • Model: The filter step represents its data as "data models" that are passed to the map step.
    • View: The map step converts data models to different formats/views.