API with NestJS #147. The data types to store money with PostgreSQL and Prisma

NestJS SQL

This entry is part 147 of 180 in the API with NestJS

Managing money is a part of developing web applications that we can’t cut corners on. This article explores various data types built into PostgreSQL and Prisma that we could use to handle finances. We also discuss which ones to avoid and why.

Why we should avoid floating point numbers

We are used to using fractions when dealing with money – for example, $15.20 means fifteen dollars and twenty cents.

Therefore, we might think of using floating-point types to store the monetary values. Using floating-point numbers is a very popular way to represent numbers that include a fraction.

schema.prisma

Unfortunately, using floating-point numbers can cause a wide variety of errors. Let’s run the following SQL query:

By using we are telling PostgreSQL to treat those values as floating-point numbers.

Unfortunately, the result of our query is not .

Binary representation of integer numbers

To understand why that’s the case, we must dive into how a computer represents numbers.

It is very straightforward for a computer to store an integer. It does that by dividing the number into a combination of smaller numbers, each one a power of 2. For example, 53 = 1 + 4 + 16 + 32. Under the hood, the computers use binary numbers that use only two digits – 0 and 1. Each digit in a binary number represents a power of 2. The rightmost digit has a value of 2⁰, the next digit to the left represents 2¹, and so on.

The number 53 can be represented by adding numbers 1, 4, 16, and 32. We use the number 1 to signify which powers of 2 we need and 0 to say which ones we don’t. This way, we can store any integer number we want.

The above system is slightly more complicated if we want to store negative numbers as well, but is built around the same core concept.

Floating-point numbers

The floating-point system uses a similar system to represent fractions of a number. Let’s use the number 53.625 as an example. We could store the 0.625 number as a sum of fractions that are a power of ½. When we do that, 0.625 becomes ½ + ⅛.

The floating point system performs additional conversions on the above number to determine the so-called exponent and the mantissa. Thanks to that, the floating-point numbers can represent a vast range of values from from very small fractions to very large numbers.

Unfortunately, not every fraction can be stored as a sum of numbers that are a power of ½. For example, there is no binary fraction that equals 0.1. Instead, the floating-point system would create an approximation. When we convert in binary into decimal, we get , which is almost 0.1. The approximation is more precise if we use more bits.

This approximation is good enough for a lot of purposes such as video games graphics.

The rounding errors

Both 0.1 and 0.2 can only be stored as approximations when using the floating-point numbers system. When we try to add them up, we experience a rounding error:

  • both 0.1 and 0.2 are stored as their closest binary approximations in the floating-point system,
  • when we add those approximations together, we get a number that’s close to 0.3 but also an approximation.

Because of the characteristics of the floating-point numbers, we can’t use them to store money. Even minor rounding errors would disrupt a banking system, for example.

Storing money as integers

A valid solution to storing money is to use integers instead of floats.

schema.prisma

The crucial thing about this approach is that we need to represent all money values in the smallest currency unit, such as cents. Instead of storing $15.20, we would store 1520 cents. When performing various calculations, we need to remember that all values are in the smallest unit.

After creating the migration, we can see the following SQL:

migration.sql

We can see that Prisma generated a migration that uses the data type. It can store numbers from -2,147,483,648 to 2,147,483,647.

If we want to store bigger numbers, we can use the data type instead. To do that with Prisma, we must adjust our model a little.

schema.prisma

Thanks to using the type, we can store values from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. Over nine quintillion should be more than enough for most applications.

The numeric data type

Another possible solution is to use the numeric type. It allows us to store numbers with fractions, but we must specify how many digits we want to use. Using a total of nineteen digits and reserving four after the decimal point is a popular choice.

schema.prisma

Most currencies only require two digits to represent cents or the smallest unit, such as cents. However, in financial applications, especially those dealing with interest rates, loans, investments, and similar financial products, calculations can result in fractional cents.

Unfortunately, Prisma does not handle a situation where the user puts a number that’s too big very well. To support it, we need to handle the .

On the other hand, if we provide a number with too many decimals after the decimal point, they are ignored.

bank-accounts.service.ts

Under the hood, Prisma uses the decimal.js library. Check out the official documentation if you want to know more.

The Money data type

Another type that can store money is the data type. While it might seem appropriate due to its name, it is heavily discouraged.

schema.prisma

The data formatting and decimal precision depend on the database’s locale settings. If we move our data across databases with different locales, the representation and behavior of monetary values can change.

Summary

In this article, we’ve covered various data types we can use with PostgreSQL and Prisma to store monetary values. To explain why we shouldn’t use floating-point numbers, we had to understand their binary format. As an alternative, we learned how to use integers and the numeric data type to handle money. We also found out about the data type and why it is discouraged. This helps us understand how to store money in databases safely and why some data types are better than others for keeping our numbers accurate.

Series Navigation<< API with NestJS #146. Polymorphic associations with PostgreSQL and PrismaAPI with NestJS #148. Understanding the injection scopes >>
Subscribe
Notify of
guest
3 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Sam
Sam
9 months ago

Awesome explanation. Thanks Marcin.

Yahya Eddhissa
Yahya Eddhissa
9 months ago

Very interesting! Keep up the good work.

Appreciative person
Appreciative person
9 months ago

Nice, straightforward post. Appreciated