The "Postgres First" Trend
Tags: database, postgres
Published: 2023-10-17 17:23:08

> **TLDR**: Because of serverless, the need for database wrapper api's like [PostgREST](https://postgrest.org/en/stable/) have become mainstay. [Supabase](https://supabase.com/) allows RPC postgres function calls which can house more complex "database logic" eliminating the need for an in-app model. [Omnigres](https://github.com/omnigres/omnigres) is a project aiming to take this concept one step further with including an HTTP server within postgres itself. I also was writing a bunch of [pg functions](https://github.com/reggi/pg_json_functions) this week which is why I noticed this trend.

There's a new way of thinking about databases / apps that's slowly morphing the way people think and opening developers up to new possibilities and ways of building apps. There's something brewing because i've been having this idea and I'm also seeing others have it as well.

The idea overall is to set postgres as the main "backend api". That we shouldn't really ever need to bundle multiple queries into a transaction in a different programming language or server at all.

To be honest i've fallen in love with [Supabase](https://supabase.com/). This post is not sponsored by them in any way. I just really love the product, and for me, as a champion of self-hosting and open source that's saying a lot. I believe that everyone should have their own personal database / api and "own their own data" and Supabase aids in that mission. I really love that they also have a storage solution so all your data (like a blog post and images) can live truly live one place.

There's two ways to use Supabase through the API they provide, or directly with any postgres client. If you wanted to run postgres queries directly you can, but if you wanted some of their out-of-the-box features like auth and signup flow, you can use that too. It's a real swiss-army knife. What I find really interesting is something they provide called [Supabase RPC](https://supabase.com/docs/reference/javascript/rpc) which allow you to call a custom postgres function from the supabase client. Calling functions directly can minimise the mount of vendor-lockin you have with supabase because if you use their api client, you can't make queries directly and so you'd have to make a model system using their library which is proprietary. This whole mess is made complicated and necessary when it comes to serverless hosting, which requires the need for an API rather then a direct postgres connection. 

So while I was brewing about all these ideas I've been thinking of designing a slew of postgres functions that would act somewhat as "the api" i'd write the to my postgres database and call those functions rather than a traditional model, things like pagination, joins, merging, user data etc, any data massaging you'd normally do within your app. 

Then the other day while scrolling hacker news this came up:

{% embed https://github.com/omnigres/omnigres %}

[omnigres](https://github.com/omnigres/omnigres) is "Postgres as a platform" that takes all of this one step further.

> Omnigres makes Postgres a developer-first application platform. You can deploy a single database instance and it can host your entire application, scaling as needed.

What's interesting is I had started working on a whole bunch of json function for postgres the same week I found omnigres on HN. Take a look at my progress here:

{% embed https://github.com/reggi/pg_json_functions %}

It's a bunch of functions that make it slightly easier to interact with jsonb columns, and it's mostly me tinkering around with functions and trying to learn the syntax with ChatGPT.

Anyway, that's all I got for this post. I do think there's a shift in how people are thinking about what a database should be and should do. Mainly because at some point in the 'serverless' architecture there needs to be a single server (or cluster of servers) to manage database connections, and what we ask of that server might change over time.