Skip to content
Tutorials Center
My learning

Topics

Lesson 29 of 32 ยท Projects

Project: a notes app with a server API

Build a Nuxt notes app: CRUD server routes, input validation, SQLite storage and an optimistic UI that rolls back on failure, with Node stand-ins.

A notes app is small enough to finish in an afternoon and complete enough to practice the four things almost every full-stack app needs: a CRUD API, validation of untrusted input, a real database, and a user interface that feels instant. The last one is the interesting part. When you press "Add", the note should appear right away, not after a round trip to the server, and it must disappear again if the server says no.

This project builds on Server routes and the Nitro engine, Databases and ORMs and Data fetching with useFetch and useAsyncData. The project name mentions "realtime" in the sense of a UI that updates immediately. It does not use WebSockets: syncing between several devices is an extension at the end.

Real or stand-in? The Nuxt code below is real and read-only, because this site cannot run a Nuxt server. The runnable examples are Node.js stand-ins: one runs the same CRUD handlers against a real SQLite database from node:sqlite (in memory), and the other simulates an optimistic UI so you can see the list change before and after the server answers, including a failure and a rollback.

The moving parts

CRUD routes List, create, update and delete notes.
Validation Check every body before it touches the database.
SQLite One file, real SQL, no extra server.
Optimistic UI Show the change first, confirm later.
1 / 2

Step 1: the database

SQLite is a database in a single file, a good fit for development and for small apps. Node 24 ships a SQLite driver in the standard library, node:sqlite, so a Node-based Nuxt server needs no extra package for this lesson. (Larger projects often use an ORM such as Drizzle or Prisma, or Nitro's experimental database layer; see Databases and ORMs.) The module is marked experimental in Node, so check your Node version's notes before depending on it in production.

TypeScript
// server/utils/db.ts (read-only)
import { DatabaseSync } from 'node:sqlite'

let db: DatabaseSync | undefined

export function useNotesDb(): DatabaseSync {
  if (!db) {
    db = new DatabaseSync('data/notes.db')
    db.exec(`
      CREATE TABLE IF NOT EXISTS notes (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        body TEXT NOT NULL DEFAULT '',
        done INTEGER NOT NULL DEFAULT 0,
        created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
      )
    `)
  }
  return db
}

The connection is created on first use and reused afterwards. That is safe for a database connection, which is an expensive shared resource, and very different from sharing per-visitor data in module state (State with useState and Pinia).

Step 2: validation

Every body that comes from the browser is untrusted. A small function checks it and returns clean values or a clear error. In a bigger project you would use Zod or Valibot (Forms, validation and auth flows); the logic is the same.

TypeScript
// server/utils/validateNote.ts (read-only)
export function validateNoteInput(input: unknown, { partial = false } = {}) {
  const body = (input ?? {}) as Record<string, unknown>
  const errors: string[] = []
  const clean: { title?: string; body?: string; done?: boolean } = {}

  if (!partial || 'title' in body) {
    const title = typeof body.title === 'string' ? body.title.trim() : ''
    if (title.length < 1 || title.length > 100) errors.push('title must be 1 to 100 characters')
    else clean.title = title
  }
  if ('body' in body) {
    if (typeof body.body !== 'string' || body.body.length > 5000) errors.push('body must be text up to 5000 characters')
    else clean.body = body.body
  }
  if ('done' in body) {
    if (typeof body.done !== 'boolean') errors.push('done must be true or false')
    else clean.done = body.done
  }

  if (errors.length) {
    throw createError({ statusCode: 422, statusMessage: 'Invalid note', data: { errors } })
  }
  return clean
}

Status 422 ("unprocessable") is a common choice for well-formed requests with invalid values. Using one status for all validation failures keeps the client code simple.

Step 3: the CRUD routes

Each route is one file, and the file name states the method.

TypeScript
// server/api/notes/index.get.ts (read-only)
export default defineEventHandler(() => {
  return useNotesDb()
    .prepare('SELECT id, title, body, done, created_at AS createdAt FROM notes ORDER BY id DESC')
    .all()
})
TypeScript
// server/api/notes/index.post.ts (read-only)
export default defineEventHandler(async (event) => {
  const input = validateNoteInput(await readBody(event))
  const db = useNotesDb()
  const result = db
    .prepare('INSERT INTO notes (title, body) VALUES (?, ?)')
    .run(input.title!, input.body ?? '')

  setResponseStatus(event, 201)
  return db
    .prepare('SELECT id, title, body, done, created_at AS createdAt FROM notes WHERE id = ?')
    .get(result.lastInsertRowid)
})
TypeScript
// server/api/notes/[id].patch.ts (read-only)
export default defineEventHandler(async (event) => {
  const id = Number(getRouterParam(event, 'id'))
  if (!Number.isInteger(id)) throw createError({ statusCode: 400, statusMessage: 'Bad id' })

  const input = validateNoteInput(await readBody(event), { partial: true })
  const db = useNotesDb()
  const current = db.prepare('SELECT * FROM notes WHERE id = ?').get(id) as any
  if (!current) throw createError({ statusCode: 404, statusMessage: 'Note not found' })

  db.prepare('UPDATE notes SET title = ?, body = ?, done = ? WHERE id = ?').run(
    input.title ?? current.title,
    input.body ?? current.body,
    input.done === undefined ? current.done : input.done ? 1 : 0,
    id,
  )
  return db
    .prepare('SELECT id, title, body, done, created_at AS createdAt FROM notes WHERE id = ?')
    .get(id)
})
TypeScript
// server/api/notes/[id].delete.ts (read-only)
export default defineEventHandler((event) => {
  const id = Number(getRouterParam(event, 'id'))
  if (!Number.isInteger(id)) throw createError({ statusCode: 400, statusMessage: 'Bad id' })

  const result = useNotesDb().prepare('DELETE FROM notes WHERE id = ?').run(id)
  if (result.changes === 0) throw createError({ statusCode: 404, statusMessage: 'Note not found' })

  setResponseStatus(event, 204)
  return null
})

Two habits are worth underlining. Every query uses ? placeholders and passes values separately. Never build SQL by gluing user text into a string, or a note titled x'); DROP TABLE notes;-- becomes an attack. And the update merges the fields the client sent with what is stored, so a partial update (just toggling done) does not wipe the title.

The first stand-in runs the same logic against an in-memory SQLite database with the real driver. It exercises create, list, update, delete, validation errors and an attempted SQL injection.

Node.js CRUD handlers on a real SQLite database

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

The partial update changed done and kept the title. The injection attempt was stored as harmless text, and the table survived, because the value never became part of the SQL. The list at the end shows the final state.

Step 4: the page with an optimistic UI

The list page loads notes with useFetch. When the user adds a note, the page does not wait. It follows the four steps from the slides:

  1. Put a temporary note, with a negative id, at the top of the list.
  2. Send POST /api/notes in the background.
  3. On success, replace the temporary note with the real one from the server.
  4. On failure, remove the temporary note and show the message.
VUE
<!-- app/pages/notes.vue (read-only) -->
<script setup lang="ts">
interface Note {
  id: number
  title: string
  body: string
  done: number | boolean
}

const { data: notes } = await useFetch<Note[]>('/api/notes', { default: () => [] })
const title = ref('')
const error = ref('')
let tempId = 0

async function addNote() {
  const text = title.value.trim()
  if (!text) return
  error.value = ''
  title.value = ''

  const temp: Note = { id: --tempId, title: text, body: '', done: false }
  notes.value = [temp, ...notes.value]

  try {
    const saved = await $fetch<Note>('/api/notes', { method: 'POST', body: { title: text } })
    notes.value = notes.value.map((n) => (n.id === temp.id ? saved : n))
  } catch (e: any) {
    notes.value = notes.value.filter((n) => n.id !== temp.id)
    title.value = text
    error.value = e?.data?.data?.errors?.join(', ') ?? e?.statusMessage ?? 'Could not save the note'
  }
}

async function toggle(note: Note) {
  const before = note.done
  note.done = !before
  try {
    await $fetch('/api/notes/' + note.id, { method: 'PATCH', body: { done: !before } })
  } catch {
    note.done = before
    error.value = 'Could not update the note'
  }
}

async function remove(note: Note) {
  const snapshot = notes.value
  notes.value = notes.value.filter((n) => n.id !== note.id)
  try {
    await $fetch('/api/notes/' + note.id, { method: 'DELETE' })
  } catch {
    notes.value = snapshot
    error.value = 'Could not delete the note'
  }
}
</script>

<template>
  <main>
    <h1>Notes</h1>
    <form @submit.prevent="addNote">
      <input v-model="title" placeholder="New note" maxlength="100" />
      <button>Add</button>
    </form>
    <p v-if="error" role="alert">{{ error }}</p>
    <ul>
      <li v-for="note in notes" :key="note.id">
        <label>
          <input type="checkbox" :checked="Boolean(note.done)" @change="toggle(note)" />
          {{ note.title }}
        </label>
        <button @click="remove(note)">Delete</button>
      </li>
    </ul>
  </main>
</template>

The key to every optimistic update is a way back. For an add, the way back is removing the temporary note. For a toggle, it is the old value. For a delete, it is the saved list. Write the rollback before you write the happy path, and test it by making the server fail on purpose.

Notice what the server still does: it validates again and decides the final data. The optimistic UI is only a guess about what the server will say, and the answer from the server always replaces the guess.

The second stand-in is a small simulator. A fake server succeeds or fails based on the note text, with delays you can see in the log order. The list is printed at each step so you can watch the temporary note appear, get replaced, or vanish.

Node.js Watch an optimistic update and a rollback

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

In the first case you see the note while it is still "sending", then the confirmed version. In the second case the bad note vanishes and the message explains why. In the last case two additions overlap, and each one finds its own temporary note by id, which is why the temporary ids must be unique. Replacing by id, not by position, is what keeps this correct.

Where realtime comes in

With the current design, a second browser tab will not see changes until it reloads. Real-time sync needs the server to push events. Nitro supports WebSockets as an experimental feature, and Server-Sent Events are a simpler option for one-way updates: the client opens one long request, and the server writes a line whenever a note changes. Either way, the API from this lesson stays the same, and the push channel only tells clients "something changed, call refresh". Check Nitro's current documentation before choosing, because this area is still evolving.

Ideas to extend the project

  • Add per-user notes: a user_id column and the session checks from Project: a dashboard with auth.
  • Replace the hand-written validation with Zod and share the schema between client and server.
  • Add pagination with LIMIT and OFFSET, and a search box with LIKE.
  • Add an index for the queries you actually run, and read the query plans.
  • Write tests for the handlers (Testing Nuxt apps).

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why does every SQL statement in the routes use ? placeholders instead of putting the title into the SQL string?

    With placeholders the database treats the value as data, never as SQL, so input like x'); DROP TABLE notes;-- is stored as plain text.
  2. 2. What is the essential part of an optimistic update?

    An optimistic UI guesses success. It must be able to undo the guess, and the server's answer always has the last word.
  3. 3. Why should the PATCH route merge the fields it received with the stored note?

    A PATCH request describes only the fields that change. Missing fields must keep their current values.
  4. 4. Why is the temporary note replaced by id instead of by its position in the list?

    While a request is pending, the list can change. A unique temporary id always finds the right note.
  5. 5. Which status code does the lesson use for a well-formed request whose values fail validation?

    422 means the server understood the request but the content is invalid. It is a common choice for validation failures.

Summary

  • A CRUD API in Nuxt is one small file per method and path under server/api, with .get, .post, .patch and .delete suffixes.
  • Validate every body on the server, return a consistent status such as 422 for invalid values, and use ? placeholders for all SQL.
  • A shared database connection in a server util is fine; per-visitor data is not.
  • Optimistic UI means: show the change, send the request, then confirm with the server's data or roll back.
  • Match temporary items by a unique temporary id, and always keep a snapshot or old value for the rollback.
  • Pushing changes to other clients (WebSockets or Server-Sent Events) is an add-on that tells clients to refresh.

Next: Practice exercises with solutions