DEV Community: Todd McNeal

The Perfect Configuration Format? Try Typescript

Todd McNeal — Wed, 17 Nov 2021 21:13:44 +0000

A lot of ink has been spilled about configuration file formats. Popular formats like JSON, TOML, YAML, and XML each have their advantages and drawbacks. There's an alternative that should be receiving more attention: Typescript!

Before getting into why Typescript is a compelling choice for config files, let's review the common file formats used in a typical web application today:

XML: Back in the late 90s to mid 2000s this was king both as a messaging protocol and flat file format. It gets a bad rap, but there is a lot to like about XML as a config file format, including great tooling support and a robust schema definition language in XSD.
JSON: Extremely common as a data interchange format in REST APIs, and also very popular as a config file format. For a lot of developers this strikes the right balance of feature-set and simplicity.
YAML: This format is packed full of features that I've never needed to use. Due to its focus on terseness, I find its features to be less discoverable versus something more verbose like XML. For me, it's the Perl of configuration formats.
TOML: Short for Tom's Obvious, Minimal Language - this format doesn't go overboard on features while still being more terse than JSON or XML. However, the hitchdev blog makes a compelling argument about difficulties with using TOML for large config files.

Here's where I think Typescript shines:

Typescript supports comments

My biggest criticism of JSON is that it doesn't support comments.
In 2012, Douglas Crockford, the creator of JSON, had an interesting reason for not supporting comments in his original spec:

I removed comments from JSON because I saw people were using them to hold parsing directives, a practice which would have destroyed interoperability. I know that the lack of comments makes some people sad, but it shouldn't.

Suppose you are using JSON to keep configuration files, which you would like to annotate. Go ahead and insert all the comments you like. Then pipe it through JSMin before handing it to your JSON parser.

It's ten years later, and unsurprisingly, generating JSON config files from some intermediate “annotated” file never caught on. What is surprising is that support for comments never made it into subsequent revisions of the JSON spec.
Since the release of Crockford's original spec, JSON was codified in RFC-4627 and revised in
RFC-7159 and RFC-8259. The latest RFC was published in 2017 and the only change there was defining that JSON must be encoded in UTF-8, so it seems unlikely this will ever change.

Keeping the JSON spec simple has a lot of benefits,
including backwards compatibility and helping avoid the various security issues that come along with a more powerful,
but complex, spec. However, think of the number of applications that would have benefited from the ability to add comments.

Wouldn't the benefits in readability outweigh any concerns around overloading comments for unintended use-cases?

Typescript lets you easily define a configuration “schema”

I love strongly-typed languages because it helps me avoid mistakes. This is also why I like using configuration files that have a pre-defined schema. Instead of waiting until runtime to know that my config file is invalid, config files that ship with a schema allow me to see my mistakes inline within my code editor.

JSON supports schemas in the form of JSON Schema. JSON Schema is a bit of a misnomer, since it supports defining schemas for multiple file formats including TOML and YAML. While JSON Schema doesn't feel nearly as bloated as XML's schema equivalents of XML Entities and XSD, it's not something that I find easy to add to a config file in my own applications.

Here's an example JSON Schema for a config file with four properties:

{
  "$schema": "https://json-schema.org/draft/2019-09/schema",
    "title": "MyAppConfig",
    "type": "object",
    "properties": {
    "locale": {
      "type": "string",
    },
    "timezone": {
      "type": "string",
    },
    "logLevel": {            
      "type": "string",
      "enum": ["trace", "debug", "info", "warn", "error", "fatal"]
    },
    "environment": {
      "type": "string",
      "enum": ["local", "dev", "staging", "production"]
    }
  },
  "required": ["locale", "timezone", "logLevel", "environment"]
}

Typescript gives you this same ability to write a schema for your configuration file in a language that's more concise,
easier to read, and that a lot of developers are already familiar with.

Here's that same “schema” in Typescript:

interface MyAppConfig {
  locale: string,
  timezone: string,
  logLevel: 'trace' | 'debug' | 'info' | 'warn' | 'error' | 'fatal',
  environment: 'local' | 'dev' | 'staging' | 'production',
}

And here's a valid configuration using this "schema":

export const config: MyAppConfig = {
  locale: 'en-US',
  timezone: 'America/New_York',
  logLevel: 'info',
  environment: 'local',
}

Typescript lets you keep your configuration DRY

It's difficult for a configuration file format to include features that keep the config DRY without also making the config spec complex. Typescript has great facilities for splitting configs into reusable segments.

Consider the common scenario of having separate config files for different environments in your application (e.g. a separate configuration file for your local environment vs staging vs production). Each configuration file will likely have a set of values that are common across all environments,
as well as values that change with each environment.
Using Typescript's "Omit" Utility Type, we can define a single base config that's shared across all environments:

export const baseConfig: Omit<MyAppConfig, 'environment' | 'logLevel'>: {
  'locale': 'en-US',
  'timezone': 'America/New_York',
}

...and define per-environment values separately:

const localConfig = {
  'logLevel': 'debug',
  'environment': 'local',
}

export const config: MyAppConfig = { baseConfig, ...localConfig }

Eventually the list of omitted properties would become unwieldy, so for larger config files, we can define an interface explicitly for the base configuration and use Intersection Types to construct the final config schema:

interface BaseConfig {
  locale: string,
  timezone: string
}

interface EnvironmentSpecificConfig {
  logLevel: 'trace' | 'debug' | 'info' | 'warn' | 'error' | 'fatal',
  environment: 'local' | 'dev' | 'staging' | 'production',
}

type MyAppConfig = BaseConfig & EnvironmentSpecificConfig

Typescript configs can be made immutable

One clear benefit of JSON is the simplicity of its spec.
We know from XML that benign behavior defined in the spec can be a landmine for parser implementers. The Billion Laughs attack exploited XML's support for nested XML entities (basically schema definitions) in an XML document. In a 20 line XML file, a vulnerable XML parser would end up loading billions of entity references and run out of memory.

We're talking about config files here and not file interchange formats where you need to defend against a malicious sender. But nevertheless, preventing footguns is important.

In Typescript, we can ensure the config file is immutable by using the 'as const' construct. Appending this to our config file definition marks all of the properties within the config as 'readonly', and prevents configuration values from changing during the lifetime of the application:

export const config: MyAppConfig = {
  'locale': 'en-US',
  'timezone': 'America/New_York',
  'logLevel': 'info',
  'environment': 'local',
} as const

// Syntax error: Cannot assign to environment because it is a read-only property.
config.environment = 'production'

Using this in practice

To use this approach today requires that your project also be written in Typescript. It also means you'll need to forgo a separate flat-file for your configuration. Given that Typescript configs can be made immutable to avoid footguns, and given the other benefits I outlined here, I think the trade-off vs standalone flat-files is worth it.

In the future, I think a successor to JSON which uses a subset of Typescript for schema definitions would be pretty amazing. This could potentially stand alone as its own format that's parsed directly, or the Typescript compiler could potentially be parse Typescript-based configs and emit valid JSON.

The latter approach would end up looking a lot like Douglas Crockford's proposal back in 2012, but instead of using JSMin we'd be using tsc. Maybe he was right all along.

Using BiDirectional Protocol support in Selenium 4 to stream console logs and network requests

Todd McNeal — Mon, 01 Nov 2021 19:29:36 +0000

BiDi Protocol support in Selenium 4

One of the new features in the recently released Selenium 4 is support for new event-driven listeners which will be powered by the currently-in-draft BiDirectional (or BiDi) protocol (though the current Selenium implementation has some limitations, which we'll discuss later). In this article we'll discuss some of these new capabilities and demonstrate how to use them in Scala to inspect console logs and network requests made from the browser.

Limitations of Console/Network Log Support in Selenium 3

In previous versions of Selenium, console and network log information was accessible by pulling via methods such as WebDriver.manage.logs.get(...). While that model does provide log access, it has a few shortcomings:

Logs need to be actively requested. No built-in interface is available for having logs pushed to your code as they occur.
No control is available for the volume of logs returned - presenting memory concerns when dealing with a long-lived session unless the logs are periodically pulled.
While network requests and responses can be recorded after they have been made, no built-in mechanism is available to modify or block the requests themselves.

Selenium 4 BiDi support

While the existing pull-based log methods remain available in Selenium 4, a new set of APIs has been added in Selenium 4 to allow users to subscribe to console logs and intercept network requests. We'll now demonstrate these APIs using Scala and Selenium's Java library.

BiDi support in action

We'll start by instantiating our WebDriver and creating buffers to hold the console log and network request information:

import org.openqa.selenium.chrome.ChromeDriver
import org.openqa.selenium.devtools.events.ConsoleEvent
import scala.collection.mutable.ListBuffer

// A case class to hold some useful data on a given network request 
// executed in the browser.
case class RequestData(method: String, url: String, responseCode: Int)

val consoleMessages = ListBuffer.empty[ConsoleEvent]

val networkRequests = ListBuffer.empty[RequestData]

val chromeDriver = new ChromeDriver()

val devTools = chromeDriver.getDevTools

devTools.createSessionIfThereIsNotOne()

To begin, we're launching Chrome and opening a connection to it using the CDP protocol (in a future release it will use the BiDi protocol). Tracking all console messages that are logged during the browser session is as simple as calling:

devTools.getDomains.events().addConsoleListener(consoleMessages.append(_))

addConsoleListener registers a function that is invoked whenever a console message is logged by the browser - in our case we simply throw it onto our
consoleMessages buffer.

Note: In order to avoid the same memory considerations as the non-streamed API you'll want your application to consume these messages rather than holding them in-memory forever - see the Potential Applications section for discussion on how that can be done.

To record network request and response information we can use the following code:

import java.util.concurrent.CountDownLatch

// A latch to track when a network request has been completed.
val networkRequestLatch = new CountDownLatch(1)

devTools.getDomains.network().interceptTrafficWith(next => {
   request => {
      val response = next.execute(request)

      networkRequests.append(RequestData(
         method = request.getMethod.toString,
         url = request.getUri,
         responseCode = response.getStatus
      ))

      networkRequestLatch.countDown()

      response
   }
})

Let's break this down: interceptTrafficWith allows us to register a Filter that is executed for every network request made by the browser. In our example, we execute each request and add a RequestData entry to our networkRequests buffer containing the HTTP method and URL of the request, as well as the status code of the response.

Note: This is only recording a subset of the data available from the request - a full list of available request information can be found in the Javadocs for HttpRequest.

networkRequestLatch is presumably not something you would use in your normal code, we add it here to provide a hook to ensure a request has been executed before closing the browser.

To demonstrate this functionality against a web page, we can use the following example.html file:

<!DOCTYPE html>
<html lang="en">
    <head><title>Selenium 4 Example</title></head>
    <body>
        <script>
            console.log('Hello, Selenium 4!')

            fetch('https://www.google.com')
        </script>
    </body>
</html>

Putting together all the earlier code snippets, you can test against the example.html file with the following code:

import java.util.concurrent.CountDownLatch
import org.openqa.selenium.chrome.ChromeDriver
import org.openqa.selenium.devtools.events.ConsoleEvent
import scala.collection.mutable.ListBuffer

// A case class to hold some useful data on a given network request 
// executed in the browser.
case class RequestData(method: String, url: String, responseCode: Int)

val consoleMessages = ListBuffer.empty[ConsoleEvent]

val networkRequests = ListBuffer.empty[RequestData]

val chromeDriver = new ChromeDriver()

val devTools = chromeDriver.getDevTools

devTools.createSessionIfThereIsNotOne()

devTools.getDomains.events().addConsoleListener(consoleMessages.append(_))

// A latch to track when a network request has been completed.
val networkRequestLatch = new CountDownLatch(1)

devTools.getDomains.network().interceptTrafficWith(next => {
   request => {
      val response = next.execute(request)

      networkRequests.append(RequestData(
         method = request.getMethod.toString,
         url = request.getUri,
         responseCode = response.getStatus
      ))

      networkRequestLatch.countDown()

      response
   }
})

// Replace this string with the location of the site or file you'd like to test
chromeDriver.get("example.html")

// Wait for the network request, as it might not complete until after 
// the page has loaded.
networkRequestLatch.await()

// Print out the collected console and network information
consoleMessages.foreach(println)
networkRequests.foreach(println)

// Shut down the browser
chromeDriver.quit()

If you run this code against the example.html file, you should see the following information printed to console:

2021-11-01T02:07:57.309Z [log] [["Hello, Selenium 4!"]]
(RequestData(GET,https://www.google.com/),ResponseData(200))

Potential Applications

While the example code simply recorded and printed the console and network request information it collected, there is lots of potential for using this new functionality for more practical applications.

Streaming into storage/ingestion

Rather than relying on pulling logs periodically, network and console logs can now be streamed directly into a file or remote storage system (such as Amazon S3). Alternatively the logs could be forwarded directly into a data ingestion pipeline such as Amazon Kinesis for additional processing/filtering before storage.

Network Request Modification

By having access to every outbound network request before it is actually executed, one could begin conditionally injecting new data - such as supplemental headers - into outgoing network requests based on the attributes (headers, path, etc.) of the request. You could even block requests if desired.

Limitations

While the new BiDi APIs offer new and interesting patterns for interacting with the browser, they suffer from the same limitation as the older pull-based APIs for log information in that they're dependent on each individual browser to implement the necessary protocols/APIs for use by the WebDriver. Because the BiDi Protocol is still in a draft state that means support across browsers is quite limited - in fact these new APIs are actually reliant on the
Chrome DevTools Protocol rather than BiDi.

As the BiDi Protocol is finalized browser support can be expected to improve, but until then you may not be able to leverage these new APIs for all the browsers you'd like to.

Conclusion

Selenium 4 provides a new mechanism for interacting with and recording log and network request information in the browser. While it has limited support today, the potential applications for using it make it at minimum a feature to monitor as the BiDi Protocol is finalized.

Reflection at Reflect: The Reflect and Proxy APIs

Todd McNeal — Tue, 26 Oct 2021 16:34:41 +0000

Reflect & Proxy

Reflect and Proxy are both standard built-in objects introduced as part of the ES6 spec and are supported in all modern browsers. Broadly speaking, they formalize the concept of metaprogramming in the context of Javascript by combining existing introspection and intercession APIs, and expanding upon them. In this article we'll explore how these objects work using examples that approximate real-world requirements.

Introduction

Javascript engines have object internal methods like [[GetOwnProperty]], [[HasProperty]], and [[Set]], some of which were already exposed for reflection in earlier versions of the spec. If you've worked with Javascript before, you're probably familiar with some of these developer-accessible equivalents. For example...

const foo = { firstName: 'SomeFirstName', age: 99 }
Object.defineProperty(foo, 'lastName', { value: 'SomeLastName', enumerable: true })
const bar = Object.keys(foo) // ['firstName', 'age', 'lastName']
const baz = Object.values(foo) // ['SomeFirstName', 99, 'SomeLastName']
Object.hasOwnProperty.call(foo, 'lastName') // true

The examples above demonstrate static introspection methods defined on the global Object. They only represent a subset of the useful engine-internal methods we'd like to access, and they're tacked on to a prototype. Together, the Reflect and Proxy APIs unify and simplify these existing methods, expand upon their introspection capabilities, and expose intercession APIs that were previously not possible.

Instead of covering every function defined on each of these objects in this article, we'll focus on the functions we use most often at Reflect. To learn more about each we recommend reading through the MDN guides.

Simple Reflect Example

Let's imagine a scenario in which you'd like to log some information every time a field on some global object was accessed. You could find every instance of a get() call
throughout your app and send the information manually...

// app.ts
// On pageload, we fetch the global session
window.globalSession = fetchSession()

// file1.ts
// We've accessed a field on globalSession, and the developer has logged that
const firstName = globalSession.firstName
console.log('GOT FIELD firstName')

// file2.ts
// Same applies here
const lastName = globalSession.lastName
const age = globalSession.age
const firstRelative = globalSession.relatives[0]
console.log('GOT FIELD lastName')
console.log('GOT FIELD age')
console.log('GOT FIELD relatives[0]')

This pattern is flawed for a number of reasons

It requires proprietary knowledge: Developers are responsible for remembering that every time they access some field on globalSession, they must also include a call to console.log(). This is difficult to enforce and easy to forget.
It does not scale: If the name of a field on globalSession changes, refactoring would be a nightmare. If you'd like to implement the same policy for some object other than globalSession, you'd need to repeat the entire original process and further expand upon the proprietary knowledge needed to develop in the codebase.
It doesn't account for more complex scenarios: The example above demonstates simple access patterns, but what happens when you have something like the following?

// file3.ts
// Point another global to the global session
window.activeSession = globalSession

// file4.ts
// Don't forget that activeSession points to the same object as globalSession, you
// still need to call console.log()!
const middleName = activeSession.middleName

The flaws in the approach above illustrate a disconnect between what we're trying to express and how we've implemented our solution. We want to log some information to the console every time a field on some object is accessed. We've solved this by enforcing a rule which requires manually calling a function.

The Proxy object allows us to solve the problem by expressing the desired behavior rather than trying to enforce a flimsy policy. Here's how that would work.

// makeStoreAccessProxy.ts
const makeStoreAccessProxy = (obj: Object) => {
  return new Proxy(obj, {
    get(target, key, receiver) {
      console.log(`GOT FIELD ${key}`)
      return Reflect.get(target, key)
    },
  })
}

// app.ts
window.globalSession = makeStoreAccessProxy(fetchSession())

Every time anyone accesses any field on globalSession (directly or indirectly), that access will automatically be logged to the console.

This solves the flaws in the pattern above

There is no proprietary knowledge needed: Developers can access fields on globalSession without remembering to store information about said access.
It scales: Refactoring globalSession is as easy as refactoring any other object, and the same makeStoreAccessProxy function can be used on any object in the entire codebase at any time.
It accounts for more complex scenarios: If you get() some field on globalSession by way of some other object that points to it, the access will still be logged to the console.

Note that we've leveraged both the Proxy and Reflect APIs in order to achieve the desired result. We'll review this piece by piece:

const makeStoreAccessProxy = (obj: Object) => {
  // This function returns a proxy of the provided 'obj'. Without defining the second
  // 'handler' argument, this is a transparent passthrough to 'obj' and would behave as
  // though it _were_ the original 'obj'.
  return new Proxy(obj, {
    // We then define a 'get' function in the handler. This means that we're redefining
    // the fundamental get operation on 'obj'
    get(target, key, receiver) {
      // We've redefined 'get' to log information in the console
      console.log(`GOT FIELD ${key}`)
      // And finally, we're calling 'get' on the original unwrapped 'obj'. We could
      // instead return 'target[key]', but this demonstrates the consistency between
      // the Proxy and Reflect APIs
      return Reflect.get(target, key)
    }
  })
}

The consistency between the Proxy's get() method in its handler and the Reflect.get function holds for all functions on both objects. Every method you can define on a Proxy handler has an equivalent function on the Reflect object. You could create a completely pointless proxy which just acted as a passthrough by overriding every supported method and simply calling the Reflect equivalent...

const p = new Proxy({}, {
  defineProperty() { return Reflect.defineProperty(...arguments) },
  getPrototypeOf() { return Reflect.getPrototypeOf(...arguments) },
  get() { return Reflect.get(...arguments) },
  set() { return Reflect.set(...arguments) },
  ... // etc
})

Advanced Reflect Example

In this case, the code we're writing needs to keep track of all images on the page that are loaded dynamically by some web application we do not control. Since we cannot manipulate the underlying application's code directly, we need some mechanism by which we'll trap access to the src attribute transparently...

// First we'll store a reference to the original property descriptor for the
// HTMLImageElement's src field
const originalImgSrc = Reflect.getOwnPropertyDescriptor(HTMLImageElement.prototype, 'src')

// Then we'll overwrite the HTMLImageElement prototype's "src" property and trap
// calls to that field's get() and set() methods
Reflect.defineProperty(HTMLImageElement.prototype, 'src', {
  get() {
    // When <someImg>.src is called anywhere, we'll log some information, then call the
    // target's get() method also using the Reflect API
    console.log('getting the src')
    return Reflect.apply(originalImgSrc.get, this, [])
  },
  set(value) {
    // When <someImg>.src = 'something' is called anywhere, we'll log some information, then call the
    // target's set() method also using the Reflect API
    console.log(`setting src to ${value}`)
    return Reflect.apply(originalImgSrc.set, this, [value])
  },
})

From an application's perspective, this change is transparent. The src attribute of any <img> node can be manipulated as though this override didn't exist. We're only intercepting access to these fields, taking some action, then carrying on as though nothing happened. The underlying app would require not knowledge of such a change and would remain functionally unchanged.

Proxy Example

How could we leverage the Proxy object? We may need to trap behaviors captured deep in the internals of some library or framework in order to redefine them entirely. Let's imagine a scenario in which a framework has two internal methods that manipulate the DOM. Both methods achieve the same end result, but one is asynchronous while the other is not. The asynchronous version may be the better choice for most apps for performance reasons, but in order to accurately track every action a user is taking we'd prefer it if developers only used the synchronous version.

With Proxy, this isn't a problem, and it's something we can control entirely ourselves without the need for applications to change their own source.

const someFramework = document.querySelector('#framework-root').framework

someFramework.blockingUpdate = new Proxy(someFramework.blockingUpdate, {
  apply(target, thisArg, argArray) {
    // Here we'll take some action whenever a call to blockingUpdate() is made
    console.log('Intercepted a call to blockingUpdate()')
    Reflect.apply(target, thisArg, argArray)
  },
})

someFramework.asyncUpdate = new Proxy(someFramework.asyncUpdate, {
  apply(target, thisArg, argArray) {
    // Here we'll redefine calls to asyncUpdate() to instead invoke blockingUpdate()
    Reflect.apply(someFramework.blockingUpdate, thisArg, argArray)
  },
})

Conclusion

It's important to be thoughtful when using the APIs described in this article. In general, web applications should not be redefining core web APIs (we think Reflect's use-case is an exception), but when Proxy and Reflect are the right tools for the job, it's also important to understand how they work. For instance, in the past we've used the Reflect.defineProperty function to redefine a global 3rd party property that exists on many sites on the web, but when we did so we forgot to include the enumerable: true field. One site in particular was relying on that property being enumerable, and so when we redefined it some functionality on their site stopped working in the context of using the Reflect app.

Reflect (the application) can be thought of as a top-to bottom reflective web application container that ideally is transparent to the web application it is observing and manipulating. If you'd like to learn more about how Reflect works, we'd love to hear from you! You can reach us at info@reflect.run. Happy testing!

Implementing a Task Queue in SQL

Todd McNeal — Wed, 20 Oct 2021 20:10:07 +0000

Introduction

Software systems often periodically execute collections of similar or identical tasks. Whether it's computing daily account analytics, or running background computations asynchronously like GitHub Actions, it's common to structure and complete this computation using a queue of tasks and the concept of a generic "worker" which executes the task. In the naive approach, a single worker sequentially executes each task, avoiding locks or other coordination.

Two downsides to the single worker approach are:

Tasks that are ready to execute will be delayed from executing until prior tasks complete (head-of-line blocking).
Independent tasks are not run in parallel (as they can be logically), so the overall wall-clock execution time is longer than necessary.

The alternative to the single worker is to use a pool of workers, each pulling a task from the queue when they are ready to execute one. In exchange for the reduced queueing delay and the reduced overall wall-clock execution time,
the programmer must manage the complexity of assigning and executing tasks. Some cloud services, such as Amazon Simple Queue Service (SQS), offer a managed abstraction for queueing tasks and assigning them to workers. However, they can be difficult to debug or impose undesirable properties, such as SQS's default policy of at least once delivery (rather than exactly once). Lastly, it might be the case that you just don't want the third-party dependency!

This article describes how to implement a task queue in SQL with the following properties:

A task is assigned to exactly 1 worker (or 0 workers) at a time.
Once completed, a task is never assigned again.
When a task reaches a configured maximum execution time without completing, it will be assigned again to a worker.

Let's jump in!

Design

A queue provides some elasticity in your system when your message (or, in our case, task) volume varies or is unpredictable. It also allows you to impose smoothing to a computational workload, which would otherwise be bursty,
by using a fixed set of resources to consume the queue and process the messages.

Let's consider some concrete examples to inform the design of our SQL task queue:

Daily Analytics - your analytics application displays usage metrics broken down by day.
Since you want these analytics available to your users by 8AM local time, you queue up one analytics job for each account every night starting at 12:01AM.
Scheduled Reminders - your online game relies on in-app ads and upgrade notifications to drive revenue, but the logic for deciding which content to trigger for the user is dynamic. So, you queue up all the notification content for each account and in real-time consume and filter the desired content.
Real-Time Events - your financial application receives millions of hits to its API throughout the day. In order to keep your endpoints fast, you queue events immediately after receiving them and process them later.

Exactly once delivery

The distinguishing property in any queueing use case is whether or not the task is idempotent. That is, can the same task be executed multiple times without adverse effects? If so, then the queue can deliver the same message to the worker pool more than once and the internal queueing coordination and locking complexity is reduced. Of course, if the tasks are not idempotent, or you simply don't want to waste the duplicative compute capacity, the worker itself can use a lock to ensure a message is only processed once in an at least once queueing system.

For our use case, we're interested in exactly once delivery of each message (i.e., exactly once execution of a task), and given that the at least once policy still requires a lock anyway to achieve exactly once behavior, we're going to use the atomicity of SQL writes as the lock in our queue.

Data model

To this point, we've been alluding to a task as a single data object, but in practice, we only really need the task's identifier in order to coordinate its lock and owner. Any worker receiving a task identifier for execution could read the task's information from another table. As such, we could use two tables: one for the task assignment and one for the task information, potentially saving bandwidth on database operations.

For simplicity, we'll consider a single table with all of the relevant information stored in a details field.
Consider the following data model definition for a Task:

table tasks:
    id serial
    details text
    status text -- One of: queued, executing, completed
    operator_id text
    expiry timestamp
    created timestamp
    last_updated timestamp

The lifecycle of a task in our model is the following:

a task begins in the queued state when it is written to the table,
a task enters the executing state when a worker is assigned the task and it begins execution, and
a worker updates a task to the completed state when it finishes execution.

Implementation

We use a Manager process to assign tasks to workers, and we run two instances of the Manager to improve fault tolerance and maintain availability during deployments. Managers repeatedly query the database for queued tasks and assign them to workers. Additionally, Managers look for tasks that have reached their maximum execution time (i.e., "timed out"), and reassign those tasks to new workers.

A worker can be a local thread on the same machine, or a remote machine that can accept work. The Manager doesn't really care as long as it can assume a reliable communication channel to the worker. (Obviously, the Manager might perform additional jobs such as worker liveness or reporting,
but we omit those details here.)

Database queries

The database queries form the backbone of our SQL queue and
focus on the areas of identifying unfinished tasks and completing tasks. The Manager (or another process) is responsible for queueing new tasks by writing them to the table. Then, the Manager queries the table periodically to identify tasks that are ready to be executed.

Find executable tasks:

    select
        *
    from tasks t
    where t.status = 'queued' or (t.status = 'executing' and t.expiry <= NOW())

Our Manager randomizes the returned tasks to reduce contention across Manager instances when selecting a task. Then, the Manager attempts to lock a task so that it can assign the task to a worker.

Attempt to lock a task

    update tasks t
        set t.status = 'executing',
            t.operator_id = $operator_id,
            t.expiry = $expiry,
            t.last_updated = NOW()
    where t.id = $id and t.last_updated = $last_updated

The Manager calculates the expiry value as the current time plus the maximum amount of time it expects a worker to take to execute the task, plus some buffer. If the query returns with 0 rows updated, then it means the Manager did not obtain the lock. Otherwise, if the query returns with a value of 1, it means the Manager successfully locked the task. The Manager can now assign the task to a worker for execution.
If the worker fails to execute the task or takes too long to execute the task, then the task will be selected in the first query to find executable tasks.

This code uses the last_updated column as an indicator for whether a row has been written since it was last read. Thus, this optimistic concurrency control mechanism will fail if a row could be written or updated without updating the last_updated column. In general, the resolution of the last_updated timestamp must be greater than the system's shortest read+write update.

The worker will then read the task from the table by its id and use the details to execute the task. You may wish to have your worker update the task row to specify its own operator_id to aid in debugging once it begins executing the task. Regardless, when the worker completes execution of the task, it updates the row to indicate that it's complete.

Update a completed task

    update tasks t
        set t.status = 'complete',
            t.operator_id = NULL,
            t.expiry = NULL,
            t.last_updated = NOW()
    where t.id = $id and t.last_updated = $last_updated

This system has workers marking tasks as complete, but if you wanted to consolidate writes to this table within the Manager exclusively, you could have the Manager look for tasks that satisfy some external property, such as the presence of a log file or a row in another table, before marking tasks as complete.

Discussion

A major benefit to this approach is that it has no external or third-party dependencies. For any system that already relies on a relational database, this approach incurs no additional infrastructure.

A second major advantage of this approach is that it automatically produces a historical record or log of all tasks that were executed. You may wish to trim the tasks table depending how quickly it grows in size, but broadly speaking, the audit log it provides is very useful for debugging problems.

Lastly, this approach is both scalable as you add more workers, and fault tolerant to worker and Manager failures. With only three database queries, the Manager or worker can fail at any point, and the task will eventually be retried. To provide further protection against a duplicate execution, you can introduce an external lock or state management to track a worker's progress in executing a task. In a similar vein, you may wish to add a column for retries of a task.

The major downside or risk to this approach of queueing is that this setup couples the throughput/volume of your system processing to the read/write capacity of your database. If your database is "small" and has an otherwise low volume of writes, but your work queue has high volume/throughput requirements, you may end up in a situation where you have to scale your database solely because of the requirements of the work queue. And, of course, if you don't proactively monitor the growth of your volume, the contention caused by workers reading and writing on this table could negatively impact the rest of your database operations and your system as a whole.

A second drawback to this approach is that you are managing the complexity yourself. With Amazon SQS you are outsourcing all implementation details to a third-party. But with this approach, you need to make sure that the queries and table indices are correct. Similarly, and related to the first downside, there isn't the warm-and-fuzzy feeling with a self-built approach like this that you might get from Amazon's eleven 9's of service reliability or throughput guarantees.
Still, over time, the operational maturity will increase your confidence.

Conclusion

The simplicity of a SQL queue makes it attractive as an alternative to a managed, third-party queue abstraction. In this article, we presented a minimal implementation of one such SQL-based task queue. If you like this article and would like to discuss more, you can reach us at info@reflect.run. We'd love to hear from you!