Database Schema

The ROQ's generated application uses Prisma (opens in a new tab) as an Object Relational Mapping (ORM).

Prisma supports many databases such as MySQL, SQL Server, CockroachDB, and MongoDB. The default database for local development is PostgreSQL. If you don't want to use PostgreSQL, it's easy to change to another database. Go to the schema database directory located at /prisma, then change the file schema.prisma and edit the provider key:

// schema.prisma
datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
}

Make sure to change the DATABASE_URL in .env file to the appropiate database URL for local development.

Modifying Schema

If you need add a custom field to the database or include additional data input from a form in the application, you must modify the Prisma' data model on Prisma Schema file to ensure that any new data changes are saved into the database.

For example, if you want to add description data to the book table database, you can modify the book data model in schema.prisma file as follows:

// schema.prisma
// book data model
model book {
  id              String        @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  title           String        @db.VarChar(255)
  author          String        @db.VarChar(255)
  // add another description data field with default value 
  description     String        @db.VarChar(255) @default("")
  organization_id String?       @db.Uuid
  user_id         String?       @db.Uuid
  created_at      DateTime      @default(now()) @db.Timestamp(6)
  updated_at      DateTime      @default(now()) @db.Timestamp(6)
  organization    organization? @relation(fields: [organization_id], references: [id], onDelete: NoAction, onUpdate: NoAction)
  user            user?         @relation(fields: [user_id], references: [id], onDelete: NoAction, onUpdate: NoAction)
  bookmark        bookmark[]
  highlight       highlight[]
}

To make the application aware of this change, you need to use the following command:

npx prisma migrate dev --name add-book-description

This command performs the following actions:

• Creates a new SQL migration file for the migration.
• Applies the generated SQL migration to the database.
• Regenerates the Prisma Client to reflect the updated schema.

The result of the command is a new SQL migration file located in the prisma\migrations\xxxx-add-book-description directory. If you examine the SQL migration file closely, you will find the following SQL statement:

-- AlterTable
ALTER TABLE "book" ADD COLUMN  "description" VARCHAR(255) NOT NULL DEFAULT '';

This SQL statement alters the book table by adding a new column called description and has a default value of an empty string.