p2p-schema

A set of utilities for managing Prisma database schemas, seeding, and maintenance operations for the Procure-to-Pay system.

Features

• Schema Synchronization: Easily synchronize your Prisma schema across multiple databases
• Database Seeding: Seed one or multiple databases with consistent data
• Table Management: Truncate specific tables with validation and safety features
• Interactive Mode: Select tables interactively for operations
• Dry Run Support: Preview changes before execution
• Comprehensive Logging: Clear, colorful console output with progress indicators
• Error Handling: Structured error handling with detailed feedback

Installation

npm install p2p-schema

Using Prisma Client

This package includes the full Prisma client generated from the schema, making it easy to use in your projects:

import { PrismaClient } from "p2p-schema";

// Create a new Prisma client instance
const prisma = new PrismaClient();

async function main() {
  // Example: Query all users
  const users = await prisma.user.findMany({
    include: {
      user_roles: {
        include: {
          role: true,
        },
      },
    },
  });

  // Example: Create a new user
  const newUser = await prisma.user.create({
    data: {
      email_id: "[email protected]",
      name: "John Doe",
      first_name: "John",
      last_name: "Doe",
      status: "Active",
      user_roles: {
        create: {
          role: {
            connect: { id: 1 }, // Connect to an existing role
          },
        },
      },
    },
  });
}

main()
  .catch((e) => {
    console.error(e);
    process.exit(1);
  })
  .finally(async () => {
    // Close the database connection when done
    await prisma.$disconnect();
  });

CLI Usage

The package provides three main command-line utilities:

Sync Database Schemas

Synchronize Prisma schema across multiple databases:

# Sync all databases
npx prisma-sync

# Sync a specific database
npx prisma-sync --db YOUR_DB_NAME

Seed Databases

Seed one or multiple databases with data:

# Seed all databases
npx prisma-seed

# Seed a specific database
npx prisma-seed --db YOUR_DB_NAME

Truncate Tables

Truncate specific tables in one or multiple databases:

# Truncate specific tables in all databases
npx prisma-truncate --tables table1,table2,table3

# Truncate specific tables in a single database
npx prisma-truncate --db YOUR_DB_NAME --tables table1,table2,table3

# Use interactive mode to select tables
npx prisma-truncate --db YOUR_DB_NAME --interactive

# Truncate all tables except specific ones
npx prisma-truncate --tables all --exclude _prisma_migrations,tenant_configurations

# Dry run (preview without executing)
npx prisma-truncate --db YOUR_DB_NAME --tables users,posts --dry-run

API Usage

You can also use the package programmatically:

import { syncDatabase, seedDatabase, truncateTables } from "p2p-schema";

// Sync schemas
syncDatabase({ db: "YOUR_DB_NAME" });

// Seed database
seedDatabase({ db: "YOUR_DB_NAME" });

// Truncate tables
truncateTables({
  db: "YOUR_DB_NAME",
  tables: ["users", "posts", "comments"],
  exclude: ["migrations"],
  dryRun: false,
  noCascade: false,
  noConfirm: true,
});

Configuration

The package expects a standard Prisma configuration:

1. A .env file in your prisma directory containing database URLs in the format:

   DATABASE_NAME_DATABASE_URL="postgresql://username:password@localhost:5432/database_name"
   DEFAULT_DATABASE_URL="postgresql://username:password@localhost:5432/default_db"

2. A Prisma schema file at prisma/schema.prisma

Schema Information

The Prisma schema included in this package contains models for a complete Procure-to-Pay system, including:

• User management (User, Role, Permission)
• Vendor management (Vendor, VendorCategory, VendorContactPerson)
• Purchase management (PurchaseIntake, PurchaseIntakeItem)
• Product catalog (Item, Category, Attribute)
• Approval workflows (ApprovalHierarchy, ApprovalLevel)
• And many more

Environment Variables

• LOG_LEVEL - Set logging level (debug, info, warn, error, silent)

Requirements

• Node.js >= 16.0.0
• Prisma >= 6.0.0
• PostgreSQL database

License

MIT © Azhar Pathan

Contributing

Contributions, issues, and feature requests are welcome! Feel free to check the issues page.