A sqlite webapp
I wrote a webapp almost entirely in sqlite to see if it is possible.
Edit 2023-01-31: Just came across this epic post that describes how to build a CRUD app with Postgres and postgREST.
After watching the classical talk “I See What You Mean” by Peter Alvaro I’ve been wondering what would happen if SQL were more of a programming language.
I’m also interested in what is wrong with SQL and how to possibly improve it (for example can SQL be saved by minimally changing it to allow more abstraction?). Or how about languages that compile to SQL like CG-SQL or Prql.
Anyways, to write a webapp we need some way to listen on a socket. Let’s just use a small application in some other language to do that for now. Let’s have that application write all the requests to a table, and read the responses from another table. Then the SQL part can generate a response from a request with triggers.
Here is a proof-of-concept python script. It might not be minimal, but still pretty short.
import http.server
import sqlite3
con = sqlite3.connect('app.db')
cur = con.cursor()
class RequestHandler(http.server.BaseHTTPRequestHandler):
def do_HEAD(self):
self.custom_handle('HEAD')
def do_GET(self):
self.custom_handle('GET')
def do_POST(self):
self.custom_handle('POST')
def do_PUT(self):
self.custom_handle('PUT')
def do_DELETE(self):
self.custom_handle('DELETE')
def custom_handle(self, verb):
content_length = int(self.headers.get('Content-Length', 0))
body = (None if content_length is None else self.rfile.read(int(content_length)).decode())
cur.execute("insert into requests (path, verb, request_headers, body) values (?, ?, ?, ?)",
(str(self.path), verb, str(self.headers), str(body)))
con.commit()
request_id = cur.lastrowid
cur.execute("select response_headers, code, body from responses where request_id=:rid LIMIT 1",
{"rid": request_id})
con.commit()
response = cur.fetchone()
response_headers, response_code, response_body = response
self.send_response(response_code)
for header_line in response_headers.split('\n'):
self.send_header(*header_line.split(':'))
self.end_headers()
self.wfile.write(bytes(response_body, encoding='utf-8'))
http = http.server.HTTPServer(('localhost', 9998), RequestHandler)
http.serve_forever()
Start the server by running the following on the command line. It won’t work yet, since the db isn’t set up yet.
python app.py
Now let’s discuss the sqlite part.
I added all the sql definitions to a file init.sql.
Apply this file with:
sqlite3 app.db < init.sql
The first part of init.sql is the definitions of the intput and output tables:
drop table if exists requests;
create table if not exists requests (
request_id integer primary key,
verb text not null,
path text not null,
request_headers text not null,
body text not null
);
drop table if exists responses;
create table if not exists responses (
response_id integer primary key,
request_id integer,
body text,
response_headers text,
code int not null,
foreign key (request_id)
references requests (request_id)
on delete cascade
on update no action
);
Just tables with fields for the verb, request and response body as text, and ids to join on.
Now a table that contains html templates to render.
drop table if exists templates;
create table templates (name text, value text);
insert into templates (name, value) values ('index', TRIM(readfile('index.html'), char(10)));
insert into templates (name, value) values ('not_found', TRIM(readfile('404.html'), char(10)));
index.html and 404.html is about as you might expect.
<!doctype html>
<html>
<head>
<title>sqlite todo app</title>
</head>
<body>
<h1>Todo list</h1>
<form action="/" method="POST">
<ul>
</ul>
<label for="iname">Item:</label><br>
<input type="text" id="iname" name="iname"><br>
<input type="submit" value="Submit">
</form>
</body>
</html>
<!doctype html>
<html>
<head>
<title>Not found</title>
</head>
<body>
<h1>404</h1>
<p>Not found</p>
</body>
</html>
Btw, as you might have noticed by now, this is another todo list app.
Let’s continue with init.sql.
Define the table that stores the todo list.
drop table if exists todo_items;
create table if not exists todo_items (
item text check(item <> ''),
done boolean default FALSE
);
insert into todo_items (item, done) values('test', 'False');
Now define a view that renders the list.
drop view if exists todo_list;
create view if not exists todo_list as
with items as (
select
rowid,
case when not done then item
else '<s>' || item || '</s>' end as done_item
from todo_items
), list_items as (
select '<li><button name="rem" value="' || rowid || '" type="submit">x</button>' || done_item || '</li>' as li
from items
)
select group_concat(li, '') as todo_string from list_items;
Now the main entrypoints of the application. Let’s wire up the requests and responses with a trigger that, when a row is written to the request table, generates a row in the reponse table.
drop trigger if exists route_get;
create trigger route_get
after insert on requests
when new.verb = 'GET'
and new.request_id not in (select request_id from responses)
begin
insert into responses (request_id, response_headers, body, code)
select
new.request_id AS request_id,
'Content-type: text/html' as response_header,
replace(templates.value, '', todo_string) AS body,
200 AS code
from templates
join todo_list
where templates.name = 'index'
and new.path = '/'
limit 1;
insert into responses (request_id, response_headers, body, code)
select
new.request_id AS request_id,
'Content-type: text/html' as response_header,
templates.value AS body,
404 AS code
from templates
where templates.name = 'not_found'
and new.request_id not in (select request_id from responses)
limit 1;
end;
We also want to write rows into the todo list or tick tasks off the list, so handle POST requests in a similar way with a trigger.
drop trigger if exists route_post;
create trigger route_post
after insert on requests
when new.path = '/'
and new.verb = 'POST'
and new.request_id not in (select request_id from responses)
begin
insert or ignore into todo_items (item, done)
values (substr(new.body, instr(new.body,'iname=')+6, length(new.body)), "False");
-- https://noties.io/blog/2019/08/19/sqlite-toggle-boolean/index.html
update todo_items set done = ((done | 1) - (done & 1))
where rowid = substr(new.body, instr(new.body,'rem=')+4, instr(new.body,'&')-(instr(new.body,'rem=')+4));
update todo_items
set done = ((done | 1) - (done & 1))
where rowid = substr(new.body, instr(new.body,'rem=')+4, instr(new.body,'&')-1);
insert into responses (request_id, response_headers, body, code)
select
new.request_id AS request_id,
'Content-type: text/html' as response_header,
replace(templates.value, '', todo_string) AS body,
200 AS code
from templates
join todo_list
where templates.name = 'index'
limit 1;
end;
That’s it. I never said it is a good idea, but it can be done.
Things I wished sqlite had (but which might be available in other sql dbs):
- Better string formatting tools to render the templates.
- Macros to avoid repeating things like column names.
- Stored procs would actually have been useful here.
Without the above it is pretty tedious to write something complicated in sqlite,
but what else can be done? Authorisation and authentication can be added by
joining on a users table. Unittests can be added directly in the init script
by writing some requests into the request table and joining on the expected responses.
We can actually get pretty far, even though the sql language isn’t designed for this.
Also, many business users already know sql!
I can even imagine creating a framework, if only it was possible to install sql packages.
(Don’t worry I’m not serious. Unless…)