Your first query
Walks through the basics — create a table, store rows, and read them back.
Add a database
Add "database": true to runlot.json and deploy. The deploy creates the PostgreSQL database.
{ "database": true }runlot deployCreate a table
Create the file migrations/0001_init.sql.
create table posts (
id bigserial primary key,
title text not null,
created_at timestamptz not null default now()
);Apply the migration.
runlot pg migrateUse the data from a worker
export default {
async fetch(request: Request, env: Env): Promise<Response> {
const url = new URL(request.url);
if (request.method === "POST" && url.pathname === "/posts") {
const { title } = (await request.json()) as { title: string };
const rows = await env.db.exec(
"insert into posts (title) values ($1) returning id, title",
[title],
);
return Response.json(rows[0], { status: 201 });
}
const rows = await env.db.exec(
"select id, title, created_at from posts order by id desc limit 20",
);
return Response.json(rows);
},
};runlot deployCheck that it works
curl -X POST https://my-app.me.runlot.app/posts \
-H 'content-type: application/json' \
-d '{"title":"hello"}'
curl https://my-app.me.runlot.app/postsYou can also check directly from the CLI.
runlot pg execute -c "select count(*) from posts"Next steps
- To handle errors by SQLSTATE, see tryExec.
- For an API closer to node-postgres, use @runlot/pg.
- To use an ORM, see ORMs.