d1-movie-example

0001_create-db.sql

CREATE TABLE IF NOT EXISTS movies (
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	title TEXT NOT NULL,
	release_date TEXT NOT NULL,
	rating INT NOT NULL
);

0002_insert-movies.sql

INSERT INTO movies (title, release_date, rating) VALUES
('The Shawshank Redemption', '1994-09-23', 6),
('The Godfather', '1972-03-24', 10),
('The Dark Knight', '2008-07-18', 7),
('The Godfather: Part II', '1974-12-20', 9),
('12 Angry Men', '1957-04-10', 8);

commands.md

• npx wrangler d1 create movie-example
• npx wrangler d1 migrations create movie-example create-db
• npx wrangler d1 migrations apply movie-example --local
• npx wrangler d1 execute movie-example --command 'pragma table_list'
• npx wrangler d1 execute movie-example --command 'select * from movies'
• npx wrangler d1 migrations create movie-example add-movies

index.ts

import { Hono } from 'hono';

const app = new Hono<{ Bindings: Env }>();

app.get('/movies', async (c) => {
	const { results: movies } = await c.env.DB.prepare('select * from movies').run();
	return c.json(movies);
});

app.get('/favorites', async (c) => {
	const { results: favorites } = await c.env.DB.prepare('select * from movies order by rating desc limit 3').run();
	return c.json(favorites);
});

app.post('/movies/:id', async (c) => {
	const body = await c.req.json();
	const result = await c.env.DB.prepare('UPDATE movies SET rating = ?1 WHERE id = ?2 RETURNING *')
		.bind(body.rating, c.req.param('id'))
		.run();
	const ok = result.success;
	return c.json({ ok });
});

export default app;

wrangler.toml

#:schema node_modules/wrangler/config-schema.json
name = "d1-movie-example"
main = "src/index.ts"
compatibility_date = "2024-08-06"

[[d1_databases]]
binding = "DB"
database_name = "movie-example"
# database_id = "db5f3227-4457-4668-b9b6-989dec4314f0"
database_id = "a"