Demo
Blog /

This Microservice Should Have Been a SQL Statement

Hubert Dulay
Decodable

In event-driven architecture, functions can be used to replace backend microservices. In this blog, we will demonstrate how to use Decodable as a serverless function written in SQL performing stateful transformations, replacing unnecessary microservices in your application. "That microservice could have been a SQL statement" made real, with Decodable.

Functions as a Service (FaaS)

Functions are serverless, event-driven, single-purpose, programs hosted and maintained by the cloud service provider - so the developer doesn't waste time on that zero value task. Serverless functions make the deployment of new code quicker and easier to automate with reduced downtime with overall reduced cost.

Serverless implies stateless functionality. Which means the logic built in the functions themselves must also be stateless. In order to hold state, the functions will need to persist the state in a database.

In an event-driven architecture, functions written in SQL can be used to replace backend microservices that don’t interact with the UI. If you are familiar with the SAGA pattern, you typically will develop a set of microservices that work together as a transaction workflow. You could potentially replace some of your microservices to be Decodable streaming pipelines.

You can use Decodable and SQL to create and run serverless functions that perform stateless transformations replacing unnecessary microservices in your application. This radically simplifies and speeds up development, maintenance and operations as it's just SQL statements with no infrastructure to provision or manage.

Materialized View

Materialized views are persistent, precomputed results. When users query the materialized view, the results return quickly because they come from the persisted precomputed data. Traditional views require computation at the time of invocation and will take longer.

The diagram below shows a database replicating data to another database using a WAL (write ahead log). A WAL keeps track of all the inserts, updates, and deletes in the “active” database and executes them in sequential order in the “passive” database. The result is an exact copy of the “active” database in the “passive” database. That copy is what is called a materialized view.

Decodable change streams can do exactly what the WAL is doing. In the diagram below, a microservice sends an event to Decodable using its REST API. Decodable creates a change stream by emitting records that describe the changes. Decodable’s Postgres connector can then interpret the change stream that results in a materialized view in Postgres.

Example

The table below shows a result that has not yet been materialized. The query engine will need to summarize the count column grouped by Col1. This is the state where most data pipeline sinks leave you.

HTTP Status Count
200 1
200 1

The next table shows a table that has already been materialized. This is the state we would like to have Postgres have without having to execute another query to get the true count.

HTTP Status Count
200 2

In Decodable’s examples repository, you can walk through how to build a pipeline that materializes data into Postgres. It will use the datagen source connection that generates mocked envoy logs and produces an updated count in Postgres.

CQRS Use Case

In a CQRS (Command Query Responsibility Segregation), an event-driven microservices pattern, you can generate materialized views for microservices that query and provide data to the application.

In the diagram above, a command microservice takes changes from an application and writes it to Decodable which then performs an aggregation and sends a changes stream to Postgres to generate a materialized view. That view is then used by the query microservice to send results back to the application.

Real-time Dashboards

This same pattern can be used to populate real-time dashboards by materializing views of events from your applications. It speeds up dashboards by not having to wait for queries to be executed since Decodable has already precomputed the results. You can now also expect to have fully curated data in the dashboards without the need to wait for batch ETL processes to do it for you.

Github

This Decodable’s examples repository, you can try the change-streams example. Run the commands step-by-step to build a pipeline that generates a materialized view in your Postgres database. Please also contact us with any questions on how to extend this example to your specific use case. Good luck!

Watch a video of this demo:


You can get started with Decodable for free - our developer account includes enough for you to build a useful pipeline and - unlike a trial - it never expires.

Learn more:

A Practical Introduction to the Data Mesh

There’s been quite a bit of talk about data meshes recently, both in terms of philosophy and technology. Unfortunately, most of the writing on the subject is thick with buzzwords, targeted toward VP and C-level executives, unparsable to engineers. The motivation behind the data mesh, however, is not only sound but practical and intuitive.

Learn more

Streaming Data Pipelines with SQL

Streaming data systems have been growing more capable and flexible over the past few years. Despite this, it is still challenging to build reliable pipelines for stream processing. In this Data Engineering Podcast hosted by Tobias Macey, Eric Sammer, discusses the shortcomings of the current set of streaming engines and how they force engineers to work at an extremely low level of abstraction. He explains how Decodable addresses that limitation and lets data engineers build streaming pipelines entirely in SQL.

Learn more

Opinionated Data Pipelines

Schema-on-write is a feature many engineers have used to their advantage when building data pipelines. In this blog, we will introduce this idea and discuss how it fits into tools like dbt’s and Decodable’s opinionated approach to building streaming data pipelines.

Learn more

The Top 5 Streaming ETL Patterns

ETL and ELT are traditionally scheduled batch operations, but as the need for always-on, always-current data services becomes the norm, realtime ELT operating on streams of data is the goal of many organizations - if not the reality, yet.In real world usage, the ‘T’ in ETL represents a wide range of patterns assembled from primitive operations. In this blog we’ll explore these operations and see examples of how they’re implemented as SQL statements.

Learn more

Heading

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Suspendisse varius enim in eros elementum tristique. Duis cursus, mi quis viverra ornare, eros dolor interdum nulla, ut commodo diam libero vitae erat. Aenean faucibus nibh et justo cursus id rutrum lorem imperdiet. Nunc ut sem vitae risus tristique posuere.

Learn more
Tags
Pintrest icon in black
Demo
Product
Ideas & Opinions
SQL
Development

Start using Decodable today.