README.md

@pgsql/utils

@pgsql/utils is a companion utility library for @pgsql/types, offering convenient functions to work with PostgreSQL Abstract Syntax Tree (AST) nodes and enums in a type-safe manner. This library facilitates the creation of AST nodes and simplifies the process of converting between enum names and their respective integer values, as defined in the PostgreSQL parser output.

Table of Contents

1. @pgsql/utils
   • Features
2. Installation
3. Usage
   • AST Node Creation
     • JSON AST
     • Select Statement
     • Creating Table Schemas Dynamically
   • Enum Value Conversion
     • Example 1: Converting Enum Name to Integer
     • Example 2: Converting Integer to Enum Name
4. Related Projects
5. Disclaimer

Features

• AST Node Creation: Simplifies the process of constructing PostgreSQL AST nodes, allowing for easy assembly of SQL queries or statements programmatically.
• Type-safe Enum Conversion: Convert between string and integer representations of PostgreSQL AST enum values.
• Comprehensive Coverage: Supports all enum types and node types defined in the PostgreSQL AST.
• Seamless Integration: Designed to be used alongside the @pgsql/types package for a complete AST handling solution.

Installation

To add @pgsql/utils to your project, use the following npm command:

npm install @pgsql/utils

Usage

AST Node Creation

With the AST helper methods, creating complex SQL ASTs becomes straightforward and intuitive.

JSON AST

Explore the PostgreSQL Abstract Syntax Tree (AST) as JSON objects with ease using @pgsql/utils. Below is an example of how you can generate a JSON AST using TypeScript:

import * as t from '@pgsql/utils';
import { SelectStmt } from '@pgsql/types';
import { deparse } from 'pgsql-deparser';

const selectStmt: { SelectStmt: SelectStmt } = t.nodes.selectStmt({
  targetList: [
    t.nodes.resTarget({
      val: t.nodes.columnRef({
        fields: [t.nodes.aStar()]
      })
    })
  ],
  fromClause: [
    t.nodes.rangeVar({
      relname: 'some_amazing_table',
      inh: true,
      relpersistence: 'p'
    })
  ],
  limitOption: 'LIMIT_OPTION_DEFAULT',
  op: 'SETOP_NONE'
});
console.log(selectStmt);
// Output: { "SelectStmt": { "targetList": [ { "ResTarget": { "val": { "ColumnRef": { "fields": [ { "A_Star": {} } ] } } } } ], "fromClause": [ { "RangeVar": { "relname": "some_amazing_table", "inh": true, "relpersistence": "p" } } ], "limitOption": "LIMIT_OPTION_DEFAULT", "op": "SETOP_NONE" } }
console.log(await deparse(stmt))
// Output: SELECT * FROM some_amazing_table

Select Statement

import * as t from '@pgsql/utils';
import { SelectStmt } from '@pgsql/types';
import { deparse } from 'pgsql-deparser';

const query: { SelectStmt: SelectStmt } = t.nodes.selectStmt({
  targetList: [
    t.nodes.resTarget({
      val: t.nodes.columnRef({
        fields: [t.nodes.string({ sval: 'name' })]
      })
    }),
    t.nodes.resTarget({
      val: t.nodes.columnRef({
        fields: [t.nodes.string({ sval: 'email' })]
      })
    })
  ],
  fromClause: [
    t.nodes.rangeVar({
      relname: 'users',
      inh: true,
      relpersistence: 'p'
    })
  ],
  whereClause: t.nodes.aExpr({
    kind: 'AEXPR_OP',
    name: [t.nodes.string({ sval: '>' })],
    lexpr: t.nodes.columnRef({
      fields: [t.nodes.string({ sval: 'age' })]
    }),
    rexpr: t.nodes.aConst({
      ival: t.ast.integer({ ival: 18 })
    })
  }),
  limitOption: 'LIMIT_OPTION_DEFAULT',
  op: 'SETOP_NONE'
});

await deparse(createStmt);
// SELECT name, email FROM users WHERE age > 18

Creating Table Schemas Dynamically

// Example JSON schema
const schema = {
  "tableName": "users",
  "columns": [
    { "name": "id", "type": "int", "constraints": ["PRIMARY KEY"] },
    { "name": "username", "type": "text" },
    { "name": "email", "type": "text", "constraints": ["UNIQUE"] },
    { "name": "created_at", "type": "timestamp", "constraints": ["NOT NULL"] }
  ]
};

// Construct the CREATE TABLE statement
const createStmt = t.nodes.createStmt({
  relation: t.ast.rangeVar({ 
    relname: schema.tableName,
    inh: true,
    relpersistence: 'p'
  }),
  tableElts: schema.columns.map(column => t.nodes.columnDef({
    colname: column.name,
    typeName: t.ast.typeName({
      names: [t.nodes.string({ sval: column.type })]
    }),
    constraints: column.constraints?.map(constraint =>
      t.nodes.constraint({
        contype: constraint === "PRIMARY KEY" ? "CONSTR_PRIMARY" : constraint === "UNIQUE" ? "CONSTR_UNIQUE" : "CONSTR_NOTNULL"
      })
    )
  }))
});

// `deparse` function converts AST to SQL string
const sql = await deparse(createStmt, { pretty: true });

console.log(sql);
// OUTPUT: 

// CREATE TABLE users (
//  id int PRIMARY KEY,
// 	username text,
// 	email text UNIQUE,
// 	created_at timestamp NOT NULL 
// )