Full stack Postgres

I tried an experimental project with this stack:

Database – PostgreSQL
API – PostgREST
Templating language – pg_render
Front-end – of course SQL can't render buttons, so I used HTMX to avoid writing any JS and keep the stack constrained.

The Project:

A simple inventory app that lets you add items, increment or decrement their counters, and delete them when needed — basically a good old to-do-style app, but each item has a counter

Breaking It Down

Docker

I used Docker to keep things clean. Especially for pg_render, which isn’t available as a standard Postgres extension, Docker made setup much easier, so I leaned on this excellent example pg_render example let’s just say my Dockerfile is... heavily inspired.😉

Postgres

PostgREST is not a direct extension of PostgreSQL — it’s a separate application that connects to a Postgres database and exposes a RESTful API for all the available resources.

It automatically turns your tables, views into API endpoints that support standard operations like GET, POST, PATCH, and DELETE.

For custom logic, you can write RPC functions using PostgreSQL’s procedural languages. These functions can then be called through the API using the following pattern:

POST /rpc/function_name

PostgREST will handle the request and execute the corresponding function, returning the result as JSON.

Here’s the table that stores items of the app:

create table items (
    id serial primary key,
    name text not null,
    stock_count integer not null default 0,
    created_at timestamp default now()
);

PostgREST automatically exposes this as a REST resource at /items.

    GET /items — list all items
    POST /items — create a new item (with JSON body like { "name": "pen" })
    PATCH /items?id=eq.3 — update item with id = 3
    DELETE /items?id=eq.3 — delete item with id = 3
    POST /rpc/increment_stock — custom RPC function to increment stock_count for an item
    POST /rpc/decrement_stock — same, but decrements instead

pg_render

pg_render is a templating extension for PostgreSQL. It lets you render HTML templates with placeholders (using {{ }}) by filling them with values from a JSON object or a database row.

    render(template, row/json) – renders a single template

    render_agg(template, setof rows) – renders a list of rows into one big HTML string

Template used for item

insert into templates (id, template) values (
  'item',
  $$
  <div class="item-card" id="item-{{ id }}">
    <div class="item-info">
        <h4>{{ name }}</h4>
        <p>Stock: <span class="stock-count" id="item-{{ id }}-count">{{ stock_count }}</span></p>
    </div>
    <div class="item-actions">
        <button 
          hx-post="/rpc/increment_stock" 
          hx-vals='{"item_id": {{ id }} }' 
          hx-target="#item-{{ id }}-count" 
          hx-swap="innerHTML">+</button>

        <button 
          hx-post="/rpc/decrement_stock" 
          hx-vals='{"item_id": {{ id }} }' 
          hx-target="#item-{{ id }}-count" 
          hx-swap="innerHTML">-</button>

        <button 
          hx-delete="/items?id=eq.{{ id }}" 
          hx-target="#item-{{ id }}" 
          hx-swap="outerHTML">🗑️</button>
    </div>
  </div>
  $$
);

This can be rendered with render function

select render(
  (select template from templates where id = 'item'),
  i
)
from items i
where id = 1;

htmx

HTMX is a JavaScript library used on the client side to build dynamic web apps without writing JavaScript.

It provides JavaScript-like functionality by extending HTML with attributes like hx-post, hx-get, and hx-target to make API calls and update parts of the page.

HTMX follows the principle of hypertext-driven applications, where the server returns rendered HTML fragments instead of JSON, and the browser swaps them into the page.

HTMX POST to /items -> This triggers a POST request with body of {"name": "input_value"} when the form is submitted.

<form 
    id="create-item"
    hx-post="/items" 
    hx-trigger="submit" 
>
    <h3>Create New Item</h3>
    <input type="text" name="name" placeholder="Enter Item Name" required />
    <button type="submit">➕ Add Item</button>
</form>

Increase the count of an item and swap the latest count

<div class="item-info">
    <h4> {{ name }} </h4>
    <p>Stock: <span class="stock-count" id="item-{{ id }}-count">{{ stock_count }}</span></p>
</div>
<button 
        hx-post="/rpc/increment_stock" 
        hx-vals='{"item_id": {{ id }} }' 
        hx-target="#item-{{ id }}-count"
        hx-swap="innerHTML"> +
</button>

hx-post specifies the RPC function to be called.
hx-vals is the payload sent with the request.
hx-target points to the element where the response will be inserted.

The RPC call returns the updated count after incrementing, and that value replaces the content inside the target element.

create or replace function increment_stock(item_id int)
returns INTEGER
language sql
as $$
  update items
  set stock_count = stock_count + 1
  where id = item_id
  returning stock_count;
$$;

Wrapping up

GitHub link – https://github.com/kamaaaal/fullstack_pg

Nginx can be used to hide the /rpc/index route and serve the initial page as just /.

This project has a few limitations (bugs) as of now — for example, adding a new product doesn’t immediately reflect on the page and requires a manual refresh. This could be fixed by using a custom RPC function that returns the newly added item as rendered HTML.

I feel this is a pretty cool idea — would love to hear your thoughts on what kind of applications could be built with this stack.