pg-mem

Because pg-mem uses immutable data structures, you can create restore points for free. This is highly effective for unit testing: you can set up a schema and shared test data once, create a backup, and then call backup.restore() before each test to instantly reset the database state.

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'}

In Deno, you can import pg-mem directly from a URL. The usage pattern is identical to the Node.js version.

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

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

To use pg-mem in a Node.js environment, install it via npm. You can then create a new database instance using newDb() and execute SQL queries through the public schema using methods like many() or none().

npm i pg-mem --save

import { newDb } from "pg-mem";

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

If a specific PostgreSQL type is not implemented, you can register an equivalent type. This allows you to map a new type name to an existing DataType while providing a custom isValid validation function. This enables type casting (e.g., SELECT 'val'::my_type) with format constraints.

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);
  },
});

Use db.on(eventName, callback) to listen for various database lifecycle events, including query success/failure, schema changes, and extension creation.

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 when schema changes
db.on("schema-change", () => {});
// called when a CREATE EXTENSION schema is encountered
db.on("create-extension", (ext) => {});

You can extend the database by registering custom functions. These functions support overloading and variadic arguments. Note that the return value is not type-checked against the returns property, so ensure your implementation matches the specified DataType to avoid bugs.

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

For debugging or performance monitoring, pg-mem provides experimental event handlers that trigger when queries cannot be optimized using existing indices (e.g., triggering a sequential scan or a catastrophic join optimization).

// called when a table is iterated entirely
db.on('seq-scan', () => {});

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

// called when pg-mem did not find any way to optimize a join
db.on('catastrophic-join-optimization', () => {});

You can hook into the database execution to intercept specific SQL statements and return ad-hoc results. If the interceptor returns null, the query proceeds to actual SQL execution.

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;
});

While native extensions are not implemented, you can simulate them using db.registerExtension. This allows you to run setup logic (like registering functions or types) when a CREATE EXTENSION "name" statement is encountered.

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

When initializing pg-mem, you can provide a MemoryDbOptions object to tune its behavior. Key options include:

• noErrorDiagnostic: If true, stops embedding SQL statement info in exception messages.
• noAstCoverageCheck: If true, skips checking if AST parts were left behind during parsing. (Use only as a workaround for reported issues).
• noIgnoreUnsupportedIndices: If true, throws an exception when using unsupported index types (currently only BTREE is supported).
• autoCreateForeignKeyIndices: If true, automatically creates an index on foreign tables when adding a foreign key. This is recommended when using TypeORM's .synchronize() method.

When calling migrate(db, config), you can provide a MigrationParams object to control the migration process.