Star Graphile Documentation

// Before eslint --fix
const add = EXPORTABLE(
  () =>
    function add(b) {
      return a + b;
    },
);

// After eslint --fix
const add = EXPORTABLE(
  (a) =>
    function add(b) {
      return a + b;
    },
  [a],
);

import { sql } from "pg-sql2";

// Example: Indenting a subquery
const innerSelect = sql`SELECT user_id FROM orders`;
const query = sql`WITH recent_orders AS (${sql.indent(innerSelect)}) SELECT * FROM users`;

console.log(sql.compile(query).text);

// ESM
import { te } from "tamedevil";

// CommonJS
const { te } = require("tamedevil");

import {
  makeIntrospectionQuery,
  parseIntrospectionResults,
} from "pg-introspection";
import { Pool } from "pg";

const pool = new Pool({
  connectionString: "postgres://localhost:5432/my_database",
});

async function main() {
  // 1. Generate the introspection SQL query
  const sql = makeIntrospectionQuery();
  
  // 2. Execute the query against the database
  const { rows } = await pool.query(sql);
  
  // 3. Parse the 'introspection' field from the first row
  const introspection = parseIntrospectionResults(rows[0].introspection);

  console.log(
    `The ${introspection.database.datname} DBA is ${
      introspection.database.getDba()?.rolname ?? "-"
    }`,
  );
}

main();

import { sql, type Transformer, type SQL } from "pg-sql2";

// Define a transformer that converts numbers to sql.value()
const numberToValue: Transformer<number> = (sql, value) => {
  if (typeof value === "number") {
    if (!Number.isFinite(value)) {
      throw new Error(`${value} is not a finite number...`);
    }
    return sql.value(value);
  }
  return value;
};

// Use the transformer to allow numbers in the template literal
const query: SQL = sql.withTransformer(
  numberToValue,
  (sql) => sql`select * from users where id = ${42}`
);

console.log(sql.compile(query).text);
// select * from users where id = $1

import { sql } from "pg-sql2";

const schema = "public";
const table = "users";
const column = "name";

// schema.table
const query1 = sql`SELECT * FROM ${sql.identifier(schema, table)}`;
// SELECT * FROM "public"."users"

// table.column
const query2 = sql`SELECT ${sql.identifier(table, column)} FROM users`;
// SELECT "users"."name" FROM users

// schema.table.column
const query3 = sql`COMMENT ON COLUMN ${sql.identifier(schema, table, column)} IS ''`;
// COMMENT ON COLUMN "public"."users"."name" IS '';

const { default: sql } = require("pg-sql2");
// or import sql from 'pg-sql2';

const tableName = "user";
const fields = ["name", "age", "height"];

// Safely escape and join identifiers
const sqlFields = sql.join(
  fields.map((fieldName) => sql.identifier(tableName, fieldName)),
  ", ",
);

// Use sql.value to create placeholders for user-provided data
const sqlConditions = sql`created_at > NOW() - interval '3 years' and age > ${sql.value(22)}`;

// Nest queries safely
const innerQuery = sql`select ${sqlFields} from ${sql.identifier(tableName)} where ${sqlConditions}`;

// Use Symbols for unique automatic identifiers (aliases)
const sqlAlias = sql.identifier(Symbol());

const query = sql`
with ${sqlAlias} as (${innerQuery})
select
  (select json_agg(row_to_json(${sqlAlias})) from ${sqlAlias}) as all_data,
  (select max(age) from ${sqlAlias}) as max_age
`;

// Compile to { text, values }
const { text, values } = sql.compile(query);

console.log(text);
// Output: with __local_0__ as (select "user"."name", "user"."age", "user"."height" from "user" where created_at > NOW() - interval '3 years' and age > $1)
// select (select json_agg(row_to_json(__local_0__)) from __local_0__) as all_data, (select max(age) from __local_0__) as max_age

console.log(values); // [ 22 ]

// Execution example using 'pg' module:
// const { rows } = await pg.query(text, values);

import type {} from "graphile-config";

const PluginA: GraphileConfig.Plugin = { name: "PluginA", after: ["PluginC"] };
const PluginB: GraphileConfig.Plugin = { name: "PluginB", before: ["PluginA"] };
const PluginC: GraphileConfig.Plugin = { name: "PluginC", after: ["PluginB"] };

const Preset1: GraphileConfig.Preset = {
  plugins: [PluginA],
};

const Preset2: GraphileConfig.Preset = {
  extends: [Preset1],
  plugins: [PluginB, PluginC],
};

// Resulting order: PluginB, PluginC, PluginA

// ❌ sql.raw(userInput) -> SQL injection!
// ✅ sql`ASC` for SQL text
// ✅ sql.identifier(tableName) for table/column names
// ✅ sql.value(userInput) for values
// ✅ sql.literal(50) for constants

// Example of safe composition using maps with null prototypes
const COLUMNS = {
  __proto__: null,
  name: sql.identifier("users", "name"),
  email: sql.identifier("users", "email"),
  created_at: sql.identifier("users", "created_at"),
};

const DIRECTIONS = {
  __proto__: null,
  asc: sql.literal("ASC"),
  desc: sql.literal("DESC"),
};

const column = COLUMNS[userInputColumn] || COLUMNS["created_at"];
const direction = DIRECTIONS[userInputDirection] || DIRECTIONS["asc"];

const query = sql`SELECT * FROM users ORDER BY ${column} ${direction}`;