ImportCSV - AI-native CSV importer for developers

Import CSV to Prisma Database: Step-by-Step Guide

Importing CSV data into a database is a common requirement for seeding development environments, migrating data, or processing user uploads. Prisma ORM provides several methods for bulk data insertion, but combining CSV parsing with Prisma requires understanding a few key patterns and limitations.

This guide walks through the complete process of parsing CSV files and inserting data into your Prisma database using TypeScript. You will learn how to handle large files efficiently, manage transactions for related data, and avoid common pitfalls.

Prerequisites

Node.js 20+ (Node.js 18 reached EOL in April 2025)
A Prisma project with database configured (PostgreSQL, MySQL, SQLite, SQL Server, or CockroachDB)
TypeScript for type safety
Basic understanding of Prisma schema and client

Architecture Overview

The CSV import process follows these stages:

Parse CSV file - Read and parse CSV content using PapaParser
Validate data - Check required fields and data types
Transform data - Map CSV columns to Prisma schema fields
Insert in batches - Use createMany with batch processing for large datasets
Handle errors - Catch and report failures without losing progress

Step 1: Set Up Your Prisma Schema

First, define the models that will receive the imported data. Here is an example schema for users and posts:

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?
  createdAt DateTime @default(now())
  posts     Post[]
}

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int
}

Run npx prisma generate after updating your schema to regenerate the Prisma Client with the latest types.

Step 2: Install Dependencies

Install PapaParser for CSV parsing along with its TypeScript types:

npm install papaparse
npm install -D @types/papaparse

Package versions used in this guide:

prisma: 7.2.0
@prisma/client: 7.2.0
papaparse: 5.5.3

Step 3: Parse the CSV File

Create a utility function to read and parse your CSV file:

import fs from 'fs'
import Papa from 'papaparse'

interface UserCSV {
  email: string
  name?: string
}

function parseCSV<T>(filePath: string): { data: T[]; errors: Papa.ParseError[] } {
  const csvContent = fs.readFileSync(filePath, 'utf-8')
  const { data, errors } = Papa.parse<T>(csvContent, {
    header: true,
    skipEmptyLines: true,
    transformHeader: (header) => header.trim(),
  })

  if (errors.length > 0) {
    console.error('Parse errors:', errors)
  }

  return { data, errors }
}

const { data, errors } = parseCSV<UserCSV>('users.csv')

Your CSV file should have headers matching the field names:

email,name
alice@example.com,Alice
bob@example.com,Bob
carol@example.com,Carol

Step 4: Validate Data Before Insert

Always validate parsed data before attempting database insertion:

interface UserCSV {
  email: string
  name?: string
}

function validateRow(row: UserCSV): boolean {
  if (!row.email || !row.email.includes('@')) {
    return false
  }
  return true
}

const validData = data.filter(validateRow)
const invalidCount = data.length - validData.length

if (invalidCount > 0) {
  console.warn(`Skipped ${invalidCount} invalid rows`)
}

Step 5: Insert Data with createMany

Prisma's createMany method efficiently inserts multiple records using a single INSERT INTO statement with multiple values:

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

const result = await prisma.user.createMany({
  data: validData,
  skipDuplicates: true,
})

console.log(`Created ${result.count} users`)

Important limitation: The createMany method returns only a count, not the created records:

console.log(result) // { count: 2 }

As stated in the Prisma documentation: "The createMany operation does not return the newly created records (and their IDs), it returns an object with a count property indicating the number of records instead."

Getting Created Records Back

If you need the created records (for example, to create related child records), you have three options:

Option 1: Use createManyAndReturn (PostgreSQL, CockroachDB, SQLite only, v5.14.0+)

const users = await prisma.user.createManyAndReturn({
  data: [
    { name: 'Alice', email: 'alice@prisma.io' },
    { name: 'Bob', email: 'bob@prisma.io' },
  ],
})
// Returns: [{ id: 1, name: 'Alice', ... }, { id: 2, name: 'Bob', ... }]

Option 2: Use raw SQL with RETURNING (PostgreSQL)

const createdRecords = await prisma.$queryRaw`
  INSERT INTO "User" ("name", "email")
  VALUES ('Alice', 'alice@prisma.io'), ('Bob', 'bob@prisma.io')
  RETURNING id, name, email;
`

Option 3: Pre-compute UUIDs

import { v4 as uuidv4 } from 'uuid'

const dataWithIds = data.map(item => ({
  id: uuidv4(),
  ...item
}))

await prisma.user.createMany({ data: dataWithIds })
// You now know all the IDs

Step 6: Handle Large Files with Batch Processing

Loading thousands of rows at once can cause memory issues. Split large datasets into batches:

const BATCH_SIZE = 500

async function importCSV(data: UserCSV[]) {
  const totalBatches = Math.ceil(data.length / BATCH_SIZE)

  for (let i = 0; i < data.length; i += BATCH_SIZE) {
    const batch = data.slice(i, i + BATCH_SIZE)
    const currentBatch = Math.floor(i / BATCH_SIZE) + 1

    await prisma.user.createMany({
      data: batch,
      skipDuplicates: true,
    })

    console.log(`Batch ${currentBatch}/${totalBatches} completed`)
  }
}

For very large files (10K+ rows), consider streaming the CSV instead of loading it all at once:

import fs from 'fs'
import csv from 'csv-parser'

const results: UserCSV[] = []

fs.createReadStream('large-file.csv')
  .pipe(csv())
  .on('data', (data) => results.push(data))
  .on('end', async () => {
    await importCSV(results)
  })

When importing data with foreign key relationships, use transactions to ensure atomicity:

await prisma.$transaction(async (tx) => {
  // Insert parent records first
  await tx.user.createMany({ data: users })

  // Then insert child records with foreign keys
  await tx.post.createMany({ data: posts })
}, {
  timeout: 30000, // 30 seconds for large imports
})

Transaction options (available since v4.4.0):

maxWait: Maximum wait time to acquire transaction (default: 2 seconds)
timeout: Maximum time for interactive transaction (default: 5 seconds)
isolationLevel: Set transaction isolation level

You can also use sequential transactions for independent operations:

const [users, posts] = await prisma.$transaction([
  prisma.user.createMany({ data: userData }),
  prisma.post.createMany({ data: postData }),
])

Nested Relations Limitation

The createMany method does not support nested connect syntax. Set foreign keys directly:

// Instead of using connect:
await prisma.post.createMany({
  data: posts.map(post => ({
    title: post.title,
    authorId: post.authorId, // Direct foreign key instead of connect
  }))
})

Complete Working Example

Here is a complete seed script you can adapt for your project:

// prisma/seed.ts
import fs from 'fs'
import Papa from 'papaparse'
import { PrismaClient, Prisma } from '@prisma/client'

const prisma = new PrismaClient()

interface UserCSV {
  email: string
  name?: string
}

const BATCH_SIZE = 500

function parseCSV<T>(filePath: string): T[] {
  const csvContent = fs.readFileSync(filePath, 'utf-8')
  const { data, errors } = Papa.parse<T>(csvContent, {
    header: true,
    skipEmptyLines: true,
    transformHeader: (header) => header.trim(),
  })

  if (errors.length > 0) {
    console.error('Parse errors:', errors)
  }

  return data
}

function validateUser(row: UserCSV): boolean {
  return Boolean(row.email && row.email.includes('@'))
}

async function safeCreateMany(data: UserCSV[]) {
  try {
    const result = await prisma.user.createMany({
      data,
      skipDuplicates: true,
    })
    return { success: true, count: result.count }
  } catch (error) {
    if (error instanceof Prisma.PrismaClientKnownRequestError) {
      if (error.code === 'P2002') {
        return { success: false, error: 'Unique constraint violation' }
      }
    }
    throw error
  }
}

async function main() {
  console.log('Starting CSV import...')

  const rawData = parseCSV<UserCSV>('prisma/data/users.csv')
  const validData = rawData.filter(validateUser)

  console.log(`Parsed ${rawData.length} rows, ${validData.length} valid`)

  const totalBatches = Math.ceil(validData.length / BATCH_SIZE)
  let totalCreated = 0

  for (let i = 0; i < validData.length; i += BATCH_SIZE) {
    const batch = validData.slice(i, i + BATCH_SIZE)
    const currentBatch = Math.floor(i / BATCH_SIZE) + 1

    const result = await safeCreateMany(batch)

    if (result.success) {
      totalCreated += result.count
      console.log(`Batch ${currentBatch}/${totalBatches}: ${result.count} created`)
    } else {
      console.error(`Batch ${currentBatch} failed: ${result.error}`)
    }
  }

  console.log(`Import complete: ${totalCreated} users created`)
}

main()
  .catch((e) => {
    console.error('Import failed:', e)
    process.exit(1)
  })
  .finally(async () => {
    await prisma.$disconnect()
  })

Configure the seed command in prisma.config.ts:

export default {
  seed: 'npx tsx prisma/seed.ts'
}

Run the seed script:

npx prisma db seed

Note: In Prisma ORM v7, seeding is only triggered explicitly by running npx prisma db seed. Automatic seeding during prisma migrate dev or prisma migrate reset has been removed.

Troubleshooting

Unique constraint violation (P2002)

This error occurs when inserting a record with a duplicate value in a unique field like email.

Solution: Use skipDuplicates: true to skip rows that would violate unique constraints:

await prisma.user.createMany({
  data: users,
  skipDuplicates: true,
})

Note: skipDuplicates is not supported on MongoDB, SQL Server, or SQLite.

Foreign key constraint failures

Foreign key errors happen when inserting child records before their parent records exist.

Solution: Import parent records first, or use transactions:

await prisma.$transaction(async (tx) => {
  await tx.category.createMany({ data: categories })
  await tx.product.createMany({ data: products })
})

Too many database connections

Using individual create calls in a loop exhausts the connection pool.

Solution: Use createMany for bulk operations:

// Avoid this pattern:
for (const item of items) {
  await prisma.user.create({ data: item })
}

// Use this instead:
await prisma.user.createMany({ data: items })

Memory issues with large files

Loading large CSV files entirely into memory causes Node.js to run out of memory.

Solution: Stream the CSV file and process in batches:

import fs from 'fs'
import csv from 'csv-parser'

const BATCH_SIZE = 1000
let batch: UserCSV[] = []

fs.createReadStream('large-file.csv')
  .pipe(csv())
  .on('data', async (row) => {
    batch.push(row)
    if (batch.length >= BATCH_SIZE) {
      await prisma.user.createMany({ data: batch })
      batch = []
    }
  })
  .on('end', async () => {
    if (batch.length > 0) {
      await prisma.user.createMany({ data: batch })
    }
  })

Next Steps

Add data transformation to clean and normalize CSV values before import
Implement rollback logic for failed batches using interactive transactions
Create a web interface for uploading and mapping CSV columns to your schema
Add progress reporting for long-running imports

Building CSV import from scratch gives you full control over the process and helps you understand Prisma's bulk operations. For production applications where users upload CSV files, you will also need to build UI components for file upload, column mapping, data preview, and error reporting.