How to Use SQLite with Remix: Simple Setup and Example

To use SQLite with Remix, set up a database file and use an ORM like Prisma to connect and query it. Configure Prisma with SQLite, generate the client, and call it inside Remix loaders or actions for data fetching and mutations.

Syntax

Using SQLite with Remix typically involves these parts:

• Database file: A local .sqlite file stores your data.
• Prisma schema: Defines your data models and points to the SQLite file.
• Prisma client: Auto-generated code to query the database.
• Remix loaders/actions: Functions where you call Prisma client to read or write data.

prisma

datasource db {
  provider = "sqlite"
  url      = "file:./dev.db"
}

generator client {
  provider = "prisma-client-js"
}

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  published Boolean  @default(false)
}

Example

This example shows a simple Remix app using Prisma with SQLite to fetch and create posts.

typescript

import { PrismaClient } from '@prisma/client';
import { json, redirect } from '@remix-run/node';
import { useLoaderData, Form } from '@remix-run/react';

const prisma = new PrismaClient();

export async function loader() {
  const posts = await prisma.post.findMany();
  return json({ posts });
}

export async function action({ request }) {
  const formData = await request.formData();
  const title = formData.get('title');
  if (typeof title !== 'string' || title.length === 0) {
    return json({ error: 'Title is required' }, { status: 400 });
  }
  await prisma.post.create({ data: { title } });
  return redirect('/');
}

export default function Posts() {
  const { posts } = useLoaderData();
  return (
    <main>
      <h1>Posts</h1>
      <ul>
        {posts.map(post => (
          <li key={post.id}>{post.title}</li>
        ))}
      </ul>
      <Form method="post">
        <input type="text" name="title" placeholder="New post title" />
        <button type="submit">Add Post</button>
      </Form>
    </main>
  );
}

Output

<main>\n  <h1>Posts</h1>\n  <ul>\n    <li>First post title</li>\n    <li>Another post</li>\n  </ul>\n  <form method="post">\n    <input type="text" name="title" placeholder="New post title" />\n    <button type="submit">Add Post</button>\n  </form>\n</main>

Common Pitfalls

Common mistakes when using SQLite with Remix include:

• Not running prisma generate after changing the schema, causing client errors.
• Using Prisma client outside Remix loaders or actions, which can cause connection issues.
• Forgetting to close the Prisma client in development, leading to too many open connections.
• Not handling form data validation properly in actions.

typescript

/* Wrong: Using Prisma client globally without proper lifecycle handling */
import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();

// This can cause too many connections in dev

/* Right: Use a singleton pattern to reuse Prisma client in dev */
import { PrismaClient } from '@prisma/client';

let prisma;
if (process.env.NODE_ENV === 'production') {
  prisma = new PrismaClient();
} else {
  if (!global.prisma) {
    global.prisma = new PrismaClient();
  }
  prisma = global.prisma;
}

export default prisma;

Quick Reference

• Run npx prisma init to set up Prisma with SQLite.
• Define your data models in schema.prisma.
• Run npx prisma migrate dev --name init to create the database.
• Use Prisma client inside Remix loaders and actions for data operations.
• Close Prisma client properly in production or use a singleton in development.

Key Takeaways

Use Prisma ORM to connect Remix with SQLite easily and safely.

Always run Prisma generate and migrations after schema changes.

Call Prisma client only inside Remix loaders or actions.

Use a singleton Prisma client in development to avoid connection issues.

Validate form data in Remix actions before database writes.