Project: Local Excel User Sync Script

In this project, we will write a Node.js data synchronization script. The script reads a local Excel user list file using the xlsx parsing package, validates the input fields, and synchronization upserts the records into a PostgreSQL database using Prisma Client queries.

1. Project Dependencies Setup

First, install the Excel parsing package:

# Install xlsx package
npm install xlsx

Make sure your schema.prisma file contains the User model:

model User {
  id    Int    @id @default(autoincrement())
  email String @unique
  name  String
}

2. Implementing the Sync Script

Create a script file named sync.ts inside scripts/:

// scripts/sync.ts
import * as fs from "fs";
import * as path from "path";
import * as xlsx from "xlsx";
import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient();

interface ExcelRow {
  Email: string;
  Name: string;
}

async function synchronizeExcelToDatabase() {
  const filePath = path.join(__dirname, "../data/users.xlsx");

  if (!fs.existsSync(filePath)) {
    console.error("Excel data file not found at:", filePath);
    process.exit(1);
  }

  console.log("Reading Excel file...");
  const workbook = xlsx.readFile(filePath);
  const sheetName = workbook.SheetNames[0];
  const worksheet = workbook.Sheets[sheetName];

  // Parse worksheet data rows
  const rows = xlsx.utils.sheet_to_json<ExcelRow>(worksheet);
  console.log(`Found ${rows.length} records in Excel sheet.`);

  let successCount = 0;
  let errorCount = 0;

  for (const row of rows) {
    const email = row.Email?.trim();
    const name = row.Name?.trim();

    // Field validation checks
    if (!email || !name) {
      console.warn("Skipping invalid record row:", row);
      errorCount++;
      continue;
    }

    try {
      // Upsert record atomically into PostgreSQL
      await prisma.user.upsert({
        where: { email },
        update: { name },
        create: { email, name },
      });
      successCount++;
    } catch (error) {
      console.error(`Failed to sync email: ${email}`, error);
      errorCount++;
    }
  }

  console.log("---------------------------------------");
  console.log(`Sync process finished. Successfully processed: ${successCount}`);
  console.log(`Failed or skipped records: ${errorCount}`);
}

synchronizeExcelToDatabase()
  .then(async () => {
    await prisma.$disconnect();
  })
  .catch(async (e) => {
    console.error(e);
    await prisma.$disconnect();
    process.exit(1);
  });

3. Running the Sync Script

To execute the script in a TypeScript environment:

# Run the sync script using ts-node compiler
npx ts-node scripts/sync.ts

The script iterates through the parsed spreadsheet rows, executing an upsert query for each record. This updates the user name if the email address matches an existing database entry, or inserts a new row if the email is not found.