Using a QwikCity Loader to Load Database Data

Yesterday I wrote a post called “Using QwikCity Loaders to Load Data” that shorty explains how to load data using the new QwikCity loader’s API. When I wrote the post I used an in-memory array and just returned it as the example data. One of the questions I got was how to do the same, but this time by working against a database.

In this post I’m going to show you exactly that 😉

Enter Prisma

In this post I’m going to use Prisma, which is a popular Node.js Object Relation Mapper (ORM), and work against a MySQL products database.

The Products Database Table

This post isn’t going to explain the concepts of ORM but to summarize it in one line — ORMs bridge the gap between in-memory app objects and relational databases.

Let’s drill down and see how to use Prisma.

Setting up Prisma Inside a QwikCity App

When you want to start using Prisma, you will have to add it to the QwikCity app. First thing, install Prisma CLI using your command line:

pnpm install prisma --save-dev

Then, run the Prisma initialization command:

npx prisma init

This command is going to do 2 things:

1. Create a folder named prisma with a schema.prisma file. You are going to use this file to configure the schema and database connection.
2. If a .env file doesn’t exists, it will create it. You use this file to add environment variables to your project.

Go to the generated .env file and replace the DATABASE_URL variable with your database connection. Now you are ready to run Prisma introspection script to investigate and create a schema out of your existing database. Use the following command to do exactly that:

npx prisma db pull

If you have the same table like I do, the schema.prisma file will be updated and might look like:

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

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

model products {
  id          Int  @id
  name        String? @db.VarChar(80)
  description String? @db.VarChar(300)
}

Last thing to do is to install the @prisma/client package, which will help you run queries against the database. Use the following command to install it:

pnpm install @prisma/client

Once the client is installed run the following command to generate all the client code from the previous schema.prisma file:

npx prisma generate

Now we can move on and update the loader I used in my previous post.

Use a QwikCity Loader with Prisma

Prisma is set up in the app and now you can use Prisma client to retrieve data from the database or change your data. How can you do that? First create a new service file to hold all the Prisma code:

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient();

export async function getProducts() {
    return await prisma.products.findMany();
}

export async function getProductById(id: number) {
    return await prisma.products.findUnique({
        where: {
            id,
        },
    });
}

You can see that I created the PrismaClient instance and then used it in both of the query functions I added. The getProducts function returns the entire products table and the getProductById function returns a single product by its id. Now you can use these new functions inside QwikCity loaders. Here is the new loader code in the main app page:

export const useProductsLoader = loader$(async () => {
  return await getProducts();
});

Pay attention to the async/await usage here. Working with databases is being done asynchronously and we need to await to the query to return the data and to Prisma to create the objects out of it.

I also added a new route inside the QwikCity routes folders for a product details page (src/routes/product/[id]). This is the implementation of the product details index.tsx:

import { component$ } from "@builder.io/qwik";
import { loader$ } from "@builder.io/qwik-city";
import { getProductById } from "~/services/dbClient";

export const useProductsLoader = loader$(async ({ params}) => {
    return await getProductById(parseInt(params.id));
});

export default component$(() => {
    const product = useProductsLoader();
   return (
       <div>
           <div>{product.value?.name}</div>
           <div>{product.value?.description}</div>
       </div>
   );
});

You can see here that you can extract the id from the loader request params object and then use it to query the database.

Here is the full code for the main app index.tsx file:

import { component$ } from '@builder.io/qwik';
import type { DocumentHead } from '@builder.io/qwik-city';
import { loader$ } from '@builder.io/qwik-city';
import { getProducts } from "~/services/dbClient";


export const useProductsLoader = loader$(async () => {
  return await getProducts();
});

export default component$(() => {
  const products = useProductsLoader();

  return (
    <div>
      <ul>
        {products.value.map((product) => {
          return (
            <li>
              <a href={`/product/${product.id}`} >go to product details page</a>
              <div>{product.name}</div>
              <div>{product.description}</div>
            </li>
          );
        })}
      </ul>
    </div>
  );
});

export const head: DocumentHead = {
  title: 'Welcome to Qwik',
  meta: [
    {
      name: 'description',
      content: 'Qwik site description',
    },
  ],
};

When you will run the app it should look very similar to the previous post app, but this time it works with a real database underneath.

The Working App

Summary

QwikCity, like any other meta framework, can work with databases. In this post I showed you how to wire Prisma into QwikCity and then use the new QwikCity data loader API to load data from database to your components.

I hope you will find this post helpful 😃