### Install pgsql-ast-parser

Source: https://github.com/oguimbal/pg-mem/blob/master/CONTRIBUTING.md

Install the pgsql-ast-parser package as a dependency.

```bash
npm install pgsql-ast-parser@latest
```

--------------------------------

### Install pg-mem with npm

Source: https://github.com/oguimbal/pg-mem/blob/master/readme.md

Install the pg-mem package using npm. This is the first step to using pg-mem in your Node.js project.

```bash
npm i pg-mem --save
```

--------------------------------

### Register and Install Custom Extension

Source: https://context7.com/oguimbal/pg-mem/llms.txt

Create a PostgreSQL extension by bundling functions and types. Use 'db.registerExtension' to define the extension's components and then 'CREATE EXTENSION' to install it.

```typescript
import { newDb, DataType } from "pg-mem";
import { v4 as uuidv4 } from "uuid";

const db = newDb();

// Register uuid-ossp extension
db.registerExtension("uuid-ossp", (schema) => {
  schema.registerFunction({
    name: "uuid_generate_v4",
    returns: DataType.uuid,
    implementation: uuidv4,
    impure: true, // Returns different value each call
  });

  schema.registerFunction({
    name: "uuid_nil",
    returns: DataType.uuid,
    implementation: () => "00000000-0000-0000-0000-000000000000",
  });
});

// Install the extension via SQL
db.public.none(`CREATE EXTENSION "uuid-ossp"`);

// Now use the functions
db.public.none(`
  CREATE TABLE documents (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    title TEXT NOT NULL
  )
`);

db.public.none(`INSERT INTO documents (title) VALUES ('My Document')`);

const doc = db.public.one(`SELECT * FROM documents`);
console.log(doc);
// Output: { id: 'a1b2c3d4-...', title: 'My Document' }
```

--------------------------------

### Run All pg-mem Tests with Bun

Source: https://github.com/oguimbal/pg-mem/blob/master/readme.md

Execute all unit tests for the pg-mem project using the Bun runtime. Ensure Bun is installed before running this command.

```bash
bun test
```

--------------------------------

### Use pg-mem with Knex

Source: https://github.com/oguimbal/pg-mem/wiki/Libraries-adapters

Configure Knex to use pg-mem as its backend database. Ensure Knex is installed.

```typescript
const db = newDb();

// use this instead of require('knex')({ ... })
const knex = await db.adapters.createKnex() as import('knex');

// => use 'knex' as usual
knex.schema.createTable(...)
```

--------------------------------

### Example plv8 Function

Source: https://github.com/oguimbal/pg-mem/wiki/Custom-language-declaration-(functions,-do-statements,-plpgsql,-plv8,-...)

An example of a plv8 function that adds two integers. This function can be executed when plv8 is mocked as shown in the previous snippet.

```pgsql
create function add(x int, y int) returns int
as $$ x + y $$ language plv8;

select add(2, 40); -- 42 !
```

--------------------------------

### Use pg-mem with TypeORM

Source: https://github.com/oguimbal/pg-mem/wiki/Libraries-adapters

Configure TypeORM to use pg-mem as its backend database. Ensure TypeORM is installed.

```typescript
const db = newDb();
const connection = await db.adapters.createTypeormConnection({
    type: 'postgres',
    entities: [/* your entities here ! */]
})

// create schema
await connection.synchronize();

// => you now can use your typeorm connection !
```

--------------------------------

### Use pg-mem with Mikro-ORM

Source: https://github.com/oguimbal/pg-mem/wiki/Libraries-adapters

Configure Mikro-ORM to use pg-mem as its backend database. Ensure @mikro-orm/core and @mikro-orm/postgresql are installed.

```typescript
const db = newDb();
const orm: MikroORM = await db.adapters.createMikroOrm({
    entities: [/* your entities here ! */],
})

// create schema
await orm.getSchemaGenerator().synchronize();

// => you now can use mikro-orm as you are used to !
```

--------------------------------

### Define Custom Extension in pg-mem

Source: https://github.com/oguimbal/pg-mem/blob/master/readme.md

Register a custom extension that can be installed using 'create extension'. This allows defining custom schema elements within the extension.

```typescript
db.registerExtension("my-ext", (schema) => {
  // install your ext in 'schema'
  // ex:  schema.registerFunction(...)
});
```

--------------------------------

### Registering a uuid-ossp extension in pg-mem

Source: https://github.com/oguimbal/pg-mem/wiki/FAQ

Implement custom PostgreSQL extensions like uuid-ossp by registering a function. This example shows how to register the uuid_generate_v4 function using the 'uuid' library.

```typescript
  import { v4 } from 'uuid';

  db.registerExtension('uuid-ossp', (schema) => {
    schema.registerFunction({
      name: 'uuid_generate_v4',
      returns: DataType.uuid,
      implementation: v4,
      impure: true,
    });
  });
```

```sql
create extension "uuid-ossp";
select uuid_generate_v4();
```

--------------------------------

### Prepare and Execute SQL Statements with pg-mem

Source: https://context7.com/oguimbal/pg-mem/llms.txt

Demonstrates how to prepare SQL statements for repeated execution with different parameters using pg-mem. Includes binding values, executing, and iterating through results. Ensure the table is created and data is inserted before preparing and executing statements.

```typescript
import { newDb } from "pg-mem";

const db = newDb();

db.public.none(`CREATE TABLE users (id INT, name TEXT, age INT)`);

// Prepare a statement
const prepared = db.public.prepare(`SELECT * FROM users WHERE age > $1 AND name LIKE $2`);

// Get parameter and result descriptions
const description = prepared.describe();
console.log("Parameters:", description.parameters);
console.log("Result fields:", description.result);

// Bind values and execute
db.public.none(`INSERT INTO users VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Anna', 35)`);

const bound = prepared.bind([28, "A%"]);
const result = bound.executeAll();

console.log(result.rows);
// Output: [{ id: 1, name: 'Alice', age: 30 }, { id: 3, name: 'Anna', age: 35 }]

// Iterate through results (useful for large result sets)
for (const row of prepared.bind([20, "%"Поиск]).iterate()) {
  console.log(row);
}
```

--------------------------------

### Initialize pg-mem in Deno

Source: https://github.com/oguimbal/pg-mem/blob/master/readme.md

Initialize a new in-memory database instance in a Deno environment. This demonstrates how to import and use pg-mem with Deno.

```typescript
import { newDb } from "https://deno.land/x/pg_mem/mod.ts";

const db = newDb();
db.public.many(/* put some sql here */);
```

--------------------------------

### pg-mem Backup and Restore Points

Source: https://context7.com/oguimbal/pg-mem/llms.txt

Create instant O(1) backups using `db.backup()` and restore to a previous state with `backup.restore()`. This is ideal for resetting test state between unit tests without recreating the schema.

```typescript
import { newDb } from "pg-mem";

const db = newDb();

// Create schema once (expensive operation)
db.public.none(`CREATE TABLE test (id TEXT PRIMARY KEY, value TEXT)`);

// Insert shared test data
db.public.none(`INSERT INTO test VALUES ('key1', 'original')`);

// Create a restore point - O(1) instant operation
const backup = db.backup();

// Run test 1: modify data
db.public.none(`UPDATE test SET value = 'modified' WHERE id = 'key1'`);
console.log(db.public.one(`SELECT value FROM test WHERE id = 'key1'`));
// Output: { value: 'modified' }

// Restore to backup - O(1) instant operation
backup.restore();
console.log(db.public.one(`SELECT value FROM test WHERE id = 'key1'`));
// Output: { value: 'original' }

// Run test 2: delete data
db.public.none(`DELETE FROM test WHERE id = 'key1'`);

// Restore again for next test
backup.restore();
// Database is back to original state
```

--------------------------------

### Create Kysely Instance with pg-mem

Source: https://context7.com/oguimbal/pg-mem/llms.txt

Set up a Kysely instance connected to an in-memory database for type-safe SQL queries. Ensure the Database interface matches your schema.

```typescript
import { newDb } from "pg-mem";
import type { Kysely, GeneratedAlways } from "kysely";

interface Database {
  users: {
    id: GeneratedAlways<number>;
    name: string;
  };
  posts: {
    id: GeneratedAlways<number>;
    title: string;
    user_id: number;
  };
}

const db = newDb();

// Create Kysely instance
const kysely = db.adapters.createKysely() as Kysely<Database>;

// Create tables
await kysely.schema
  .createTable("users")
  .addColumn("id", "serial", (cb) => cb.primaryKey())
  .addColumn("name", "varchar(255)")
  .execute();

await kysely.schema
  .createTable("posts")
  .addColumn("id", "serial", (cb) => cb.primaryKey())
  .addColumn("title", "varchar(255)")
  .addColumn("user_id", "integer", (cb) => cb.references("users.id"))
  .execute();

// Insert data
await kysely.insertInto("users").values({ name: "Alice" }).execute();
await kysely.insertInto("posts").values({ title: "Hello", user_id: 1 }).execute();

// Type-safe queries
const results = await kysely
  .selectFrom("users")
  .innerJoin("posts", "users.id", "posts.user_id")
  .select(["users.name", "posts.title"])
  .execute();

console.log(results);
// Output: [{ name: 'Alice', title: 'Hello' }]
```

--------------------------------

### Create node-postgres (pg) Adapter with pg-mem

Source: https://context7.com/oguimbal/pg-mem/llms.txt

Create a drop-in replacement for the node-postgres `pg` module using pg-mem. This allows testing `pg` compatible code without a real database.

```typescript
import { newDb } from "pg-mem";

const db = newDb();

// Create pg-compatible Client and Pool
const { Client, Pool } = db.adapters.createPg();

// Use like regular pg
const client = new Client();
await client.connect();

await client.query(`
  CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
  )
`);

await client.query(`INSERT INTO users (name) VALUES ($1)`, ["Alice"]);
await client.query(`INSERT INTO users (name) VALUES ($1)`, ["Bob"]);

const result = await client.query(`SELECT * FROM users WHERE name = $1`, ["Alice"]);
console.log(result.rows);
// Output: [{ id: 1, name: 'Alice' }]

await client.end();

```

--------------------------------

### Initialize pg-mem in Node.js

Source: https://github.com/oguimbal/pg-mem/blob/master/readme.md

Initialize a new in-memory database instance in a Node.js environment. You can then execute SQL commands on this instance.

```typescript
import { newDb } from "pg-mem";

const db = newDb();
db.public.many(/* put some sql here */);
```

--------------------------------

### Create Knex.js Adapter with pg-mem

Source: https://context7.com/oguimbal/pg-mem/llms.txt

Create a Knex instance connected to the in-memory database. This is useful for testing applications that use Knex.js for database interactions.

```typescript
import { newDb } from "pg-mem";
import type { Knex } from "knex";

const db = newDb();

// Create Knex instance
const knex = db.adapters.createKnex() as Knex;

// Create tables with Knex schema builder
await knex.schema
  .createTable("users", (table) => {
    table.increments("id");
    table.string("name");
  })
  .createTable("posts", (table) => {
    table.increments("id");
    table.string("title");
    table.integer("user_id").unsigned().references("users.id");
  });

// Insert data
await knex("users").insert({ name: "Alice" });
await knex("posts").insert({ title: "Hello World", user_id: 1 });

// Query with joins
const results = await knex("users")
  .join("posts", "users.id", "posts.user_id")
  .select("users.name", "posts.title");

console.log(results);
// Output: [{ name: 'Alice', title: 'Hello World' }]

// Verify with raw SQL
console.log(db.public.many(`SELECT * FROM users`));
// Output: [{ id: 1, name: 'Alice' }]

```

--------------------------------

### Create Mikro-ORM Instance with pg-mem

Source: https://context7.com/oguimbal/pg-mem/llms.txt

Initialize a Mikro-ORM instance connected to the in-memory database. Ensure entities are correctly defined and passed to the ORM configuration.

```typescript
import { Entity, PrimaryKey, Property, ManyToOne, OneToMany, Collection, MikroORM } from "@mikro-orm/core";
import { newDb } from "pg-mem";

@Entity()
class Author {
  @PrimaryKey({ type: "text" })
  id!: string;

  @Property({ type: "text" })
  name!: string;

  @OneToMany(() => Book, (book) => book.author)
  books = new Collection<Book>(this);
}

@Entity()
class Book {
  @PrimaryKey({ type: "text" })
  id!: string;

  @Property({ type: "text" })
  title!: string;

  @ManyToOne(() => Author)
  author!: Author;
}

async function main() {
  const db = newDb();

  // Create Mikro-ORM instance
  const orm: MikroORM = await db.adapters.createMikroOrm({
    entities: [Author, Book],
  });

  // Create schema
  await orm.getSchemaGenerator().createSchema();

  const em = orm.em.fork();

  // Create entities
  const author = em.create(Author, { id: "author1", name: "Victor Hugo" });
  const book = em.create(Book, {
    id: "book1",
    title: "Les Misérables",
    author,
  });

  await em.persistAndFlush([author, book]);

  // Query
  const books = await em.find(Book, {});
  console.log(books.map((b) => b.title));
  // Output: ['Les Misérables']
}
```

--------------------------------

### Run SQL Migrations with pg-mem

Source: https://context7.com/oguimbal/pg-mem/llms.txt

Automatically run SQL migration files with rollback support. Migrations can be run from a default folder or a custom path.

```typescript
import { newDb } from "pg-mem";

const db = newDb();

// Run migrations from default ./migrations folder
await db.public.migrate();

// Or with custom configuration
await db.public.migrate({
  // Path to migrations folder
  migrationsPath: "./db/migrations",
  // Name of migrations tracking table
  table: "schema_migrations",
  // Force re-run of last migration (useful for development)
  force: false,
});

// Migration file format (001-initial.sql):
// CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT);
//
// -- Down
// DROP TABLE users;

```

--------------------------------

### Use pg-mem with slonik

Source: https://github.com/oguimbal/pg-mem/wiki/Libraries-adapters

Replace slonik's createPool with pg-mem's adapter for in-memory pool creation.

```typescript
import {createPool} from 'slonik';
const pool = createPool(/* args */);

// use:
import {newDb} from 'pg-mem';
const pool = newDb().adapters.createSlonik();
```

--------------------------------

### Create pg-mem Database Instance

Source: https://context7.com/oguimbal/pg-mem/llms.txt

Use `newDb()` to create a new in-memory PostgreSQL database instance. Options can be provided for error handling and index behavior, which are recommended when using TypeORM.

```typescript
import { newDb, DataType } from "pg-mem";

// Create a basic database instance
const db = newDb();

// Create with options (recommended for TypeORM)
const dbWithOptions = newDb({
  // Recommended when using TypeORM .synchronize() 
  autoCreateForeignKeyIndices: true,
  // Disable SQL statement info in error messages
  noErrorDiagnostic: false,
  // Throw on unsupported index types
  noIgnoreUnsupportedIndices: false,
});

// Access the default 'public' schema
const publicSchema = db.public;
```

--------------------------------

### Create pg-promise Adapter with pg-mem

Source: https://context7.com/oguimbal/pg-mem/llms.txt

Create a pg-promise instance connected to the in-memory database. This is useful for testing applications that use pg-promise.

```typescript
import { newDb } from "pg-mem";

const db = newDb();

// Create pg-promise instance (requires pg-promise >= 10.8.7)
const pgp = db.adapters.createPgPromise();

// Use like regular pg-promise
await pgp.none(`
  CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    price DECIMAL
  )
`);

await pgp.none(`INSERT INTO products (name, price) VALUES ($1, $2)`, ["Widget", 9.99]);

const products = await pgp.many(`SELECT * FROM products`);
console.log(products);
// Output: [{ id: 1, name: 'Widget', price: 9.99 }]

const widget = await pgp.one(`SELECT * FROM products WHERE name = $1`, ["Widget"]);
console.log(widget);
// Output: { id: 1, name: 'Widget', price: 9.99 }

```

--------------------------------

### Create Slonik Pool with pg-mem

Source: https://context7.com/oguimbal/pg-mem/llms.txt

Initialize a Slonik connection pool with the in-memory database. Use `sql.unsafe` for raw SQL queries, ensuring proper escaping if dynamic values are used.

```typescript
import { newDb } from "pg-mem";
import { sql } from "slonik";

const db = newDb();

// Create Slonik pool
const pool = await db.adapters.createSlonik();

// Create table
await pool.query(sql.unsafe`
  CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
  )
`);

// Insert data
await pool.query(sql.unsafe`INSERT INTO users (name) VALUES ('Alice')`);
await pool.query(sql.unsafe`INSERT INTO users (name) VALUES ('Bob')`);

// Query data
const users = await pool.many(sql.unsafe`SELECT * FROM users`);
console.log(users);
// Output: [{ id: 1, name: 'Alice' }, { id: 2, name: 'Bob' }]
```

--------------------------------

### Use pg-mem with pg-native

Source: https://github.com/oguimbal/pg-mem/wiki/Libraries-adapters

Replace pg-native imports with pg-mem's adapter for in-memory testing.

```typescript
import Client from 'pg-native';

// use:
import {newDb} from 'pg-mem';
const Client = newDb().adapters.createPgNative();
```

--------------------------------

### Rollback to a previous state

Source: https://github.com/oguimbal/pg-mem/blob/master/readme.md

Demonstrates how to create a backup of the current database state and restore it later. This is useful for resetting the database between tests.

```typescript
const db = newDb();
db.public.none(`create table test(id text);
                insert into test values ('value');`);
// create a restore point & mess with data
const backup = db.backup();
db.public.none(`update test set id='new value';`);
// restore it !
backup.restore();
db.public.many(`select * from test`); // => {test: 'value'}
```

--------------------------------

### Add CREATE SEQUENCE Unit Tests

Source: https://github.com/oguimbal/pg-mem/blob/master/CONTRIBUTING.md

Write unit tests for the CREATE SEQUENCE statement in pg-mem.

```typescript
import { expect } from "chai";

import { createPgMem } from "../.." 

describe("sequence.spec", () => {
  it("simple create sequence", async () => {
    const pgmem = await createPgMem();
    await pgmem.query("CREATE SEQUENCE my_seq");
    // Add assertions to check if the sequence was created correctly
  });

  it("create sequence with properties", async () => {
    const pgmem = await createPgMem();
    await pgmem.query("CREATE SEQUENCE my_seq START WITH 10 INCREMENT BY 5");
    // Add assertions to check sequence properties
  });
});
```

--------------------------------

### Create TypeORM Adapter with pg-mem

Source: https://context7.com/oguimbal/pg-mem/llms.txt

Create a TypeORM DataSource connected to the in-memory database for testing entity-based applications. This allows testing TypeORM entities and repositories.

```typescript
import { Entity, PrimaryGeneratedColumn, Column, BaseEntity, LessThan } from "typeorm";
import { newDb } from "pg-mem";

@Entity()
class User extends BaseEntity {
  @PrimaryGeneratedColumn()
  id!: number;

  @Column({ type: "text" })
  name!: string;

  @Column({ type: "int" })
  age!: number;
}

async function main() {
  const db = newDb({
    autoCreateForeignKeyIndices: true,
  });

  // Create TypeORM DataSource
  const dataSource = db.adapters.createTypeormDataSource({
    type: "postgres",
    entities: [User],
  });

  await dataSource.initialize();
  await dataSource.synchronize();

  const userRepo = dataSource.getRepository(User);

  // Create users
  await userRepo.save([
    { name: "Alice", age: 30 },
    { name: "Bob", age: 25 },
    { name: "Charlie", age: 35 },
  ]);

  // Query users
  const youngUsers = await userRepo.find({
    where: { age: LessThan(30) },
  });
  console.log(youngUsers.map((u) => u.name));
  // Output: ['Bob']

  await dataSource.destroy();
}

```

--------------------------------

### Use pg-mem with node-postgres (pg)

Source: https://github.com/oguimbal/pg-mem/wiki/Libraries-adapters

Replace node-postgres imports with pg-mem's adapter for in-memory testing.

```typescript
import {Client} from 'pg';

// use:
import {newDb} from 'pg-mem';
const {Client} = newDb().adapters.createPg();
```

--------------------------------

### Use pg-mem with postgres.js

Source: https://github.com/oguimbal/pg-mem/wiki/Libraries-adapters

Integrate postgres.js with pg-mem using pg-server for in-memory communication. Requires 'postgres' and 'pg-server' peer dependencies.

```typescript
import { newDb } from 'pg-mem';

// init db
const db = newDb();
await sql`create table test(name text)`;
await sql`insert into test values ('Alice'), ('Bob')`;

// create postgres.js tag (use this instead of require('postgres').connect())
const sql = db.adapters.createPostgresJsTag() as import('postgres').Sql;

const pattern = 'A%';
const results = [...await sql`select * from test where name like ${pattern}`];
console.log(results);
// prints [{    name: "Alice",   }]
```

--------------------------------

### Implement toSql for CREATE SEQUENCE

Source: https://github.com/oguimbal/pg-mem/blob/master/CONTRIBUTING.md

Implement the 'toSql' function for the CREATE SEQUENCE statement to convert the AST back to SQL.

```typescript
function toSql(ast: Ast): string {
  switch (ast.type) {
    // ... other statement types
    case "createSequenceStatement":
      return `CREATE SEQUENCE ${toSqlIdentifier(ast.name)}${ast.properties ? ` ${toSqlSequenceProperties(ast.properties)}` : ""}`;
    // ... other statement types
  }
}

function toSqlSequenceProperties(properties: SequenceProperties): string {
  let sql = "";
  if (properties.start) {
    sql += ` START WITH ${properties.start}`;
  }
  if (properties.increment) {
    sql += ` INCREMENT BY ${properties.increment}`;
  }
  // ... other properties
  return sql;
}
```

--------------------------------

### Implement AST Mapper for CREATE SEQUENCE

Source: https://github.com/oguimbal/pg-mem/blob/master/CONTRIBUTING.md

Add a case to IAstPartialMapper for handling CREATE SEQUENCE statements and provide a default visitor implementation.

```typescript
export const astPartialMapper: IAstPartialMapper = {
  // ... other mappers
  createSequenceStatement: (statement) => {
    return {
      ...statement,
      name: mapIdentifier(statement.name),
      properties: statement.properties ? mapSequenceProperties(statement.properties) : undefined,
    };
  },
  // ... other mappers
};

function mapSequenceProperties(properties: SequenceProperties): SequenceProperties {
  return {
    ...properties,
    name: mapIdentifier(properties.name),
    // ... map other properties
  };
}
```

--------------------------------

### Register SQL Function with Multiple Arguments

Source: https://context7.com/oguimbal/pg-mem/llms.txt

Register a SQL function that accepts multiple arguments. The 'args' array should list each argument's type in order.

```typescript
// Function with multiple arguments
db.public.registerFunction({
  name: "add_numbers",
  args: [DataType.integer, DataType.integer],
  returns: DataType.integer,
  implementation: (a, b) => a + b,
});

console.log(db.public.one(`SELECT add_numbers(5, 3) as sum`));
// Output: { sum: 8 }
```

--------------------------------

### Applying SQL migrations with pg-mem

Source: https://github.com/oguimbal/pg-mem/wiki/FAQ

pg-mem supports SQL migrations using an API adapted from node-sqlite. You can configure the migrations path, table name, and whether to force re-application of the latest migration.

```typescript
await db.public.migrate({		
    /**
    * If true, will force the migration API to rollback and re-apply the latest migration over
    * again each time when Node.js app launches.
    */
    force?: boolean
    /**
    * Migrations table name. Default is 'migrations'
    */
    table?: string
    /**
    * Path to the migrations folder. Default is `path.join(process.cwd(), 'migrations')`
    */
    migrationsPath?: string
})
```

--------------------------------

### Dumping and importing a production schema into pg-mem

Source: https://github.com/oguimbal/pg-mem/wiki/FAQ

Use pg_dump to create a schema-only SQL dump from a production PostgreSQL database. This dump can then be imported into pg-mem for testing purposes. Ensure to mock any extensions used.

```bash
 pg_dump --schema-only --no-owner --no-acl --disable-triggers --no-comments --no-publications --no-security-labels --no-subscriptions --no-tablespaces --host HOST --user USER --password DBNAME > dump.sql
```

```typescript
import fs from 'fs';
import {newDb} from 'pg-mem';

cons db = newDb();

// create schema
db.public.none(fs.readFileSync('dump.sql', 'utf8'));

// then, create a backup (insert data that will be common to all tests before that if required)
const backup = db.backup();
```

```typescript
// restore 'db' as original
backup.restore();
// => use 'db' !
```

--------------------------------

### Register Simple SQL Function

Source: https://context7.com/oguimbal/pg-mem/llms.txt

Register a basic SQL function with type-safe arguments and return values. Ensure the 'name', 'args', 'returns', and 'implementation' are correctly defined.

```typescript
import { newDb, DataType } from "pg-mem";

const db = newDb();

// Simple function registration
db.public.registerFunction({
  name: "say_hello",
  args: [DataType.text],
  returns: DataType.text,
  implementation: (name) => `Hello, ${name}!`,
});

console.log(db.public.one(`SELECT say_hello('World') as greeting`));
// Output: { greeting: 'Hello, World!' }
```

--------------------------------

### Implement AST Node for CREATE SEQUENCE

Source: https://github.com/oguimbal/pg-mem/blob/master/CONTRIBUTING.md

Define the AST node structure for the CREATE SEQUENCE statement in pgsql-ast-parser.

```typescript
export interface SequenceProperties {
  name: Identifier;
  // ... other properties like start, increment, minvalue, maxvalue, cycle, cache, order
}

export interface CreateSequenceStatement {
  type: "createSequenceStatement";
  name: Identifier;
  ifNotExists?: boolean;
  properties?: SequenceProperties;
}
```

--------------------------------

### Execute SQL Queries with pg-mem

Source: https://context7.com/oguimbal/pg-mem/llms.txt

The schema interface allows executing SQL queries using `none()`, `one()`, `many()`, and `query()`. Use `none()` for statements without results, `one()` or `many()` for queries returning rows, and `query()` for full result metadata.

```typescript
import { newDb } from "pg-mem";

const db = newDb();

// Execute without expecting results
db.public.none(`
  CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    age INTEGER
  )
`);

// Insert data
db.public.none(`INSERT INTO users (name, email, age) VALUES ('Alice', 'alice@example.com', 30)`);
db.public.none(`INSERT INTO users (name, email, age) VALUES ('Bob', 'bob@example.com', 25)`);

// Query multiple rows
const allUsers = db.public.many(`SELECT * FROM users`);
// Returns: [{ id: 1, name: 'Alice', email: 'alice@example.com', age: 30 }, { id: 2, name: 'Bob', ... }]

// Query single row
const alice = db.public.one(`SELECT * FROM users WHERE name = 'Alice'`);
// Returns: { id: 1, name: 'Alice', email: 'alice@example.com', age: 30 }

// Get full query result with metadata
const result = db.public.query(`SELECT * FROM users WHERE age > 20`);
// Returns: { command: 'SELECT', rowCount: 2, rows: [...], fields: [...] }
```

--------------------------------

### Subscribe to Database Events with pg-mem

Source: https://context7.com/oguimbal/pg-mem/llms.txt

Subscribe to various database events like queries, schema changes, and extension creation for debugging, logging, or custom behavior. Table-level events such as sequential scans are also available.

```typescript
import { newDb } from "pg-mem";

const db = newDb();

// Subscribe to all queries
const querySub = db.on("query", (sql) => {
  console.log("Query executed:", sql);
});

// Subscribe to failed queries
db.on("query-failed", (sql) => {
  console.error("Query failed:", sql);
});

// Subscribe to schema changes
db.on("schema-change", () => {
  console.log("Schema modified");
});

// Subscribe to extension creation
db.on("create-extension", (ext) => {
  console.log("Extension created:", ext);
});

// Table-level events for performance monitoring
db.public.none(`CREATE TABLE data (id INT, value TEXT)`);
const dataTable = db.getTable("data");

dataTable.on("seq-scan", () => {
  console.warn("Warning: Sequential scan on data table");
});

// Global sequential scan monitoring
db.on("seq-scan", () => {
  console.warn("Warning: Sequential scan detected");
});

// Unoptimized join detection
db.on("catastrophic-join-optimization", () => {
  console.warn("Warning: Unoptimized O(n*m) join detected");
});

// Execute queries
db.public.none(`INSERT INTO data VALUES (1, 'test')`);
// Output: Query executed: INSERT INTO data VALUES (1, 'test')

// Unsubscribe when done
querySub.unsubscribe();
```

--------------------------------

### Implement CREATE SEQUENCE Executor Logic

Source: https://github.com/oguimbal/pg-mem/blob/master/CONTRIBUTING.md

Add the executor logic for the CREATE SEQUENCE statement in pg-mem's schema.ts.

```typescript
async execute(schema: Schema, statement: AstStatement): Promise<Result> {
  switch (statement.type) {
    // ... other statement types
    case "createSequenceStatement":
      return schema.createSequence(statement);
    // ... other statement types
  }
}
```

--------------------------------

### Implement Nearley Syntax for CREATE SEQUENCE

Source: https://github.com/oguimbal/pg-mem/blob/master/CONTRIBUTING.md

Define the Nearley grammar rules for parsing the CREATE SEQUENCE statement.

```nearley
%import common.WS
%ignore WS


createSequence -> "CREATE" "SEQUENCE" sequenceName:name
  (properties:props)?
  -> {
    type: "createSequenceStatement",
    name,
    properties: props
  }

sequenceName -> identifier:name
  -> name

properties ->
  startWith
| incrementBy
| minValue
| maxValue
| cycle
| noCycle
| cache
| order
| noOrder

startWith -> "START" "WITH" value:val
  -> {
    type: "startWith",
    value: val
  }

incrementBy -> "INCREMENT" "BY" value:val
  -> {
    type: "incrementBy",
    value: val
  }

minValue -> "MINVALUE" value:val
  -> {
    type: "minValue",
    value: val
  }

maxValue -> "MAXVALUE" value:val
  -> {
    type: "maxValue",
    value: val
  }

cycle -> "CYCLE"
  -> {
    type: "cycle"
  }

noCycle -> "NO" "CYCLE"
  -> {
    type: "noCycle"
  }

cache -> "CACHE" value:val
  -> {
    type: "cache",
    value: val
  }

order -> "ORDER"
  -> {
    type: "order"
  }

noOrder -> "NO" "ORDER"
  -> {
    type: "noOrder"
  }

value
  : number
  | "ALL"
  | "NO"

number
  : positive_number
  | negative_number

positive_number
  : "0" | /[1-9]/ "0..9
```

--------------------------------

### Manually Insert Items into a Table in pg-mem

Source: https://github.com/oguimbal/pg-mem/blob/master/readme.md

Insert items manually into a table using the `insert()` method.

```typescript
db.public.getTable<TItem>('mytable').insert({ /* item to insert */ }))
```

--------------------------------

### Use pg-mem with pg-promise

Source: https://github.com/oguimbal/pg-mem/wiki/Libraries-adapters

Integrate pg-promise with pg-mem for in-memory database operations. Requires pg-promise@10.8.7 or newer.

```typescript
import pgp from 'pg-promise';
const pg = pgp(opts)

// use:
import {newDb} from 'pg-mem';
const pg = await newDb().adapters.createPgPromise();

// then use it like you would with pg-promise
await pg.connect();
```

--------------------------------

### Register a custom SQL function

Source: https://github.com/oguimbal/pg-mem/blob/master/readme.md

Shows how to register a custom SQL function in pg-mem. This function can then be called from SQL queries.

```typescript
db.public.registerFunction({
  name: "say_hello",
  args: [DataType.text],
  returns: DataType.text,
  implementation: (x) => "hello " + x,
});
```

--------------------------------

### Declare Tables Programmatically with pg-mem

Source: https://context7.com/oguimbal/pg-mem/llms.txt

Use the programmatic API to declare tables with full type definitions, constraints, and defaults. This method allows for precise control over table structure before inserting or querying data.

```typescript
import { newDb, DataType } from "pg-mem";

const db = newDb();

// Declare table with programmatic API
db.public.declareTable({
  name: "products",
  fields: [
    {
      name: "id",
      type: DataType.integer,
      constraints: [{ type: "primary key" }],
    },
    {
      name: "name",
      type: DataType.text,
      constraints: [{ type: "not null" }],
    },
    {
      name: "price",
      type: DataType.decimal,
    },
    {
      name: "category",
      type: DataType.text,
      default: { value: "uncategorized" },
    },
  ],
});

// Insert and query using SQL
db.public.none(`INSERT INTO products (id, name, price) VALUES (1, 'Widget', 9.99)`);

const products = db.public.many(`SELECT * FROM products`);
console.log(products);
// Output: [{ id: 1, name: 'Widget', price: 9.99, category: 'uncategorized' }]
```

--------------------------------

### Subscribe to Experimental pg-mem Events

Source: https://github.com/oguimbal/pg-mem/blob/master/readme.md

Subscribe to experimental events related to index usage and join optimization. These handlers are called when a request cannot be optimized using existing indices.

```typescript
// called when a table is iterated entirely (ex: 'select * from data where notIndex=3' triggers it)
db.on('seq-scan', () => {});

// same, but on a specific table
db.getTable('myTable').on('seq-scan', () = {});

// will be called if pg-mem did not find any way to optimize a join
// (which leads to a O(n*m) lookup with the current implementation)
db.on('catastrophic-join-optimization', () => {});
```

--------------------------------

### Bind pg-mem to TCP Server

Source: https://context7.com/oguimbal/pg-mem/llms.txt

Bind the in-memory database to a TCP port to allow connections from external PostgreSQL clients. The `port` option is optional and defaults to a random available port.

```typescript
import { newDb } from "pg-mem";

const db = newDb();

// Setup schema
db.public.none(`CREATE TABLE test (id INT, value TEXT)`);
db.public.none(`INSERT INTO test VALUES (1, 'hello')`);

// Bind to a TCP server
const server = await db.adapters.bindServer({
  port: 5432, // Optional, uses random port if not specified
  host: "127.0.0.1",
});

console.log(`Connection string: ${server.postgresConnectionString}`);
// Output: Connection string: postgresql://127.0.0.1:5432/postgres?sslmode=disable

console.log(`Port: ${server.connectionSettings.port}`);
// Now you can connect with psql, DBeaver, or any PostgreSQL client

// When done, close the server
server.close();
```

--------------------------------

### Subscribe to Database Events in pg-mem

Source: https://github.com/oguimbal/pg-mem/blob/master/readme.md

Subscribe to various database events such as successful or failed queries, schema changes, and extension creation.

```typescript
const db = newDb();

// called on each successful sql request
db.on("query", (sql) => {});
// called on each failed sql request
db.on("query-failed", (sql) => {});
// called on schema changes
db.on("schema-change", () => {});
// called when a CREATE EXTENSION schema is encountered.
db.on("create-extension", (ext) => {});
```

--------------------------------

### Inspect Table Content in pg-mem

Source: https://github.com/oguimbal/pg-mem/blob/master/readme.md

Manually inspect the content of a table using the `find()` method with a template object.

```typescript
for (const item of db.public.getTable<TItem>("mytable").find(itemTemplate)) {
  console.log(item);
}
```

--------------------------------

### Register Custom Language Compiler

Source: https://context7.com/oguimbal/pg-mem/llms.txt

Enable custom languages for 'CREATE FUNCTION' and 'DO' statements by registering a language compiler. The compiler function receives code and arguments, returning a function to execute the code.

```typescript
import { newDb, DataType, CompiledFunction } from "pg-mem";

const db = newDb();

// Register a JavaScript-like language compiler
db.registerLanguage("plv8", ({ code, args, returns }) => {
  const argNames = args.map((x, i) => x.name ?? `$${i}`);
  return new Function(...argNames, code) as CompiledFunction;
});

// Create a function using the custom language
db.public.none(`
  CREATE FUNCTION calculate(x int, y int, op text)
  RETURNS int AS $$
    if (op === '+') return x + y;
    if (op === '-') return x - y;
    if (op === '*') return x * y;
    if (op === '/') return Math.floor(x / y);
    throw new Error('Unknown operator');
  $$ LANGUAGE plv8
`);

console.log(db.public.one(`SELECT calculate(10, 3, '+') as result`));
// Output: { result: 13 }

console.log(db.public.one(`SELECT calculate(10, 3, '*') as result`));
// Output: { result: 30 }
```

--------------------------------

### Debug a Specific pg-mem Test in VS Code

Source: https://github.com/oguimbal/pg-mem/blob/master/readme.md

Instructions for debugging a single unit test within the pg-mem project using Visual Studio Code. This involves marking the test with `.only` and using the debugger.

```typescript
1. Add a `.only` on the test you'd like to debug
2. Just hit F5 (or execute via the debugger tab), which should launch your test with debugger attached
```

--------------------------------

### Register a Basic Custom Language

Source: https://github.com/oguimbal/pg-mem/wiki/Custom-language-declaration-(functions,-do-statements,-plpgsql,-plv8,-...)

Register a custom language that throws an error when functions are called. This is useful for testing scenarios where function execution is not supported.

```typescript
db.registerLanguage('mylang', () => {
    return () => {
        throw new Error('Functions not supported !')
    }
});
```

--------------------------------

### Mocking plv8 for JavaScript Functions

Source: https://github.com/oguimbal/pg-mem/wiki/Custom-language-declaration-(functions,-do-statements,-plpgsql,-plv8,-...)

Mock the plv8 language to execute JavaScript functions. This implementation works for plv8 code that only uses its arguments and does not access external data or out arguments.

```typescript
db.registerLanguage('plv8', ({ code, args }) => {
            const argNames = args.map((x, i) => x.name ?? ('$' + i));
            return new Function(...argNames, code) as CompiledFunction;
        });
```

--------------------------------

### Execute DO Statements with Custom Language

Source: https://context7.com/oguimbal/pg-mem/llms.txt

Register a language compiler for 'DO' statements. The compiler should return a function that executes the provided code block.

```typescript
// Execute DO statements
db.registerLanguage("mylang", ({ code }) => {
  return () => {
    console.log("Executing:", code);
  };
});

db.public.none(`DO LANGUAGE mylang $$ some code here $$`);
// Output: Executing: some code here
```

--------------------------------

### Add Parser Unit Test for DELETE Statement

Source: https://github.com/oguimbal/pg-mem/blob/master/CONTRIBUTING.md

Add a parser unit test for the DELETE statement in the pgsql-ast-parser. Reference existing tests for guidance.

```typescript
import { expect } from "chai";

import { parse } from "pgsql-ast-parser";

describe("DELETE", () => {
  it("simple delete", () => {
    const result = parse("DELETE FROM "users" WHERE id = 1");
    expect(result).deep.equal({
      type: "deleteStatement",
      from: {
        type: "table",
        name: "users",
        alias: null
      },
      where: {
        type: "binary",
        operator: "=",
        left: {
          type: "column",
          name: "id"
        },
        right: {
          type: "value",
          value: 1
        }
      }
    });
  });
});
```

--------------------------------

### Intercept SQL Queries with pg-mem

Source: https://context7.com/oguimbal/pg-mem/llms.txt

Intercept SQL queries to return custom results or mock specific queries during testing. This allows for simulating external API responses or controlling query behavior without actual database execution.

```typescript
import { newDb } from "pg-mem";

const db = newDb();

db.public.none(`CREATE TABLE users (id INT, name TEXT)`);

// Intercept specific queries
const subscription = db.public.interceptQueries((sql) => {
  // Return mock data for specific query
  if (sql.includes("external_api_data")) {
    return [
      { id: 1, data: "mocked response 1" },
      { id: 2, data: "mocked response 2" },
    ];
  }

  // Return null to proceed with actual execution
  return null;
});

// This query is intercepted
const mocked = db.public.many(`SELECT * FROM external_api_data`);
console.log(mocked);
// Output: [{ id: 1, data: 'mocked response 1' }, { id: 2, data: 'mocked response 2' }]

// This query executes normally
db.public.none(`INSERT INTO users VALUES (1, 'Alice')`);
const users = db.public.many(`SELECT * FROM users`);
console.log(users);
// Output: [{ id: 1, name: 'Alice' }]

// Remove interceptor
subscription.unsubscribe();
```

--------------------------------

### Inspect and Operate Tables Manually with pg-mem

Source: https://context7.com/oguimbal/pg-mem/llms.txt

Access and manipulate table data directly through the table inspection API, bypassing SQL for certain operations. This is useful for quick data manipulation or when direct object interaction is preferred.

```typescript
import { newDb } from "pg-mem";

const db = newDb();

db.public.none(`
  CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    role TEXT DEFAULT 'user'
  )
`);

// Get table reference
const usersTable = db.getTable("users");

// Insert items directly (bypassing SQL)
usersTable.insert({ name: "Alice", role: "admin" });
usersTable.insert({ name: "Bob" }); // Uses default role

// Find items matching a template
const admins = usersTable.find({ role: "admin" });
console.log([...admins]);
// Output: [{ id: 1, name: 'Alice', role: 'admin' }]

// Find all items
const allUsers = usersTable.find();
console.log([...allUsers]);
// Output: [{ id: 1, name: 'Alice', role: 'admin' }, { id: 2, name: 'Bob', role: 'user' }]

// Get column definitions
for (const col of usersTable.getColumns()) {
  console.log(`${col.name}: ${col.type.name}, nullable: ${col.nullable}`);
}
// Output:
// id: integer, nullable: false
// name: text, nullable: false
// role: text, nullable: true

// List indices
console.log(usersTable.listIndices());
// Output: [{ name: 'users_pkey', expressions: ['id'], unique: true }]
```

--------------------------------

### Intercept Database Queries in pg-mem

Source: https://github.com/oguimbal/pg-mem/blob/master/readme.md

Hook into database queries to return custom results or allow normal execution. Useful for mocking specific query responses.

```typescript
const db = newDb();

db.public.interceptQueries((sql) => {
  if (sql === "select * from whatever") {
    // intercept this statement, and return something custom:
    return [{ something: 42 }];
  }
  // proceed to actual SQL execution for other requests.
  return null;
});
```

--------------------------------

### Add Runner Unit Test for DELETE Statement

Source: https://github.com/oguimbal/pg-mem/blob/master/CONTRIBUTING.md

Add a runner unit test for the DELETE statement in pg-mem. This involves testing the execution of a DELETE query.

```typescript
import { expect } from "chai";

import { createPgMem } from "../.." 

describe("delete.queries", () => {
  it("simple delete", async () => {
    const pgmem = await createPgMem();
    await pgmem.query("CREATE TABLE users (id integer)");
    await pgmem.query("INSERT INTO users (id) VALUES (1)");
    await pgmem.query("INSERT INTO users (id) VALUES (2)");
    await pgmem.query("DELETE FROM users WHERE id = 1");
    const result = await pgmem.query("SELECT * FROM users");
    expect(result.rows).deep.equal([{ id: 2 }]);
  });
});
```

--------------------------------

### Register SQL Function with Null Argument Handling

Source: https://context7.com/oguimbal/pg-mem/llms.txt

Register a function that explicitly handles null arguments. Set 'allowNullArguments: true' and use nullish coalescing operators (??) in the implementation.

```typescript
// Function that handles null arguments
db.public.registerFunction({
  name: "null_safe_concat",
  args: [DataType.text, DataType.text],
  returns: DataType.text,
  allowNullArguments: true,
  implementation: (a, b) => `${a ?? "NULL"}-${b ?? "NULL"}`,
});

console.log(db.public.one(`SELECT null_safe_concat('hello', null) as result`));
// Output: { result: 'hello-NULL' }
```

--------------------------------

### Register Custom Equivalent Type in pg-mem

Source: https://github.com/oguimbal/pg-mem/blob/master/readme.md

Register a custom type that is equivalent to an existing data type, providing validation logic. Useful for types like MACADDR that have format constraints.

```typescript
db.public.registerEquivalentType({
  name: "macaddr",
  // which type is it equivalent to (will be able to cast it from it)
  equivalentTo: DataType.text,
  isValid(val: string) {
    // check that it will be this format
    return isValidMacAddress(val);
  },
});
```

--------------------------------

### Register Impure SQL Function

Source: https://context7.com/oguimbal/pg-mem/llms.txt

Register a function that can produce different results on each call, even with the same arguments. Set 'impure: true' to indicate this behavior.

```typescript
// Impure function (called for each row even with constant args)
db.public.registerFunction({
  name: "random_id",
  returns: DataType.text,
  impure: true,
  implementation: () => Math.random().toString(36).substring(7),
});
```