DOCSEARCHbyAlgolia
Submit Docs

pgtyped

repository·master·Indexed 25 days ago

https://github.com/adelsz/pgtyped

PgTyped enables type-safe raw SQL in TypeScript by using a running PostgreSQL database as the source of truth for types. It automatically generates TypeScript interfaces for SQL query parameters and results, eliminating the need for manual schema mapping. The tool includes a CLI for type generation in build or watch mode and a runtime package providing a tagged template for executing queries via an IDatabaseConnection implementation.

Tokens
13.8K
Snippets
44
Records
86
Agent score
84%

What's inside pgtyped

  1. Overview of PgTyped

    master

    PgTyped allows you to use raw SQL in TypeScript with guaranteed type-safety. Instead of manually mapping or translating your database schema to TypeScript, PgTyped automatically generates types and interfaces for your SQL queries by using your running Postgres database as the source of type information.

    Key characteristics:

    • Static typing: SQL queries are validated and fully usable by the TypeScript typechecker.
    • No magic: PgTyped is not a query builder or an ORM; it is a tool for generating types from raw SQL.
    • Smooth developer experience: Designed for engineers who want to write raw SQL while maintaining type safety.

  2. Core Features of PgTyped

    master

    PgTyped provides a typesafe workflow for interacting with PostgreSQL using TypeScript. Key capabilities include:

    • Typesafe SQL: Automatically generates TypeScript types for both query parameters and result sets, regardless of query complexity.
    • SQL File Support: Can extract queries from both .sql files and .ts files.
    • Watch Mode: Supports a watch mode to automatically regenerate query types as you modify your SQL.
    • Single Source of Truth: Uses your live PostgreSQL database as the source of type data, eliminating the need to manually define your database schema in TypeScript.
    • SQL Injection Prevention: Instead of performing explicit parameter substitution in the application, PgTyped sends queries and parameters separately to the database driver, allowing the PostgreSQL server to handle substitution safely.
    • Interpolation Helpers: Provides helpers for interpolating parameters like arrays and objects.
    • ESM Support: Built with ESM modules in mind. The runtime and generated code are ESM-first, though CommonJS is also supported.

  4. Run the dockerized pgtyped example setup

    master

    The dockerized setup provides a pre-configured PostgreSQL database with a schema and seed records (defined in sql/schema.sql) and runs pgtyped in a separate container. This is useful for testing the library without setting up your own database.

    1. Clone the monorepo: git clone git@github.com:adelsz/pgtyped.git pgtyped
    2. Navigate to the example package: cd pgtyped/packages/example
    3. Install dependencies: npm install
    4. Build the project: npm run build
    5. Start the dockerized environment: docker compose run watch

    Once running, you can edit queries in the .sql and .ts files to see live type generation.

    git clone git@github.com:adelsz/pgtyped.git pgtyped
cd pgtyped/packages/example
npm install
npm run build
docker compose run watch

  6. Define named queries in SQL files

    master

    To create a query that PgTyped can recognize and name, write your SQL in a .sql file and prefix the query with a special comment containing the @name annotation. The name provided in the comment will be used to generate the corresponding TypeScript function name.

    Example books/queries.sql:

    /* @name FindBookById */
SELECT * FROM books WHERE id = :bookId;
    /* @name FindBookById */
SELECT * FROM books WHERE id = :bookId;

  7. Use pgtyped with your own database

    master

    To use pgtyped with an existing database, follow these steps:

    1. Install dependencies: npm install
    2. Create a configuration file named config.json containing your database connection details.
    3. Run pgtyped in watch mode using your config: npx pgtyped -w -c config.json.

    Running in watch mode (-w) allows for live query type generation as you edit your query files.

    npm install
npx pgtyped -w -c config.json

  8. Use PgTyped to execute type-safe SQL queries

    master

    PgTyped allows you to write raw SQL in .sql files and automatically generates TypeScript interfaces for query parameters and results.

    1. Define a query in a .sql file using a named comment block /* @name QueryName */.
    2. Run PgTyped to generate a .queries.ts file containing PreparedQuery instances.
    3. Import the generated query and use its .run() method, passing in your parameters and a database client (e.g., from the pg package).
    /* @name FindBookById */
SELECT * FROM books WHERE id = :bookId;
    import { Client } from 'pg';
import { findBookById } from './books.queries';

export const client = new Client({
  host: 'localhost',
  user: 'test',
  password: 'example',
  database: 'test',
});

async function main() {
  await client.connect();
  // findBookById.run(params, client)
  const books = await findBookById.run(
    {
      bookId: 5,
    },
    client,
  );
  console.log(`Book name: ${books[0].name}`);
  await client.end();
}

main();

  9. Execute queries using the `sql` tagged template

    master

    The @pgtyped/runtime package provides the sql tagged template for executing SQL queries with full type safety.

    To use it, you must provide a generic parameter <TQueryType> which corresponds to the interface generated by PgTyped for that specific query. This ensures that the inputs (TParams) and the resulting rows (TResult) are correctly typed.

    To execute the query, call the sql.run method, passing in your query parameters and a database connection object.

  10. Configure SSL for Database Connections

    master

    You can configure SSL in the db.ssl field. You can provide a boolean or a full TLS connection options object (compatible with Node.js tls.connect).

    To use a custom CA:

    "db": {
  "ssl": {
    "host": "someremote.host.com",
    "port": 5432,
    "ca": ["insert CA here"]
  }
}

    To ignore SSL certificate validation (e.g., self-signed certs):

    "db": {
  "ssl": {
    "rejectUnauthorized": false
  }
}

  12. Install and set up PgTyped

    master

    To use PgTyped in your project, install the CLI and TypeScript as development dependencies, and install the runtime dependency. You will also need to create a config.json file to configure PgTyped.

    1. Install CLI and TypeScript: npm install -D @pgtyped/cli typescript
    2. Install Runtime: npm install @pgtyped/runtime
    3. Create a config.json file.
    4. Run PgTyped in watch mode: npx pgtyped -w -c config.json
    npm install -D @pgtyped/cli typescript
npm install @pgtyped/runtime
npx pgtyped -w -c config.json