SQLite over email

What is this?

An experiment based on sqlmail.dev. Send an email Send an email containing SQLite blocks fenced by <sql>...</sql> tags to:

sql@nicobaier.com

This will spin up a SQLite database in a Durable Object and run the queries against it. The beauty of this architecture is that each email address gets its own isolated database.

What can I do?

Anything you can do with SQLite, you can do here! I wouldn't put anything sensitive or crucial, but it's a fun way to experiment with SQLite. Here are some quick examples.

Check connection

compose

Simple sanity check that returns the current timestamp

<sql>
SELECT
  datetime() AS now;
</sql>

Create books table

compose

Creates a basic books table if it does not exist

<sql>
CREATE TABLE IF NOT EXISTS books (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  title TEXT NOT NULL,
  author TEXT NOT NULL,
  genre TEXT NOT NULL,
  published_date DATE NOT NULL
);
</sql>

Insert sample books

compose

Adds a couple of example rows to the books table

<sql>
INSERT INTO
  books (title, author, genre, published_date)
VALUES
  ('The Great Gatsby', 'F. Scott Fitzgerald', 'Classic', '1925-04-10'),
  ('1984', 'George Orwell', 'Dystopian', '1949-06-08'),
  ('To Kill a Mockingbird', 'Harper Lee', 'Southern Gothic', '1960-07-11');
</sql>

List books

compose

Returns all books ordered by most recent

<sql>
SELECT
  *
FROM
  books
ORDER BY
  published_date DESC;
</sql>

Tips: Emails can contain multiple <sql>...</sql> blocks. Queries run top-to-bottom. Anything outside of <sql>...</sql> blocks will be ignored.

Built by Nico Baier

github