Replace MongoDB with PosgreSQL
- Drop-in replacement Applications do not need code changes. pgmongo appears to your app as a MongoDB server.
- Stateless proxy pgmongo rewrites queries and proxies them to a Postgres database.
- JSON Primarily supports regular JSON data. Advanced BSON types like binary data, JavaSrcipt, ints and timestamps are not well supported.
This implements the MongoDB wire protocol and adapts queries to work with a PostgreSQL database using jsonb fields. I've tested it with Keystone.js and it seemed to work reasonably well.
pgmongo requires node 8 or newer and Postgres 9.4+. Then run the following.
npm install -g pgmongo
pgmongoThis will start a mongo-like server on port 27017. If you already have mongo running on your machine you can start it on a different port with the following.
pgmongo localhost 27018- listing/creating/dropping collections
- find (including sorting, skip and offset)
- count, distinct
- update (including support for upserting)
- insert (including batch insertion)
- deletion
- creating and listing basic indexes
- most custom parameters like $gt, $exists, $regex, $push, $set, $unset, etc. See this repo for the full list
- admin commands (returns mostly stubbed/fake data)
It's not production ready yet, but definitely working enough to play around with or use in basic apps.
Currently passes 190 of the 916 core mongo jstests.
db.createCollection('users') -> CREATE TABLE IF NOT EXISTS "users" (data jsonb)
db.users.find({ lastLogin: { $lte: '2016' } }) -> SELECT data FROM "users" WHERE data->>'lastLogin'<='2016'
db.users.update({}, { $set: { active: true } }) -> UPDATE "users" SET data = jsonb_set(data,'{active}','true'::jsonb)
db.users.find({}, { firstName: 1 } ) -> SELECT jsonb_build_object('firstName', data->'firstName', '_id', data->'_id') as data FROM "users"
db.blogs.insert({ title: 'first post', comments: [] }) -> INSERT INTO "blogs" VALUES ('{"_id":"5b45b641eb4bd93896d57888","title":"first post","comments":[]}'::jsonb)
db.blogs.remove({ 'state.trash': true }) -> DELETE FROM "blogs" WHERE data->'state'->'trash'='true'::jsonb
Note: contributions/PRs are very much welcome.
- Support for findandmodify
- Better for queries matching array elements
- Preserve BSON (Dates, ObjectIDs, other than _id)
- Cursors (currently all data is returned in first result)
- Better Indexes support (not sure if compound indexes are possible)
- min and max
- Support numeric object keys (currently numbers are assumed to be an array index)
- Capped collections
- geo support
- explain queries
- aggregation framework/map reduce queries
- NaN and Infinity
- Preserve the initial order of object keys
- $eval and $where
- mongo-query-to-postgres-jsonb (heavily used as a dependency)
- Mongo Admin Commands
- JSONB Index Performance
Anyone can help make this project better. Feel free to open an issue to discuss what you want to work on.