README.md

Typed SQL - [WORK IN PROGRESS]

Write raw SQL in TypeScript with full type safety — no type casts required!

This repository is a proof of concept (POC) for a typed query function in TypeScript. It allows you to write raw SQL queries and automatically infer the correct return types for SELECT statements. This provides a lightweight alternative to an ORM, keeping your database as the single source of truth for your schema.

Workflow Overview

1. Define your database schema and seed it with data.
2. Write raw SQL queries in .sql files.
3. Run pnpm db:gen to generate TypeScript types for your queries.
4. Use the query function in your application code to execute the queries and access typed results.

Supported Databases

• SQLite (via @libsql/client)

Support for additional databases may be added in the future.

How to Use

1. Set Up Your Database

Create a SQLite database with the required tables and data. For example, in this POC, the database includes tables for users, roles, and countries.

Adjust the database configuration in the file typed_sql/script.ts.

2. Write Your SQL Queries

For each query you want to use in your application, create a .sql file in a queries folder. For example:

-- queries/users.sql

SELECT
    u.id,
    u.username,
    u.country_code AS location,
    r.rolename AS role
FROM
    users u
    INNER JOIN roles r ON u.role_id = r.id
ORDER BY
    u.created_at

3. Generate TypeScript Types

Run the following command to generate TypeScript types for your queries:

pnpm db:gen

This script:

• Connects to your database to retrieve the table schemas.
• Parses the SQL queries to infer the correct return types.

4. Use the Typed Query Function

Import the query function and pass the filename of the SQL file to execute the query:

import { query } from './typed_sql/db'

const { rows, err } = await query('users')

5. Access Typed Results

The rows object will have the following inferred type:

const rows:
	| {
			id: number
			username: string
			location: string | null
			role: string
	  }[]
	| null

The rows array contains the results of the query, with each row typed according to the SQL query's output.

If an error occurs during the query, rows will be null, and the err object of type LibsqlError will contain the error details. This is already logged in the query function, so usually you only need to process it further if you want to know the error code, for example, or forward the error message elsewhere.

TODOs

1. Support queries with parameters.

2. Support INSERT and UPDATE queries that end with RETURNING ....

3. Support subqueries.

4. Make the approach agnostic to @libsql/client.