Right, so you’ve decided you want to talk directly to your database, without an ORM holding your hand. Good. Sometimes you need that raw power, that surgical precision. Prisma and Drizzle are fantastic, but they’re translators. Sometimes you just need to speak the native tongue: SQL. The problem is, doing this in TypeScript usually means sacrificing type safety, throwing any around like confetti, and praying you spelled that WHERE clause correctly. It’s a recipe for runtime surprises, and I hate surprises.

Let’s fix that. We’re going to use two tools that are so good, they feel like cheating: postgres.js and typed SQL templates.

Why postgres.js is Your New Best Friend

Forget pg. The classic node-postgres library is fine, I suppose, but it’s a bit… bureaucratic. It’s callback-y, it’s promise-y, it’s a bit of a mess. postgres.js by porsager is what pg would be if it were designed by someone who actually enjoys writing code. It’s a single, dependency-free library that does one thing exceptionally well: execute SQL.

Its secret sauce is that it uses tagged templates. This means you call it like this:

import postgres from 'postgres';

const sql = postgres('postgres://username:password@localhost:5432/database'); // Your connection string

// And then you write SQL like this:
const users = await sql`SELECT * FROM users WHERE email = ${email}`;

See that? The ${email} isn’t string interpolation in the traditional, dangerous sense. The library parameterizes it for you. This is the first and most important line of defense against SQL injection attacks. You get the security of prepared statements with the simplicity of writing a template literal. It’s brilliant.

The Magic of Typed Templates

Okay, security is solved. Now for the type safety. This is where the magic happens. Look at the result of that query above. By default, users has the type any[]. Gross. We’re better than that.

We’re going to use a library like @pgtyped/runtime or kysely-codegen (my current favorite) to generate types from our actual SQL. You write your SQL in a .sql file, run a command, and it generates a corresponding .ts file with fully typed functions. No more guessing the return type.

Here’s how it works in practice. First, you write your SQL query in a file, say sql/get-user-by-email.sql.

/* @name getUserByEmail */
SELECT id, email, name, created_at FROM users WHERE email = :email;

Note the :email parameter. This is a named parameter, not a template literal.

Then, you run your codegen script (which you’d set up with kysely-codegen or similar). It reads your schema and this SQL file and generates a TypeScript file for you:

// Output in generated/get-user-by-email.ts
import { Generated, ColumnType } from 'kysely';

export interface UsersTable {
  id: Generated<number>;
  email: string;
  name: string | null;
  created_at: ColumnType<Date, Date | string>;
}

export interface Params {
  email: string;
}

export interface Result {
  id: number;
  email: string;
  name: string | null;
  created_at: Date;
}

export type Query = {
  params: Params;
  result: Result;
};

Now, you can import this generated function and use it with your postgres.js client, wrapping it for full type safety:

import { sql } from './db'; // your configured postgres.js client
import { Query } from './generated/get-user-by-email';

// Helper to make our lives easier
const executeTypedQuery = async <T>(query: TemplateStringsArray, ...params: any[]): Promise<T[]> => {
  return sql(query, ...params) as unknown as Promise<T[]>;
};

// Your actual, fully-typed database function
async function getUserByEmail(email: string) {
  // The generated `Query` type gives us the shape of params and result!
  const query = `SELECT id, email, name, created_at FROM users WHERE email = ${email}`;
  const users = await executeTypedQuery<Query['result']>(query);
  return users[0] || null; // Now 'users' is typed as Result[]
}

const user = await getUserByEmail('foo@example.com');
if (user) {
  console.log(user.name); // TypeScript knows this is string | null
  console.log(user.some_made_up_field); // TS Error: Property 'some_made_up_field' does not exist on type 'Result'
}

Boom. You just wrote raw SQL. It’s parameterized. And it’s fully type-safe from the database all the way to your console log. Your editor will autocomplete, your compiler will scream if you mess up, and you can refactor with confidence.

Common Pitfalls and How to Avoid Them

  1. The NULL Quagmire: This is the biggest footgun. Your database NULL and your TypeScript null are not the same thing. The generated types will correctly type columns that can be NULL as some_field: string | null. Always check for null before assuming a value exists. The type system has your back here, but only if you listen to it.

  2. Over-fetching: The beauty of this approach is that your return type is exactly what you asked for. SELECT id, name returns {id: number, name: string}. This is a feature! It forces you to think about the data you actually need. Don’t just SELECT * out of habit. Be specific. Your database and your memory footprint will thank you.

  3. Migrations are Still Your Job: Remember, these tools don’t handle schema migrations. You’re still responsible for that. Use a dedicated migration tool like dbmate or node-pg-migrate alongside this setup. The type generation depends on a current schema, so keep your migrations and your codegen in sync.

  4. Complex Joins and Nested Results: postgres.js returns a flat array of objects. If you do a complex join, you’ll get a flat list. It’s on you to reshape that into a nested object structure if that’s what your application needs. This is often where people get tempted back to ORMs, but a few lines of JavaScript (or a library like zod for transformation) is usually simpler than wrestling with an ORM’s complex abstraction for the same thing.

This setup isn’t for every project, but when you need raw power, precision, and type safety, it’s utterly unbeatable. You’re writing SQL, the language your database understands best, and you’re getting all the benefits of a modern TypeScript development experience. It’s not a compromise; it’s an upgrade.