Theo's Typesafe Cult•2y ago

Prisma schema question

How can I efficiently maintain a user's balance in a database given the following prisma schema, where the balance is the sum of all trades profitAndLoss? Should I store the balance as a field in the User model and update it manually or should I aggregate it from the Trade model on every request? How can I ensure that the balance stays up-to-date when old trades are removed or modified?

8 Replies

Mendy•2y ago

Easier to aggregate, performant to store and update. You can keep the data up to date with a prisma middleware, making sure every update, insert or delete updates the value. Or in PG for example you could implement trigger functions to do it, so it won’t matter which client query’s the db, data will be kept synchronized. A con to this approach is the devEx. Regardless of the approach if the balance will be used to decide whether to allow a user to do an action, you’ll need to make sure that the related transactions are serialized to avoid some security concerns. Hassim Nassar has a good video about my last point, watch it to understand what can go wrong. Btw I have no clue if and how prisma handles that.

Mendy•2y ago

https://youtu.be/pomxJOFVcQs

Hussein Nasser

YouTube

Relational Database ACID Transactions (Explained by Example)

ACID are four properties of relational databases, Atomocity, consistency, isolation and durability, and I think anyone working with a relational database like postgres, mysql, sqlserver oracle, should understand these properties. In this video, we will go through the four properties and explain why each is critical to make a relational database ...

bostonsheraff•2y ago

I don't have enough experience to help architect a database, but I do know that prisma does transactions: https://www.prisma.io/docs/concepts/components/prisma-client/transactions

Prisma

Transactions and batch queries (Reference)

This page explains the transactions API of Prisma Client.

Sybatron•2y ago

I can think of

const groupBy = await prisma.trade.groupBy({
      by: ['userId'],
  where: {
    userId: {
      equals: userId,
    },
  },
      _sum: {
        profitAndLoss: true,
      },
    }

const groupBy = await prisma.trade.groupBy({
      by: ['userId'],
  where: {
    userId: {
      equals: userId,
    },
  },
      _sum: {
        profitAndLoss: true,
      },
    }

Or something like this Not sure how to invoke it only when trade with this userId is changed or added

Mendy•2y ago

There are multiple types of transactions, I read your link for a bit, there are some scenarios that you have to take extra steps to ensure the transaction safety - with prisma. Great resource tho. @abcdef you should probably read that.

Mendy•2y ago

Here’s a link to the important section: https://www.prisma.io/docs/concepts/components/prisma-client/transactions#transaction-isolation-level

Prisma

Transactions and batch queries (Reference)

This page explains the transactions API of Prisma Client.

Sybatron•2y ago

Wont the groupby work best to get the balance As batch queries will fetch all trades instead of just getting the balance with group by And not sure how they can subscribe to changes in trades with that user id

Mendy•2y ago

Aggregating the data every time is definitely easier to handle then keeping the balance up to date. But it won’t save you from having to think about acid transactions. For example: While you fetch your groupby query, another query is updating a new transaction, or modifying an existing one etc, the data returned by the groupby query is not up to date and you might make the wrong decision. This apply even when doing it all in the same transaction, if it’s not serialized

Gaming

Programming

Prisma schema question