Database

The template uses Prisma (opens in a new tab) to store data, and by default, is set to use PostgreSQL. The database is defined as a Prisma schema in prisma/schema.prisma.

Configuration

The process is straightforward. Follow these steps:

1. Create a .env file from the .env.example file.
2. In the .env file, add the value for the DB_URL variable. This should be the URL of your PostgreSQL database.

For example, if your PostgreSQL database is hosted at postgres://username:password@host:5432/database_name, you would add the following line to your .env file:

DB_URL=postgres://user:password@host:port/database_name

Note: If PostgreSQL doesn't work for you, you can use a different database provider. To do this, follow these steps:

• Open the prisma/schema.prisma file.
• Locate the provider field and change it from "postgresql" to "mysql".

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

More datasources can be found in here (opens in a new tab).

Create table & migration

To create a new table in your database, you'll need to define a new model in your Prisma schema and then generate a migration.

1. Open your prisma/schema.prisma file and define a new model, for example, a Product model:

model Product {
  id               Int      @id @default(autoincrement())
  title            String
  shop             String
}

This model defines the structure of the QRCode table, including the fields and their data types.

2. To create the migration, run the following command in your terminal:

npm run prisma migrate dev --name product

This command will generate a new migration file in the prisma/migrations directory and apply the changes to your database.

3. To confirm that your migration worked, open Prisma Studio:

npm run prisma studio

Prisma Studio opens in your browser, in Prisma Studio, click the Product tab to view the table, You should see a table with the columns that you created, and no data.

Handle CRUD Operations

To perform CRUD (Create, Read, Update, Delete) operations on your data, you can create a model file in your app/models directory. For example, you can create a product.model.js file with the following content:

import prisma from "../config/db.js";
 
export const ProductModel = {
  async create({ shop, title }) {
    return await this.model.create({
      data: {
        shop,
        title,
      },
    });
  },
  async find(id) {
    return await this.model.findUnique({
      where: {
        id: +id,
      },
    });
  },
  async update(id, title) {
    return await this.model.update({
      where: {
        id: +id,
      },
      title: title,
    });
  },
  async list(shop, conditions = {}) {
    return await this.model.findMany({
      where: {
        shop: shop,
        ...conditions,
      },
    });
  },
  async delete(id) {
    return await this.model.delete({
      where: {
        id: +id,
      },
    });
  },
  async deleteMany(shop) {
    return await this.model.deleteMany({
      where: {
        shop: shop,
      },
    });
  },
  model: prisma.product,
};

In this example, the ProductModel provides methods for performing CRUD operations on the product model. Each method uses the Prisma client to interact with the database.

You can use this model in your application to perform various operations on the product data, such as creating new products, retrieving existing products, updating product information, and deleting products.