Pgmicro Postgres Sqlite

import { connect } from "pg-micro";
// In-memory databaseconst db = await connect(":memory:");
// File-backed databaseconst db = await connect("./myapp.db");
// DDLawait db.exec(`  CREATE TABLE users (    id SERIAL PRIMARY KEY,    name TEXT NOT NULL,    email TEXT UNIQUE,    created_at TEXT DEFAULT CURRENT_TIMESTAMP  )`);
// Insertawait db.exec(`  INSERT INTO users (name, email) VALUES ('Alice', '[email protected]')`);
// Prepared statement — fetch all rowsconst stmt = await db.prepare("SELECT * FROM users WHERE name = ?");const rows = await stmt.all("Alice");console.log(rows);// [{ id: 1, name: 'Alice', email: '[email protected]', created_at: '...' }]
// Fetch single rowconst row = await stmt.get("Alice");
// Execute with bound parametersawait db.exec("INSERT INTO users (name, email) VALUES (?, ?)", ["Bob", "[email protected]"]);
await db.close();

import { connect } from "pg-micro";
const db = await connect(":memory:");
await db.exec(`  CREATE TABLE events (    id SERIAL PRIMARY KEY,    type TEXT NOT NULL,    payload TEXT,    ts TEXT DEFAULT CURRENT_TIMESTAMP  )`);
// Positional parametersconst insert = await db.prepare(  "INSERT INTO events (type, payload) VALUES ($1, $2)");await insert.run("user.signup", JSON.stringify({ userId: 42 }));await insert.run("page.view", JSON.stringify({ path: "/home" }));
// Query with filterconst query = await db.prepare(  "SELECT * FROM events WHERE type = $1 ORDER BY id DESC");const signups = await query.all("user.signup");console.log(signups);
await db.close();

import { connect } from "pg-micro";
const db = await connect(":memory:");
await db.exec("CREATE TABLE accounts (id INT PRIMARY KEY, balance INT)");await db.exec("INSERT INTO accounts VALUES (1, 1000), (2, 500)");
// Manual transactionawait db.exec("BEGIN");try {  await db.exec("UPDATE accounts SET balance = balance - 100 WHERE id = 1");  await db.exec("UPDATE accounts SET balance = balance + 100 WHERE id = 2");  await db.exec("COMMIT");} catch (err) {  await db.exec("ROLLBACK");  throw err;}
const rows = await db.prepare("SELECT * FROM accounts").all();console.log(rows); // [{ id: 1, balance: 900 }, { id: 2, balance: 600 }]
await db.close();

import { connect } from "pg-micro";
interface User {  id: number;  name: string;  email: string;  created_at: string;}
const db = await connect(":memory:");
await db.exec(`  CREATE TABLE users (    id SERIAL PRIMARY KEY,    name TEXT NOT NULL,    email TEXT,    created_at TEXT DEFAULT CURRENT_TIMESTAMP  )`);
await db.exec("INSERT INTO users (name, email) VALUES ('Alice', '[email protected]')");
const stmt = db.prepare<User>("SELECT * FROM users");const users: User[] = await stmt.all();console.log(users[0].name); // 'Alice'
await db.close();

// Using node-postgres (pg) against pgmicro serverimport { Client } from "pg";
const client = new Client({  host: "127.0.0.1",  port: 5432,  user: "turso",  database: "main",});
await client.connect();const res = await client.query("SELECT * FROM users");console.log(res.rows);await client.end();

import { connect } from "pg-micro";
async function runAgentSession(agentId: string, sql: string) {  // Each session gets its own isolated in-memory DB — no cleanup needed  const db = await connect(":memory:");
  await db.exec("CREATE TABLE scratch (key TEXT PRIMARY KEY, value TEXT)");
  // Agent writes intermediate results  await db.exec(    "INSERT INTO scratch VALUES ($1, $2)",    [`agent-${agentId}`, sql]  );
  const result = await db.prepare("SELECT * FROM scratch").all();  await db.close();  return result;}