@squaredmade/db v1.2.0

@squaredmade/db

A database package that integrates with PostgreSQL using Prisma for schema management and database interactions. This package is part of a monorepo and is responsible for managing database models, migrations, and interactions with the PostgreSQL database.

Features

• Database management using Prisma.
• PostgreSQL as the database provider.
• Enum types for statuses, priorities, activities, and notifications.
• Predefined models such as Task, Commit, Branch, User, Workspace, and more.
• Easy migration and database schema generation with simple commands.
• Supports relation management with cascades and unique constraints.

Installation

The package is private and intended for use within the monorepo. To install it in the workspace, ensure it is linked in your project using pnpm:

pnpm install

Usage

You can use the package to interact with your PostgreSQL database through Prisma. All database models are defined using Prisma schema, and you can use generated Prisma client to query and manipulate data.

Importing Prisma Client

To start using the Prisma client generated by this package, import it like so:

import { PrismaClient } from "@squaredmade/db";

const prisma = new PrismaClient();

async function getTasks() {
  const tasks = await prisma.task.findMany();
  console.log(tasks);
}

getTasks();

Database Commands

The package comes with several predefined commands in package.json for managing database migrations, schema generation, and more:

1. Generate Prisma Client:

   • This command generates the Prisma client for your project.

   pnpm run db:gen

   It is also automatically triggered after installation via the prepare hook.

2. Deploy Migrations:

   • Apply database migrations to the PostgreSQL database.

   pnpm run db:deploy

3. Push Database Schema:

   • Push your current Prisma schema to the database without generating a migration.

   pnpm run db:push

4. Launch Prisma Studio:

   • Use Prisma Studio, a visual interface to explore and manage your database data.

   pnpm run studio

Schema

The Prisma schema defines models and enums used throughout the application. Below are the key models and enums available in the database package.

Enums

Status

Represents the different stages a task can be in.

• backlog
• todo
• inProgress
• inReview
• done
• canceled
• archived

Priority

Represents the priority of a task.

• noPriority
• urgent
• high
• medium
• low

ActivityType

Represents types of activities that can occur in the system.

• TASK_EVENT
• COMMIT

NotificationType

Represents different types of notifications.

• ASSIGNED
• PARTICIPATING
• MENTIONED
• CREATED

SavedFilterType

Represents the type of saved filters available for users.

• TEAM
• WORKSPACE

SprintStatus

Represents the status of a sprint in a project.

• PLANNED
• ACTIVE
• COMPLETED

Models

Task

The Task model represents a task in the system and includes fields for title, description, status, priority, assignee information, and more.

model Task {
  id             String    @id @default(uuid())
  title          String
  description    String?
  status         Status     @default(backlog)
  priority       Priority   @default(noPriority)
  authorId       String
  assigneeId     String?
  workspaceId    String
  dateCreated    DateTime   @default(now())
  dueDate        DateTime?
  labels         String[]
  Comments       Comment[]
  updatedAt      DateTime   @updatedAt
  deleted        Boolean    @default(false)
}

Commit

Represents a commit in a version control system, such as Git. Each commit is associated with a Branch and has metadata such as the commit message, author information, and changes made.

model Commit {
  id            String    @id @default(uuid())
  message       String
  authorName    String?
  timestamp     String
  added         String[]
  modified      String[]
  activityId    String    @unique
  activity      Activity  @relation(fields: [activityId], references: [id], onDelete: Cascade, onUpdate: Cascade)
}

Branch

Represents a branch in a version control system. A branch can have multiple commits associated with it.

model Branch {
  id           String   @id @default(uuid())
  name         String
  commits      Commit[]
  taskId       String
  task         Task     @relation(fields: [taskId], references: [id], onDelete: Cascade, onUpdate: Cascade)
}

Notification

The Notification model represents a notification sent to a user about task or workspace-related events.

model Notification {
  id          String    @id @default(uuid())
  description String?
  taskId      String
  userId      String
  read        Boolean   @default(false)
  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt
}

Datasource Configuration

The Prisma client is configured to use PostgreSQL as the datasource. The POSTGRES_PRISMA_URL environment variable is used to define the connection string.

datasource db {
  provider = "postgresql"
  url      = env("POSTGRES_PRISMA_URL")
}

Generator

The Prisma client is generated using the prisma-client-js generator.

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

Environment Variables

The following environment variables are required for the package:

• POSTGRES_PRISMA_URL: The connection string for the PostgreSQL database.

Make sure this variable is set in your environment configuration files (e.g., .env).