Write your own queries
We explore a scenario that highlights how writing bespoke queries achieves the best possible performance for your APIs
In today's digital age, databases have become a fundamental piece of modern applications by storing, organising and retrieving vast amounts of data.
The importance of databases cannot be overstated — they enable businesses to make data-driven decisions, help researchers analyse complex patterns and much more. As we generate more data than ever before, efficient database management has become a critical skill for any technology professional.
By writing optimised queries, we can significantly improve the performance of our APIs by making efficient data retrieval and manipulation. This is crucial, especially when data volumes continue to grow exponentially.
In this blog post, we are going to explore a scenario in which we extract the same set of data using Prisma in two different ways:
The first one as an ORM, which generates the query for us;
The second one as a plain client to run the raw queries we are going to write.
Context of the scenario
Let’s suppose we have a project that uses PostgreSQL as object-relational database management system (ORDBMS).
The table where our data is stored, named users, has the following shape:
| Column name | Column type |
|---|---|
| id | integer, auto-increment |
| first_name | text |
| last_name | text |
| birth_date | date |
The database is queried by a Node.js microservice backed by the Fastify web framework.
In Fastify, we have created a plugin to create a decorator named prisma, which contains an instance of the Prisma Client.
Additionally, the database is seeded with 100 items.
Every piece of code showed here can be found into its dedicated repository.
First scenario: Decoupling
Decoupling is the practice of separating different components or layers of an application to reduce dependencies between them. This architectural principle allows each part of the system to evolve independently, making the codebase more maintainable and flexible.
When it comes to database interactions, decoupling the database schema from the API response means being able to modify its structure without changing the API contract.
To achieve this, we want to return a response that looks like this:
[
{
"id": 0,
"firstName": "Matteo Pietro 0",
"lastName": "Dazzi 0",
"birthDate": "1924-01-01"
}
]Essentially, we are switching from the snake_case format used by the column name to the camelCase format commonly used by the JSON format.
Decoupling with an ORM
To do so, using the ORM we need to retrieve all the users first, and then map all of them to the desired shape.
fastify.get('/orm', { schema }, async () => {
const users = await fastify.prisma.users.findMany()
return users.map(user => ({
id: user.id,
firstName: user.first_name,
lastName: user.last_name,
birthDate: user.birth_date
}))
})This could be a problem as we will need to increase the amount of memory needed for this operation. In fact, in this handler we are consuming memory for two arrays of 100 objects each: the first array stores the original data retrieved from the database, while the second array stores the data in the desired shape.
Usually, this kind of problem can be avoided using a technique called select alias, but Prisma doesn't support it yet.
Decoupling with a raw query
To achieve the same result with a raw query, we just need to write the select statement using aliases. There will be a camelCase alias for each snake_case column.
fastify.get('/query', { schema }, () => fastify.prisma.$queryRaw`
SELECT id,
first_name as "firstName",
last_name as "lastName",
birth_date as "birthDate"
FROM users
`)This solution allows us to:
Halve the amount of memory required by the handler to run, since we are now directly returning the 100 objects retrieved from the database;
Dodge creating an additional promise in the request handler function;
Avoid an additional loop to change the data’s shape.
Performance
Talk is cheap, it’s time to take a look at the advantages in terms of performance that this solution brings.
To make these tests, we are going to use autocannon with its default configuration on a MacBook Pro M3.
ORM performance test
npm run bench:rename:orm
> autocannon http://localhost:3000/rename/orm
Running 10s test @ http://localhost:3000/rename/orm
10 connections
┌─────────┬──────┬──────┬───────┬──────┬─────────┬─────────┬───────┐
│ Stat │ 2.5% │ 50% │ 97.5% │ 99% │ Avg │ Stdev │ Max │
├─────────┼──────┼──────┼───────┼──────┼─────────┼─────────┼───────┤
│ Latency │ 1 ms │ 2 ms │ 4 ms │ 5 ms │ 2.52 ms │ 0.97 ms │ 14 ms │
└─────────┴──────┴──────┴───────┴──────┴─────────┴─────────┴───────┘
┌───────────┬───────┬───────┬─────────┬─────────┬─────────┬────────┬───────┐
│ Stat │ 1% │ 2.5% │ 50% │ 97.5% │ Avg │ Stdev │ Min │
├───────────┼───────┼───────┼─────────┼─────────┼─────────┼────────┼───────┤
│ Req/Sec │ 3,245 │ 3,245 │ 3,279 │ 3,509 │ 3,306.2 │ 76.86 │ 3,244 │
├───────────┼───────┼───────┼─────────┼─────────┼─────────┼────────┼───────┤
│ Bytes/Sec │ 29 MB │ 29 MB │ 29.3 MB │ 31.4 MB │ 29.6 MB │ 689 kB │ 29 MB │
└───────────┴───────┴───────┴─────────┴─────────┴─────────┴────────┴───────┘
Req/Bytes counts sampled once per second.
# of samples: 10
33k requests in 10.01s, 296 MB readUsing the ORM implementation, we can handle 33k requests with an average response time of 2.52 ms.
Raw query performance test
npm run bench:rename:query
> autocannon http://localhost:3000/rename/query
Running 10s test @ http://localhost:3000/rename/query
10 connections
┌─────────┬──────┬──────┬───────┬──────┬─────────┬─────────┬───────┐
│ Stat │ 2.5% │ 50% │ 97.5% │ 99% │ Avg │ Stdev │ Max │
├─────────┼──────┼──────┼───────┼──────┼─────────┼─────────┼───────┤
│ Latency │ 1 ms │ 2 ms │ 4 ms │ 5 ms │ 2.02 ms │ 0.84 ms │ 18 ms │
└─────────┴──────┴──────┴───────┴──────┴─────────┴─────────┴───────┘
┌───────────┬─────────┬─────────┬─────────┬─────────┬──────────┬────────┬─────────┐
│ Stat │ 1% │ 2.5% │ 50% │ 97.5% │ Avg │ Stdev │ Min │
├───────────┼─────────┼─────────┼─────────┼─────────┼──────────┼────────┼─────────┤
│ Req/Sec │ 3,855 │ 3,855 │ 3,993 │ 4,077 │ 3,970.82 │ 72.73 │ 3,855 │
├───────────┼─────────┼─────────┼─────────┼─────────┼──────────┼────────┼─────────┤
│ Bytes/Sec │ 34.5 MB │ 34.5 MB │ 35.7 MB │ 36.5 MB │ 35.5 MB │ 646 kB │ 34.5 MB │
└───────────┴─────────┴─────────┴─────────┴─────────┴──────────┴────────┴─────────┘
Req/Bytes counts sampled once per second.
# of samples: 11
44k requests in 11.01s, 391 MB readWOW! 🤯
We can process ~30% more requests than before, reducing the average latency as well!
Second scenario: Data operations
We will now look at a scenario in which data will not only be queried, but also processed.
Suppose that the shape of our response looks like this:
[{
"id": 0,
"name": {
"first": "Matteo Pietro 0",
"last": "Dazzi 0"
},
"birth": {
"date": "1924-01-01",
"age": 101
}
}]We have multiple challenges here:
Nesting some of the columns (e.g.
first_nameandlast_name) inside a JSON object (e.g.name);Creating a computed field (e.g.
age) based on another column (e.g.birth_date).
In scenarios like this, a simple alias is not enough. Let’s see what we can do then.
Data operations with an ORM
The solution doesn’t change that much compared to the previous case.
Prisma supports computed fields using a client extension — it is no different than re-mapping the object to the desired shape. These kinds of fields get computed only during data retrieval, so you cannot apply any WHERE clauses since they don't exist on the database.
fastify.get('/orm', { schema }, async () => {
const users = await fastify.prisma.users.findMany()
const currentYear = new Date().getFullYear()
return users.map(user => ({
id: user.id,
name: {
first: user.first_name,
last: user.last_name
},
birth: {
date: user.birth_date,
age: currentYear - user.birth_date.getFullYear()
}
}))
})Data operations with a raw query
The story changes a bit with the raw query.
To achieve the desired result, we can use a mix of select alias and PostgreSQL functions.
fastify.get('/query', { schema }, () => fastify.prisma.$queryRaw`
SELECT id,
json_build_object('first', first_name, 'last', last_name) as "name",
json_build_object('date', birth_date, 'age', date_part('year', age(birth_date))) as "birth"
FROM users
`)The column aliasing is used to generate the key of the new object, while its content is created through the jsonbuildobject function provided by PostgreSQL. It allows you to build a new JSON object out of a variadic argument list.
To calculate the age, we simply invoked the age function and then extracted only the year using the date_part function. Both are provided by PostgreSQL.
Performance
Once again, to test the provided approaches, we will run some tests using the same setup we used before.
ORM performance test
npm run bench:object:orm
> autocannon http://localhost:3000/object/orm
Running 10s test @ http://localhost:3000/object/orm
10 connections
┌─────────┬──────┬──────┬───────┬──────┬─────────┬─────────┬───────┐
│ Stat │ 2.5% │ 50% │ 97.5% │ 99% │ Avg │ Stdev │ Max │
├─────────┼──────┼──────┼───────┼──────┼─────────┼─────────┼───────┤
│ Latency │ 1 ms │ 3 ms │ 5 ms │ 6 ms │ 2.66 ms │ 1.09 ms │ 21 ms │
└─────────┴──────┴──────┴───────┴──────┴─────────┴─────────┴───────┘
┌───────────┬─────────┬─────────┬─────────┬─────────┬─────────┬────────┬─────────┐
│ Stat │ 1% │ 2.5% │ 50% │ 97.5% │ Avg │ Stdev │ Min │
├───────────┼─────────┼─────────┼─────────┼─────────┼─────────┼────────┼─────────┤
│ Req/Sec │ 3,083 │ 3,083 │ 3,133 │ 3,205 │ 3,152.2 │ 39.55 │ 3,082 │
├───────────┼─────────┼─────────┼─────────┼─────────┼─────────┼────────┼─────────┤
│ Bytes/Sec │ 32.2 MB │ 32.2 MB │ 32.7 MB │ 33.5 MB │ 32.9 MB │ 420 kB │ 32.2 MB │
└───────────┴─────────┴─────────┴─────────┴─────────┴─────────┴────────┴─────────┘
Req/Bytes counts sampled once per second.
# of samples: 10
32k requests in 10.01s, 329 MB readUsing the ORM implementation, we can handle 27,000 requests with an average response time of 2.66 ms.
Query builder performance test
node bench:object:query
> autocannon http://localhost:3000/object/query
Running 10s test @ http://localhost:3000/object/query
10 connections
┌─────────┬──────┬──────┬───────┬──────┬─────────┬─────────┬───────┐
│ Stat │ 2.5% │ 50% │ 97.5% │ 99% │ Avg │ Stdev │ Max │
├─────────┼──────┼──────┼───────┼──────┼─────────┼─────────┼───────┤
│ Latency │ 1 ms │ 2 ms │ 3 ms │ 5 ms │ 1.65 ms │ 0.87 ms │ 22 ms │
└─────────┴──────┴──────┴───────┴──────┴─────────┴─────────┴───────┘
┌───────────┬─────────┬─────────┬─────────┬─────────┬──────────┬─────────┬─────────┐
│ Stat │ 1% │ 2.5% │ 50% │ 97.5% │ Avg │ Stdev │ Min │
├───────────┼─────────┼─────────┼─────────┼─────────┼──────────┼─────────┼─────────┤
│ Req/Sec │ 4,239 │ 4,239 │ 4,627 │ 5,123 │ 4,632.19 │ 248.27 │ 4,239 │
├───────────┼─────────┼─────────┼─────────┼─────────┼──────────┼─────────┼─────────┤
│ Bytes/Sec │ 48.1 MB │ 48.1 MB │ 52.5 MB │ 58.1 MB │ 52.5 MB │ 2.81 MB │ 48.1 MB │
└───────────┴─────────┴─────────┴─────────┴─────────┴──────────┴─────────┴─────────┘
Req/Bytes counts sampled once per second.
# of samples: 11
51k requests in 11.01s, 578 MB readAMAZING 😍
This result is even better than the one we had before. We can process ~60% more requests with the raw query approach, reducing also the average latency of one ms!
Final considerations
Throughout this analysis, we've seen a clear pattern emerge: ORMs enhance developer experience as long as it’s ok for you to stay in between their boundaries. If you need advanced functionalities, you need to understand that they come with a notable performance cost.
The raw query approach consistently outperformed the ORM approach in our tests, showing significant improvements in both request handling capacity and response times due to the high performance gap related to data transformations. This highlights the importance of writing your own queries to achieve the best possible performance.
Additionally, a raw query allows you to take advantage of SQL’s power, which is not limited to a single language or platform and can be reused everywhere, contrary to ORMs that are tied to their implementation.
Most of the limitations related to Prisma’s ORM showed in this blog post are going to be disappear soon due to the introduction of TypedSQL, but what said is generally valid for all the ORMs.
Insight, imagination and expertly engineered solutions to accelerate and sustain progress.
Contact