sql-code-generator

Generate code from your SQL schema and queries for type safety and development speed.

Generates type definitions and query functions with a single command!

Goals

The goal of sql-code-generator is to use the SQL you've already defined in order to speed up development and eliminate errors. This is done by extracting type definitions from sql and exposing those type definitions in code, automatically generating the interface that bridges your sql and your primary development language.

This includes:

generating type definitions from SQL resources (e.g., tables, views, functions, procedures)
generating type definitions from SQL queries (e.g., select * from table)
generating typed functions that execute SQL queries from SQL queries (e.g., const sqlQueryFindAllUsersByName = async ({ input: InputType }): Promise<OutputType>)

This enables:

controlling and mastering database logic fully in SQL
strictly bound types between sql and typescript for compile time error checking
strongly typed, auto-generated database query functions for speed, consistency, and encoding of best practices
autocompletion and explore-ability of sql resources in your IDE

Inspired by graphql-code-generator

Installation

1. Save the package as a dev dependency

npm install --save-dev sql-code-generator

2. Define a config yml

This file will define the sql language to extract type definitions from, where your sql resources and sql queries are, and where to output the generated types and query functions. By default, the generator looks for a file named codegen.sql.yml in your projects root.

For example:

language: postgres # note: mysql is supported, too
dialect: 10.7
resources: # where to find your tables, functions, views, procedures
  - "schema/**/*.sql"
queries: # where to find your queries
  - "src/dao/**/*.ts"
  - "!src/**/*.test.ts"
  - "!src/**/*.test.integration.ts"
generates: # where to output the generated code
  types: src/dao/generated/types.ts
  queryFunctions: src/dao/generated/queryFunctions.ts

3. Test it out!

  $ npx sql-code-generator version
  $ npx sql-code-generator generate

Usage

Resources

Resources should be defined in .sql files. We'll extract both the name and the type from the create definition automatically. For example:

CREATE TABLE photo (
  id bigserial NOT NULL,
  uuid uuid NOT NULL,
  created_at timestamp with time zone NOT NULL DEFAULT now(),
  url varchar NOT NULL,
  description varchar NULL,
  CONSTRAINT photo_pk PRIMARY KEY (id),
  CONSTRAINT photo_ux1 UNIQUE (url, description)
);

The above definition would generate the following typescript type definitions:

// types for table 'photo'
export interface SqlTablePhoto {
  id: number;
  uuid: string;
  created_at: Date;
  url: string;
  description: string | null;
}

Queries

Queries can be defined in .ts or .sql files. If in a .ts file, this file should contain a named export called sql exporting the sql of your query. We'll extract the name of the query from a specially formatted comment in your sql, e.g.: -- query_name = find_photos_by_url would resolve a query name of find_photos_by_url. For example:

export const sql = `
-- query_name = find_photos_by_url
SELECT
  p.uuid,
  p.url,
  p.description
FROM photo p
WHERE p.url = :url
`.trim();

The above definition would generate the following typescript type definitions:

// types for query 'find_photos_by_url'
export interface SqlQueryFindPhotosByUrlInput {
  url: SqlTablePhoto['url'];
}
export interface SqlQueryFindPhotosByUrlOutput {
  uuid: SqlTablePhoto['uuid'];
  url: SqlTablePhoto['url'];
  description: SqlTablePhoto['caption'];
}

And that same definition would also generate the following typescript query function:

import { pg as prepare } from 'yesql';
import { sql as sqlQueryFindPhotosByUrlSql } from '../../dao/user/findAllByName';
import { SqlQueryFindPhotosByUrlInput, SqlQueryFindPhotosByUrlOutput } from './types';

// typedefs common to each query function
export type DatabaseExecuteCommand = (args: { sql: string; values: any[] }) => Promise<any[]>;
export type LogMethod = (message: string, metadata: any) => void;

// client method for query 'find_photos_by_url'
export const sqlQueryFindPhotosByUrl = async ({
  dbExecute,
  logDebug,
  input,
}: {
  dbExecute: DatabaseExecuteCommand;
  logDebug: LogMethod;
  input: SqlQueryFindPhotosByUrlInput;
}): Promise<SqlQueryFindPhotosByUrlOutput[]> => {
  // 1. define the query with yesql
  const { text: preparedSql, values: preparedValues } = prepare(sqlQueryFindPhotosByUrlSql)(input);

  // 2. log that we're running the request
  logDebug('sqlQueryFindPhotosByUrl.input', { input });

  // 3. execute the query
  const output = await dbExecute({ sql: preparedSql, values: preparedValues });

  // 4. log that we've executed the request
  logDebug('sqlQueryFindPhotosByUrl.output', { output });

  // 5. return the output
  return output;
};

Commands

sql-code-generator generate
sql-code-generator help [COMMAND]