DEV Community: Ayomide Ajewole

Understanding Subqueries in SQL and Building JSON Directly in PostgreSQL

Ayomide Ajewole — Mon, 13 Oct 2025 15:11:57 +0000

There's a level of obsession with the need for optimization (speed and memory) required to be a good backend engineer. One useful tool in database optimization is SQL subqueries.
A subquery is a query inside another query. It allows you to pull related data or computed results without writing multiple queries or heavy joins. It also allows you to perform calculations dynamically and aggregate data.
There are different kinds of subqueries based on the type of data you want to return.

Scalar Subqueries

Scalar subqueries return a single computed value needed within an outer query, which is useful for comparisons or assignments.
For example, say you have two tables:

users
-----
id | name

posts
-----
id | title | user_id | status

A way to get the post count for each user could be:

SELECT u.*, COUNT(p.id) AS post_count
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
GROUP BY u.id;

But with a scalar subquery, you can retrieve it like this:

SELECT 
  u.*,
  (SELECT COUNT(*) FROM posts WHERE posts.user_id = u.id) AS post_count
FROM users u;

This form is often easier to extend and can sometimes perform better, especially with proper indexes.

Column Subqueries

A column subquery returns a single column for multiple rows. This is useful when the outer query needs to compare values with multiple rows, so it is often used after the IN, ANY, or EXISTS operator.

SELECT u.* 
FROM users u 
WHERE u.id IN (
  SELECT DISTINCT p.user_id 
  FROM posts p 
  WHERE p.status = 'active'
);

-- OR 
SELECT u.* 
FROM users u 
WHERE EXISTS (
  SELECT 1 
  FROM posts p 
  WHERE p.user_id = u.id 
  AND p.status = 'active'
);

Row Subqueries

A row subquery returns a single row with multiple columns. This is where PostgreSQL's JSON constructors come in. With json_build_object, you can build objects that contain multiple columns from another table in each row of the outer query.

SELECT
  u.id,
  u.name,
  (
    SELECT json_build_object(
      'id', p.id,
      'title', p.title,
      'status', p.status
    )
    FROM posts p
    WHERE p.user_id = u.id
    ORDER BY p.created_at DESC
    LIMIT 1
  ) AS latest_post
FROM users u;

This allows you to fetch the latest post for each user without multiple queries or joins. The result would look like this in JSON:

[
  {
    "u_id": 1,
    "u_name": "Alice",
    "latest_post": {
      "id": 12,
      "title": "Optimizing SQL Queries",
      "status": "published",
      "created_at": "2025-10-11T09:25:43.125Z"
    }
  },
  {
    "u_id": 2,
    "u_name": "Bob",
    "latest_post": {
      "id": 15,
      "title": "Working with Subqueries",
      "status": "draft",
      "created_at": "2025-10-10T14:02:19.761Z"
    }
  }
]

Very cool stuff.

Table Subqueries

A table subquery returns multiple rows and columns and can be useful for aggregating, filtering, or joining computed data. It behaves like a temporary, in-memory table you can join or select from, and is powerful for modularizing complex queries. Here, we use another powerful PostgreSQL JSON constructor called json_agg.
A good example is if you want to fetch each user with their last 10 posts.

SELECT
  u.id,
  u.name,
  (
    SELECT json_agg(
      json_build_object(
        'id', p.id,
        'title', p.title,
        'status', p.status,
        'created_at', p.created_at
      )
    )
    FROM posts p
    WHERE p.user_id = u.id
    ORDER BY p.created_at DESC
    LIMIT 10
  ) AS posts
FROM users u;

json_build_object() creates a JSON object for each post and json_agg() aggregates all those post objects into a single JSON array. Each user gets their own array of posts, automatically ordered by created_at DESC. This can outperform ORM relations and joins, especially when the tables and data are large and only specific fields are needed.
The result would look like this:

[
  {
    "u_id": 1,
    "u_name": "Alice",
    "posts": [
      {
        "id": 14,
        "title": "Optimizing SQL Queries",
        "status": "published",
        "created_at": "2025-10-11T09:25:43.125Z"
      },
      {
        "id": 9,
        "title": "Getting Started with PostgreSQL JSON Functions",
        "status": "published",
        "created_at": "2025-09-15T17:12:09.872Z"
      },
      //...up to 10 posts
    ]
  },
  {
    "u_id": 2,
    "u_name": "Bob",
    "posts": [
      {
        "id": 15,
        "title": "Working with Subqueries",
        "status": "draft",
        "created_at": "2025-10-10T14:02:19.761Z"
      },
      //...up to 10 posts
    ]
  }
]

This is very useful when fetching data for back-office dashboards, analytics, etc.

Caveats

As powerful as subqueries and JSON constructors are, there are potential mistakes that can lead to performance issues, and considerations to be made based on your application's use case.

Forgetting to Handle Null Results

Use COALESCE to handle null values.

COALESCE (
(
    SELECT json_agg(
      json_build_object(
        'id', p.id,
        'title', p.title,
        'status', p.status,
        'created_at', p.created_at
      )
    )
    FROM posts p
    WHERE p.user_id = u.id
    ORDER BY p.created_at DESC
    LIMIT 10
  ), '[]'
) AS posts

Not Indexing Foreign Keys

This can be disastrous, as PostgreSQL will perform a full table scan per row if your subquery references another table and the foreign key is not indexed.

Building Huge JSON Objects in One Go

If you try to aggregate too many rows into one JSON array (e.g., all posts in your entire database), you can hit memory limits or experience slowdowns. Instead:

Use pagination (LIMIT, OFFSET) in subqueries
Or aggregate per entity (user, order, etc.), not globally.

Subqueries vs. Joins - Structuring vs. Combining Data

The prevalent argument against subqueries is whether it's better to join tables since PostgreSQL optimizes JOINs so well. Most arguments relegate the need for subqueries to code readability and maintainability, but I believe it is always dependent on the data and your use case.

One thing that always helps is to use PostgreSQL's EXPLAIN ANALYZE. It is your best friend for performance debugging.

EXPLAIN ANALYZE
SELECT ...

It will show you whether your subquery is executed once per row (bad) or optimized via index scans (good), and you can ultimately see for yourself which approach is best.

Joins are great for combining data across tables, for example, retrieving a list of users with their corresponding orders. They shine when you need flat, tabular data and care about speed across large datasets.

Subqueries, on the other hand, are better for structuring data. They let you embed small, scoped queries inside larger ones, so each part of your query can focus on a specific task: filtering, computing, or shaping data into a nested structure, which enables you to reduce post-processing in your backend code and focus on business logic- every backend engineer's desire.

Bonus - TypeORM Example

I love TypeORM, and since most software developers use ORMs, here are a couple of samples of what using subqueries in TypeORM would look like in relation to the earlier used queries:

This gets an array of users with a computed post_count in a single query.

const userRepo = dataSource.getRepository(User);

const users = await userRepo
  .createQueryBuilder('u')
  .addSelect((qb) => {
    return qb
      .select('COUNT(p.id)', 'post_count')
      .from(Post, 'p')
      .where('p.user_id = u.id');
  }, 'post_count')
  .getRawMany();

This fetches all users and their posts, and merges them into a single JSON with one query.

const users = await dataSource
  .getRepository(User)
  .createQueryBuilder('u')
  .select(['u.id', 'u.name'])
  .addSelect((qb) => {
    return qb
      .subQuery()
      .select(`json_agg(json_build_object('id', p.id, 'title', p.title))`)
      .from(Post, 'p')
      .where('p.user_id = u.id');
  }, 'posts')
  .getRawMany();

Summarily, by combining subqueries and PostgreSQL's JSON functions, you're letting the database handle both data retrieval and shaping, something it's really good at. Your backend no longer needs to map, merge, or format objects. It simply returns the JSON the client needs, straight from the database with zero to minimal round-trips. Again, every backend engineer's desire.

Cheers.

How to Use Postman Scripts and Dynamic Variables for Faster Testing

Ayomide Ajewole — Sat, 04 Oct 2025 11:14:25 +0000

If you use Postman to test your APIs, you’re already saving time, but there’s an easy way to make it even more powerful. With a few lightweight scripts, you can automate repetitive tasks, chain requests together, and focus more on building.
In this article, you'll see how to supercharge your API testing with Postman's lightweight scripting environment.
It all lies in the scripts tab of your request.

Environment Variables

Just like in code, you can use environment variables within each Postman collection to hold values that are frequently used or used in multiple requests.
Common use cases include base URLs, IDs, and auth tokens/JWTs. So, instead of using full endpoints for each request, you can use base_url like in the image above. This is especially useful if you run a microservice architecture with different base URLs for different services. It is also very useful if you often need to test with multiple user IDs or auth tokens. Realistically, your collection could then look like this:

To create a new environment, click on the dropdown highlighted in black in the image, and to view the variables in your environment, click on the icon highlighted in red.

You can mark environment variables as sensitive, so the values are masked and your teammates know to be careful with them. You can also share variable values with your teammates in real time.

Now, let's talk about scripts.

Postman provides a lightweight environment that allows you to modify, automate, and transform your requests and responses using JavaScript. The scripts can either be Pre-request or Post-response.

Pre-request Scripts

Pre-request scripts are JavaScript (or TypeScript-like) snippets that execute before your request goes out.
Example: Signing Requests (HMAC or SHA256)
Some APIs require you to sign your requests with a secret key. Doing this manually is a pain, but a pre-request script makes it easy:

// Pre-request script for signing request body
const crypto = require('crypto-js');

const body = pm.request.body.raw;
const secret = pm.environment.get("API_SECRET");

const signature = crypto.HmacSHA256(body, secret).toString();
pm.environment.set("signature", signature); // Do this if you have the signed secret key as a separate environment variable

// Add signature header dynamically
pm.request.headers.add({
  key: "X-Signature",
  value: signature
});

Now, each outgoing request is securely signed without any manual step.

Post-response Scripts

Post-response scripts allow you to validate responses, store values for future requests, and even automate multi-step workflows.

// Test script
pm.test("Login successful", () => {
  pm.response.to.have.status(200);
});

// Sets an environment variable with the token and user's ID from the login response
const response = pm.response.json();
pm.environment.set("USER_1_AUTH_TOKEN", response.token);
pm.environment.set("USER_1_ID", response.id);

Now, the token and user ID are automatically stored in your environment and ready to use in any future request.

Bonus

Postman provides auto-generated dynamic variables that you can drop right into your request body without writing any script. Here are a few examples:
{{$guid}} → Generates a random UUID
{{$timestamp}} → Current UNIX timestamp
{{$randomInt}} → Random integer
{{$randomEmail}} → Random email address
{{$randomPassword}} → Random email address
{{$randomUsername}} → Random email address
{{$isoTimestamp}} → ISO timestamp

{
  "username": "{{$randomUsername}}",
  "timestamp": "{{$isoTimestamp}}",
  "email": "{{$randomEmail}}",
  "randomPassword": "{{$randomPassword}}"
}

These are especially useful when you need mock data for testing your endpoints, and there are so many options. You can find them out here, or simply start typing {{$ in a request body value to see the options.
So, Postman offers a wide range of tools to make API testing easier, and I hope I've been able to help you see some, and that you're encouraged to explore even more to make your testing workflow easier.
Cheers.

How to Make Your Mongoose Models Smarter and Your Code Cleaner with Setters, Getters, and More

Ayomide Ajewole — Fri, 26 Sep 2025 08:46:16 +0000

I've been building, deploying, and maintaining Node.js applications for years now, and my ODM of choice, like for most others, when using MongoDB, the most popular non-relational database in the Node ecosystem, is Mongoose.

I'm pretty sure you know what Mongoose is if you're reading this. Still, in case you don't, it is an object data modelling tool that provides a straightforward, schema-based approach to model application data with validation, query building, and more out of the box. All of this is useful because it can be a hassle to deal with MongoDB's native Node.js drivers, query language, and schema-less structure, which makes data consistency a nightmare. In the words of Mongoose's creators:

Let's face it, writing MongoDB validation, casting and business logic boilerplate is a drag. That's why we wrote Mongoose.

So Mongoose makes it very easy to work with MongoDB, but many developers miss just how feature-packed it is. Beyond simple schema definitions and queries, Mongoose has a toolbox of powerful features that can dramatically improve how you structure, validate, and maintain your codebase.

In this post, we'll go beyond the basics - diving into getters, setters, virtuals, custom validators, middleware, statics, and methods - so you can start writing cleaner and smarter models.

Getters and Setters

Every developer has encountered use cases that require data to be stored separately from its usage in code. A common use case is the standard practice of storing names and emails in lowercase for consistency, but displaying them in title case.
With getters and setters, you can handle that directly at the schema level:

const transactionSchema = new mongoose.schema({
  email: {
    type: String,
    set: v => v.toLowerCase(),
  },
  firstName: {
    type: String,
    set: v => v.toLowerCase(),
    get: v => toTitleCase(v), // assuming you have a toTitleCase util
  },
  lastName: {
    type: String,
    set: v => v.toLowerCase(),
    get: v => toTitleCase(v),
  },
})

The get and set methods are functions that can be used to modify the variable as desired before it gets fetched from or saved to the DB, similar to transformers in TypeORM.

Virtuals

Let's say the product team suddenly wants to display a user's full name on the frontend. Instead of concatenating names manually everywhere in your code, you can define a virtual property. It lets you define computed properties that don't actually exist in the database.
Example:

userSchema.virtual('fullName').get(function () {
  return `${this.firstName} ${this.lastName}`;
});

This will allow the fullName field to get returned whenever the user collection is queried, even though it's not stored in the database.

Middleware(Hooks)

Think of it just like a regular middleware. A logic you want to execute before you go on to the main logic, the model event in this case, like save, find, remove, and more. Mongoose provides built-in event-based middleware. It can be used for complex validation, removing dependent documents, triggers, and more.

userSchema.pre('save', function (next) {
  if (this.phoneNumber) {
  this.phoneNumber = formatPhoneNumber(this.phoneNumber); // format phone number to desired form.
}
  next();
});
userSchema.pre('remove', async function (next) {
  await Post.deleteMany({ author: this._id }); // delete all of the user's posts
  next();
});
userSchema.pre(/^find/, function (next) {
  this.where({ isDeleted: { $ne: true } }); // Exclude soft-deleted users
  next();
});

So, we format certain fields before saving, delete dependent data to avoid orphaned data, and exclude soft-deleted users without ever worrying about adding that condition everywhere the query is made.

Statics and Methods: Class-Like Behavior

Mongoose models behave like classes, and just like in classes, you can add static methods (model-level) and instance methods (document-level):

userSchema.statics.findByEmail = function (email) {
  return this.findOne({ email });
};
userSchema.methods.isAdult = function () {
  return this.age >= 18;
};

So that:

const user = await User.findByEmail('xyz@yopmail.com');
console.log(user.isAdult) // true or false

Clean, expressive, and object-oriented.

Bonus

A few extra schema features that make your models better:

match: /^[a-zA-Z0-9_-]$/

This allows you to enforce valid characters for a field using a regex.

index: true

Index a field to increase query performance when you query by that field. Useful when you have a field that is queried regularly. You can also use compound indexes.

unique: true

To prevent duplicate values for a field. It also creates an index on the field.

And schema-level options:

timestamps: true

Automatically add createdAt and updatedAt

expireAt: { type: Date, default: Date.now, expires: 600 }
// OR
expireAt: { type: Date, default: Date.now, expires: '10m' }

This is a TTL (Time-to-live) index that automatically deletes the document 10 minutes (configurable) after the expireAt field is set or after the document was created when a default value is set. Useful for one-time passwords.

toObject: { getters: true, virtuals: true }
toJSON: { getters: true, virtuals: true }

The toJSON and toObject schema options convert the mongoose document to a JavaScript object and can also contain modifications that apply to every document in the model, with the difference being that toJSON takes effect when toJSON or JSON.stringify() is called in JavaScript code. It's important to set the getters and virtuals fields to true in both.

The final model then looks like this:

const userSchema = new mongoose.schema({
  email: {
    type: String,
    set: v => v.toLowerCase(),
    unique: true,
  },
  firstName: {
    type: String,
    set: v => v.toLowerCase(),
    get: v => toTitleCase(v), // assuming you have a toTitleCase util
  },
  lastName: {
    type: String,
    set: v => v.toLowerCase(),
    get: v => toTitleCase(v),
  },
  username: {
    type: String,
    match: /^[a-zA-Z0-9_-]$/,
    set: v => v.toLowerCase(),
    unique: true,
  },
  age: {
    type: Number,
},
  pin: {
    type: String,
},
phoneNumber: {
    type: String,
},
},
{
  timestamps: true,
  toJSON: { getters: true, virtuals: true},
  toObject: { getters: true, virtuals: true},
});

userSchema.virtual('fullName').get(function () {
  return `${this.firstName} ${this.lastName}`;
});

userSchema.pre('save', function (next) {
  if (this.phoneNumber) {
  this.phoneNumber = formatPhoneNumber(this.phoneNumber); // format phone number to desired form.
}
  next();
});
userSchema.pre('remove', async function (next) {
  await Post.deleteMany({ author: this._id }); // delete all of the user's posts
  next();
});
userSchema.pre(/^find/, function (next) {
  this.where({ isDeleted: { $ne: true } }); // Exclude soft-deleted users
  next();
});
userSchema.statics.findByEmail = function (email) {
  return this.findOne({ email });
};
userSchema.methods.isAdult = function () {
  return this.age >= 18;
};

export const User = mongoose.model('User', userSchema);

Looks like the better, more functional, and more maintainable way to write production code, doesn't it?
So, whether you prefer using it as an extra layer of validation or to enforce consistency, or to reduce boilerplate, or have simply been convinced to write cool code like this, it is very clear that Mongoose is much more powerful than most are aware of. So, don't just use it, leverage it.