Building Reusable PostgreSQL Helper Functions in Deno with deno-postgres

Recently I have been deep into Deno, I love its APIs, it’s a fresh take into the never-ending JavaScript runtime war, which while it’s not as stacked as the other never-ending JavaScript UI library war, it has now given us one of the best ways to write JavaScript for the server.

I have been re-building some of my projects that were previously running on Node.js/Express to run on Deno/Oak, which as enjoyable as it has been, there has been some stuff that still can be annoying.

You see, Prisma is my ORM of preference, but it still is a hassle to hook up with Deno, which led me to use Deno-Postgres, a fantastic and flexible driver for my DB queries. However, since I’m not using an ORM, I started to work on the never-ending DB queries boilerplate code.

In modern backend development, writing clean and maintainable database code is essential. Raw usage of any library can lead to repetitive boilerplate code and error-prone query execution.

In this article, we’ll explore how to abstract your database logic using reusable helper functions for querying PostgreSQL in Deno. This approach helps keep your codebase clean and promotes a consistent way to handle database interactions.

Take this block of code:

const client = await pool.connect();
try {
  const result = await client.queryObject("SELECT * FROM users WHERE id = $1", [id]);
  return result.rows[0];
} catch (e) {
  console.error("Query error", e);
} finally {
  client.release();
}

This pattern—connecting to the DB, querying, handling errors, and releasing the client—repeats in every DB-related function. It clutters your code and increases the risk of missing the release step or mismanaging the connection pool.

The solution

// db.ts
import { Pool } from "https://deno.land/x/postgres/mod.ts";

export default new Pool({
  user: "postgres",
  password: "yourpassword",
  database: "yourdb",
  hostname: "localhost",
  port: 5432,
}, 3, true);

Now create a utility function to standardize queries:

import pool from "./db.ts";
import { QueryObjectResult } from "https://deno.land/x/postgres/mod.ts";

export async function query<T = unknown>(
  sql: string,
  params: unknown[] = [],
): Promise<QueryObjectResult<T>> {
  const client = await pool.connect();
  try {
    const result = await client.queryObject<T>(sql, ...params);
    return result;
  } catch (error) {
    console.error("DB Query Error:", error);
    throw error;
  } finally {
    client.release();
  }
}

This isolates the connection and error handling logic, so your business logic remains focused.

Complete usage

// userRepo.ts

export async function getUserById(id: string) {
  const result = await query<{ id: string; name: string }>(
    "SELECT id, name FROM users WHERE id = $1",
    [id],
  );
  return result.rows[0];
}

This is clean, easy to test, and enforces consistency across all your queries.

Bonus: Transaction Support

If you need transaction support, you can expand your utility:

export async function withTransaction<T>(
  callback: (client: ReturnType<typeof pool.connect>) => Promise<T>,
): Promise<T> {
  const client = await pool.connect();
  try {
    await client.queryObject("BEGIN");
    const result = await callback(client);
    await client.queryObject("COMMIT");
    return result;
  } catch (err) {
    await client.queryObject("ROLLBACK");
    throw err;
  } finally {
    client.release();
  }
}

Then we can use it like this:

await withTransaction(async (client) => {
  await client.queryObject(
    "UPDATE users SET credits = credits - 10 WHERE id = $1", [userId]);
  await client.queryObject(
    "INSERT INTO transactions (...) VALUES (...)");
});

Conclusion

Using reusable helper functions for database access improves your Deno app’s readability, testability, and maintainability. It reduces boilerplate and enforces a structured way to write queries, making scaling your codebase much easier.

Whether you’re building a REST API, a game server, or a streaming platform, having clean database access patterns pays off in the long run.