npm package discovery and stats viewer.

Discover Tips

  • General search

    [free text search, go nuts!]

  • Package details

    pkg:[package-name]

  • User packages

    @[username]

Sponsor

Optimize Toolset

I’ve always been into building performant and accessible sites, but lately I’ve been taking it extremely seriously. So much so that I’ve been building a tool to help me optimize and monitor the sites that I build to make sure that I’m making an attempt to offer the best experience to those who visit them. If you’re into performant, accessible and SEO friendly sites, you might like it too! You can check it out at Optimize Toolset.

About

Hi, 👋, I’m Ryan Hefner  and I built this site for me, and you! The goal of this site was to provide an easy way for me to check the stats on my npm packages, both for prioritizing issues and updates, and to give me a little kick in the pants to keep up on stuff.

As I was building it, I realized that I was actually using the tool to build the tool, and figured I might as well put this out there and hopefully others will find it to be a fast and useful way to search and browse npm packages as I have.

If you’re interested in other things I’m working on, follow me on Twitter or check out the open source projects I’ve been publishing on GitHub.

I am also working on a Twitter bot for this site to tweet the most popular, newest, random packages from npm. Please follow that account now and it will start sending out packages soon–ish.

Open Software & Tools

This site wouldn’t be possible without the immense generosity and tireless efforts from the people who make contributions to the world and share their work via open source initiatives. Thank you 🙏

© 2024 – Pkg Stats / Ryan Hefner

pg-ast-utils

v1.6.0

Published

This package provides some typing and common utilities for working with Postgres ASTs returned by [pgsql-parser](https://github.com/pyramation/pgsql-parser)--which uses the real Postgres parser. Transformations leverage the, `Deparser` provided by [pgsql-

Downloads

490

Readme

pg-ast-utils

This package provides some typing and common utilities for working with Postgres ASTs returned by pgsql-parser--which uses the real Postgres parser. Transformations leverage the, Deparser provided by pgsql-parser.

AST node typings are generated by parsing struct definitions from the Postgres source code (https://github.com/postgres/postgres/blob/REL_13_STABLE/src/include/nodes/parsenodes.h).

The primary feature is typed traversal over an AST (src/ast) and then there are some analysis/transformation functions that handle use cases that I come across often.

Install with:

npm i pg-ast-utils

Usage

AST

traverse

Takes an AST node returned by pgsql-parser's parse func and returns an iterator over all of its descendants.

// parse is just a re-exported, typed version of pgsql-parser's parse
import {traverse, parse} from 'pg-ast-utils';
const parsed = parse(`select a,b,c from t`);
// parsed is an array of nodes, traverse accepts a single node
const node = parsed[0];
for (let n of traverse(node)) {
  if (n.type === 'ColumnRef') {
    // n.node type is now inferred by TS
    console.log(n.node.fields[0].String.str);
  }
}

outputs:

a
b
c

Traversing the AST yields objects with the shape {type: AstNodeTypeName; node: AstNodeType} where type is the name of the type of node it is and node contains the actual data within the node.

AST typings are defined in the namespace PgAst (in src/ast.ts).

Objects in the node field are references to the AST, so you can modify the AST by simply writing to the object in node.

Analyze

tablesQueried

Returns all tables queried from (e.g. insert into a select * from b would return b but not a since a is not being queried from.)

import {tablesQueried} from 'pg-ast-utils';
tablesQueried('select * from ns.a');
// would return
[[{name: 'a', schema: 'ns'}]];
// and
tablesQueried('select * from ns.a join c; select * from ns.b');
// would return
[[{name: 'a', schema: 'ns'}, {name: 'c'}], [{name: 'b', schema: 'ns'}]];

Transform

queryCountRows

Takes a query and generates a new query that returns the row count of the original query. This is done by moving the root select up into a CTE and then doing count(*) on the CTE.

For example:

queryCountRows(`select * from a`, '_count_col', '_cte');

would result in a sql query like:

with _cte as (select * from a)
select COUNT(*) as _count_col
from _cte

and

queryCountRows(
  `with b as (select * from c) select * from b`,
  '_count_col',
  '_cte'
);

would generate this:

with b as (select * from c),
_cte as (select * from b)
select COUNT(*) as _count_col
from _cte

addRowCountColumn

This is like queryCountRows but also gives you the actual results of the original query, simply adding a new column to the result that contains the row count. A common requirement in an application (for example, a SQL GUI) is to display the first few query results and show the total number of results, so this allows you to do that in a single query (you would probably want to paginate with a cursor).

This is done by moving the root select statement up into a CTE and then making the root SelectStmt select all columns from that CTE and do a COUNT OVER window fn to get the total row count of the CTE that the select statement was moved into.

For example:

addRowCountColumn(`select * from a`, '_count_col', '_cte');

would result in a sql query like:

with _cte as (select * from a)
select *, COUNT(*) OVER () as _count_col
from _cte

and

addRowCountColumn(
  `with b as (select * from c) select * from b`,
  '_count_col',
  '_cte'
);

would generate this:

with b as (select * from c),
_cte as (select * from b)
select *, COUNT(*) OVER () as _count_col
from _cte

normalize

Use the parser to convert a query into a "normalized" form. Could be used for key generation when caching query results.

import {normalize} from 'pg-ast-utils';
import {createHash} from 'crypto';
function queryCacheKey(query: string) {
  return `cached-query:${createHash('md5')
    .update(normalize(query))
    .digest('base64')}`;
}
const q1 = 'select a,b,c from table';
const q2 = `SELECT
    a,
    b,
    c
  FROM table;`;
// now equivalent queries with different formatting will produce the same cache key
console.log(queryCacheKey(q1) ==== queryCacheKey(q2));

TODO

  • transform funcs
    • rewrite tables queried from using a mapping
  • run tests in github actions