Mongoose-SQL

Mongoose compatible interface for PostgreSQL

Mongoose-SQL covers the basic API surface of Mongoose [ORM for Mongo] to interface and migrate data to PostgreSQL. This is effectively a small ORM over PostgreSQL that resembles the Mongoose API.

This library requires ES6 with Node.JS 6+ and uses Knex to interface with PostgreSQL.

npm install mongoose-sql

vardb=require("mongoose-sql");vare=process.environment;// Create connection: note default environment variables// returns a Knex instancedb.connect({client: e.DB_CLIENT||"pg",connection: {host: e.DB_HOST||"127.0.0.1",user: e.DB_USER||"user",password: e.DB_PASSWORD||"",database: e.DB_DATABASE||"test"}});// Get Knex instance if neededvarknex=db.getKnex();// Use Mongoose-like operations upon PostgreSQL tablesvarCat_Schema=newdb.Schema(CatModel);varCat=db.model("Cat",Cat_Schema);Cat.find().exec(myHandler);// find() returns all rowsCat.findById(123).exec(myHandler);// find by row idCat.findOne({name: 'fluffy'}).exec(myHandler);// findOneCat.where({name: 'fluffy'}).findOne().exec(myHandler);// find by whereCat.find().sort('breed').exec(myHandler);// sortCat.find().populate('owner').exec(myHandler);// outer left joinvarsimba=newCat({ CatObject });simba.save(function(){});simba.remove(function(){});// Migrations (WIP)varmongoose=require("mongoose");// instance MongoosevarCat_Schema_Mongo=newmongoose.Schema(CatModel);// make a mongoose schemavarCat_Mongo=mongoose.model("Cat",Cat_Schema_Mongo);// make a mongoose modeldb.migrateSchemas([Cat_Mongo]).then(function(){// call migrateSchemas with modelconsole.log("moved data to PostgreSQL from Mongoose");});

Mongoose API reference: http://mongoosejs.com/index.html

note that not all Mongoose apis are covered

Migrations (WIP)

Based client Schema definitions, the library will try to create PostgreSQL tables with fields of the right types.

• One-to-one relationships will have foreign key constraints
• Many-to-many relationships will get their own link table
• Object or list of object key values (without schema links) will become jsonb fields