Static HTML comments
2022-10-08If you have a static HTML website, but you want to include comments, here’s an interesting way to do it using PostgreSQL’s NOTIFY and LISTEN.
The big idea is to write the comments as static HTML, only when comments change, instead of doing a database query to display them every time. This prevents the “hug of death” if you get a burst of traffic.
I’ve been doing it this way for over six years, and it works great. Here’s the recipe, using Ruby as the glue, though you could use any scripting language.
- PostgreSQL database table for comments
- Ruby receives form posts, inserts into database
- When comments change, PostgreSQL trigger sends NOTIFY
- Ruby runs PostgreSQL LISTEN, exporting updated comments to HTML
- JavaScript on static page includes HTML
PostgreSQL database table for comments
create table comments (
id integer primary key generated by default as identity,
uri text,
created_at date default current_date,
name text,
email text,
comment text
);
create index on comments(uri);
Ruby receives form posts, inserts into database
Put this on any HTML page where you want comments:
<section id="comments"></section>
<script src="/comments.js"></script>
Put this next code in your Nginx config, to send /comments to localhost.
location = /comments {
proxy_pass http://127.0.0.1:4567;
}
Ruby Sinatra receives form posts.
require 'pg'
require 'sinatra'
DB = PG::Connection.new(dbname: 'test', user: 'tester')
post '/comments' do
DB.exec_params("insert into comments
(uri, name, email, comment)
values ($1, $2, $3, $4)",
[params[:uri], params[:name], params[:email], params[:comment]])
redirect to(request.env['HTTP_REFERER'])
end
Run that in a terminal on the server, and it should default to listen on port 4567.
When comments change, PostgreSQL trigger sends NOTIFY
create function comments_changed() returns trigger as $$
begin
perform pg_notify('comments_changed', new.uri);
return new;
end;
$$ language plpgsql;
create trigger comments_changed after insert or update on comments
for each row execute procedure comments_changed();
Load that function into the PostgreSQL database that has your comments table.
It sends the listener (below) a notification that comments for this URI have changed. Then the listener will re-output comments just for this URI, instead of all.
Ruby runs PostgreSQL LISTEN, exporting updated comments to HTML
Make a directory in your web root called /commentcache/, to hold the static comments.
Then keep this Ruby script running in a terminal to listen for database changes, and write the updated comments to disk as HTML.
require 'pg'
DB = PG::Connection.new(dbname: 'test', user: 'tester')
BASEDIR = '/var/www/htdocs/commentcache/' # directory in your web root
# a single comment list entry, used in ol map, below
def li(row)
'<li><cite>%s (%s)</cite><p>%s</p></li>' %
[row['name'], row['created_at'], row['comment']]
end
# top-level map of database rows into HTML list
def ol(rows)
rows.inject('') {|html, row| html += li(row) ; html}
end
# write comments to disk for this URI
def save_comments(uri)
rows = DB.exec_params("select name, created_at, comment
from comments where uri = $1 order by id", [uri]).to_a
File.open(BASEDIR + uri, 'w') do |f|
f.puts ol(rows)
end
end
# first write them all
DB.exec("select distinct(uri) from comments").each do |r|
save_comments(r['uri'])
end
# listen for changes. re-write when changed
DB.exec('listen comments_changed')
while true do
DB.wait_for_notify do |event, pid, uri|
save_comments(uri)
end
end
JavaScript on static page includes current HTML when viewed
Use JavaScript to show the form to post a comment, and load the list of comments from the /commentcache/ path.
function showForm(uri) {
document.getElementById('comments').innerHTML = `
<header><h1>Comments:</h1></header>
<form method="post" action="/comments">
<input type="hidden" name="uri" value="${uri}">
<label for="name">Your Name</label>
<input type="text" name="name" id="name" required>
<label for="email">Your Email</label>
<input type="email" name="email" id="email" required>
<label for="comment">Comment</label>
<textarea name="comment" id="comment" cols="80" rows="10" required></textarea>
<input type="submit" value="post comment">
</form>
<ol id="commentlist"></ol>`;
}
function getComments(uri) {
try {
const xhr = new XMLHttpRequest();
xhr.open('get', '/commentcache/' + uri);
xhr.send(null);
xhr.onload = function() {
if (xhr.status === 200) {
document.getElementById('commentlist').innerHTML = xhr.responseText;
}
};
} catch(e) { }
}
// /blog/topic/page.html uri = 'blog_topic_page.html' for filesystem
const uri = location.pathname.substring(1).replace(/\//g, '_');
showForm(uri);
getComments(uri);
That’s all. I’ve simplified it a bit from my real usage, where I have constraints and checks that would have distracted from the core point of this example.
There are other ways to do it. The NOTIFY and LISTEN isn’t necessary. The Ruby Sinatra route that receives the posted comment could just write the HTML to disk immediately. But I have other scripts that delete and update comments, and I like how the combination of NOTIFY trigger and LISTEN script always keeps them updated on disk.
Another interesting approach would be to write the comments into each HTML file directly, instead of in a separate file, so you wouldn’t need JavaScript at all.
Optional upgrade: NOTIFY on delete
I simplified the PostgreSQL trigger for the example, but with a few more lines of code, you can use the same trigger to notify of deleted comments, too. The value of a deleted row is in “old”, whereas inserted and updated is in “new”, so we have to make a uri variable, and an if/then/else to know which to use.
create or replace function comments_changed() returns trigger as $$
declare
uri text;
begin
if tg_op = 'DELETE' then
uri = old.uri;
else
uri = new.uri;
end if;
perform pg_notify('comments_changed', uri);
return old;
end;
$$ language plpgsql;
create trigger comments_changed after insert or update or delete on comments
for each row execute procedure comments_changed();