Adding a Database

❗ This is not a tutorial ❗

In https://zalgorithm.com/hello-remix/ I created a Remix app with npx create-remix@latest. That creates a basic app, but doesn’t configure a database. This app doesn’t need a database but I’m going to set one up for fun. I’ll give these instructions a try: https://remix.run/docs/en/main/tutorials/jokes#database.

SQLite and Prisma

SQLight is the database. Prisma is the ORM. Here it goes:

$ npm install --save-dev prisma
$ npm install @prisma/client

Now initialize (???) Prisma with SQLight:

$ npx prisma init --datasource-provider sqlite

The above command creates a prisma/schema.prisma file:

// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

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

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

I’m unsure about the url property. I see that in the .env file that’s been generated for the app, DATABASE_URL is set to "file:./dev.db". When I push the changes to my production site, do I just create a .env file with something like this?

DATABASE_URL="file:./prod.db"

???

(Edit: yes, it looks like I can call it anything I want: https://www.prisma.io/docs/orm/overview/databases/sqlite.)

I’m getting ahead of myself. The next step is to add a model. I won’t use the Joke model from the tutorial I’m following.

I’ll give this a try:

model Blog {
  id String @id @default(uuid())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  title String 
  description String
  url String
}

$ npx prisma db push

# output:
Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
Datasource "db": SQLite database "dev.db" at "file:./dev.db"

SQLite database dev.db created at file:./dev.db

🚀  Your database is now in sync with your Prisma schema. Done in 18ms

✔ Generated Prisma Client (v5.10.2) to ./node_modules/@prisma/client in 54ms

To avoid possible git issues, I’m going to delete prisma/dev.db, add /prisma/dev.db to my .gitignore file, then run npx prisma db push again. (Done, but probably unnecessary.)

Seed the Database

The Blog model is an idea for my next project. For now, I just want to get Remix working with SQLite in a production environment. I’ll seed the database with something:

touch prisma/seed.ts

// prisma/seed.ts

import { PrismaClient } from "@prisma/client";
const db = new PrismaClient();

async function seed() {
  await Promise.all(
    getBlogs().map((blog) => {
      return db.blog.create({ data: blog });
    })
  );
}

seed();

function getBlogs() {
  return [
    {
      title: "Amateur Web Development",
      description: "Stake your claim on the internet.",
      url: "https://amateur.example.com",
    },
    {
      title: "Other ways of knowing",
      description: "You've got this.",
      url: "https://otherways.example.com",
    },
    {
      title: "Algorithm Shmalgorithm",
      description: "Free yourself from the machine",
      url: "https://zalgorithm.com",
    },
  ];
}

Now I need to run the file. This is where I got stuck when initially following the full Joke tutorial. The instructions are to install ts-node and tsconfig-paths as dev dependencies:

$ npm install --save-dev ts-node tsconfig-paths

Then run the seed.ts file with:

$ npx ts-node --require tsconfig-paths/register prisma/seed.ts

But… that returns the following error:

TypeError: Unknown file extension ".ts" for /home/scossar/remix/starting-over/hello_world/prisma/seed.ts
    at Object.getFileProtocolModuleFormat [as file:] (node:internal/modules/esm/get_format:160:9)
    at defaultGetFormat (node:internal/modules/esm/get_format:203:36)
    at defaultLoad (node:internal/modules/esm/load:141:22)
    at async ModuleLoader.load (node:internal/modules/esm/loader:409:7)
    at async ModuleLoader.moduleProvider (node:internal/modules/esm/loader:291:45)
    at async link (node:internal/modules/esm/module_job:76:21) {
  code: 'ERR_UNKNOWN_FILE_EXTENSION'

I don’t remember how I fixed it, but I posted the solution on the Remix Discord server a few months ago. Maybe I can find that post. https://discord.com/channels/770287896669978684/1178260789665218590. I figured it out from this: https://github.com/prisma/prisma/issues/7053#issuecomment-1563824382.

$ npm i -D tsx

# then add a "prisma" entry to package.json:
"prisma": {
    "seed": "tsx prisma/seed.ts"
  }

# then
$ npx prisma db seed
# returns
Environment variables loaded from .env
Running seed command `tsx prisma/seed.ts` ...

🌱  The seed command has been executed.

There’s probably a better way. Also, do I still need the ts-node and tsconfig-paths dev dependencies? (With comments off on this blog, I’m asking _myself_ questions. Also, can the WordPress editor accept markdown?)

Prisma Studio

Run npx prisma studio to view and edit the database:

Prisma Dev Multiple Connections Issue

The problem is that in development mode, @remix-run/serve creates a new database connection every time the code is saved. To avoid that, use the global singleton workaround: https://remix.run/docs/en/main/guides/manual-mode#keeping-in-memory-server-state-across-rebuilds.

The basic idea makes sense:

// singleton.server.ts
export const singleton = <Value>(
  name: string,
  valueFactory: () => Value
): Value => {
  const g = global as any;
  g.__singletons ??= {};
  g.__singletons[name] ??= valueFactory();
  return g.__singletons[name];
};

<Value> is a generic type parameter in TypeScript. It’s a way of defining a function that can work with any type, while still keeping type safety.

In the singleton function, <Value> is used to denote the type of the value that the valueFactory function returns, and also the type of the value that the singleton function itself returns.

When you call the singleton function, you can specify what type Value should be. For example, if you’re creating a singleton that holds a number, you could call the function like this:

const mySingleton = singleton<number>('mySingleton', () => 42);

In this case, TypeScript will know that mySingleton is a number.

If you don’t specify a type when calling the function, TypeScript will try to infer it from the valueFactory argument. For example:

const mySingleton = singleton('mySingleton', () => 42);

In this case, TypeScript will infer that Value is number, because the valueFactory function returns a number.

By using a generic type parameter, the singleton function can create singletons of any type, while still providing type safety.

GitHub Copilot 🙁

Using the Data

I’ll display the blog list at /blogs:

$ touch app/routes/blogs.tsx

// app/routes/blogs.tsx

export default function Blogs() {
  return (
    <div>
      <h1>Blogs</h1>
    </div>
  );
}

That gives me:

Now display the blogs:

import { json } from "@remix-run/node";
import { useLoaderData } from "@remix-run/react";

import { db } from "~/utils/db.server";

export const loader = async () => {
  return json({
    blogs: await db.blog.findMany(),
  });
};

export default function Blogs() {
  const data = useLoaderData<typeof loader>();
  return (
    <div>
      <h1>Blogs</h1>
      <ul>
        {data.blogs.map((blog) => (
          <li key={blog.id}>
            <a href={blog.url}>{blog.title}</a>
          </li>
        ))}
      </ul>
    </div>
  );
}

Great! Except subdomains of example.com return a “page not found” error. I’ll update those URLs with Prisma Studio. Also, I don’t _think_ I’ve set title, description and url as required fields on the model. Why isn’t typescript giving me an error?

Push the Changes to Production

$ git status
On branch main
nothing to commit, working tree clean

$ git push live main
Enumerating objects: 28, done.
Counting objects: 100% (28/28), done.
Delta compression using up to 8 threads
Compressing objects: 100% (21/21), done.
Writing objects: 100% (22/22), 7.86 KiB | 731.00 KiB/s, done.
Total 22 (delta 9), reused 0 (delta 0), pack-reused 0
To ssh://<redacted>/repo/site.git

$ ssh <redacted path to my repo>
$ cd /var/www/hello_zalgorithm_com
$ touch .env
$ emacs .env

# .env
DATABASE_URL="file:./prod.db"

then:

$ npm install

$ npx prisma db push
$ npx prisma db seed

I _think_ I need to run the build command:

$ npm run build
> build
> remix build

 info  building... (NODE_ENV=production)
 info  built (1.5s)

$ pm2 list
│ id │ name         │ namespace   │ version │ mode    │ pid      │ uptime │ ↺    │ status    │ cpu      │ mem      │ user     │ watching │
├────┼──────────────┼─────────────┼─────────┼─────────┼──────────┼────────┼──────┼───────────┼──────────┼──────────┼──────────┼──────────┤
│ 0  │ npm start    │ default     │ N/A     │ fork    │ 31109    │ 24h    │ 1    │ online    │ 0%       │ 51.8mb   │ scossar  │ disabled

$ pm2 reload 0

Success: https://hello.zalgorithm.com/blogs (these links will be broken soon 🙁 )