PostgreSQL, PostgREST & Svelte are a bliss to develop with!

The combination of svelte + postgrest enables rapid prototyping while reducing boilerplate, helps minimize the mental footprint, and is straightforward to grasp, all while being surprisingly flexible.

Preface

In this text I’d like to describe the reasoning and road taken to reach the tech stack that I grew to adopt, explain the choices made and suggest who in particular may benefit from using this combination of technologies.

The journey was almost two decades long. However, the presented solution began to emerge in the last two years or so.

But first, some history

I’ve been programming web sites & apps since 2002 or so, hitting the road with PHP4. What follows are my rather personal observations on how things went from that point to the present in web development.

Back then, things were converging around the MVC pattern. Since the client was (mostly) static content, changes to which involved page refreshes, it was sufficient to organize user actions, data flow, business logic and what the user ended up seeing in a way that made sense.

In those rather early days, no specializations existed in web development. Since the field was young and rather narrow, *everyone* was a full-stack engineer.

Needless to say, that sweet innocence didn’t last. As soon as the browser (JS) evolved towards dynamic pages (DHTML was a term back then), with JS becoming increasingly capable, the discovery of “AJAX”, permitting to query partial HTML snippets from the server, and eventually the advent of JSON, enabling “proper” SPAs, complexity sharply increased, taking its toll on the client side logic.

This entropy, in turn, was answered with new tooling to amend the chaos that client side programming was becoming.

Initially, the venerable jQuery took on as a way to streamline DOM manipulations & events, succeeded by more comprehensive attempts to marshall state, rendering and event / action flows in setups that grew increasingly complex as the front-end gained an ever more complex business logic.

To bring an order into this chaos, frameworks such as Backbone and eventually Angular and React were introduced.

As client-side development evolved, developer knowledge and focus shifted towards these new domains. Web development as a whole began to suffer from a domain split, where separate disciplines and sets of skills were required to create the backend (persistence & business logic), and a completely different skill set was emerging for the creation of an ever-evolving, more and more elaborate world of UI and UX, fueled by an ever growing matrix of Devices, increasingly capable Browsers, multiplying Operating Systems - each its own set of intricacies, features, limitations and usage patterns.

This resulted in the emergence of the front-end developer.

Their distinction from back-end programmers, and, well, of those who claim they’re “full stack”, though that latter group does not instill much trust from oldtimers as myself, given that no matter how large the volume of knowledge that a “full stack developer” with two years of experience would have, it would still be very thinly spread.

Throughout this evolution of the web stack described above, I kept bouncing between various corners of the field, never staying in one spot for too long.

Early days I wrote domain specific PHP frameworks & tooling, which were both back and front-end. Proceeded to hop on the Python bandwagon (Pylons circa 2007), then to write JS animation and graph scene frameworks in 2012-3, only to bounce back into solving scalability and performance problems on the backend (database sharding, green threads for concurrency with the advent of websockets). In the last decade I dabbled in browser extensions, and 2021 threw me into the world of Electron apps.

This experience led me to discover that what I really enjoyed was the research and architecture building activities that often characterize the early stages of a project.

This kind of activity requires rapid iterations, trial & error, and the ability to quickly throw different approaches at a problem, resulting in sweeping architecture modifications and refactoring.

This kind of activity is best done by as few participants as possible. The more people - the higher is the communication overhead, the slower the speed and efficiency.

And this, if you read this far, brought me to identify the problem:

Overspecialization in web development hampers rapid prototyping.

As the field evolved, it became increasingly difficult to do the one thing that I enjoyed doing: single handedly churn out dirty proof-of-concept solutions that could later be built upon and expanded into real products.

The mere act of wrapping one's head around the entire combination of technologies required just to bootstrap any unexceptional project became burdensome.

The mental context switches required every time one’s attention moves from infrastructure (whatever form it took), persistence (SQL or any other flavor), backend business logic (be it PHP, python, or even nodejs), and, mainly, the latest choice of another ever-evolving frontend framework, with its yet-again-novel way of doing things, introduced an overhead so high, while the necessity to hold the vast and heterogenous body of knowledge to support those activities did not leave much room in that head of mine for the actual task at hand.

Well, at least the problem’s identified. And we can move on to seek a solution!

So what do we need?

A concise, small collection of tools that allows us to deliver without getting in the way, doesn’t force us to keep too much on our mind, and at the same time be expressive and not get in the way of a product’s long term growth.

To keep the mental footprint small, we’d like
  • As few different languages as possible
  • As few different services as possible
  • As few new concepts outside of the “standard” web dev body of knowledge as possible
To become fluent in it as quickly as possible, and to get stuff done efficiently (with the least context switches) we’d like it to:
  • Be concise and expressive. The less LoC - the better.
  • Employ tooling as standard (common) as possible
  • Be easily refactorable, permit & encourage DRY and component/code reuse.

From the bottom up

Persistence, data modeling, message passing & authorization.

PostgreSQL is the most popular and feature-complete open source database that’s out there. In addition to providing the Relational solution (SQL), it also replaces a Document store (such as MongoDB) with its JSON(B) support and a Message broker (such as RabbitMQ).

In addition, it comes packing

  • SQL (relational)
  • JSON(B) - document/object storage
  • LISTEN/NOTIFY - event broadcasting & message passing
  • Expressive roles & permissions allowing to control access on a fine grained level (row level security).
  • JWT is supported as an extension

PostgreSQL is so widely adopted that it has a bunch of both opensource as well as commercial solutions for everything from columnar storage through sharding and down to replication, providing plenty of room to grow. SQL is a widely adopted standard.

Back-end. Do we need one?

All of the features listed above need a way to be unlocked and made available to the front-end. The way that’s traditionally done is via a server-side solution such as Python, PHP, NodeJS, Ruby (and plenty of others).

Such server-side solutions typically also hold the business logic, enforce constraints, validate input, perform auth, using the database for storage alone, complex queries aside.

PostgREST is a tiny and concise module that utilizes PostgreSQL’s power to radically deal away (for the most part) with the need for a server-side language.

This is possible because the underlying database allows to express (and enforce) business logic on various levels (declaratively, using data definition constraints, and procedurally with triggers, custom functions, views and other abstractions), protects this logic via an elaborate role & permission system, and allows to easily define custom authentication with its JWT support.

So the only thing that PostgREST does is expose the database through a REST API, and, for the most part, does not get in the way.

While one still needs server-side code for tasks such as worker queues and functionality that cannot be expressed in the DB, it is nevertheless surprising, for someone who’s written server-side code for almost two decades, to discover that server-side code, in its traditional sense, is simply not needed.

And there’s more! Getting used to expressing business logic and constraints declaratively, brings one to the discovery that data-based constraints, other than being concise, are a very solid safeguard against errors and lacking validation in code that’s higher up. In other words:

The database is where an application’s state lives. Business logic that’s expressed in the database applies to the entire app.

And when we express business logic declaratively, concisely, in the database, we relieve ourselves of the need to express it elsewhere. Hell, we relieve ourselves of the need to even have a server-side app. Well, we delay that need, until it surfaces, sooner or later. But let’s not be picky.

This leaves us with the front-end to decide on.

As I was trying to force React down my throat, getting used to the excess boilerplate of redux, and trying to wrap my head around functional components, convincing myself this is the one tool to use due to its wide adoption, a friend suggested that I try out Svelte. I did, and haven’t looked back. Here’s what so good about it:

  • The least amount of new concepts to learn on top of vanilla JS.
  • Things follow a logic and seem to just align with my way of thinking. Yes, that’s subjective, but if you’re a web dinosaur too, you may remember gentler times, when MVC made sense, was simple to work with, and had predictable results. Svelte, other than the fact that is browser based instead of being server-rendered, feels very much like that.
  • The produced bundle is truly tiny.
  • It’s very much alive and keeps getting better. Sapper, which is the framework around Svelte providing SSR, project structure and other amenities, is being replaced with SvelteKit, which solved my two grievances with sapper: long compile times for large codebases and the presence of server.js, which makes projects potentially undeployable as a static bundle.

And here are some other pleasant perks

  • Traditional self-hosted web app aside, I was able to use this stack, with little changes:
  • On AWS RDS + Lambda + S3/Cloudfront. So yes, if you’d like to host with minimal infrastructure costs and pay for usage, this is a solid way to go. Run PostgREST in Lambda, compile and deploy static front-end on S3, while the DB lives in RDS. it all works!
  • To build a Windows/macOS/Linux Electron app. Well, minus the sapper/sveltekit - you don’t need those. Yes, the installer weighs a ton (200MB), but so does Electron to begin with. but you can bundle PostgreSQL+ PostgREST and develop with Svelte. It works great!

On the subject of bloat, I’m inclined to try out Tauri as an Electron alternative, now that Edge versions have mostly stabilized on Win*.

  • For developing browser extensions. Svelte can be compiled to live within the extension, if you’d like to bundle UI with it. And the persistence is exposed the same way, via PostgREST from a remote resource.
  • Easy to write a websocket server using PostgreSQL’s LISTEN/NOTIFY. Any server process or stored procedure can fire an event (with optional payload), which will be immediately pushed to any connected client, based on a need to know basis. Since PostgREST does not support Websockets this is required if you want an app that quickly reacts to things happening in the background.
  • Worker processes that process a queue are easy to write utilizing the LISTEN/NOTIFY construct, which is used to quickly wake a sleeping worker up to any fresh items that require processing.
  • PostgreSQL’s support for JSONB allows to prototype without defining a strict data model by pushing everything to a unstructured fields, and as data structure emerges, to painlessly migrate it to separate fields, or maybe just add indexes to the data as its still inside of the JSON.
  • SQL aside, unless, for some strange reason, we have to write server code in a language that’s not JS, we are isomorphic and can share and reuse components between the client & server.
  • This stack enables an untypical, but highly efficient inside-out collaboration pattern. I can quickly create a fully functional, butt-ugly solution, and then pass it to a developer specializing on front-end for beautification. This exempts me from the need to design screens first. It’s possible to iterate over functionality without being concerned about the design & UX aspects. Once a functioning result is attained it can be passed as-is to the front-ender and forgotten about.
  • PostgREST exposes a Swagger/OpenAPI spec out of the box. This enables collaboration with front-end developers and 3rd parties by merely declaring the data structure in the DB and giving them access to it. no further action is required.
  • It would probably be straightforward to swap PostgREST for PostGraphile, if one wants to gain GraphQL which may have benefits over REST for dedicated front-end devs who won’t touch the schema itself. However, I haven’t tried that.

Oh, the shameless self-plug!

Here’s my little boilerplate template that can be used to jumpstart the above evangelized combination of tools. Caution: SvelteKit support is just fresh in, so, there be dragons.

But hey, if you read this far, you must be one brave adventurer!

On the business side.

I run a small consulting shop that specializes in rapidly churning out custom-built web solutions. And I just gave you the recipe for our secret sauce. If you like the idea, we can cook something together. Welcome to drop me a line.


© 2021 Web GMA R&D Ltd.