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 do 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();
}

You can see that I created the PrismaClient instance and then use it to query the products table in the getProducts function. Now you can use this new function inside QwikCity loader. Here is the new loader code:

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 our of it.

When you will run the app it should look exactly like in the previous post, but this time you work 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 😃