Interacting with the database - synthofficial/discord-idle-game-bot-template GitHub Wiki

Interacting with the Database

Prisma provides a powerful and type-safe way to interact with your database. This guide covers all the essential operations you'll need for your Discord idle game.

📚 Basic Setup

First, import Prisma in your command or event file:

import { prisma } from "../Client/database";

Important: Always use await with Prisma queries since they're asynchronous operations.

📖 Reading Data (SELECT)

Find a Single User

Retrieve one user by their unique identifier:

const userData = await prisma.user.findUnique({
  where: {
    userId: interaction.user.id
  }
});

console.log(userData); // { userId: "123...", level: 5, experience: 1250, money: 5000 }

Find with Null Check

Always check if the user exists before using their data:

const userData = await prisma.user.findUnique({
  where: {
    userId: interaction.user.id
  }
});

if (!userData) {
  return interaction.reply("You don't have a profile! Use `/start` to create one.");
}

// Safe to use userData here
console.log(`Level: ${userData.level}`);

Find Many Users

Get multiple users with filtering and sorting:

// Get top 10 users by level
const topUsers = await prisma.user.findMany({
  orderBy: {
    level: 'desc'
  },
  take: 10
});

// Get users with level greater than 10
const experiencedUsers = await prisma.user.findMany({
  where: {
    level: {
      gt: 10
    }
  }
});

Find First Match

Get the first user that matches a condition:

const firstRichUser = await prisma.user.findFirst({
  where: {
    money: {
      gte: 100000
    }
  }
});

✏️ Creating Data (INSERT)

Create a New User

Add a new user to the database:

const newUser = await prisma.user.create({
  data: {
    userId: interaction.user.id,
    level: 1,
    experience: 0,
    money: 1000
  }
});

console.log(`Created user: ${newUser.userId}`);

Create with Default Values

If you have @default() in your schema, you can omit those fields:

// Assuming level, experience, money have @default() in schema
const newUser = await prisma.user.create({
  data: {
    userId: interaction.user.id
  }
});

Create or Get Existing (Upsert)

Create a user if they don't exist, or return existing data:

const userData = await prisma.user.upsert({
  where: {
    userId: interaction.user.id
  },
  update: {}, // Don't update if exists
  create: {
    userId: interaction.user.id,
    level: 1,
    experience: 0,
    money: 1000
  }
});

🔄 Updating Data (UPDATE)

Update a Single Field

Change one field value:

await prisma.user.update({
  where: {
    userId: interaction.user.id
  },
  data: {
    level: 5
  }
});

Update Multiple Fields

Change several fields at once:

await prisma.user.update({
  where: {
    userId: interaction.user.id
  },
  data: {
    level: 5,
    experience: 0,
    money: 2000
  }
});

Increment/Decrement Values

Use atomic operations to increase or decrease values:

// Add experience and money
await prisma.user.update({
  where: {
    userId: interaction.user.id
  },
  data: {
    experience: {
      increment: 50 // Add 50 XP
    },
    money: {
      increment: 100 // Add 100 money
    }
  }
});

// Subtract money for a purchase
await prisma.user.update({
  where: {
    userId: interaction.user.id
  },
  data: {
    money: {
      decrement: 500 // Remove 500 money
    }
  }
});

Multiply/Divide Values

// Double the user's money
await prisma.user.update({
  where: {
    userId: interaction.user.id
  },
  data: {
    money: {
      multiply: 2
    }
  }
});

Update Many Records

Update multiple users at once:

// Give bonus money to all high-level users
await prisma.user.updateMany({
  where: {
    level: {
      gte: 50
    }
  },
  data: {
    money: {
      increment: 10000
    }
  }
});

🗑️ Deleting Data (DELETE)

Delete a Single User

Remove one user from the database:

await prisma.user.delete({
  where: {
    userId: interaction.user.id
  }
});

Delete Many Records

Remove multiple users matching a condition:

// Delete all inactive users (example condition)
await prisma.user.deleteMany({
  where: {
    level: {
      lt: 5
    }
  }
});

🎮 Common Idle Game Patterns

Level Up System

Check if user has enough XP and level them up:

const userData = await prisma.user.findUnique({
  where: { userId: interaction.user.id }
});

if (!userData) return;

const xpNeeded = userData.level * 100; // Example: 100 XP per level

if (userData.experience >= xpNeeded) {
  await prisma.user.update({
    where: { userId: interaction.user.id },
    data: {
      level: { increment: 1 },
      experience: 0 // Reset XP
    }
  });
  
  interaction.reply(`🎉 Congratulations! You leveled up to level ${userData.level + 1}!`);
}

Shop Purchase System

Check if user has enough money before purchasing:

const userData = await prisma.user.findUnique({
  where: { userId: interaction.user.id }
});

if (!userData) return;

const itemCost = 500;

if (userData.money < itemCost) {
  return interaction.reply("❌ You don't have enough money!");
}

await prisma.user.update({
  where: { userId: interaction.user.id },
  data: {
    money: { decrement: itemCost }
  }
});

interaction.reply(`✅ Purchase successful! Remaining balance: ${userData.money - itemCost}`);

Daily Reward System

Give rewards and update last claim time:

const userData = await prisma.user.findUnique({
  where: { userId: interaction.user.id }
});

if (!userData) return;

const now = new Date();
const lastClaim = userData.lastDailyClaim; // Assuming you have this field

// Check if 24 hours have passed
if (lastClaim && now.getTime() - lastClaim.getTime() < 86400000) {
  return interaction.reply("⏰ You already claimed your daily reward!");
}

await prisma.user.update({
  where: { userId: interaction.user.id },
  data: {
    money: { increment: 1000 },
    lastDailyClaim: now
  }
});

interaction.reply("🎁 You received 1000 coins!");

🔒 Transactions

Use transactions when you need to perform multiple operations atomically (all succeed or all fail):

import { prisma } from "../Client/database";

// Example: Transfer money between users
await prisma.$transaction([
  // Subtract from sender
  prisma.user.update({
    where: { userId: senderId },
    data: { money: { decrement: 500 } }
  }),
  // Add to receiver
  prisma.user.update({
    where: { userId: receiverId },
    data: { money: { increment: 500 } }
  })
]);

Interactive Transaction

For more complex logic with conditions:

await prisma.$transaction(async (tx) => {
  const sender = await tx.user.findUnique({
    where: { userId: senderId }
  });
  
  if (!sender || sender.money < 500) {
    throw new Error("Insufficient funds");
  }
  
  await tx.user.update({
    where: { userId: senderId },
    data: { money: { decrement: 500 } }
  });
  
  await tx.user.update({
    where: { userId: receiverId },
    data: { money: { increment: 500 } }
  });
});

⚠️ Error Handling

Always wrap database operations in try-catch blocks:

try {
  await prisma.user.update({
    where: { userId: interaction.user.id },
    data: { level: { increment: 1 } }
  });
  
  interaction.reply("✅ Level increased!");
} catch (error) {
  console.error("Database error:", error);
  interaction.reply("❌ An error occurred. Please try again later.");
}

📊 Advanced Queries

Counting Records

const userCount = await prisma.user.count();
const highLevelCount = await prisma.user.count({
  where: { level: { gte: 50 } }
});

Aggregations

const stats = await prisma.user.aggregate({
  _avg: { level: true },
  _max: { money: true },
  _min: { level: true },
  _sum: { money: true }
});

console.log(`Average level: ${stats._avg.level}`);
console.log(`Richest player has: ${stats._max.money}`);

Grouping Data

const levelDistribution = await prisma.user.groupBy({
  by: ['level'],
  _count: { level: true },
  orderBy: { level: 'asc' }
});

💡 Best Practices

Always validate data - Check if records exist before operations
Use transactions - For operations that must succeed or fail together
Handle errors - Use try-catch blocks to prevent crashes
Use atomic operations - Prefer increment/decrement over reading and updating
Index properly - Make sure your @id and @unique fields are indexed
Avoid N+1 queries - Use include or select to fetch related data efficiently

🔗 Related Pages

Creating database model - Learn how to create models
Using prisma globally - How to import Prisma in your files