
Security News
pnpm 10.12 Introduces Global Virtual Store and Expanded Version Catalogs
pnpm 10.12.1 introduces a global virtual store for faster installs and new options for managing dependencies with version catalogs.
sql-code-generator
Advanced tools
Generate code from your SQL schema and queries for type safety and development speed.
Generate code from your SQL schema and queries for type safety and development speed.
Generates type definitions and query functions with a single command!
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:
select * from table
)const sqlQueryFindAllUsersByName = async ({ input: InputType }): Promise<OutputType>
)This enables:
Inspired by graphql-code-generator
npm install --save-dev sql-code-generator
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
$ npx sql-code-generator version
$ npx sql-code-generator generate
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 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;
};
sql-code-generator generate
generate typescript code by parsing sql definitions for types and usage
USAGE
$ sql-code-generator generate
OPTIONS
-c, --config=config (required) [default: codegen.sql.yml] path to config yml
-h, --help show CLI help
See code: dist/contract/commands/generate.ts
sql-code-generator help [COMMAND]
display help for sql-code-generator
USAGE
$ sql-code-generator help [COMMAND]
ARGUMENTS
COMMAND command to show help for
OPTIONS
--all see all commands in CLI
See code: @oclif/plugin-help
Team work makes the dream work! Please create a ticket for any features you think are missing and, if willing and able, draft a PR for the feature :)
FAQs
Generate code from your SQL schema and queries for type safety and development speed.
The npm package sql-code-generator receives a total of 34 weekly downloads. As such, sql-code-generator popularity was classified as not popular.
We found that sql-code-generator demonstrated a healthy version release cadence and project activity because the last version was released less than a year ago. It has 0 open source maintainers collaborating on the project.
Did you know?
Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.
Security News
pnpm 10.12.1 introduces a global virtual store for faster installs and new options for managing dependencies with version catalogs.
Security News
Amaro 1.0 lays the groundwork for stable TypeScript support in Node.js, bringing official .ts loading closer to reality.
Research
A deceptive PyPI package posing as an Instagram growth tool collects user credentials and sends them to third-party bot services.