@repo/db

The @repo/db package provides a centralized Prisma client for PostgreSQL database operations across the Exness Trading Platform.

Installation

This package is internal to the monorepo and installed automatically:

"dependencies": {
  "@repo/db": "workspace:*"
}

Features

Prisma ORM: Type-safe database queries with Prisma Client
Accelerate Extension: Enhanced performance with connection pooling
Generated Types: Automatic TypeScript types from schema
Global Singleton: Prevents multiple client instances in development

Database Schema

The package uses PostgreSQL with the following schema:

User Model

model User {
  id      Int     @id       @default(autoincrement())
  userID  String  @unique
  email   String  @unique
  balance Float   @default(0)
  Orders Orders[]
}

Orders Model

enum Symbol {
  btc
  sol
  eth
}

enum OrderSide {
  buy
  sell
}

model Orders {
  orderId     String    @id
  userId      String
  user        User      @relation(fields: [userId], references: [userID], onDelete: Cascade)
  symbol      Symbol
  type        OrderSide
  quantity    Float
  leverage    Int
  takeProfit  Float?
  stopLoss    Float?
  stippage    Float?
  openPrice   Float
  closePrice  Float
  openTime    DateTime
  closeTime   DateTime
  profitLoss  Float

  @@index([userId])
}

Basic Usage

Importing the Client

import { prisma } from '@repo/db';

// Client is ready to use - no initialization needed
const users = await prisma.user.findMany();

The Prisma client is a singleton instance with the Accelerate extension pre-configured.

User Operations

Create a User

import { prisma } from '@repo/db';

const user = await prisma.user.create({
  data: {
    userID: 'user_123456',
    email: '[email protected]',
    balance: 10000.00
  }
});

console.log('Created user:', user);

Find Users

By ID
By Email
With Orders
All Users

const user = await prisma.user.findUnique({
  where: { userID: 'user_123456' }
});

if (user) {
  console.log(`Balance: $${user.balance}`);
}

const user = await prisma.user.findUnique({
  where: { email: '[email protected]' }
});

const user = await prisma.user.findUnique({
  where: { userID: 'user_123456' },
  include: {
    Orders: true
  }
});

console.log(`User has ${user?.Orders.length} orders`);

const users = await prisma.user.findMany({
  where: {
    balance: {
      gte: 1000 // Users with balance >= $1000
    }
  },
  orderBy: {
    balance: 'desc'
  },
  take: 10 // Top 10 by balance
});

Update User Balance

// Update balance after trade
const updatedUser = await prisma.user.update({
  where: { userID: 'user_123456' },
  data: {
    balance: {
      increment: 250.50 // Add profit
    }
  }
});

console.log(`New balance: $${updatedUser.balance}`);

Order Operations

Create an Order

import { prisma } from '@repo/db';

const order = await prisma.orders.create({
  data: {
    orderId: `order_${Date.now()}`,
    userId: 'user_123456',
    symbol: 'btc',
    type: 'buy',
    quantity: 0.5,
    leverage: 10,
    openPrice: 50000.00,
    closePrice: 51000.00,
    openTime: new Date(),
    closeTime: new Date(),
    profitLoss: 500.00,
    takeProfit: 52000.00,
    stopLoss: 49000.00
  }
});

console.log('Order created:', order.orderId);

Query Orders

// Get all orders for a user
const userOrders = await prisma.orders.findMany({
  where: { userId: 'user_123456' },
  orderBy: { openTime: 'desc' }
});

const totalProfit = userOrders.reduce(
  (sum, order) => sum + order.profitLoss,
  0
);

Update an Order

// Close an order and calculate profit/loss
const closedOrder = await prisma.orders.update({
  where: { orderId: 'order_123' },
  data: {
    closePrice: 51500.00,
    closeTime: new Date(),
    profitLoss: 750.00
  }
});

Delete Orders

// Delete a specific order
await prisma.orders.delete({
  where: { orderId: 'order_123' }
});

// Delete all orders for a user (cascade is automatic)
await prisma.user.delete({
  where: { userID: 'user_123456' }
});
// All associated orders are deleted due to onDelete: Cascade

Complex Queries

Aggregations

// Calculate total volume and profit by symbol
const stats = await prisma.orders.groupBy({
  by: ['symbol'],
  _sum: {
    quantity: true,
    profitLoss: true
  },
  _avg: {
    profitLoss: true
  },
  _count: {
    orderId: true
  }
});

stats.forEach(stat => {
  console.log(`${stat.symbol.toUpperCase()}:`);
  console.log(`  Total Volume: ${stat._sum.quantity}`);
  console.log(`  Total P/L: $${stat._sum.profitLoss}`);
  console.log(`  Average P/L: $${stat._avg.profitLoss}`);
  console.log(`  Order Count: ${stat._count.orderId}`);
});

Transactions

// Execute multiple operations atomically
const result = await prisma.$transaction(async (tx) => {
  // Create order
  const order = await tx.orders.create({
    data: {
      orderId: `order_${Date.now()}`,
      userId: 'user_123456',
      symbol: 'btc',
      type: 'buy',
      quantity: 1.0,
      leverage: 5,
      openPrice: 50000,
      closePrice: 50000,
      openTime: new Date(),
      closeTime: new Date(),
      profitLoss: 0
    }
  });
  
  // Deduct margin from user balance
  const user = await tx.user.update({
    where: { userID: 'user_123456' },
    data: {
      balance: {
        decrement: 10000 // Margin required
      }
    }
  });
  
  return { order, user };
});

console.log('Transaction completed:', result);

Raw SQL Queries

// Execute raw SQL when needed
const topTraders = await prisma.$queryRaw`
  SELECT 
    u."userID",
    u.email,
    COUNT(o."orderId") as order_count,
    SUM(o."profitLoss") as total_profit
  FROM "User" u
  LEFT JOIN "Orders" o ON u."userID" = o."userId"
  GROUP BY u."userID", u.email
  ORDER BY total_profit DESC
  LIMIT 10
`;

TypeScript Types

The package exports generated Prisma types:

import { prisma, User, Orders, Symbol, OrderSide } from '@repo/db';

// Use generated types
function calculateProfit(order: Orders): number {
  const priceDiff = order.closePrice - order.openPrice;
  const direction = order.type === 'buy' ? 1 : -1;
  return priceDiff * order.quantity * order.leverage * direction;
}

// Type-safe symbol enum
const symbol: Symbol = 'btc'; // Valid
// const invalid: Symbol = 'invalid'; // TypeScript error

// Type-safe order side
const side: OrderSide = 'buy'; // Valid

Prisma Commands

The package includes npm scripts for database management:

# Generate Prisma Client after schema changes
bun run --filter @repo/db db:generate

Environment Configuration

The package requires the DATABASE_URL environment variable:

# .env
DATABASE_URL="postgresql://user:password@localhost:5432/exness?schema=public"

The database URL is managed by @repo/config and automatically loaded from environment variables.

Best Practices

Use transactions for related operations

Select only needed fields

Improve performance by selecting only the fields you need:

const users = await prisma.user.findMany({
  select: {
    userID: true,
    email: true
    // Don't select balance if not needed
  }
});

Use indexes for common queries

The schema includes an index on userId in the Orders table. Add more indexes for frequently queried fields:

@@index([symbol])
@@index([openTime])

Handle unique constraint violations

Wrap operations that might violate unique constraints:

try {
  await prisma.user.create({ data: { ... } });
} catch (error) {
  if (error.code === 'P2002') {
    console.log('User already exists');
  }
}

Performance Optimization

The package uses Prisma Accelerate for enhanced performance:

// Accelerate provides:
// - Connection pooling
// - Query caching
// - Global database cache

const user = await prisma.user.findUnique({
  where: { userID: 'user_123' },
  cacheStrategy: { 
    ttl: 60, // Cache for 60 seconds
    swr: 10  // Serve stale while revalidate
  }
});

@repo/config

Provides DATABASE_URL configuration

@repo/types

Additional shared type definitions

Shared Packages

Installation

Features

Database Schema

User Model

Orders Model

Basic Usage

Importing the Client

User Operations

Create a User

Find Users

Update User Balance

Order Operations

Create an Order

Query Orders

Update an Order

Delete Orders

Complex Queries

Aggregations

Transactions

Raw SQL Queries

TypeScript Types

Prisma Commands

Environment Configuration

Best Practices

Performance Optimization

@repo/config

@repo/types

Build docs developers (and LLMs) love

Shared Packages

​Installation

​Features

​Database Schema

​User Model

​Orders Model

​Basic Usage

​Importing the Client

​User Operations

​Create a User

​Find Users

​Update User Balance

​Order Operations

​Create an Order

​Query Orders

​Update an Order

​Delete Orders

​Complex Queries

​Aggregations

​Transactions

​Raw SQL Queries

​TypeScript Types

​Prisma Commands

​Environment Configuration

​Best Practices

​Performance Optimization

​Related Packages

@repo/config

@repo/types

Build docs developers (and LLMs) love

Installation

Features

Database Schema

User Model

Orders Model

Basic Usage

Importing the Client

User Operations

Create a User

Find Users

Update User Balance

Order Operations

Create an Order

Query Orders

Update an Order

Delete Orders

Complex Queries

Aggregations

Transactions

Raw SQL Queries

TypeScript Types

Prisma Commands

Environment Configuration

Best Practices

Performance Optimization

Related Packages