In this article, we will see how to implement a basic transaction history of a user using Prisma and PostgreSQL.
Setting up the project
To set up the project, you need to have Node.js and PostgreSQL installed on your machine. You can download and install them from the official websites. If you do not want to install PostgreSQL locally on your machine, you can use a cloud-based PostgreSQL service like neon.tech.
Creating the Prisma schema
schema.prisma
generator client {
provider = "prisma-client-js"
}
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
}
model userbankdetails {
id Int @default(autoincrement())
username String @id @db.VarChar(50) // Primary key and foreign key to users.username
email String @unique @db.VarChar(255)
upiid String @unique @db.VarChar(255)
balance Decimal? @default(1000.00) @db.Decimal(10, 2)
created_at DateTime? @default(now()) @db.Timestamptz(6)
user users @relation(fields: [username], references: [username]) // Foreign key relation
transactionsFrom Transaction[] @relation("TransactionsFrom") // Transactions initiated by the user
transactionsTo Transaction[] @relation("TransactionsTo") // Transactions received by the user
}
model users {
id Int @default(autoincrement())
username String @id @db.VarChar(50)
email String @unique @db.VarChar(255)
password String @db.VarChar(255)
created_at DateTime? @default(now()) @db.Timestamptz(6)
bankdetails userbankdetails? // One-to-one relation
}
model Transaction {
id Int @id @default(autoincrement())
from_upi_id String @db.VarChar(255)
to_upi_id String @db.VarChar(255)
amount Decimal @db.Decimal(10, 2)
created_at DateTime @default(now()) @db.Timestamptz(6)
sender userbankdetails @relation("TransactionsFrom", fields: [from_upi_id], references: [upiid])
receiver userbankdetails @relation("TransactionsTo", fields: [to_upi_id], references: [upiid])
}Migrating the database
To create the database tables based on the Prisma schema, you need to run the following commands:
npx prisma migrate dev --name initThis command will create the necessary tables in the database based on the Prisma schema.
Let us create couple of endpoints in the API
- /create-user - This endpoint will create a new user in the database.
async function createUser(username: string, email: string, password: string) {
const user = await prisma.users.create({
data: {
username,
email,
password,
},
});
return user;
}
app.post("/create-user", async (req, res) => {
const { username, email, password } = req.body;
try {
// Create user and wait for the operation to complete
createUser(username, email, password);
// Send a successful response once the user is created
res.status(201).json({
message: "User created successfully",
});
} catch (err) {
if (err instanceof Error) {
console.error("Error creating user:", err.message);
} else {
console.error("Error creating user:", err);
}
res.status(500).json({
error: "An error occurred while creating the user.",
});
}
});
- /create-bank-account - This endpoint will create a new bank account for a user in the database.
async function createUserBankDetails(
username: string,
email: string,
upiid: string,
balance: number,
) {
const userBankDetails = await prisma.userbankdetails.create({
data: {
username,
email,
upiid,
balance,
},
});
return userBankDetails;
}
app.post("/create-bank-account", async (req, res) => {
const { username, email, upiid, balance } = req.body;
try {
const userBankDetails = await createUserBankDetails(
username,
email,
upiid,
balance,
);
res.status(201).json(userBankDetails);
} catch (err) {
if (err instanceof Error) {
console.error("Error creating user bank details:", err.message);
} else {
console.error("Error creating user bank details:", err);
}
res.status(500).json({
error: "An error occurred while creating the user bank details.",
});
}
});
- /send-money - This endpoint will send money from one user to another based on their upi_id.
async function sendMoney(from: string, to: string, amount: number) {
const result = await prisma.$transaction(async (prisma) => {
// Check sender's balance
const sender = await prisma.userbankdetails.findUnique({
where: { upiid: from },
});
if (!sender) {
throw new Error("Sender account does not exist.");
}
const senderBalance = sender.balance ?? 0;
if (Number(senderBalance) < amount) {
throw new Error("Insufficient balance.");
}
// Check receiver's balance
const receiver = await prisma.userbankdetails.findUnique({
where: { upiid: to },
});
if (!receiver) {
throw new Error("Receiver account does not exist.");
}
const receiverBalance = receiver.balance ?? 0;
// Update sender's balance
await prisma.userbankdetails.update({
where: { upiid: from },
data: {
balance: new Decimal(senderBalance).minus(new Decimal(amount)),
},
});
// Update receiver's balance
await prisma.userbankdetails.update({
where: { upiid: to },
data: {
balance: new Decimal(receiverBalance).plus(new Decimal(amount)),
},
});
// Create the transaction history record
await prisma.transaction.create({
data: {
from_upi_id: from,
to_upi_id: to,
amount: amount,
},
});
});
}
app.post("/send-money", async (req, res) => {
const { from, to, amount } = req.body;
if (amount <= 0) {
return res.status(400).json({ error: "Invalid transaction amount." });
}
try {
await sendMoney(from, to, amount);
res.status(200).json({ message: "Transaction successful." });
} catch (err) {
if (err instanceof Error) {
console.error("Error sending money:", err.message);
}
}
});
Implementing the transaction history
- /get-transaction-history - This endpoint will return the transaction history of a user based on their upi_id.
async function getTransactionHistory(upiid: string) {
const transactions = await prisma.transaction.findMany({
where: {
OR: [
{ from_upi_id: upiid },
{ to_upi_id: upiid },
],
},
orderBy: {
created_at: 'desc',
},
});
console.log("Transactions found:", transactions);
return transactions;
}
app.get("/get-transaction-history", async (req, res) => {
const { upiid } = req.query;
if (typeof upiid !== 'string') {
return res.status(400).json({ error: "Invalid UPI ID." });
}
try {
const transactions = await getTransactionHistory(upiid);
res.status(200).json(transactions);
} catch (err) {
if (err instanceof Error) {
console.error("Error getting transaction history:", err.message);
} else {
console.error("Error getting transaction history:", err);
}
res.status(500).json({
error: "An error occurred while getting the transaction history.",
});
}
});