Remix, SQLite, and Prisma, Oh my!

Previously published at fullstackfish.com

Let's create a Remix app and connect it to SQLite with Prisma.

Open up a terminal and create a new Remix app.

npx create-remix@latest

You'll go through a short interview of how you want to set up your app. Pick these options to follow along or modify if you're in the mood for a little adventure.

Call the app whatever you like. I'm calling mine "run-rsp".

Pick the option to deploy to the Remix App Server for now. This is easy to change later.

Pick JavaScript.

Decline the offer to run npm install as we will be using Yarn today.

Once the setup process is complete, cd into the project folder.

cd run-rsp

Open the project in VS Code, or whatever editor you want to use.

code .

Install the dependencies.

yarn

Verify that everything his set up correctly.

yarn dev

You should now see the template Remix app running in your browser.

Now let's replace the default code with our own. We're going to be working primarily in the app folder.

Let's open up the root.jsx file and examine its contents.

import {
  Links,
  LiveReload,
  Meta,
  Outlet,
  Scripts,
  ScrollRestoration
} from "remix";

export function meta() {
  return { title: "New Remix App" };
}

export default function App() {
  return (
    <html lang="en">
      <head>
        <meta charSet="utf-8" />
        <meta name="viewport" content="width=device-width,initial-scale=1" />
        <Meta />
        <Links />
      </head>
      <body>
        <Outlet />
        <ScrollRestoration />
        <Scripts />
        {process.env.NODE_ENV === "development" && <LiveReload />}
      </body>
    </html>
  );
}

The content we are seeing on the home page is coming from the <Outlet /> component. We'll edit that in a second.

Let's make a small change so we can see LiveReload in action.

Change the title of the app in the meta() function to whatever you want. Save the file and notice that the title of your app in the browser changes almost instantly. (Check the browser tab of the app or right-click and view page source)

The home page content is routes\index.jsx. Open the file up and delete everything. Save the file. You'll now see an error in the browser.

Add the following function to fix the error.

export default function Index() {
  return (
    <div>
      <h1>I'll soon be connected to SQLite</h1>
    </div>
  );
}

You may need to refresh your browser to get LiveReload working again. Once you've done that, you'll see your heading rendered with the browser's default styling. Let's add a CSS reset and some global styles to our app so that it looks a little better.

In our root.jsx file, add a links() function. We are already importing the Links component and using it in the head of our document, so whatever we export from our links() function will be used in the Links component.

// root.jsx
export function links() {
  return [
    {
      rel: "stylesheet",
      href: "https://unpkg.com/[email protected]/dist/reset.min.css"
    }
  ]; 
}

Now add a styles folder in app and add a file called global.css with a rule or two.

Save the file. Whatever styles you've added won't be applied yet — we haven't linked the stylesheet to the app.

Go back to root.jsx. Import your styles and then add another link to the array in links().

import globalStyles from '~/styles/global.css'
...
// root.jsx
export function links() {
  return [
    {
      rel: "stylesheet",
      href: "https://unpkg.com/[email protected]/dist/reset.min.css"
    },
    {
      rel: "stylesheet",
      href: globalStyles
    }
  ]; 
}

Save your changes and you should now see your styles applied in the browser.

Prisma and SQLite setup

Stop the app and let's add some dependencies.

yarn add @prisma/client
yarn add -D prisma

After they finish installing, initialize prisma with the sqlite provider.

npx prisma init --datasource-provider sqlite

This will add a prisma folder with a schema.prisma file. It also adds a .env file if you don't already have one.

By including the datasource provider, we don't need to edit the schema.prisma file to change the default provider to sqlite, and the .env file is populated for us with a connection string to a sqlite file. Nice touch, Prisma.

Add a model to your schema.prisma file for Soccer Teams. Install the Prisma extension if you haven't already to format your Prisma files. Prettier may try to override Prisma's formatting, so if that's the case, right-click the file, select format document, and indicate that Prisma should format the file. Now you don't need to worry about getting your models formatted. We are going to keep things simple here just to show how everything is wired up together. Our database will be a list of soccer teams, or whatever kind of teams you like.

Add the Team model to your schema.prisma file.

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

datasource db {
  provider = "sqlite"
  url      = env("DATABASE_URL")
}

model Team {
  id        String   @id @default(uuid())
  team      String
  country   String
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

Deploy the model to your database

npx prisma db push

We should now have a dev.db file in the prisma folder next to our schema.prisma file.

Launch prisma studio to see the results.

npx prisma studio

We can add a few teams directly in Prisma Studio. Click the Add record button to do so.

Add a file (app/lib/db.server.js) to configure our connections to the database. Note that the "server" in the file tells Remix to run this on the server.

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;

Now let's get the teams from the database and display them in our app. We'll use a loader function (this code runs on the server), which will feed our component with the useLoaderData() hook.

// routes/index.js
import { useLoaderData, Link } from 'remix'
import  prisma from '~/lib/db.server'

export const loader = async () => {
  const data = {
    teams: await prisma.team.findMany(),
  }
  return data
}

export default function Index() {
  const { teams } = useLoaderData()

  return (
    <>
      <div>
        <h1>Soccer Teams around the world</h1>
      </div>
      <ul>
        {teams.map((team) => (
          <li key={team.id}>
            <h1>{team.team}</h1>
            <p>{team.country}</p>
          </li>
        ))}
      </ul>
    </>
  )
}

We are using the Prisma findMany() function to get all the teams in our database.

That's it for now. The app we've built isn't particularly useful as is, but you now have the building blocks to build anything you want and style it however you like.

It's been said many times but remains true: there has never been a better time to be a developer. There are so many wonderful tools to choose from. The biggest challenge can be deciding what to include in your toolbelt. Remix and Prisma should be on your short list. The developer experience with both is top notch.

Enjoy, and Happy New Year!