← Back to README

# Database Adapters

Tinqer ships dedicated adapters for PostgreSQL (@webpods/tinqer-sql-pg-promise) and SQLite (@webpods/tinqer-sql-better-sqlite3). Both share the same builder APIs while handling dialect-specific SQL generation and parameter formatting.

# Table of Contents

• 1. PostgreSQL Adapter
  • 1.1 Installation
  • 1.2 Setup & Query Execution
  • 1.3 PostgreSQL Dialect Notes
• 2. SQLite Adapter
  • 2.1 Installation
  • 2.2 Setup & Query Execution
  • 2.3 SQLite Dialect Notes
• 3. Key Differences
  • 3.1 Parameter Placeholders
  • 3.2 Data Types
  • 3.3 Case-Insensitive Matching
  • 3.4 RETURNING Behaviour

# 1. PostgreSQL Adapter

# 1.1 Installation

npm install @webpods/tinqer-sql-pg-promise pg-promise

# 1.2 Setup & Query Execution

import pgPromise from "pg-promise";
import { createSchema } from "@webpods/tinqer";
import {
  executeSelect,
  executeSelectSimple,
  executeInsert,
  executeUpdate,
  executeDelete,
  selectStatement,
} from "@webpods/tinqer-sql-pg-promise";

interface Schema {
  users: { id: number; name: string; email: string; age: number; active: boolean };
}

const pgp = pgPromise();
const db = pgp("postgresql://user:pass@localhost:5432/mydb");
const schema = createSchema<Schema>();

// Execute without external params
const activeUsers = await executeSelectSimple(db, schema, (q) =>
  q
    .from("users")
    .where((u) => u.active)
    .orderBy((u) => u.name),
);

// Execute with params
const matchingUsers = await executeSelect(
  db,
  schema,
  (q, p) =>
    q
      .from("users")
      .where((u) => u.age >= p.minAge)
      .select((u) => ({ id: u.id, name: u.name })),
  { minAge: 21 },
);

// INSERT with RETURNING
const createdUsers = await executeInsert(
  db,
  schema,
  (q) =>
    q
      .insertInto("users")
      .values({ name: "Alice", email: "alice@example.com", age: 30, active: true })
      .returning((u) => ({ id: u.id, createdAt: u.createdAt })),
  {},
);

// UPDATE
const updatedCount = await executeUpdate(
  db,
  schema,
  (q) =>
    q
      .update("users")
      .set({ active: false })
      .where((u) => u.age > 65),
  {},
);

// DELETE
const deletedCount = await executeDelete(
  db,
  schema,
  (q) => q.deleteFrom("users").where((u) => !u.active),
  {},
);

// Generate SQL without executing
const { sql, params } = selectStatement(
  schema,
  (q) => q.from("users").where((u) => u.email.endsWith("@example.com")),
  {},
);

# 1.3 PostgreSQL Dialect Notes

• Booleans map to BOOLEAN values (true/false).
• Case-insensitive comparisons use ILIKE when you call helper functions such as contains.
• RETURNING clauses are fully supported on INSERT, UPDATE, and DELETE through the execution helpers.
• Parameter placeholders use the $() syntax expected by pg-promise (e.g., $(minAge)).
• Window functions (ROW_NUMBER(), RANK(), DENSE_RANK()) are fully supported.

# 2. SQLite Adapter

# 2.1 Installation

npm install @webpods/tinqer-sql-better-sqlite3 better-sqlite3

# 2.2 Setup & Query Execution

import Database from "better-sqlite3";
import { createSchema } from "@webpods/tinqer";
import {
  executeSelect,
  executeInsert,
  executeUpdate,
  executeDelete,
  selectStatement,
} from "@webpods/tinqer-sql-better-sqlite3";

interface Schema {
  users: { id: number; name: string; email: string; age: number; isActive: number };
}

const db = new Database(":memory:");
const schema = createSchema<Schema>();

db.exec(`
  CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT,
    age INTEGER,
    isActive INTEGER NOT NULL DEFAULT 1
  );
`);

const inserted = executeInsert(
  db,
  schema,
  (q) => q.insertInto("users").values({ name: "Sam", email: "sam@example.com", age: 28 }),
  {},
);
// inserted === 1

const users = executeSelect(
  db,
  schema,
  (q, params) =>
    q
      .from("users")
      .where((u) => u.isActive === params.active)
      .orderBy((u) => u.name),
  { active: 1 },
);

const updated = executeUpdate(
  db,
  schema,
  (q) =>
    q
      .update("users")
      .set({ isActive: 0 })
      .where((u) => u.age > 60),
  {},
);

const removed = executeDelete(
  db,
  schema,
  (q, p) => q.deleteFrom("users").where((u) => u.age < p.cutoff),
  { cutoff: 18 },
);

// Need the SQL text for custom execution?
const { sql, params } = selectStatement(
  schema,
  (q) => q.from("users").where((u) => u.name.startsWith("S")),
  {},
);
const rows = db.prepare(sql).all(params);

# 2.3 SQLite Dialect Notes

• SQLite has no native boolean type; represent booleans as INTEGER 0/1 in your schema.
• All parameters are passed as named values (e.g., @__p1, @minAge). The adapter converts booleans and dates to SQLite-friendly values automatically.
• The execution helpers return row counts. SQLite currently ignores RETURNING clauses when running through the helpers; use a follow-up SELECT if you need inserted rows.
• Window functions (ROW_NUMBER(), RANK(), DENSE_RANK()) require SQLite 3.25 or later.

# 3. Key Differences

# 3.1 Parameter Placeholders

# 3.2 Data Types

# 3.3 Case-Insensitive Matching

Use query helpers for portable case-insensitive comparisons:

import { createSchema } from "@webpods/tinqer";
import { selectStatement } from "@webpods/tinqer-sql-pg-promise";

interface Schema {
  users: { id: number; name: string; email: string };
}

const schema = createSchema<Schema>();

const { sql } = selectStatement(
  schema,
  (q, params, helpers) =>
    q.from("users").where((u) => helpers.functions.icontains(u.name, "alice")),
  {},
);
// PostgreSQL: WHERE "name" ILIKE $(__p1)
// SQLite: WHERE LOWER("name") LIKE '%' || LOWER(@__p1) || '%'

# 3.4 RETURNING Behaviour

• PostgreSQL: executeInsert, executeUpdate, and executeDelete return the projected values when a .returning() clause is present.
• SQLite: executeInsert, executeUpdate, and executeDelete return the row count. To inspect affected rows, issue a follow-up SELECT.