We’ve seen how to display data in a Remix application using a loader.

This loader in app/routes/blog.jsx that got the blog posts data was very simple:

import { json } from '@remix-run/node'

//...

export const loader = async () => {
  const posts = [
    { id: 1, title: 'First' },
    { id: 2, title: 'Second' },
    { id: 3, title: 'Third' },
    { id: 4, title: 'Fourth' },
  ]

  return json({ posts })
}

It’s interesting to note that the loader only runs on the server.

Now I want to show you how to connect to a database.

We’ll do it with SQLite.

Install

npm install sqlite sqlite3

Note: if you use Bun, this does not work with Bun, which has its own SQLite implementation. Check out https://bun.sh/docs/api/sqlite, things will be a bit different.

sqlite3 is the official Node.js SQLite library, but it does not support promises (yet).

sqlite is a wrapper for it that lets us use promises (and async/await).

So we need both.

Create an app/data folder, that will host our database.

I want to add some initial “seed” content to the database.

To do so, we create a file seed.js in the project root folder, with this content:

import { open } from 'sqlite'
import sqlite3 from 'sqlite3'

const db = await open({
  filename: './app/data/db.sqlite',
  driver: sqlite3.Database,
})

await db.exec('CREATE TABLE IF NOT EXISTS posts (id INTEGER PRIMARY KEY, title TEXT)')

const stmt = await db.prepare('INSERT INTO posts (id, title) VALUES (?, ?)')

stmt.run(1, 'First')
stmt.run(2, 'Second')
stmt.run(3, 'Third')
stmt.run(4, 'Fourth')
stmt.run(5, 'Fifth')

await stmt.finalize()
await db.close()

We connect to the database (this creates the file if not created yet), we create the database if it does not exist, and we insert some sample posts.

Run node seed.js to run it, you’ll see an app/data/db.sqlite file, and if you use an app like TablePlus you can see the data has been added to the database:

Now let’s go to app/routes/blog.jsx.

We’re going to change the loader to use the database instead of the hardcoded array of posts.

First create a file app/database.server.js.

The .server. part tells Remix to not load that file in the client. If you remove that part from the file name, you’ll see some errors related to trying to include some Node library in the client side, which is not possible.

In that file we export a getDb function whose job is to connect to the database:

import { open } from 'sqlite'
import sqlite3 from 'sqlite3'

export const getDb = async () => {
  console.log(`attempting to open DB`)

  const db = await open({
    filename: `./app/data/db.sqlite`,
    driver: sqlite3.Database,
  })
  return db
}

In app/routes/blog.jsx we import this function:

import { getDb } from '../database.server.js'

and we change the loader function to retrieve the blog posts:

export const loader = async () => {
  const db = await getDb()
  const posts = await db.all('SELECT id, title FROM posts')
  await db.close()
  return json({ posts })
}

Things should be working as before now, except if you go on the “Fifth” blog post, and you get “No post found” on the main part of the screen.

Remember, before we had just 4 blog posts, and we still have hardcoded post data in the app/routes/blog.$id.jsx file.

So open that and import the getDB function

import { getDb } from '../database.server.js'

and we change the loader function to

export const loader = async ({ params }) => {
  const db = await getDb()
  const post = await db.get(
    'SELECT id, title FROM posts WHERE id = ?',
    params.id
  )
  await db.close()
  return json({ post })
}

That’s it!

Lessons in this unit: