Core Querying

/**
 * Create a postgres connection instance
 * @param options - Connection configuration options
 * @returns Configured SQL instance
 */
function postgres(options?: ConnectionOptions): Sql;

/**
 * Create a postgres connection instance from URL
 * @param url - PostgreSQL connection URL
 * @param options - Additional connection options
 * @returns Configured SQL instance
 */
function postgres(url: string, options?: ConnectionOptions): Sql;

import postgres from "postgres";

// Using connection options
const sql = postgres({
  host: "localhost",
  port: 5432,
  database: "myapp",
  username: "user",
  password: "password"
});

// Using connection URL
const sql = postgres("postgres://user:password@localhost:5432/myapp");

// With additional options
const sql = postgres("postgres://localhost/myapp", {
  max: 20,
  idle_timeout: 30,
  ssl: "require"
});

/**
 * Execute SQL query using tagged template literal
 * @param template - Template strings array from template literal
 * @param parameters - Interpolated values from template literal
 * @returns Promise resolving to query results
 */
<T extends readonly any[] = Row[]>(
  template: TemplateStringsArray, 
  ...parameters: any[]
): PendingQuery<T>;

// Basic query
const users = await sql`SELECT * FROM users`;

// Query with parameters (safe from SQL injection)
const userId = 123;
const user = await sql`
  SELECT * FROM users 
  WHERE id = ${userId}
`;

// Multiple parameters
const activeUsers = await sql`
  SELECT * FROM users 
  WHERE active = ${true} 
  AND created_at > ${new Date('2023-01-01')}
`;

// Type-specific queries
const products = await sql`
  SELECT id, name, price 
  FROM products 
  WHERE category = ${category}
`;

/**
 * Execute raw SQL string (use with caution)
 * @param query - Raw SQL query string
 * @param parameters - Optional array of parameters
 * @param options - Query execution options
 * @returns Promise resolving to query results
 */
unsafe<T extends any[] = Row[]>(
  query: string, 
  parameters?: any[], 
  options?: UnsafeQueryOptions
): PendingQuery<T>;

interface UnsafeQueryOptions {
  /** Execute as prepared statement */
  prepare?: boolean; // default: false
}

// Dynamic query construction
const tableName = "users";
const results = await sql.unsafe(`SELECT * FROM ${tableName}`);

// With parameters
const results = await sql.unsafe(
  "SELECT * FROM users WHERE age > $1 AND city = $2",
  [25, "New York"]
);

// With options
const results = await sql.unsafe(
  "SELECT * FROM products WHERE category = $1",
  ["electronics"],
  { prepare: true }
);

// Dynamic SQL with simple protocol (no parameters)
const results = await sql.unsafe("SHOW server_version");

/**
 * Execute SQL from file
 * @param path - Path to SQL file
 * @param args - Optional parameters for the query
 * @param options - Query execution options
 * @returns Promise resolving to query results
 */
file<T extends readonly any[] = Row[]>(
  path: string | Buffer | URL | number,
  args?: any[],
  options?: FileQueryOptions
): PendingQuery<T>;

interface FileQueryOptions {
  /** Use simple query protocol */
  simple?: boolean;
  /** Cache file contents */
  cache?: boolean;
}

// Execute SQL from file
const results = await sql.file("./queries/get-users.sql");

// With parameters
const results = await sql.file("./queries/get-user-by-id.sql", [userId]);

// With options
const results = await sql.file("./migrations/001-create-tables.sql", [], {
  simple: true
});

/**
 * Create SQL identifier (safely escaped)
 * @param identifier - Column or table name to escape
 * @returns Escaped identifier object
 */
(identifier: string): Identifier;

interface Identifier {
  readonly value: string;
}

// Dynamic table name
const tableName = "user_profiles";
const results = await sql`SELECT * FROM ${sql(tableName)}`;

// Dynamic column name
const columnName = "created_at";
const results = await sql`
  SELECT ${sql(columnName)} 
  FROM users 
  ORDER BY ${sql(columnName)} DESC
`;

// Multiple identifiers
const results = await sql`
  SELECT ${sql("u.name")}, ${sql("p.title")}
  FROM ${sql("users")} u
  JOIN ${sql("posts")} p ON u.id = p.user_id
`;

/**
 * SQL helper for dynamic query building
 * @param first - First argument defining helper behavior
 * @param rest - Additional arguments based on helper type
 * @returns Helper object for use in template literals
 */
<T, K extends Rest<T>>(
  first: T, 
  ...rest: K
): Helper<T, K>;

interface Helper<T, U> {
  readonly first: T;
  readonly rest: U;
}

// Conditional WHERE clauses
const filters = [];
if (minAge) filters.push(sql`age >= ${minAge}`);
if (city) filters.push(sql`city = ${city}`);

const users = await sql`
  SELECT * FROM users
  ${filters.length ? sql`WHERE ${sql.join(filters, sql` AND `)}` : sql``}
`;

// Dynamic column selection
const columns = ["id", "name"];
if (includeEmail) columns.push("email");

const users = await sql`
  SELECT ${sql(columns)} 
  FROM users
`;

// Insert helper with object
const userData = { name: "Alice", email: "alice@example.com", age: 30 };
const result = await sql`
  INSERT INTO users ${sql(userData)}
  RETURNING id
`;

// Insert multiple records
const users = [
  { name: "Bob", email: "bob@example.com" },
  { name: "Carol", email: "carol@example.com" }
];
await sql`INSERT INTO users ${sql(users)}`;

// Update with object (requires WHERE clause)
const updateData = { email: "newemail@example.com", updated_at: new Date() };
await sql`
  UPDATE users SET ${sql(updateData)} 
  WHERE id = ${userId}
`;

// Insert with specific columns
const columns = ["name", "email"];
const values = [["Alice", "alice@example.com"], ["Bob", "bob@example.com"]];
await sql`INSERT INTO users ${sql(values, columns)}`;

// Complex dynamic queries
const query = sql`
  SELECT u.name, COUNT(p.id) as post_count
  FROM users u
  LEFT JOIN posts p ON u.id = p.user_id
  ${groupBy ? sql`GROUP BY u.id, u.name` : sql``}
  ${having ? sql`HAVING COUNT(p.id) > ${having}` : sql``}
`;

interface RowList<T extends readonly any[]> extends Array<T[number]> {
  // Result metadata
  readonly count: number;
  readonly command: string;
  readonly columns: ColumnList<keyof T[number]>;
  readonly state: ConnectionState;
  readonly statement: Statement;
}

interface Statement {
  readonly name: string;
  readonly string: string;
  readonly types: number[];
  readonly columns: ColumnList<string>;
}

interface ExecutionResult<T> extends Array<never> {
  readonly count: number;
  readonly command: string;
  readonly statement: Statement;
  readonly state: ConnectionState;
}

// Access result metadata
const result = await sql`SELECT * FROM users`;
console.log(`Found ${result.count} users`);
console.log(`Command: ${result.command}`);
console.log(`Columns:`, result.columns);

// Iterate over results
for (const user of result) {
  console.log(user.name);
}

// Result is also an array
const firstUser = result[0];
const userCount = result.length;