A journey into realtime computing with Postgres, Bun, and the reactive gang.
Let's start with docker; we'll create a postgres database initialized with an init_db folder — so that every time the database spins up, it auto-loads our schema and functions — and keep it a simple as possible:
services:
postgres:
image: postgres:latest
environment:
POSTGRES_USER: postgres
POSTGRES_PASSWORD: secret
POSTGRES_DB: simple
volumes:
- ./init_db:/docker-entrypoint-initdb.d
ports:
- "5432:5432"
Now we can connect to it with a DATABASE_URL environment variable:
DATABASE_URL=postgres://postgres:secret@localhost:5432/simple
To run the database we can run docker compose down -v && docker compose up -d. The command will first try to pull down the database and remove all volumes, while the second command will bring up the database in the background. This can be a script in your package.json - then everytime you change the contents of the init_db you can reset the database…
If we install bun.sh, we can put that variable in a .env file and then access postgres with bun's built in package:
import {sql} from bun;
async function main() {
const rows = await sql`SELECT now()`;
console.log(rows);
}
main();
If we run bun index.js we'll see the current time. Let's now create a public folder and drop an html5 hello world into it - let it link to a style.css file and a client.jsx file.
public/
├── index.html
├── style.css
└── client.jsx
Now if we create a bun server in index.js:
import {serve} from "bun";
import home from "./public/index.html";
serve({
port: 3000,
routes: {
"/": home,
},
});
console.log("Server started on http://localhost:3000");
You'll notice that home is a bundle — and supplies all the routes for the assets referred to in public/index.html. Now let's step back to client.jsx — why jsx? Well, it's a react file and bun's bundler will convert it to javascript!
import { createRoot } from "react-dom/client";
function App() {
return (
<div className="prose m-8">
<h1>Hello, World!</h1>
</div>
);
}
const root = createRoot(document.getElementById("root"));
root.render(<App />);
We have tailwindcss installed and configured with a bunfig.toml. This enables the bun bundler to compile your css. Now if we add a route to our server:
"/api": async (req) => {
const rows = await sql`SELECT now()`;
return new Response(JSON.stringify(rows), {
headers: { "Content-Type": "application/json" },
});
},
We can make our App reactive:
import { useState, useEffect } from "react";
function App() {
const [time, setTime] = useState("");
useEffect(() => {
fetch("/api")
.then(async (response) => {
const now = await response.text();
setTime(`it's ${now}`);
})
.catch((error) => {
console.error("Failed to fetch time:", error);
});
}, []);
return (
<div className="prose m-8">
<h1>Hello, World!</h1>
<p>{time}</p>
</div>
);
}
Our response is a select result, a table, an array - and the column selected: 'now'. But I have not told you the whole story as I've also loaded some other packages with my package.json:
{
"scripts": {
"dev": "bun --watch index.js",
"db": "docker compose down -v && docker compose up -d"
},
"dependencies": {
"@headlessui/react": "^2.2.7",
"@heroicons/react": "^2.2.0",
"@tailwindcss/typography": "^0.5.16",
"@types/react": "^19.1.10",
"bun-plugin-tailwind": "^0.0.15",
"react": "^19.1.1",
"react-dom": "^19.1.1",
"tailwindcss": "^4.1.12"
}
}
So now we can use headlessui and heroicons!
import { createRoot } from "react-dom/client";
import { useState, useEffect } from "react";
import { Button } from "@headlessui/react";
import { HomeIcon } from "@heroicons/react/24/solid";
function App() {
const [time, setTime] = useState("");
async function fetchTime() {
try {
const response = await fetch("/api");
const now = await response.text();
setTime(`it's ${now}`);
} catch (error) {
console.error("Failed to fetch time:", error);
}
}
useEffect(() => {
fetchTime();
}, []);
return (
<div className="prose m-8">
<h1 className="flex items-center gap-2">
<HomeIcon className="h-8 w-8" />
Hello, World!
</h1>
<p>{time}</p>
<Button
className="rounded bg-sky-600 px-4 py-2 text-sm text-white data-hover:bg-sky-500 data-hover:data-active:bg-sky-700"
onClick={fetchTime}
>
Now
</Button>
</div>
);
}
const root = createRoot(document.getElementById("root"));
root.render(<App />);
Why am I walking you down this path? Because I want to show you what an AI said it found easy to code. But there is more, the Bun server transforms into a lightweight RPC layer for database functions — essentially a real-time server. With just a small tweak to index.js, we can add authenticated JSON-RPC 2.0 WebSocket support. And real-time isn't just for stock tickers or traders: WebSockets deliver low latency, minimal handshaking, and true bi-directional communication. REST, eat your heart out. GraphQL? Go cache yourself.
We'll use JWT for authentication and add some special functions to the database to support it. What special functions? Why, login_user, _register_user and _profile — the first and second will provide an _profile the second will return an _profile for an id. We just have to add a file to our init_db directory:
-- Enable pgcrypto extension for password hashing functions
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE TABLE IF NOT EXISTS users (
id SERIAL PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
password_hash VARCHAR(255) NOT NULL,
name VARCHAR(255) NOT NULL
);
Our table and the crypto extension. Then we have the _profile function.
-- Create a function to get a user's profile by id
CREATE OR REPLACE FUNCTION _profile(user_id INTEGER)
RETURNS JSON AS $$
BEGIN
RETURN (
SELECT json_build_object(
'user_id', id,
'email', email,
'name', name
)
FROM users
WHERE id = user_id
);
END;
$$ LANGUAGE plpgsql;
Then login_user and _register_user:
-- Login function that returns user profile on success
CREATE OR REPLACE FUNCTION login_user(user_email VARCHAR(255), user_password VARCHAR(255))
RETURNS JSON AS $$
DECLARE
user_id INTEGER;
stored_hash VARCHAR(255);
BEGIN
-- Get user id and password hash
SELECT id, password_hash INTO user_id, stored_hash
FROM users
WHERE email = user_email;
-- Check if user exists and password matches using crypt
IF user_id IS NOT NULL AND stored_hash = crypt(user_password, stored_hash) THEN
RETURN _profile(user_id);
ELSE
RETURN NULL;
END IF;
END;
$$ LANGUAGE plpgsql;
-- Register user function that creates a new user and returns their profile
CREATE OR REPLACE FUNCTION _register_user(user_email VARCHAR(255), user_password VARCHAR(255), user_name VARCHAR(255))
RETURNS JSON AS $$
DECLARE
new_user_id INTEGER;
hashed_password VARCHAR(255);
BEGIN
-- Hash the password using bcrypt
hashed_password := crypt(user_password, gen_salt('bf', 12));
-- Insert new user and get the id
INSERT INTO users (email, password_hash, name)
VALUES (user_email, hashed_password, user_name)
RETURNING id INTO new_user_id;
-- Return the new user's profile
RETURN _profile(new_user_id);
EXCEPTION
WHEN unique_violation THEN
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
Why _register_user? I have a coding convention that functions with _ cannot be called from the client. All other functions will have the websocket user_id as the first parameter. So both register_user and profile are internal/private methods.
So now we can use JWT for authentication. I've used Jose — but there are others. Bun.server asks for a fetch function where it upgrades requests to websockets. In doing so you can provide a data object and in our case we supply a user_id.
if (token) {
const payload = await verify_jwt_token(token);
if (payload) {
user_data = {
user_id: payload.user_id,
email: payload.email,
};
}
}
// Upgrade to WebSocket (allow anonymous for login/register)
const success = server.upgrade(req, {
data: user_data || {},
});
That verify_jwt_token is an effective wrapper of the _profile stored function. So now when we call methods, we can pass the user_id as the first parameter.
So now our public directory looks like this:
public/
├── client.jsx
├── index.html
├── login.jsx
├── styles.css
└── websocket.js
We have a websocket that connects and recieves a welcome package that will contain a profile or null — if null display a login form. We have a login form that allows users to log in. Then when we have connected, and have a profile we display the content.
Now our content is minimal. We add a simple now function to the database:
CREATE OR REPLACE FUNCTION now_function(user_id INTEGER)
RETURNS TIMESTAMP AS $$
BEGIN
RETURN NOW();
END;
$$ LANGUAGE plpgsql;
And then we can call it from our client:
const now = await api.now_function();
console.log(now);
Api is a proxy object to the websocket call method:
return new Proxy(
{},
{
get(target, method) {
return (params) => wsManager.call(method, params);
},
},
);
And the call method is a simple wrapper around the websocket send method:
call(method, params) {
return new Promise((resolve, reject) => {
const id = ++this.messageId;
const message = {
jsonrpc: "2.0",
method,
params,
id,
};
this.pendingRequests.set(id, { resolve, reject });
this.ws.send(JSON.stringify(message));
});
}
So now we have a json-rpc websocket and the ability to call postgres stored functions from our client. We can login and logout and this stay on the same page. We can also call the now function and display the result.
What makes this even more powerful is that we can display realtime data and let triggers update our client state. This is the Event Sourcing with CQRS (Command Query Responsibility Segregation) pattern we'll use: the client calls a stored function to retrieve a view from the database. The client then calls a stored function to update the view. But the client is only interested in errors from the response, because a trigger will fire and send the payload of the update via NOTIFY. The server uses LISTEN to broadcast the payload to all the clients. So then our client will update the view with the new data as if anyone updated it. This pattern allows for complex views and atom sized updates.
For example we could get the seating plan from a cinema booking system. We're looking at one screening and we won't get the entire screening update just the changes to the seat status as each booking is transacted. You could say we get the table result as a view and the row updates as events. This is just the tip of the iceberg. There is the scaling and filtering of broadcasts, the designing of views and updates apis. But this is how it all begins.
So I wrote the above paragraph and Claude went and wrote the example code for us. We now have a cinema booking system that demonstrates the power of Event Sourcing with CQRS. The client can retrieve the seating plan and update it in real-time as bookings are made or cancelled. This allows for complex views and atom-sized updates. The api is: get_cinema_seats() and toggle_seat() — and the trigger effectively calls:
PERFORM pg_notify('table_changed', json_build_object(
'table', TG_TABLE_NAME,
'action', TG_OP,
'row', row_to_json(NEW)
)::text);
Only the row that changed to update the view is broadcast. It would be nice to use that channel name as a method name, but you cannot subscribe to '*' channels — just by name — so we'll just have to use one channel and make the message richer. But you could always do more…
I think this now demonstrates the simplicity of this architecture and the promise it holds for building simple applications. The ai can write the stored functions and test them through an MCP and then see the results in the browser with another MCP. I get it to write bun test to make sure the database layer works, but it does not like setting up complex test cases. It just a matter of time and cost.
I had Claude write three clients — react, vue and juris. They were all pretty much identical. There were more libraries in react. I explicitly said not to use vite — but vite is brilliant and you can run it concurrently with bun. So we could have had a more complex Vue3 implementation, but Claude preferred the all in one file Vue3 version to the other two. It said juris was too young — but I think it's exciting and will keep my eye on it — and react too complicated. It looks like Gemini also agrees with Claude:
Vue (alt2) is the clear winner here in terms of ease of development for this specific project. The code is the most concise and arguably the easiest to read. It achieves the same level of reactivity as the React version but with less code and fewer new concepts for a developer coming from a traditional web background. The "all in one file" browser-compatible version is a perfect fit for this kind of simple, single-page application.
What starts as a simple Postgres container and a Bun server quickly evolves into something much more powerful: a real-time application stack that stays simple by design. By pushing logic into stored functions, exposing them over JSON-RPC WebSockets, and letting Postgres triggers drive event sourcing, we end up with a system that is reactive, testable, and remarkably concise.
The most exciting part, though, is what this means for the future. I still don't know all the ins and outs of PL/pgSQL — yet AI has been writing tons of it for me over the past few months. It doesn't just generate snippets, it scaffolds functions, tests them, and plugs them into the working client. The heavy lifting of boilerplate and syntax is shifting to machines, while we focus on design, architecture, and experience.
This is the promise of modern app development: small, understandable building blocks that scale into something greater, accelerated by AI that can already bridge the gap between idea and implementation. The tools are taking over, and they're only getting better.
PS. If you'd like to see the source of the project let me know in the comments and I'll make the git repo public.