type-safe-sql-query

Bridging the Gap Between SQL and TypeScript Types

About

type-safe-sql-query provides two well-tested util types

• InferReturnTypeFromSqlStatement
• InferParamsTypeFromSqlStatement

that allow us to automatically infer the return type and the parameter type of a SQL statement. e.g.:

type T0 = InferReturnTypeFromSqlStatement<"SELECT * FROM users", Tables>;
// => { id: number, name: string, age: number, email: string }[]

Feel free to be creative and use them however you like.

Features

• ✅ Automatic type inference from SQL statements.
• ✅ No compilation step
• ✅ No runtime overhead. 🏎️
• ✅ Zero dependencies.
• ✅ Minimalistic and easy to use.
• ✅ Well-tested
• ✅ MIT License

Roadmap / Current Status

⚠️ Please note that this project is in a very early stage and is not yet ready for production use.

The MVP of this project is to support basic CRUD operations on a single table. The following is a list features that are already implemented or are planned to be implemented.

Legend:

• 📝: Planned
• 🏗️: In Progress
• ✅: Done

InferReturnTypeFromSqlStatement

Feature	Status
Support for INSERT statements	✅
Support for UPDATE statements	✅
Support for DELETE statements	✅
Support for SELECT statements	🏗️

InferParamsTypeFromSqlStatement

Feature	Status
Support for INSERT statements	✅
Support for UPDATE statements	✅
Support for DELETE statements	✅
Support for SELECT statements	🏗️

For more details please check the source code and the test files.

Installation

npm install -D type-safe-sql-query

# or
yarn add -D type-safe-sql-query

# or
pnpm add -D type-safe-sql-query

Basic Usage

The following examples demonstrates how to use type-safe-sql-query with MySQL.

import type { InferReturnTypeFromSqlStatement, InferParamsTypeFromSqlStatement } from "type-safe-sql-query";
import type { Tables } from "./tables";

type Result = InferReturnTypeFromSqlStatement<"SELECT * FROM users WHERE name = ? AND age > ?", Tables>;
// Result is: { id: number, name: string, age: number, email: string }[]

type Params = InferParamsTypeFromSqlStatement<"SELECT * FROM users WHERE name = ? AND age > ?", Tables>;
// Params is: [string, number]

type ResultWithAlias = InferReturnTypeFromSqlStatement<"SELECT name AS fullName, age FROM Users", Tables>;
// ResultWithAlias is: { fullName: string, age: number }[]

The examples above assumes that we have a file called tables.ts that contains the type information of our database tables. This file should be auto-generated with schemats for example.

// tables.ts (auto-generated with schemats)

export type Tables = {
  users: {
    id: number;
    name: string;
    age: number;
    email: string;
  };
  // ...
};

Usage with Low Level Database Drivers

The following example demonstrates how to use type-safe-sql-query with the mysql2 driver.

import mysql from "mysql2/promise";
import type { InferParamsTypeFromSqlStatement, InferParamsFromSqlStatement } from "type-safe-sql-query";
import type { Tables } from "./tables";

// Create the connection to database
const connection = await mysql.createConnection({
  host: "localhost",
  user: "root",
  database: "test",
});

// Create a type-safe query wrapper
async function query<S extends string>(
  sql: S,
  params: InferParamsTypeFromSqlStatement<S, Tables>,
): InferReturnTypeFromSqlStatement<S, Tables> {
  const [results] = await connection.query(sql, params);
  return results as any;
}

// Use the type-safe query wrapper to query the database.
const users = await query("SELECT * FROM users WHERE name = ? AND age > ?", ["Michael", 36]);

Other low level database drivers like mysql, postgres or pg should work similarly.

Why did we build this?

• ORMs and SQL Query Builders tend to be leaky abstractions. => Many people prefer writing SQL directly.
• ORMs and SQL Query Builders do not use the database as the source of truth. => Potential schema drift.
• Most ORMs and Query Builders are just wrappers around common packages/drivers like mysql, mysql2, postgres or pg. => Unnecessary performance/runtime overhead.
• Having a dedicated compilation step that depends on the target platform (e.g., Prisma) is quite cumbersome. => Unnecessary complexity.

Motivation: Do we need an Abstraction?

ThePrimeagen (I think he works at Netflix) recently published some videos about ORMs and SQL:

• DONT USE AN ORM | Prime Reacts
• The Only Database Abstraction You Need | Prime Reacts

He did a quick poll in one of those videos and people seem enjoy writing SQL statements. Approximately 50% of the people who voted chose SQL over ORMs and SQL query builders.

(Source: DONT USE AN ORM | Prime Reacts)

Maybe that's also the reason why Drizzle ORM is so popular at the moment (9th Place at 2023 JavaScript Rising Stars) since one of it's main selling points is:

If you know SQL — you know Drizzle.

SQL has been around since the 1970s, and was standardized by the American National Standards Institute (ANSI) in 1986 (A Brief History of SQL and its Usefulness). There must be a good reason why it's still around after approximately half a century and is still one of the most used databases according to the Stackoverflow Survey 2023, while other software technologies barely survive a decade.

(Source: Stackoverflow Survey 2023)

3 Different Classes of Abstraction

ORMs and SQL query builders are an attempt to abstract away the SQL language. Under the hood they are still using SQL to interact with the database.

The CompSciGuy did a great job in his video The Only Database Abstraction You Need in explaining the 3 classes of query families:

• Query-By-Example
• Query-By-API
• Query-By-Language

Query-By-Example

TODO: add short and simple example

Query-By-API

It's just SQL but we call functions instead

TODO: add short and simple example

Typical examples are:

• Prisma
• TypeORM
• Drizzle

Query-By-Language

TODO: add short and simple example

A Single Source of Truth?

When developing server code with TypeScript (for example with frameworks like Nextjs, Express, Fastlify or Nestjs), we'll inevitably come up against the question of how to interact with our database.

Dan Vanderkam wrote a great article about TypeScript and SQL: Six Ways to Bridge the Divide.

There's lots of type information in our SQL database (the structure of the tables) and at the time of writing it's challenging to share that type information between the DB and TypeScript.

So you want a single source of truth? Where should it be? In the database or in the TypeScript code?

Most ORMs (like Prisma, TypeORM and Drizzle for example) use TypeScript as the source of truth. We define our models in TypeScript and the ORM generates the types from that. E.g.:

• Prisma uses the schema.prisma file which is used by a compiler,
• Drizzle uses the pgSchema object for declaring SQL schemas,
• and TypeORM uses Entities which are classes that map to a database tables.

In contrast to the choices made by the ORMs mentioned above, I think it's quite obvious that the database should be the source of truth. It's the only place where the data is stored and TypeScript is a consumer of that data and thus should infer the types from the database.