Docs / Database and file storage

Database and file storage

Examples for bindings, migrations, indexes and permission-aware file management.

Schema and migrations

Use the project database through c.env.DB in Hono or env.DBHono guide.

Terminal

joripspace db schema --project PROJECT
joripspace db migrate --project PROJECT --file migrations/002_add_feature.sql

Write the new SQL before running the second command. Add backward-compatible columns and indexes instead of dropping and recreating existing tables. Test locally and verify backup and recovery options before touching production data.

Read, create, update and delete

Bind values with bind(); never concatenate user input into SQL. The following server-side repository example still needs a separate administrator authorization check when exposed through HTTP.

Server database operations

const product = await env.DB.prepare('SELECT id,name,price FROM products WHERE id=?').bind(id).first();
await env.DB.prepare('INSERT INTO products(name,description,price,published) VALUES(?,?,?,0)').bind(name,description,price).run();
await env.DB.prepare('UPDATE products SET name=?,price=? WHERE id=?').bind(name,price,id).run();
await env.DB.prepare('UPDATE products SET published=0 WHERE id=?').bind(id).run();
await env.DB.prepare('DELETE FROM products WHERE id=? AND published=0').bind(id).run();

Group changes that must succeed together with an actual D1 batch or database transaction mechanism. Two independent awaited statements are not a transaction.

Cursor pagination and indexes

List query and query plan

SELECT id,name,price FROM products
WHERE published=1 AND id>? ORDER BY id LIMIT 21;

EXPLAIN QUERY PLAN
SELECT id,name,price FROM products
WHERE published=1 AND id>0 ORDER BY id LIMIT 21;

Display 20 rows and use the 21st only to determine whether another page exists. Pass the last displayed ID as the next cursor. Confirm that idx_products_public(published,id) serves both filtering and ordering.

Indexes consume storage and write capacity. Add them for real filtering and sorting paths instead of indexing every column.

Upload, list, read and delete files

The example applies the session middleware so each user can manage only their own PNG files. The 2 MiB limit belongs to the sample app, not the platform. Validate the actual file signature as well as Content-Type.

src/routes/files.ts

import { Hono } from 'hono';
import { bodyLimit } from 'hono/body-limit';
import { requireSession } from '../middleware/session';
import type { AppEnv } from '../types';
export const files = new Hono<AppEnv>();
files.use('*', requireSession);
files.use('*', bodyLimit({ maxSize: 2 * 1024 * 1024 })); // 이 예제 앱이 선택한 제한

files.put('/:id', async (c) => {
  const id = c.req.param('id');
  if (!/^[a-zA-Z0-9_-]{1,64}$/.test(id)) return c.json({ error: 'invalid_id' }, 400);
  const bytes = new Uint8Array(await c.req.arrayBuffer());
  const png = [137,80,78,71,13,10,26,10];
  if (!png.every((v,i) => bytes[i] === v)) return c.json({ error: 'png_required' }, 415);
  const key = 'images/' + c.get('identity').user_id + '/' + id + '.png';
  await c.env.STORAGE.put(key, bytes, { httpMetadata: { contentType: 'image/png' } });
  return c.json({ id }); // 임의의 다른 User 키를 받지 않습니다.
});
files.get('/:id', async (c) => {
  const id = c.req.param('id');
  if (!/^[a-zA-Z0-9_-]{1,64}$/.test(id)) return c.json({ error: 'invalid_id' }, 400);
  const key = 'images/' + c.get('identity').user_id + '/' + id + '.png';
  const object = await c.env.STORAGE.get(key);
  if (!object) return c.json({ error: 'not_found' }, 404);
  return new Response(object.body, { headers: {
    'Content-Type': 'image/png', 'Cache-Control': 'private, no-store',
    'X-Content-Type-Options': 'nosniff', 'Content-Disposition': 'attachment; filename="image.png"',
  } });
});
files.delete('/:id', async (c) => {
  const id = c.req.param('id');
  if (!/^[a-zA-Z0-9_-]{1,64}$/.test(id)) return c.json({ error: 'invalid_id' }, 400);
  await c.env.STORAGE.delete('images/' + c.get('identity').user_id + '/' + id + '.png');
  return c.json({ ok: true });
});
files.get('/', async (c) => {
  const page = await c.env.STORAGE.list({
    prefix: 'images/' + c.get('identity').user_id + '/', limit: 50,
    ...(c.req.query('cursor') ? { cursor: c.req.query('cursor') } : {}),
  });
  return c.json({ files: page.objects.map(o => ({ id: o.key.split('/').pop()?.slice(0,-4), size: o.size })),
    cursor: page.truncated ? page.cursor : null });
});

Add to src/index.ts

import { files } from './routes/files';
app.route('/api/files', files);

After login, send PNG bytes to PUT /api/files/FILE_ID; GET and DELETE use the same URL. List files with GET /api/files?cursor=.... Add the local R2 binding to wrangler.jsonc when running locally.

This example is for non-sensitive images. Personal or evidentiary files need encryption plus explicit retention and deletion rules. Apply MIME validation, size limits, authorization and appropriate malware scanning.

View all docs Open projects