![](\"http://img.shields.io/npm/v/pgsql-ast-parser.svg\")
\n ![](\"https://img.shields.io/npm/dm/pgsql-ast-parser.svg\")
\n ![](\"https://david-dm.org/oguimbal/pgsql-ast-parser.svg\")
\n ![](\"https://github.com/oguimbal/pgsql-ast-parser/workflows/CI/badge.svg\"){kind=icon}
\n\n \n \n \n \n
\nā¤ It works both in node or in browser.\n
\n\n\nā This parser does not support (yet) PL/pgSQL. It might not even cover some funky syntaxes.\n
\n\n\n\nā¤ Open an issue if you find an bug or unsupported syntax !\n
\n\n\n\n š This parser has been created to implement pg-mem, an in-memory postgres db emulator. š play with it here\n
\n\n\n\nā this repo if you like this package, it helps to motivate me :)\n
\n\n# š Installation\n\n## With NodeJS\n\n```bash\nnpm i pgsql-ast-parser\n```\n\n## With Deno\n\nJust reference it like that:\n\n```typescript\nimport { /* imports here */ } from 'https://deno.land/x/pgsql_ast_parser/mod.ts';\n```\n\n# š Parsing SQL\n\nā I strongly recommend NOT using this parser without Typescript. It will work, but types are awesome.\n\nParse sql to an AST like this:\n\n```typescript\nimport { parse, Statement } from 'pgsql-ast-parser';\n\n// parse multiple statements\nconst ast: Statement[] = parse(`BEGIN TRANSACTION;\n insert into my_table values (1, 'two')`);\n\n// parse a single statement\nconst ast: Statement = parseFirst(`SELECT * FROM \"my_table\";`);\n```\n\n\n# š Inspecting SQL AST\n\nOnce you have parsed an AST, you might want to traverse it easily to know what's in it.\n\nThere is a helper for that: [astVisitor](/src/ast-visitor.ts).\n\nHere is an example which lists all the tables used in a request, and which counts how many joins it contains:\n\n```typescript\n\nimport { astVisitor, parse } from 'pgsql-ast-parser';\n\nconst tables = new Set();\nlet joins = 0;\nconst visitor = astVisitor(map => ({\n\n // implement here AST parts you want to hook\n\n tableRef: t => tables.add(t.name),\n join: t => {\n joins++;\n // call the default implementation of 'join'\n // this will ensure that the subtree is also traversed.\n map.super().join(t);\n }\n}))\n\n// start traversing a statement\nvisitor.statement(parseFirst(`select * from ta left join tb on ta.id=tb.id`));\n\n// print result\nconsole.log(`Used tables ${[...tables].join(', ')} with ${joins} joins !`)\n\n```\n\nYou'll find that AST visitors (that's the name of this pattern) are quite flexible and powerful once you get used to them !\n\nš Here is the implementation of [toSql](/src/to-sql.ts) which uses an astVisitor to reconstitude SQL from an AST (see below).\n\n\n\n# šØ Converting an AST to SQL\n\nThat's super easy:\n\n```typescript\nimport { toSql } from 'pgsql-ast-parser';\n\nconst sql: string = toSql.statement(myAst);\n\n```\n\nLike with `astVisitor()` or `astModifier()`, you can also convert subparts of AST to SQL (not necessarily a whole statement) by calling other methods of toSql.\n\n\n\n# š Modifying SQL AST\n\n\nThere is a special kind of visitor, which I called [astMapper](/src/ast-mapper.ts), which allows you to traverse & modify ASTs on the fly.\n\nFor instance, you could rename a table in a request like this:\n\n```typescript\nimport { toSql, parseFirst, astMapper } from 'pgsql-ast-parser';\n\n// create a mapper\nconst mapper = astMapper(map => ({\n tableRef: t => {\n if (t.name === 'foo') {\n return {\n // Dont do that... see below\n // (I wrote this like that for the sake of explainability)\n ...t,\n name: 'bar',\n }\n }\n\n // call the default implementation of 'tableRef'\n // this will ensure that the subtree is also traversed.\n return map.super().tableRef(t);\n }\n}))\n\n// parse + map + reconvert to sql\nconst modified = mapper.statement(parseFirst('select * from foo'));\n\nconsole.log(toSql.statement(modified!)); // => SELECT * FROM \"bar\"\n\n```\n\nGood to know: If you use Typescript, return types will force you to return something compatible with a valid AST.\n\nHowever, if you wish to remove a node from a tree, you can return null. For instance, this sample removes all references to column `'foo'`:\n\n```typescript\n// create a mapper\nconst mapper = astMapper(map => ({\n ref: c => c.name === 'foo' ? null : c,\n}))\n\n// process sql\nconst result = mapper.statement(parseFirst('select foo, bar from test'));\n\n// Prints: SELECT \"bar\" FROM \"test\"\nconsole.log(toSql.statement(result!));\n```\n\nIf no valid AST can be produced after having removed it, `result` will be null.\n\n\n## A note on `astMapper` performance:\n\nThe AST default modifier tries to be as efficient as possible:\nIt does not copy AST parts as long as they do not have changed.\n\nIf you wan to avoid unnecessary copies, try to return the original argument\nas much as possible when nothing has changed.\n\nFor instance, instead of writing this:\n\n```typescript\n member(val: a.ExprMember) {\n const operand = someOperandTransformation(val.operand);\n if (!operand) {\n return null;\n }\n return {\n ...val,\n operand,\n }\n }\n```\n\nPrefer an implement that checks that nothing has changed, for instance by using the `assignChanged()` helper.\n\n```typescript\n member(val: a.ExprMember) {\n const operand = someOperandTransformation(val.operand);\n if (!operand) {\n return null;\n }\n return assignChanged(val, {\n operand,\n });\n }\n```\n\nIt's pretty easy to implement.\nTo deal with this kind optimization with arrays, there is a `arrayNilMap()` helper exposed:\n\n```typescript\nconst newArray = arrayNilMap(array, elem => transform(elem));\nif (newArray === array) {\n // transform() has not changed any element in the array !\n}\n```\n\n# Parsing literal values\n\nPostgres implements several literal syntaxes (string-to-something converters), whiches parsers are exposed as helper functions by this pgsql-ast-parser:\n\n- `parseArrayLiteral()` parses arrays literals syntaxes (for instance `{a,b,c}`)\n- `parseGeometricLiteral()` parses [geometric types](https://www.postgresql.org/docs/current/datatype-geometric.html) (for instance, things like `(1,2)` or `<(1,2),3>`)\n- `parseIntervalLiteral()` parses [interval inputs](https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-INTERVAL-INPUT) literals (such as `P1Y2DT1H` or `1 yr 2 days 1 hr`)\n\n\n# FAQ\n\n- **How to parse named parameters like `:name` ?** š _See [here](https://github.com/oguimbal/pgsql-ast-parser/issues/8#issuecomment-774280514) ([TLDR](https://runkit.com/oguimbal/pgsql-ast-parser.circumvent-named-arguments))_\n- **Can I get detailed a location for each AST node ?** š _Yes. Pass the option `{locationTracking: true}` to `parse()`, and use the `locationOf(node)` function._\n- **Can I get the comments that the parser has ignored ?** š _Yes. Use `parseWithComments()` instead of `parse()`_\n\n# Development\n\nPull requests are welcome :)\n\nTo start hacking this lib, you'll have to:\n- Use vscode\n- Install [mocha test explorer with HMR support](https://marketplace.visualstudio.com/items?itemName=oguimbal.vscode-mocha-test-adapter) extension\n- `npm start`\n- Reload unit tests in vscode\n\n... once done, tests should appear. HMR is on, which means that changes in your code are instantly propagated to unit tests.\nThis allows for ultra fast development cycles (running tests takes less than 1 sec).\n\nTo debug tests: Just hit \"run\" (F5, or whatever)... vscode should attach the mocha worker. Then run the test you want to debug.\n","readmeFilename":"readme.md"}