import { RangeVar, SelectStmt } from '@pgsql/types'; import { deparseSync as deparse } from 'pgsql-deparser'; import * as t from '../src'; it('simple SelectStmt', () => { const stmt: { SelectStmt: SelectStmt } = t.nodes.selectStmt({ targetList: [ t.nodes.resTarget({ val: t.nodes.columnRef({ fields: [t.nodes.aStar()] }) }) ], fromClause: [ t.nodes.rangeVar({ relname: 'some_table', inh: true, relpersistence: 'p' }) ], limitOption: 'LIMIT_OPTION_DEFAULT', op: 'SETOP_NONE' }); (stmt.SelectStmt.fromClause[0] as {RangeVar: RangeVar}).RangeVar.relname = 'another_table'; expect(stmt).toMatchSnapshot(); expect(deparse(stmt, { pretty: false })).toMatchSnapshot(); }); it('SelectStmt with WHERE clause', () => { const selectStmt: { SelectStmt: SelectStmt } = t.nodes.selectStmt({ targetList: [ t.nodes.resTarget({ val: t.nodes.columnRef({ fields: [t.nodes.aStar()] }) }) ], fromClause: [ t.nodes.rangeVar({ schemaname: 'myschema', relname: 'mytable', 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: 'a' })] }), rexpr: t.nodes.typeCast({ arg: t.nodes.aConst({ sval: t.ast.string({ sval: 't' }) }), typeName: t.ast.typeName({ names: [ t.nodes.string({ sval: 'pg_catalog' }), t.nodes.string({ sval: 'bool' }) ], typemod: -1 }) }) }), limitOption: 'LIMIT_OPTION_DEFAULT', op: 'SETOP_NONE' }); expect(deparse(selectStmt, { pretty: false })).toEqual(`SELECT * FROM myschema.mytable WHERE a = CAST('t' AS boolean)`); }); it('queries', () => { 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' }); expect(deparse(query, { pretty: false })).toEqual(`SELECT name, email FROM users WHERE age > 18`); }); it('dynamic creation of tables', () => { // 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 = deparse(createStmt, { pretty: false }); expect(sql).toMatchSnapshot(); });