Using Prisma Client - BD Scan Face

Prisma Client is a type-safe database client auto-generated from your Prisma schema. This guide covers how to use it with the BD Scan Face database.

Generating the Client

The Prisma Client is configured to be generated in a custom location:

schema.prisma

generator client {
  provider = "prisma-client"
  output   = "../generated/prisma"
}

Generate Command

Run this command whenever you update the schema:

npx prisma generate

The generated client will be located in generated/prisma relative to your schema file.

Initializing the Client

Create a Prisma Client instance in your application:

import { PrismaClient } from '../generated/prisma';
import { Pool } from 'pg';
import { PrismaPg } from '@prisma/adapter-pg';

const connectionString = process.env.DATABASE_URL;

const pool = new Pool({ connectionString });
const adapter = new PrismaPg(pool);

const prisma = new PrismaClient({ adapter });

export default prisma;

Using the PostgreSQL adapter provides better connection pooling and performance for PostgreSQL databases.

CRUD Operations

User Operations

Create a User

const newUser = await prisma.user.create({
  data: {
    ci: "1234567",
    first_name: "Juan",
    last_name: "Perez",
    email: "[email protected]",
    phone: "+1234567890",
    user_type_id: 1,
    code: 1001,
    status: true
  }
});

console.log("Created user:", newUser);

The registration_date field is automatically set to the current timestamp via @default(now()).

Find Users

const users = await prisma.user.findMany();

Update a User

const updatedUser = await prisma.user.update({
  where: { user_id: 1 },
  data: {
    phone: "+9876543210",
    email: "[email protected]"
  }
});

Delete a User

Deleting a user will fail if they have related faces or access logs due to foreign key constraints.

// Soft delete (recommended)
const deactivatedUser = await prisma.user.update({
  where: { user_id: 1 },
  data: { status: false }
});

// Hard delete (use with caution)
const deletedUser = await prisma.user.delete({
  where: { user_id: 1 }
});

UserType Operations

Create User Types

const userTypes = await prisma.userType.createMany({
  data: [
    { type_name: "Employee" },
    { type_name: "Visitor" },
    { type_name: "Contractor" },
    { type_name: "Administrator" }
  ]
});

Find User Types

const allTypes = await prisma.userType.findMany();

const employeeType = await prisma.userType.findUnique({
  where: { type_name: "Employee" }
});

Face Operations

Store Face Encoding

const face = await prisma.face.create({
  data: {
    user_id: 1,
    encoding: JSON.stringify([0.123, 0.456, 0.789, /* ...128 values */]),
    image_path: "/uploads/faces/user_1_face_1.jpg"
  }
});

Face encodings are typically 128-dimensional vectors. Store them as JSON strings or binary format depending on your needs.

Get User’s Faces

const userFaces = await prisma.face.findMany({
  where: { user_id: 1 },
  orderBy: { upload_date: 'desc' }
});

Delete a Face

const deletedFace = await prisma.face.delete({
  where: { face_id: 5 }
});

Device Operations

Register a Device

const device = await prisma.device.create({
  data: {
    name: "Main Entrance Scanner",
    location: "Building A - Main Entrance",
    ip_address: "192.168.1.100",
    status: true
  }
});

Update Device Status

const device = await prisma.device.update({
  where: { device_id: 1 },
  data: { status: false }
});

Find Active Devices

const activeDevices = await prisma.device.findMany({
  where: { status: true },
  orderBy: { name: 'asc' }
});

Access Log Operations

Create Access Log

const accessLog = await prisma.accessLog.create({
  data: {
    user_id: 1,
    device_id: 1,
    confidence: 98.5,
    access_type: "entry",
    status: "granted",
    enterCode: false
  }
});

confidence is stored as Decimal(5, 2), allowing values like 99.99
access_date is automatically set to current timestamp
user_id can be null for failed recognition attempts

Query Access Logs

const recentLogs = await prisma.accessLog.findMany({
  take: 50,
  orderBy: { access_date: 'desc' }
});

Relationship Queries

User with User Type

const userWithType = await prisma.user.findUnique({
  where: { user_id: 1 },
  include: {
    user_type: true
  }
});

console.log(`${userWithType.first_name} is a ${userWithType.user_type.type_name}`);

const fullUser = await prisma.user.findUnique({
  where: { user_id: 1 },
  include: {
    user_type: true,
    faces: true,
    access_logs: {
      include: {
        device: true
      },
      orderBy: { access_date: 'desc' },
      take: 10
    }
  }
});

Access Log with Full Context

const detailedLog = await prisma.accessLog.findUnique({
  where: { log_id: 1 },
  include: {
    user: {
      include: {
        user_type: true
      }
    },
    device: true
  }
});

if (detailedLog.user) {
  console.log(
    `${detailedLog.user.first_name} ${detailedLog.user.last_name} ` +
    `accessed ${detailedLog.device.name} at ${detailedLog.access_date}`
  );
}

Device with Recent Access Logs

const deviceActivity = await prisma.device.findUnique({
  where: { device_id: 1 },
  include: {
    access_logs: {
      include: {
        user: true
      },
      orderBy: { access_date: 'desc' },
      take: 20
    }
  }
});

Select Specific Fields

const userSummary = await prisma.user.findMany({
  select: {
    user_id: true,
    first_name: true,
    last_name: true,
    email: true,
    user_type: {
      select: {
        type_name: true
      }
    }
  }
});

Advanced Queries

Aggregations

const userCountByType = await prisma.user.groupBy({
  by: ['user_type_id'],
  _count: {
    user_id: true
  }
});

Transactions

const result = await prisma.$transaction(async (tx) => {
  // Create user
  const user = await tx.user.create({
    data: {
      ci: "7654321",
      first_name: "Maria",
      last_name: "Garcia",
      email: "[email protected]",
      user_type_id: 1,
      code: 1002
    }
  });

  // Create face encoding
  const face = await tx.face.create({
    data: {
      user_id: user.user_id,
      encoding: JSON.stringify([/* encoding data */]),
      image_path: `/uploads/faces/user_${user.user_id}_face_1.jpg`
    }
  });

  return { user, face };
});

console.log("User and face created successfully:", result);

Transactions ensure that all operations succeed or fail together, maintaining data consistency.

Batch Operations

// Create multiple access logs
const logs = await prisma.accessLog.createMany({
  data: [
    { user_id: 1, device_id: 1, confidence: 99.2, access_type: "entry", status: "granted" },
    { user_id: 2, device_id: 1, confidence: 97.5, access_type: "entry", status: "granted" },
    { user_id: 3, device_id: 2, confidence: 85.0, access_type: "exit", status: "granted" }
  ],
  skipDuplicates: true
});

console.log(`Created ${logs.count} access logs`);

Raw Queries

For complex queries not supported by Prisma’s query builder:

const result = await prisma.$queryRaw`
  SELECT 
    d.name as device_name,
    COUNT(al.log_id) as access_count,
    AVG(al.confidence) as avg_confidence
  FROM devices d
  LEFT JOIN access_logs al ON d.device_id = al.device_id
  WHERE al.access_date >= NOW() - INTERVAL '7 days'
  GROUP BY d.device_id, d.name
  ORDER BY access_count DESC
`;

Raw queries bypass Prisma’s type safety. Use them only when necessary.

Best Practices

Connection Management

// Disconnect when application shuts down
process.on('beforeExit', async () => {
  await prisma.$disconnect();
});

Error Handling

import { Prisma } from '../generated/prisma';

try {
  await prisma.user.create({
    data: { /* ... */ }
  });
} catch (error) {
  if (error instanceof Prisma.PrismaClientKnownRequestError) {
    // Handle specific errors
    if (error.code === 'P2002') {
      console.error('Unique constraint violation:', error.meta?.target);
    }
  }
  throw error;
}

Common Error Codes

Code	Description
P2002	Unique constraint violation
P2003	Foreign key constraint violation
P2025	Record not found
P2014	Relation violation

Next Steps

Database Schema

Explore the complete database schema

Docker Setup

Learn about the Docker configuration

Models Reference

Detailed reference for each model

Setup Guide

Complete environment setup

Get Started

Database

Guides

​Generating the Client

​Generate Command

​Initializing the Client

​CRUD Operations

​User Operations

​Create a User

​Find Users

​Update a User

​Delete a User

​UserType Operations

​Create User Types

​Find User Types

​Face Operations

​Store Face Encoding

​Get User’s Faces

​Delete a Face

​Device Operations

​Register a Device

​Update Device Status

​Find Active Devices

​Access Log Operations

​Create Access Log

​Query Access Logs

​Relationship Queries

​Include Related Data

​User with User Type

​User with All Related Data

​Access Log with Full Context

​Device with Recent Access Logs

​Select Specific Fields

​Advanced Queries

​Aggregations

​Transactions

​Batch Operations

​Raw Queries

​Best Practices

​Connection Management

​Error Handling

​Common Error Codes

​Next Steps

Database Schema

Docker Setup

Models Reference

Setup Guide

Build docs developers (and LLMs) love

Generating the Client

Generate Command

Initializing the Client

CRUD Operations

User Operations

Create a User

Find Users

Update a User

Delete a User

UserType Operations

Create User Types

Find User Types

Face Operations

Store Face Encoding

Get User’s Faces

Delete a Face

Device Operations

Register a Device

Update Device Status

Find Active Devices

Access Log Operations

Create Access Log

Query Access Logs

Relationship Queries

Include Related Data

User with User Type

User with All Related Data

Access Log with Full Context

Device with Recent Access Logs

Select Specific Fields

Advanced Queries

Aggregations

Transactions

Batch Operations

Raw Queries

Best Practices

Connection Management

Error Handling

Common Error Codes

Next Steps