4. Database

In Part 3 you built pages using hardcoded book data. That works for a demo, but a real application needs to store and retrieve data from a database. In this part, you will set up a SQLite database, define tables, seed it with sample books, and query that data in your route handlers.

By the end, your book listing and detail pages will pull data from a real database instead of a hardcoded array.

What is a Database?

A database is a program that stores data in an organized way and lets you efficiently search, create, update, and delete that data. Think of it as a structured filing cabinet that your application can query.

Most databases organize data into tables. A table is like a spreadsheet: it has named columns (like "title", "author", "price") and rows (each row is one record, like one book). Here is what a books table might look like:

id	slug	title	author	price	genre
1	bbq	Ash & Smoke	Rusty Char-Broil	16.99	cookbook
2	heavy-metal	Heavy Metal Guitar Riffs	Axe Master Krush	27.00	music
3	three-ways	Three Ways to Change Your Life	Wisdom Sage	28.99	self-help

To get data from a table, you write a query --- a request that describes what data you want. For example, "find all books where genre is 'cookbook'" or "find the book where slug is 'bbq'."

Databases vs. files

You could store data in JSON files, but databases are much better for web applications. They handle multiple simultaneous users, let you search data efficiently (even with millions of rows), and ensure data stays consistent when things go wrong (like the server crashing mid-write).

Choosing SQLite

There are many database systems --- PostgreSQL, MySQL, SQLite, and others. For this tutorial, we use SQLite because:

It stores everything in a single file on disk (no separate server to install or manage).
It works on every operating system.
It is fast enough for most applications.
Remix's data-table package supports it out of the box.

For a production application with many concurrent users, you might switch to PostgreSQL or MySQL. Remix's data-table package makes this easy --- you just change the adapter (the part that talks to the database), and your queries stay the same.

Install SQLite

Install the better-sqlite3 package, which gives Node.js the ability to talk to SQLite databases:

bash

npm install better-sqlite3
npm install -D @types/better-sqlite3

Package	Purpose
`better-sqlite3`	A fast, synchronous SQLite driver for Node.js. It creates and manages the database file.
`@types/better-sqlite3`	TypeScript type definitions for the driver. Installed as a dev dependency because it is only needed during development for type checking.

Define the Schema

A schema is the blueprint for your database --- it describes what tables exist and what columns each table has. Remix's data-table package lets you define your schema in TypeScript.

Create the schema file:

bash

mkdir -p app/data

import { belongsTo, column as c, table, hasMany } from 'remix/data-table'
import type { TableRow } from 'remix/data-table'

export const books = table({
  name: 'books',
  columns: {
    id: c.integer(),
    slug: c.text(),
    title: c.text(),
    author: c.text(),
    description: c.text(),
    price: c.decimal(10, 2),
    genre: c.text(),
    cover_url: c.text(),
    isbn: c.text(),
    published_year: c.integer(),
    in_stock: c.boolean(),
  },
})

export const users = table({
  name: 'users',
  columns: {
    id: c.integer(),
    email: c.text(),
    password_hash: c.text(),
    name: c.text(),
    role: c.enum(['customer', 'admin']),
    created_at: c.integer(),
  },
})

export const orders = table({
  name: 'orders',
  columns: {
    id: c.integer(),
    user_id: c.integer(),
    total: c.decimal(10, 2),
    status: c.enum(['pending', 'processing', 'shipped', 'delivered']),
    shipping_address_json: c.text(),
    created_at: c.integer(),
  },
})

export const orderItems = table({
  name: 'order_items',
  primaryKey: ['order_id', 'book_id'],
  columns: {
    order_id: c.integer(),
    book_id: c.integer(),
    title: c.text(),
    unit_price: c.decimal(10, 2),
    quantity: c.integer(),
  },
})

// Relationships
export const itemsByOrder = hasMany(orders, orderItems)
export const bookForOrderItem = belongsTo(orderItems, books)

// Type aliases for convenience
export type Book = TableRow<typeof books>
export type User = TableRow<typeof users>
export type Order = TableRow<typeof orders>
export type OrderItem = TableRow<typeof orderItems>

Let's examine each part:

The `table()` Function

export const books = table({
  name: 'books',
  columns: { ... },
})

table() defines a table in your schema. It takes an object with:

name --- the name of the table in the database. By convention, table names are plural and lowercase.
columns --- an object where each key is a column name and each value describes the column's type.

Column Types

The column import (aliased as c for brevity) provides methods for each column type:

Method	Description	Example values
`c.integer()`	Whole numbers	`1`, `42`, `2024`
`c.text()`	Text strings	`"Ash & Smoke"`, `"fiction"`
`c.decimal(10, 2)`	Numbers with decimal places	`16.99`, `27.00`
`c.boolean()`	True or false	`true`, `false`
`c.enum([...])`	A text value restricted to specific options	`"admin"`, `"customer"`

The arguments to c.decimal(10, 2) mean "up to 10 total digits, with 2 after the decimal point." This is used for monetary values like prices.

What is c.enum()?

An enum (short for "enumeration") restricts a column to a specific set of values. For example, c.enum(['customer', 'admin']) means the role column can only be 'customer' or 'admin'. This prevents invalid data from being stored and gives you autocomplete in your editor.

Composite Primary Keys

Most tables use a single id column as the primary key --- the unique identifier for each row. The order_items table is different:

export const orderItems = table({
  name: 'order_items',
  primaryKey: ['order_id', 'book_id'],
  columns: { ... },
})

The primaryKey option creates a composite primary key --- the combination of order_id and book_id uniquely identifies each row. This means one order can have many books, and each book appears at most once per order.

What is a primary key?

A primary key is a column (or set of columns) that uniquely identifies each row in a table. No two rows can have the same primary key. For most tables, this is an auto-incrementing id column (1, 2, 3, ...). Primary keys let you look up a specific row quickly and are used to link tables together.

Relationships

export const itemsByOrder = hasMany(orders, orderItems)
export const bookForOrderItem = belongsTo(orderItems, books)

Relationships describe how tables connect to each other:

hasMany(orders, orderItems) --- one order has many order items. This lets you query "give me all items for order #1001."
belongsTo(orderItems, books) --- each order item belongs to one book. This lets you query "what book is this order item for?"

These relationships are used when you want to load related data together, which you will do later in the tutorial.

Type Aliases

export type Book = TableRow<typeof books>

TableRow<typeof books> generates a TypeScript type based on the columns you defined. The resulting Book type looks like:

{
  id: number
  slug: string
  title: string
  author: string
  description: string
  price: number
  genre: string
  cover_url: string
  isbn: string
  published_year: number
  in_stock: boolean
}

This means when you query the books table, every result is automatically typed --- your editor knows exactly what properties are available.

Create the Database

Now create the file that initializes the database, runs migrations, and seeds it with sample data.

What Are Migrations?

A migration is a script that modifies the database structure --- creating tables, adding columns, renaming things, and so on. Migrations are run in order, and the database keeps track of which ones have been applied. This way, when you change your schema, you just write a new migration and everyone's database gets updated correctly.

Create a directory for migrations:

bash

mkdir -p db/migrations

Create the first migration that sets up all the tables:

sql

CREATE TABLE IF NOT EXISTS books (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  slug TEXT NOT NULL UNIQUE,
  title TEXT NOT NULL,
  author TEXT,
  description TEXT,
  price REAL DEFAULT 0,
  genre TEXT,
  cover_url TEXT DEFAULT '/images/placeholder.jpg',
  isbn TEXT,
  published_year INTEGER,
  in_stock INTEGER DEFAULT 1
);

CREATE TABLE IF NOT EXISTS users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  email TEXT NOT NULL UNIQUE,
  password_hash TEXT NOT NULL,
  name TEXT NOT NULL,
  role TEXT NOT NULL DEFAULT 'customer',
  created_at INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS orders (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER NOT NULL,
  total REAL DEFAULT 0,
  status TEXT NOT NULL DEFAULT 'pending',
  shipping_address_json TEXT,
  created_at INTEGER NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS order_items (
  order_id INTEGER NOT NULL,
  book_id INTEGER NOT NULL,
  title TEXT NOT NULL,
  unit_price REAL NOT NULL,
  quantity INTEGER NOT NULL DEFAULT 1,
  PRIMARY KEY (order_id, book_id),
  FOREIGN KEY (order_id) REFERENCES orders(id),
  FOREIGN KEY (book_id) REFERENCES books(id)
);

CREATE TABLE IF NOT EXISTS password_reset_tokens (
  token TEXT PRIMARY KEY,
  user_id INTEGER NOT NULL,
  expires_at INTEGER NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

What is SQL?

SQL (Structured Query Language) is the standard language for interacting with databases. The CREATE TABLE statements above tell the database what tables to create and what columns they should have. You do not need to write SQL for most tasks --- Remix's data-table package generates it for you --- but migrations are one place where raw SQL is used.

The Setup File

Now create the database setup file that initializes everything:

import * as fs from 'node:fs'
import { fileURLToPath } from 'node:url'
import BetterSqlite3 from 'better-sqlite3'
import { createDatabase } from 'remix/data-table'
import { createMigrationRunner } from 'remix/data-table/migrations'
import { loadMigrations } from 'remix/data-table/migrations/node'
import { createSqliteDatabaseAdapter } from 'remix/data-table-sqlite'

import { books, users } from './schema.ts'

// Determine where to store the database file
let databaseDirectoryUrl = new URL('../../db/', import.meta.url)
let databaseFilePath = fileURLToPath(new URL('bookstore.sqlite', databaseDirectoryUrl))

// Make sure the directory exists
fs.mkdirSync(fileURLToPath(databaseDirectoryUrl), { recursive: true })

// Create the SQLite connection
let sqlite = new BetterSqlite3(databaseFilePath)
sqlite.pragma('foreign_keys = ON')

// Create the database adapter and the database instance
let adapter = createSqliteDatabaseAdapter(sqlite)
export let db = createDatabase(adapter)

// Migration and seeding
let migrationsDirectoryPath = fileURLToPath(new URL('../../db/migrations/', import.meta.url))

export async function initializeBookstoreDatabase(): Promise<void> {
  // Run any pending migrations
  let migrations = await loadMigrations(migrationsDirectoryPath)
  let migrationRunner = createMigrationRunner(adapter, migrations)
  await migrationRunner.up()

  // Seed sample data if the database is empty
  let booksCount = await db.count(books)
  if (booksCount === 0) {
    await db.createMany(books, [
      {
        id: 1,
        slug: 'bbq',
        title: 'Ash & Smoke',
        author: 'Rusty Char-Broil',
        description: 'The perfect gift for the BBQ enthusiast in your life!',
        price: 16.99,
        genre: 'cookbook',
        cover_url: '/images/bbq-1.png',
        isbn: '978-0525559474',
        published_year: 2020,
        in_stock: true,
      },
      {
        id: 2,
        slug: 'heavy-metal',
        title: 'Heavy Metal Guitar Riffs',
        author: 'Axe Master Krush',
        description: 'The ultimate guide to heavy metal guitar riffs!',
        price: 27.00,
        genre: 'music',
        cover_url: '/images/heavy-metal-1.png',
        isbn: '978-0735211292',
        published_year: 2018,
        in_stock: true,
      },
      {
        id: 3,
        slug: 'three-ways',
        title: 'Three Ways to Change Your Life',
        author: 'Wisdom Sage',
        description:
          'Life-changing strategies for modern living and personal growth.',
        price: 28.99,
        genre: 'self-help',
        cover_url: '/images/three-ways-1.png',
        isbn: '978-0061120084',
        published_year: 2021,
        in_stock: false,
      },
    ])
  }

  let usersCount = await db.count(users)
  if (usersCount === 0) {
    await db.createMany(users, [
      {
        id: 1,
        email: 'admin@bookstore.com',
        password_hash: 'placeholder',
        name: 'Admin User',
        role: 'admin',
        created_at: Date.now(),
      },
      {
        id: 2,
        email: 'customer@example.com',
        password_hash: 'placeholder',
        name: 'John Doe',
        role: 'customer',
        created_at: Date.now(),
      },
    ])
  }
}

Let's walk through the key parts:

Creating the Database

let sqlite = new BetterSqlite3(databaseFilePath)
sqlite.pragma('foreign_keys = ON')

let adapter = createSqliteDatabaseAdapter(sqlite)
export let db = createDatabase(adapter)

This creates the database in three steps:

Open the SQLite file --- new BetterSqlite3(databaseFilePath) opens (or creates) the database file at db/bookstore.sqlite.
Create an adapter --- createSqliteDatabaseAdapter(sqlite) wraps the SQLite driver in Remix's adapter interface. Adapters are what let you swap between SQLite, PostgreSQL, and MySQL without changing your queries.
Create the database instance --- createDatabase(adapter) creates the main db object you use to query data.

The sqlite.pragma('foreign_keys = ON') line enables foreign key constraints in SQLite. A foreign key is a column that references a row in another table (like orders.user_id referencing users.id). With this enabled, the database will prevent you from, say, creating an order for a user that does not exist.

Running Migrations

let migrations = await loadMigrations(migrationsDirectoryPath)
let migrationRunner = createMigrationRunner(adapter, migrations)
await migrationRunner.up()

This loads all .sql files from the db/migrations/ directory and runs any that have not been applied yet. The database tracks which migrations have run, so calling up() multiple times is safe --- it only runs new migrations.

Seeding Data

let booksCount = await db.count(books)
if (booksCount === 0) {
  await db.createMany(books, [ ... ])
}

Seeding means inserting initial data into the database. The if (booksCount === 0) check ensures we only seed once --- if the database already has books, we skip seeding.

db.createMany() inserts multiple rows at once. You pass it the table definition and an array of objects, where each object represents one row.

Create the Database Middleware

To use the database in route handlers, you need a way to access it. Remix uses middleware to make shared resources available through a context system.

import type { Middleware } from 'remix/fetch-router'
import { Database } from 'remix/data-table'

import { db } from '../data/setup.ts'

type SetDatabaseContextTransform = readonly [
  readonly [typeof Database, Database],
]

export function loadDatabase(): Middleware<
  'ANY',
  {},
  SetDatabaseContextTransform
> {
  return async (context, next) => {
    context.set(Database, db)
    return next()
  }
}

bash

mkdir -p app/middleware

This middleware does one thing: it puts the db instance into the request context using context.set(Database, db). The Database import is a context key --- a unique identifier that handlers use to retrieve the database. This pattern keeps your code decoupled: handlers do not import the database directly, they ask for it from the context.

Why use middleware for the database?

Passing the database through middleware (instead of importing it directly in every handler) has two benefits:

Testability --- in tests, you can swap in a different database (like an in-memory one) by providing different middleware.
Separation of concerns --- your route handlers do not need to know how the database is created or configured. They just ask for it.

Wire Up the Database

Update server.ts to initialize the database at startup:

import * as http from 'node:http'
import { createRequestListener } from 'remix/node-fetch-server'

import { initializeBookstoreDatabase } from './app/data/setup.ts'
import { router } from './app/router.ts'

// Initialize the database (run migrations, seed data)
await initializeBookstoreDatabase()

let server = http.createServer(
  createRequestListener(async (request) => {
    try {
      return await router.fetch(request)
    } catch (error) {
      console.error(error)
      return new Response('Internal Server Error', { status: 500 })
    }
  }),
)

let port = 3000

server.listen(port, () => {
  console.log(`Bookstore is running on http://localhost:${port}`)
})

The await initializeBookstoreDatabase() call runs before the server starts listening, ensuring the database tables exist and are seeded.

Update app/router.ts to include the database middleware:

import { createRouter } from 'remix/fetch-router'
import { compression } from 'remix/compression-middleware'
import { logger } from 'remix/logger-middleware'
import { staticFiles } from 'remix/static-middleware'

import { routes } from './routes.ts'
import { loadDatabase } from './middleware/database.ts'
import { home } from './controllers/home.tsx'
import { about } from './controllers/about.tsx'
import contactController from './controllers/contact.tsx'
import booksController from './controllers/books.tsx'

let middleware = []

if (process.env.NODE_ENV === 'development') {
  middleware.push(logger())
}

middleware.push(compression())
middleware.push(staticFiles('./public'))
middleware.push(loadDatabase())

export let router = createRouter({ middleware })

router.map(routes.home, home)
router.map(routes.about, about)
router.map(routes.contact, contactController)
router.map(routes.books, booksController)

The loadDatabase() middleware is added after staticFiles so that static file requests (CSS, images) do not unnecessarily load the database context.

Query Data in Route Handlers

Now update the books controller to query real data from the database. Replace app/controllers/books.tsx:

tsx

import type { Controller } from 'remix/fetch-router'
import { Database, ilike } from 'remix/data-table'
import { css } from 'remix/component'

import type { Book } from '../data/schema.ts'
import { books } from '../data/schema.ts'
import { routes } from '../routes.ts'
import { render } from '../utils/render.tsx'
import { Layout } from '../ui/layout.tsx'

export default {
  actions: {
    async index({ get }) {
      let db = get(Database)
      let allBooks = await db.findMany(books, { orderBy: ['id', 'asc'] })

      return render(<BooksIndexPage books={allBooks} />)
    },

    async genre({ get, params }) {
      let db = get(Database)
      let matchingBooks = await db.findMany(books, {
        where: ilike('genre', params.genre),
        orderBy: ['id', 'asc'],
      })

      if (matchingBooks.length === 0) {
        return render(<GenreNotFoundPage genre={params.genre} />, {
          status: 404,
        })
      }

      return render(
        <BooksGenrePage genre={params.genre} books={matchingBooks} />,
      )
    },

    async show({ get, params }) {
      let db = get(Database)
      let book = await db.findOne(books, { where: { slug: params.slug } })

      if (!book) {
        return render(<BookNotFoundPage />, { status: 404 })
      }

      return render(<BookShowPage book={book} />)
    },
  },
} satisfies Controller<typeof routes.books>

// --- Components ---

function BookCard() {
  return ({ book }: { book: Book }) => (
    <div class="book-card">
      <img src={book.cover_url} alt={book.title} />
      <div class="book-card-body">
        <h3>{book.title}</h3>
        <p class="author">by {book.author}</p>
        <p class="price">${book.price.toFixed(2)}</p>
        <a
          href={routes.books.show.href({ slug: book.slug })}
          class="btn"
        >
          View Details
        </a>
      </div>
    </div>
  )
}

function BooksIndexPage() {
  return ({ books }: { books: Book[] }) => (
    <Layout title="Books">
      <h1>Browse Books</h1>
      <div class="grid">
        {books.map((book) => (
          <BookCard book={book} />
        ))}
      </div>
    </Layout>
  )
}

function BooksGenrePage() {
  return ({ genre, books }: { genre: string; books: Book[] }) => (
    <Layout title={`${genre} Books`}>
      <h1>{genre} Books</h1>
      <div class="grid">
        {books.map((book) => (
          <BookCard book={book} />
        ))}
      </div>
      <p mix={css({ marginTop: '2rem' })}>
        <a href={routes.books.index.href()}>View all books</a>
      </p>
    </Layout>
  )
}

function BookShowPage() {
  return ({ book }: { book: Book }) => (
    <Layout title={book.title}>
      <div class="card">
        <h1>{book.title}</h1>
        <p><strong>Author:</strong> {book.author}</p>
        <p><strong>Genre:</strong> {book.genre}</p>
        <p><strong>ISBN:</strong> {book.isbn}</p>
        <p><strong>Published:</strong> {book.published_year}</p>
        <p><strong>Price:</strong> ${book.price.toFixed(2)}</p>
        <p><strong>In Stock:</strong> {book.in_stock ? 'Yes' : 'No'}</p>
        {book.description ? (
          <p mix={css({ marginTop: '1rem' })}>{book.description}</p>
        ) : null}
      </div>
      <p mix={css({ marginTop: '1rem' })}>
        <a href={routes.books.index.href()}>Back to all books</a>
      </p>
    </Layout>
  )
}

function GenreNotFoundPage() {
  return ({ genre }: { genre: string }) => (
    <Layout title="Genre Not Found">
      <h1>Genre Not Found</h1>
      <p>Sorry, we could not find any books in the "{genre}" genre.</p>
      <a href={routes.books.index.href()}>Browse all books</a>
    </Layout>
  )
}

function BookNotFoundPage() {
  return () => (
    <Layout title="Not Found">
      <h1>Book Not Found</h1>
      <p>Sorry, we could not find that book.</p>
      <a href={routes.books.index.href()}>Browse all books</a>
    </Layout>
  )
}

Let's examine the database queries:

`db.findMany()` --- Querying Multiple Rows

let allBooks = await db.findMany(books, { orderBy: ['id', 'asc'] })

findMany returns an array of all rows matching the criteria. The first argument is the table definition (books), and the second is an options object:

orderBy: ['id', 'asc'] --- sort by the id column in ascending order. You can also use 'desc' for descending.

The result is a Book[] --- TypeScript knows the exact shape of each object because you defined the columns in the schema.

`db.findMany()` with `where`

let matchingBooks = await db.findMany(books, {
  where: ilike('genre', params.genre),
  orderBy: ['id', 'asc'],
})

The where option filters rows. ilike('genre', params.genre) means "the genre column matches the given value, case-insensitively." The ilike function (short for "case-Insensitive LIKE") is imported from remix/data-table.

You can also use a plain object for exact matching:

await db.findMany(books, { where: { genre: 'fiction' } })

`db.findOne()` --- Querying a Single Row

let book = await db.findOne(books, { where: { slug: params.slug } })

findOne returns a single row or null if no match is found. The where option here uses an object --- { slug: params.slug } means "the slug column equals the value of params.slug."

Since findOne can return null, you must handle the case where the book is not found:

if (!book) {
  return render(<BookNotFoundPage />, { status: 404 })
}

Accessing the Database via Context

async index({ get }) {
  let db = get(Database)
  // ...
}

The get function retrieves values from the middleware context. get(Database) returns the database instance that the loadDatabase middleware put there. This is the same pattern you will use for other shared resources like sessions and the current user.

Update the Home Page

Update app/controllers/home.tsx to load featured books from the database:

tsx

import type { BuildAction } from 'remix/fetch-router'
import { css } from 'remix/component'
import { Database, inList } from 'remix/data-table'

import type { Book } from '../data/schema.ts'
import { books } from '../data/schema.ts'
import { routes } from '../routes.ts'
import { render } from '../utils/render.tsx'
import { Layout } from '../ui/layout.tsx'

export const home: BuildAction<'GET', typeof routes.home> = {
  async handler({ get }) {
    let db = get(Database)
    let featuredSlugs = ['bbq', 'heavy-metal', 'three-ways']
    let featuredBooks = await db.findMany(books, {
      where: inList('slug', featuredSlugs),
    })

    return render(<HomePage featuredBooks={featuredBooks} />)
  },
}

function HomePage() {
  return ({ featuredBooks }: { featuredBooks: Book[] }) => (
    <Layout>
      <div class="card">
        <h1>Welcome to the Bookstore</h1>
        <p mix={css({ margin: '1rem 0' })}>
          Discover your next favorite book from our curated collection.
        </p>
        <p>
          <a href={routes.books.index.href()} class="btn">
            Browse Books
          </a>
        </p>
      </div>

      <h2 mix={css({ margin: '2rem 0 1rem' })}>Featured Books</h2>
      <div class="grid">
        {featuredBooks.map((book) => (
          <div class="book-card">
            <img src={book.cover_url} alt={book.title} />
            <div class="book-card-body">
              <h3>{book.title}</h3>
              <p class="author">by {book.author}</p>
              <p class="price">${book.price.toFixed(2)}</p>
              <a
                href={routes.books.show.href({ slug: book.slug })}
                class="btn"
              >
                View Details
              </a>
            </div>
          </div>
        ))}
      </div>
    </Layout>
  )
}

The inList('slug', featuredSlugs) query filter means "where the slug column is one of the values in the array." This is equivalent to SQL's WHERE slug IN ('bbq', 'heavy-metal', 'three-ways').

Try It Out

Start the dev server:

bash

npm run dev

The first time you run it, you will see the migrations run and the seed data inserted. Visit:

http://localhost:3000/ --- featured books loaded from the database
http://localhost:3000/books --- all books from the database
http://localhost:3000/books/bbq --- book detail page with full data
http://localhost:3000/books/genre/cookbook --- filtered by genre
http://localhost:3000/books/nonexistent --- the 404 page

Check the db/ directory --- you should see a bookstore.sqlite file. That is your entire database.

Resetting the database

If you want to start fresh, just delete the db/bookstore.sqlite file and restart the server. The migrations will run again and the seed data will be re-inserted.

Your Project So Far

bookstore/
  app/
    controllers/
      about.tsx
      books.tsx          # Now queries the database
      contact.tsx
      home.tsx           # Now loads featured books from DB
    data/
      schema.ts          # Table definitions
      setup.ts           # Database initialization and seeding
    middleware/
      database.ts        # Makes DB available via context
    ui/
      document.tsx
      layout.tsx
    utils/
      render.tsx
    router.ts            # Now includes database middleware
    routes.ts
  db/
    migrations/
      001-create-tables.sql  # Database migration
    bookstore.sqlite         # The database file (auto-created)
  public/
    app.css
  package.json
  server.ts              # Now initializes DB at startup
  tsconfig.json

Other Query Methods

You have seen findMany, findOne, count, and createMany. Here is a summary of the main query methods you will use throughout the tutorial:

Method	Description	Returns
`db.findMany(table, options?)`	Find all matching rows	Array of rows
`db.findOne(table, options)`	Find one row	Row or `null`
`db.count(table, options?)`	Count matching rows	Number
`db.create(table, data)`	Insert one row	The inserted row
`db.createMany(table, data[])`	Insert multiple rows	Array of inserted rows
`db.update(table, id, data)`	Update one row	The updated row
`db.destroy(table, id)`	Delete one row	`void`

You will use create, update, and destroy in the next part when you build forms that let users submit data.

Recap

In this part you learned:

A database stores structured data in tables with rows and columns.
SQLite stores everything in a single file --- no separate server needed.
table() defines a table schema with typed columns.
Column types include integer(), text(), decimal(), boolean(), and enum().
Relationships like hasMany and belongsTo describe how tables connect.
Migrations are SQL scripts that create or modify tables.
Seeding inserts initial data into a new database.
createDatabase() and adapters create the database instance.
Database middleware makes the db instance available to handlers via context.set().
db.findMany() queries multiple rows; db.findOne() queries one row.
where filters results; orderBy sorts them.
ilike() and inList() are query helpers for flexible filtering.

Your bookstore now reads from a real database. In the next part, you will learn how to write data --- handling form submissions to create, update, and delete books.

Continue to Part 5: Forms & Mutations -->

4. Database ​

What is a Database? ​

Choosing SQLite ​

Install SQLite ​

Define the Schema ​

The table() Function ​

Column Types ​

Composite Primary Keys ​

Relationships ​

Type Aliases ​

Create the Database ​

What Are Migrations? ​

The Setup File ​

Creating the Database ​

Running Migrations ​

Seeding Data ​

Create the Database Middleware ​

Wire Up the Database ​

Query Data in Route Handlers ​

db.findMany() --- Querying Multiple Rows ​

db.findMany() with where ​

db.findOne() --- Querying a Single Row ​

Accessing the Database via Context ​

Update the Home Page ​

Try It Out ​

Your Project So Far ​

Other Query Methods ​

Recap ​

4. Database

What is a Database?

Choosing SQLite

Install SQLite

Define the Schema

The `table()` Function

Column Types

Composite Primary Keys

Relationships

Type Aliases

Create the Database

What Are Migrations?

The Setup File

Creating the Database

Running Migrations

Seeding Data

Create the Database Middleware

Wire Up the Database

Query Data in Route Handlers

`db.findMany()` --- Querying Multiple Rows

`db.findMany()` with `where`

`db.findOne()` --- Querying a Single Row

Accessing the Database via Context

Update the Home Page

Try It Out

Your Project So Far

Other Query Methods

Recap