Static HTML comments
If 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();
Love it
This is cool!
Love this! I'll give it a go with Go or Python.
I'm commenting! My words are soon to be static HTML, wowie!
Here today I won't comment specifically on the blog page.
But I can seize the moment to remind us of some context. Back when the Internet was new, and newspapers ran frequent articles to review interesting web sites, and the Readers Digest had an article about a nerd-father and his nerd sons, there was an observation about nerds at cocktail parties.
Some of the nerds holding glasses, like some of the tech support people on the telephone, would use words people didn't understand. Like a communist peppering his speech with lackey, running dog and bourgeoisie, the nerds were oblivious that they weren't being understood.
Obviously a blog piece by computer users for computer users is expected to use the words of computer users. Like here, today, labeled "tech blog."
Meanwhile, a teachable moment for ourselves would be if we asked, "Would a member of the older generation be comfortable if this was directed at them, at the general public?
Not to get a job in tech support, but to be more empathetic at parties. Hey, you may call me a stereotypical stiff uptight nerd, dressed all in brown, but I can enjoy parties too.
Nice! wonder if this can be applied to Crystal ? and / or using SQLite ?
It's a cool implementation showing what you can do with Postgres.
I wonder whether the “hug of death” is realistic nowadays, though. Postgres is very performant and has sophisticated caching concepts.
Still, I like the idea of static files. But why don't you let the first ruby script make the update of the static file immediatly after sending the comment to Postgres?
If I were concerned about too many (spam) comments, I would rather consider a cron job to run an update script every 10 minutes or so, that first checks whether there has been an update in the database.
Wow, this is nice. Thanks!
Do you do anything for spam comments? I guess I will find out with this comment if it goes up immediately or if it is filtered in some way...
(I did do a quick search on your site and didn't see anything about how you deal with spam comments.)
I have a separate little web app where I monitor new comments, and delete the spam. — Derek
That's cool. Gonna try it.
Interesting!
You can use dynamic server side includes in nginx [1] to avoid the xhr call and keep the comments in a separate file at the same time
[1]: www.nginx.com/resources/wiki/start/topics/examples/dynamic_ssi/
I would have gone this path myself if it weren’t for the fact that my blogs tech stack seems like it’s from the 90s, and people rarely leave blog comments either way
Cool! Thanks for this tip. — Derek
Pretty clever but won't the ruby script be ran n times for each comment that's made ? shouldn't there be some rate limiter to the frequency you update the comments.html ?
The Ruby script is listening in the background. When a comment is posted, the query is run once to re-output the static HTML comments for that one page. (So n=1.) — Derek
Awesome!!
I wonder if HTML formatting is supported?
Bold
Italics
Marquee
alert("Script");
h1
Nope. I strip all tags before saving it in the database. — Derek
Love the thinking here. It goes to the whole minimalist lifestyle I guess, the web has become this overloaded thing with hundreds of scripts just to load a page, especially on a social media site. Tracking, updates, checks and balances, robots and the like all working harder and harder to make it happen.
Though I think there is purpose in a "commenting" tool like Wordpress comments methods for subscribing/ unsubscribing and notification settings and the like.
I have recently got sick of typing my name, email, phone and address into yet another website.
Is it simpler for some 3rd party ID service to manage the logins for many websites?
Not Fb or Google login - that is not really a 3rd party, a specialist, we only do Identity, type web service, Stripe for ID if you like.
Then your subscriptions would be in one place too...
How do you manage subscriptions on your statis HTML commenting form? Will I be notified if you reply? Wil I now be notified if others reply to this, or should I be responsible for creating a reminder to return here at some future point in time to review?
I have been reading your posts and was wondering how you included comments. Thanks for showing how you do it! Might try and add this to some future posts ;)
Awesome! I love simplicity, reliability, and efficiency. KISS all the way.
This thing will so easily survive being slashdotted, er, HN'd.
Hi Derek, like your blog a lot.
As I rarely leverage relations on comments to my blog and get only few, I stuck with static files and embed them client side: blog.mro.name/2019/05/wp-to-hugo-making-of/
indieweb.org/manual_until_it_hurts doesn't hurt yet.
Hello Derek, I discovered your blog a week ago and I can't stop reading your articles. A lot of them really resonated with me and changed my vision on a few things.
When I saw this post about your comment system, I had to write a comment myself to report a little bug (this is quite meta). It seems that when you answer to someone in the comments and add a link, it appears broken. You can see an example here : sive.rs/led#comment-80561
Thank you again for your blog!
Thanks Anh! — Derek
Hey, I wanted to comment about redundant ajax calls - each visit would still make an http request for the comments, but I like how you handled that with lazyloading
Feels like there should be a comment on this article :)
Wanted to do a little test for 'constraints and checks' 💩
document.getElementsByTagName('article')[0].style.color = "orange"
I have it strip away HTML tags and then use HTML-entities for the other things like ampersands and quote-marks. — Derek
I may have missed it, and I'm not that familiar with databases overall, but I assume you have other code for avoiding SQL injection attacks? Not sure if that would even work with this setup, I'm just not familiar enough with databases haha. If you could, I'd appreciate some insight. :)
Yes in these code examples, DB.exec_params() with $1, $2, $3, $4. — Derek
Thanks for sharing this, Derek. I'm going to try it out on my own blog this weekend.
Yeah, the site can be static HTML, but for every comment it has to be regenerated. If we post enough comments, it will get overloaded!
/s
Come on everybody, start shit posting