Messages

The chat keeps its chats and messages in an SQL database of its own, a SQLite file in the store's directory. This chapter writes the schema as migrations, saves messages, starts a chat and its first message in one transaction, and reads a chat back a page at a time. It uses the SQL engine.

#The schema

A database's schema lives in migration files, one change per file, applied in the order of their names:

create table chats (
    id         integer primary key,
    user_id    integer not null,
    title      text not null,
    created_at integer not null
) strict;

create table messages (
    id         integer primary key,
    chat_id    integer not null references chats (id) on delete cascade,
    role       text not null check (role in ('user', 'assistant')),
    text       text not null,
    created_at integer not null
) strict;

create index messages_by_chat on messages (chat_id, id);
create index chats_by_user on chats (user_id, id);

This is plain SQLite. strict makes SQLite refuse a value of the wrong type, such as text in an integer column, instead of storing it anyway. The foreign key deletes a chat's messages with the chat. The check keeps role to the two values the chat knows. Times are unix milliseconds, the way every engine of TinyStore stores them.

#Open the database

interface Message {
	id: number
	chat_id: number
	role: 'user' | 'assistant'
	text: string
	created_at: number
}

const db = await store.database('chat', { migrations: `${import.meta.dirname}/migrations` })
const messages = db.table<Message>('messages')

@dataclass
class Message:
    id: int
    chat_id: int
    role: str
    text: str
    created_at: int


db = await store.database("chat", migrations=Path(__file__).parent / "migrations")
messages = db.table("messages", Message)

type Message struct {
	ID        int64
	ChatID    int64
	Role      string
	Text      string
	CreatedAt int64
}

db, err := sqldb.Open(ctx, store, "chat", sqldb.Migrations(os.DirFS("migrations")))
if err != nil {
	return err // a migration that fails, or one changed after it was applied
}
messages := sqldb.OpenTable[Message](db, "messages")

#[derive(Serialize, Deserialize, Debug)]
struct Message {
    id: i64,
    chat_id: i64,
    role: String,
    text: String,
    created_at: i64,
}

let db = store.database("chat").migrations("migrations").open()?;
let messages = db.table::<Message>("messages");

The database's file is data/sql/chat.db. Opening it applies every migration the file has not applied yet, all of them in one transaction, so the file has the old schema or the whole new one. Then it checks the ones applied before: if a migration file was edited, renamed or deleted after it ran, the open fails and names the file. A program that starts has the schema its code expects, or it does not start.

db.table is a typed handle on one table. It reads rows into your type, by the columns' names: Go's ChatID is the column chat_id. It costs nothing until its first call.

In Go, os.DirFS reads the files from the working directory. To build them into the binary, embed them with //go:embed migrations/*.sql and pass fs.Sub(migrations, "migrations").

#Save a message

const reply = await db.exec`insert into messages (chat_id, role, text, created_at)
	values (${chatId}, 'assistant', ${'Day one: Alfama.'}, ${Date.now()})`
reply.lastInsertRowid // 2: the new message's id

reply = await db.exec(
    "insert into messages (chat_id, role, text, created_at) values (?, 'assistant', ?, ?)",
    chat_id, "Day one: Alfama.", time.time_ns() // 1_000_000,
)
reply.last_insert_rowid  # 2: the new message's id

reply, err := db.Exec(ctx, `insert into messages (chat_id, role, text, created_at) values (?, 'assistant', ?, ?)`,
	chatID, "Day one: Alfama.", time.Now().UnixMilli())
reply.LastInsertRowid // 2: the new message's id

let reply = db.exec(sql!(
    "insert into messages (chat_id, role, text, created_at) values (?, 'assistant', ?, ?)",
    chat_id,
    "Day one: Alfama.",
    unix_millis(SystemTime::now())
))?;
reply.last_insert_rowid // 2: the new message's id

exec runs a write and returns after it is on the disk. It returns how many rows changed and the id SQLite gave the inserted row. A value is always a value, never SQL: TypeScript's ${…} in a template, a ? in Python and Go, and the arguments of Rust's sql! all become parameters. A message that contains '); drop table chats; -- is stored as that text.

#Many messages at once

At a busy moment, many requests write at the same time. TinyStore puts the writes that arrive together into one commit, so that they share one sync of the disk, and gives each write its own savepoint inside it. If one of them breaks a rule of the schema, only that one fails:

await db.exec`insert into messages (chat_id, role, text, created_at)
  values (${chatId}, 'user', ${text}, ${Date.now()})`

A write in a shared commit fails alone: one broken constraint never fails its neighbours (contract DUR-03). The error says which rule broke, so the program can answer the user properly (contract SQL-08):

try {
	await db.exec`insert into messages (chat_id, role, text, created_at) values (${chatId}, 'user', ${text}, ${Date.now()})`
} catch (err) {
	if (err instanceof InvalidError && err.constraint?.kind === 'foreignKey') {
		return notFound('this chat is gone')
	}
	throw err
}

try:
    await db.exec(
        "insert into messages (chat_id, role, text, created_at) values (?, 'user', ?, ?)",
        chat_id, text, time.time_ns() // 1_000_000,
    )
except tinystore.InvalidError as err:
    if err.constraint and err.constraint.kind == "foreignKey":
        return not_found("this chat is gone")
    raise

_, err = db.Exec(ctx, `insert into messages (chat_id, role, text, created_at) values (?, 'user', ?, ?)`,
	chatID, text, time.Now().UnixMilli())
var e *tinystore.Error
if errors.As(err, &e) && e.Kind == tinystore.Invalid && e.Constraint != nil && e.Constraint.Kind == "foreignKey" {
	return notFound("this chat is gone")
}

let sent = db.exec(sql!(
    "insert into messages (chat_id, role, text, created_at) values (?, 'user', ?, ?)",
    chat_id,
    text,
    unix_millis(SystemTime::now())
));
if let Err(err) = &sent {
    if err.constraint().is_some_and(|c| c.kind == ConstraintKind::ForeignKey) {
        return Err(not_found("this chat is gone"));
    }
}

#A new chat and its first message

A chat must never exist without its first message, and the message needs the chat's id, which only the first insert knows. That is a transaction: a function whose writes commit together, or not at all.

const chatId = await db.tx(async tx => {
	const chat = await tx.exec`insert into chats (user_id, title, created_at)
		values (${userId}, ${'Trip to Lisbon'}, ${Date.now()})`
	await tx.exec`insert into messages (chat_id, role, text, created_at)
		values (${chat.lastInsertRowid}, 'user', ${text}, ${Date.now()})`
	return chat.lastInsertRowid
})

async def start(tx: tinystore.SqlTx) -> int:
    chat = await tx.exec(
        "insert into chats (user_id, title, created_at) values (?, ?, ?)",
        user_id, "Trip to Lisbon", time.time_ns() // 1_000_000,
    )
    await tx.exec(
        "insert into messages (chat_id, role, text, created_at) values (?, 'user', ?, ?)",
        chat.last_insert_rowid, text, time.time_ns() // 1_000_000,
    )
    return chat.last_insert_rowid


chat_id = await db.tx(start)

var chatID int64
err = db.Tx(ctx, func(tx *sqldb.Tx) error {
	chat, err := tx.Exec(ctx, `insert into chats (user_id, title, created_at) values (?, ?, ?)`,
		userID, "Trip to Lisbon", time.Now().UnixMilli())
	if err != nil {
		return err
	}
	chatID = chat.LastInsertRowid
	_, err = tx.Exec(ctx, `insert into messages (chat_id, role, text, created_at) values (?, 'user', ?, ?)`,
		chatID, text, time.Now().UnixMilli())
	return err
})

let chat_id = db.tx(|tx| -> tinystore::Result<i64> {
    let now = unix_millis(SystemTime::now());
    let chat = tx.exec(sql!("insert into chats (user_id, title, created_at) values (?, ?, ?)", user_id, "Trip to Lisbon", now))?;
    tx.exec(sql!(
        "insert into messages (chat_id, role, text, created_at) values (?, 'user', ?, ?)",
        chat.last_insert_rowid,
        text,
        now
    ))?;
    Ok(chat.last_insert_rowid)
})?;

If the function returns, both rows are committed. If it throws or returns an error, neither row exists. A read inside the transaction sees the transaction's own writes.

A transaction holds the database's writer while its function runs, so keep it short: no calls to the model, no HTTP requests, nothing that waits on the outside world. TinyStore enforces that: a transaction that runs longer than five seconds is rolled back and fails with a limit error. Its function runs exactly once, and TinyStore never retries it for you.

#Read a chat, a page at a time

The chat screen shows the newest 50 messages and loads older ones as the user scrolls up. The table's query builder writes that query:

const newest = messages.where({ chat_id: chatId }).orderBy('id', 'desc')
const page = await newest.list({ limit: 50 })
const older = await newest.list({ limit: 50, after: page.next }) // page.next is undefined after the last page

newest = messages.where(chat_id=chat_id).order_by("id", "desc")
page = await newest.list(limit=50)
older = await newest.list(limit=50, after=page.next)  # page.next is None after the last page

newest := messages.Where(sqldb.Eq{"chat_id": chatID}).OrderBy("id", sqldb.Desc)
page, err := newest.List(ctx, sqldb.Limit(50))
older, err := newest.List(ctx, sqldb.Limit(50), sqldb.After(page.Next)) // page.Next is "" after the last page

let newest = messages.filter(sql::eq("chat_id", &chat_id)).order_by("id", Direction::Desc);
let page = newest.list(50, None)?;
let older = newest.list(50, page.next.as_deref())?; // page.next is None after the last page

list returns the rows and a cursor, next, for the page after them. The next page starts right after the last row of this one, by the query's order, so a message that arrives while the user scrolls never shows up twice and never pushes another one out of view. A page by offset would do both.

The builder writes the SQL a person would. newest.limit(50).toSQL() (to_sql() in Python and Rust) shows it:

select * from "messages" where "chat_id" = ? order by "id" desc limit cast(? as integer)

#The list of chats

The sidebar shows the user's chats, each with its last message. With a join and a subquery, that reads best as SQL:

const chats = await db.all<{ id: number; title: string; last: string }>`
	select c.id, c.title, m.text as last
	from chats c
	join messages m on m.id = (select max(id) from messages where chat_id = c.id)
	where c.user_id = ${userId}
	order by m.id desc
	limit 20`

@dataclass
class ChatRow:
    id: int
    title: str
    last: str


chats = await db.all(
    ChatRow,
    """select c.id, c.title, m.text as last
    from chats c
    join messages m on m.id = (select max(id) from messages where chat_id = c.id)
    where c.user_id = ?
    order by m.id desc
    limit 20""",
    user_id,
)

type ChatRow struct {
	ID    int64
	Title string
	Last  string
}

chats, err := sqldb.All[ChatRow](ctx, db, `
	select c.id, c.title, m.text as last
	from chats c
	join messages m on m.id = (select max(id) from messages where chat_id = c.id)
	where c.user_id = ?
	order by m.id desc
	limit 20`, userID)

#[derive(Deserialize, Debug)]
struct ChatRow {
    id: i64,
    title: String,
    last: String,
}

let chats: Vec<ChatRow> = db.all(sql!(
    "select c.id, c.title, m.text as last
     from chats c
     join messages m on m.id = (select max(id) from messages where chat_id = c.id)
     where c.user_id = ?
     order by m.id desc
     limit 20",
    user_id
))?;

all returns every row, one returns one row or nothing, and scalar returns one value, such as a count(*). Each of them also takes a write with returning, which runs on the writer and returns its rows after the commit.

Use the builder when the query depends on the request, such as filters a user turns on and off, or pages. Use SQL when the query is fixed, or when it has joins and subqueries. Both end up as the same kind of statement, with values as parameters.

#Change the schema

To change the schema, add a new migration file. Never edit one that has run:

alter table chats add column pinned integer not null default 0 check (pinned in (0, 1));

The next start applies 0002_pinned.sql and records it. A file that contains 0001 but not 0002 is updated, and a file that already ran both is left as it is. If you edit 0001_chats.sql after it ran, every start refuses to open the database, until the file is back as it was.

#When a write fails

The classes are ConflictError, InvalidError, LimitError and OutcomeUnknownError in TypeScript and Python. In Go they are the kinds tinystore.Conflict, tinystore.Invalid, tinystore.Limit and tinystore.OutcomeUnknown of a *tinystore.Error, and in Rust the same names in ErrorKind. Every error names the database and the statement or migration it is about.

#Why this design?

Why SQL stays SQL. An ORM hides the query, and then you debug a query you cannot see. In TinyStore every call takes SQL, and the query builder writes the SQL a person would write and shows it with toSQL(). TinyStore takes care of what is around the SQL: the file, its connections, migrations, a commit shared by many requests, and bounds so that one query cannot take the process down.

Why every start checks old migrations. A migration that someone edited after it ran means the files on disk and the code disagree about the schema, and nothing tells you until a query fails in production. TinyStore keeps a checksum of each applied migration and refuses to open the file when one changed. There are no "down" migrations: to undo a change, write the next migration.

Why writes share a commit but a transaction does not. Syncing the disk is the slow part of a write. When many requests insert at once, TinyStore syncs once for all of them, and a savepoint around each insert lets one fail without the others. A transaction is different: its function decides what to write from what it reads, so it must hold the writer alone while it runs and pay for its own sync. That is why exec and tx are two calls: they cost different things.

Why a transaction runs once and stops at five seconds. Some libraries run a transaction's function again when it conflicts. If that function called a payment API, the card would be charged twice. TinyStore runs it once. While it runs, every other write to the file waits, so a transaction that hangs would stop the whole database. The five-second bound turns that hang into an error.

Why a statement may not set a pragma or begin a transaction. All the writes of a commit share one connection. A pragma foreign_keys = off or a commit inside one write would change the rules for the writes beside it. So TinyStore refuses attach, begin, commit, rollback, savepoints and pragmas that change a setting, when it compiles the statement (contract SQL-02). Pragmas that only read, such as pragma table_info(messages), still work.

Why undefined in a condition is an error. messages.where({ chat_id: undefined }) could mean "every chat". If a request forgot to send the chat's id, that would show one user every message of every user. TinyStore refuses the condition instead, and refuses update and delete with an empty one (contract SQL-09). A filter that applies only sometimes is an if in your code.

#Limits and defaults

#See also

• The chat we build: where this chapter fits in the application.
• One directory, many engines: keep a bucket or a queue inside this database, so that it commits with the rows.
• SQL API reference: every call of the SQL engine.