npm package discovery and stats viewer.

Discover Tips

  • General search

    [free text search, go nuts!]

  • Package details

    pkg:[package-name]

  • User packages

    @[username]

Sponsor

Optimize Toolset

I’ve always been into building performant and accessible sites, but lately I’ve been taking it extremely seriously. So much so that I’ve been building a tool to help me optimize and monitor the sites that I build to make sure that I’m making an attempt to offer the best experience to those who visit them. If you’re into performant, accessible and SEO friendly sites, you might like it too! You can check it out at Optimize Toolset.

About

Hi, 👋, I’m Ryan Hefner  and I built this site for me, and you! The goal of this site was to provide an easy way for me to check the stats on my npm packages, both for prioritizing issues and updates, and to give me a little kick in the pants to keep up on stuff.

As I was building it, I realized that I was actually using the tool to build the tool, and figured I might as well put this out there and hopefully others will find it to be a fast and useful way to search and browse npm packages as I have.

If you’re interested in other things I’m working on, follow me on Twitter or check out the open source projects I’ve been publishing on GitHub.

I am also working on a Twitter bot for this site to tweet the most popular, newest, random packages from npm. Please follow that account now and it will start sending out packages soon–ish.

Open Software & Tools

This site wouldn’t be possible without the immense generosity and tireless efforts from the people who make contributions to the world and share their work via open source initiatives. Thank you 🙏

© 2024 – Pkg Stats / Ryan Hefner

@vacodoceo/pg-mem

v2.8.2

Published

A memory version of postgres

Downloads

2,098

Readme

📐 Usage

Using Node.js

As always, it starts with an:

npm i pg-mem --save

Then, assuming you're using something like webpack, if you're targeting a browser:

import { newDb } from "pg-mem";

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

Using Deno

Pretty straightforward :)

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

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

Only use the SQL syntax parser

❤ Head to the pgsql-ast-parser repo

⚠ Disclaimer

The sql syntax parser is home-made. Which means that some features are not implemented, and will be considered as invalid syntaxes.

This lib is quite new, so forgive it if some obvious pg syntax is not supported !

... And open an issue if you feel like a feature should be implemented :)

Moreover, even if I wrote hundreds of tests, keep in mind that this implementation is a best effort to replicate PG. Keep an eye on your query results if you perform complex queries. Please file issues if some results seem incoherent with what should be returned.

Finally, I invite you to read the below section to have an idea of you can or cannot do.

🔍 Features

Rollback to a previous state

pg-mem uses immutable data structures (here and here), which means that you can have restore points for free!

This is super useful if you intend to use pg-mem to mock your database for unit tests.

You could:

  1. Create your schema only once (which could be a heavy operation for a single unit test)
  2. Insert test data which will be shared by all test
  3. Create a restore point
  4. Run your tests with the same db instance, executing a backup.restore() before each test (which instantly resets db to the state it has after creating the restore point)

Usage:

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

Custom functions

You can declare custom functions like this:

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

And then use them like in SQL select say_hello('world').

Custom functions support overloading and variadic arguments.

⚠ However, the value you return is not type checked. It MUST correspond to the datatype you provided as 'returns' (it won't fail if not, but could lead to weird bugs).

Custom types

Not all pg types are implemented in pg-mem. That said, most of the types are often equivalent to other types, with a format validation. pg-mem provides a way to register such types.

For instance, lets say you'd like to register the MACADDR type, which is basically a string, with a format constraint.

You can register it like this:

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

Doing so, you'll be able to do things such as:

SELECT '08:00:2b:01:02:03:04:05'::macaddr; -- WORKS
SELECT 'invalid'::macaddr; -- will throw a conversion error

If you feel your implementation of a type matches the standard, and would like to include it in pg-mem for others to enjoy it, please consider filing a pull request ! (tip: see the INET type implementation as an example, and the pg_catalog index where supported types are registered)

Extensions

No native extension is implemented (pull requests are welcome), but you can define kind-of extensions like this:

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

Statements like create extension "my-ext" will then be supported.

📃 Libraries adapters

pg-mem provides handy shortcuts to create instances of popular libraries that will be bound to pg-mem instead of a real postgres db.

  • pg-native
  • node-postgres (pg)
  • pg-promise (pgp)
  • slonik
  • typeorm
  • knex
  • kysely
  • mikro-orm

See the wiki for more details

💥 Inspection

Intercept queries

If you would like to hook your database, and return ad-hoc results, you can do so like this:

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

Inspect a table

You can manually inspect a table content using the find() method:

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

Manually insert items

If you'd like to insert items manually into a table, you can do this like that:

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

Subscribe to events

You can subscribe to some events, like:

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

Experimental events

pg-mem implements a basic support for indices.

These handlers are called when a request cannot be optimized using one of the created indices.

However, a real postgres instance will be much smarter to optimize its requests... so when pg-mem says "this request does not use an index", dont take my word for it.

// 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', () => {});

🙋‍♂️ FAQ

Detailed answers in the wiki

⚠️ Current limitations

  • Materialized views are implemented as views (meaning that they are always up-to-date, without needing them to refresh)
  • Indices implementations are basic
  • No support for timezones
  • All number-like types are all handled as javascript numbers, meaning that types like numeric(x,y) could not behave as expected.

🐜 Development

Pull requests are welcome :)

To start hacking this lib, you'll have to:

... once done, tests should appear. HMR is on, which means that changes in your code are instantly propagated to unit tests. This allows for ultra fast development cycles (running tests takes less than 1 sec).

To debug tests: Just hit "run" (F5, or whatever)... VS Code should attach the mocha worker. Then run the test you want to debug.

Alternatively, you could just run npm run test without installing anything, but this is a bit long.