Typescript generator for Postgresql SQL statements
TLDR: This is somewhat similar to an ORM, but rather bridges typescript with SQL: write SQL statements and use these statements as typescript code.
This plugs into your application, reads SQL statements, database connection env variables and generates typescript files with functions matching SQl statements.
How to use it?
- step 1: create SQL files with annotations about the arguments (names & types) and the data returned (names & types)
- step 2: call
postgres-typescript-codegen <srcDir1> <srcDir2>
- step 3: use typescript in your application logic
The command postgres-typescript-codegen
will recursively parse all folders passed as argument.
for better developer experience, create a SQL file watcher that automatically calls
postgres-typescript-codegen
.
For example:
-- file ./queries/fetchUsers.query.sql -- @arg company: string -- @arg isActive: boolean -- @return id: number -- @return email: string -- @return company: string -- @return isActive: boolean SELECT id, email, company, is_activeFROM app_usersWHERE company = :company AND is_active = :isActive
Will generate the following files
// file ./queries/index.ts
// file ./queries/fetchUsers.query.ts
which you can use in your application as follow:
fetchUsers.then.catch
Annotations
arg
describes an input of the SQL query. eg@arg myInput:string
return
describes an output object or field of the SQL query. eg.@return id:number
unique
flag the SQL query as returning only one row. When used, this will make the typescript function return a single object and not an array of objects. Usage:@unique
Note that when specifying arg
or return
data types, you can regroup the returned fields into a datatype you have defined in your typescript application by using directly the default export @return ./User
or a specific export @return ./DataTypes{User}
. You can do the same with external dependencies; for example @return moment{Moment}
As a practcal example, these 2 files would generate what you'd expect:
// file ./DataTypes.ts
-- file ./queries/fetchUsers.query.sql -- @arg company: string -- @arg isActive: boolean -- @return ../DataTypes{User} SELECT id, email, company, is_activeFROM app_usersWHERE company = :company AND is_active = :isActive
Collisions
Using default imports, you may create a collision that this library won't solve for you. For example, the following annotations:
-- @arg ../DataType -- @return ../other/DataType
will generate the following conflicting imports:
this holds true for importing types. The following is NOT valid:
-- @arg ../File1{User} -- @return ../File2{User}
as it'll generate:
Thus you need to make sure you specifically import non-colliding sub types:
-- @arg ../File1{User} -- @return ../File2{ShallowUser}
as it'll generate thje imports: