Basic ORM build for and around postgres.
Served with ❤️ to the amazing community of node and pg.
With npm installed, run
$ npm install orm
- Models.
- Query: Basic query using objects instead of SQL.
- Migrations: create and run migrations.
- DB Config: Have different DB configs for every environment.
Models are representation of the data in the DB, you can add instance and class functions (static
) to the model.
The ORM handles creating, finding, querying, creating table and creating the model itself.
After the query is done, the ORM will cast the result into the model's instance.
To create the new model, you need to run the following command:
$ orm model new model_name
The commend creates the new model and migration to create the new model's table.
For the next examples we will be creating "User" model.
Let's create "User" model, for doing that we need to run the following command:
$ orm model new User
Let's look in the model's code:
// Requiring the relevant packages
const bcrypt = require('bcryptjs'); // package that hashes passwords
const Model = require('orm').Model;
module.exports = class User extends Model {
/**
* Overwriting the Model#create function to hash the user's password (using bcryptjs) before creating the user and then calling the Model#create function
* @param {Object} attrs
* @return {Promise}
*/
static create(attrs) {
let superMe = super; // Saving the super because of the scope
return new Promise(function (resolve, reject) {
bcrypt.genSalt((saltError, salt) => {
if (saltError)
reject(hashError);
return bcrypt.hash(attrs.password, salt, (hashError, hashPassword) => {
if (hashError)
reject(hashError);
// replace a password string with hash value
attrs.password = hashPassword;
superMe.create(attrs).then(resolve).catch(reject); // calls the Model create
});
});
});
}
}
Relations between tables or models are a key in relational databases. The ORM can handle and help the developer with a few types of relations by adding functions that call relevant where.
Types of relations:
- hasOne
- hasMany
- belongsTo
- hasAndBelongsToMany
Adds function for the model named as the relation model.
const Model = require('orm').Model;
const Name = require('./name');
module.exports = class User extends Model {
static hasOne(){
return [Name];
}
}
User.find(1).then((user) => user.Name)
The SQL that will be generated:
SELECT * FROM names WHERE user_id = 1
Adds function for the model named as the plural of the relation model.
const Model = require('orm').Model;
const Comment = require('./Comment');
module.exports = class User extends Model {
static hasMany(){
return [Comment];
}
}
User.find(1).then((user) => user.Comments)
The SQL that will be generated:
SELECT * FROM comments WHERE user_id = 1
Adds function for the model named as the plural of the relation model.
const Model = require('orm').Model;
const Rank = require('./rank');
module.exports = class User extends Model {
static belongsTo(){
return [Comment];
}
}
User.find(1).then((user) => user.Rank)
The SQL that will be generated:
SELECT * FROM ranks WHERE id = 10 -- user.rank_id
Adds function for the model named as the plural of the relation model.
const Model = require('orm').Model;
const Repo = require('./repo');
module.exports = class User extends Model {
static hasAndBelongsToMany(){
return [Repo];
}
}
User.find(1).then((user) => user.Repos)
The SQL that will be generated:
SELECT * FROM users_repos WHERE user_id = 1
In the ORM there is a few types of built in queries:
- where - immediately executing basic where sql query
- where not - immediately executing basic where sql query
- where - gathers where (but not immediately executing)
- where not - gathers where not (but not immediately executing)
- select - set select (but not immediately executing)
- execute - executing the gathered query (where, whereNot and select)
The ORM creates and executes the query by generating value clause (so no risk for SQL injection).
Creates where query, can receive Object (best practice) and return Promise
, for example:
User.where({name: 'Bar', is_awesome: true}).then((res) => {
console.log(res);
}).catch((err)=> {
throw err
});
The SQL that was generated:
SELECT * FROM 'users' WHERE name="Bar" AND is_awesome="t"
Creates where not query, can receive Object (best practice) and return Promise
, for example:
User.whereNot({name: 'Bar',is_awesome: true}).then((res) => {
console.log(res);
}).catch((err)=> {
throw err
});
The SQL that was generated:
SELECT * FROM 'users' WHERE NOT name="Bar" AND NOT is_awesome="t"
You can gather query to allow you the create complex queries, to run the query you need to call .execute()
.
Adds to the class' gathered query where
User.gatherWhere({name: 'Barrrr'});
User.gatherWhere({is_awesome: true});
User.gatherWhere({name: 'Bar'});
// The where QueryData is: {name: 'Bar', is_awesome: true}
Adds to the class' gathered query whereNot
User.gatherWhereNot({name: 'Barrrr'});
User.gatherWhereNot({is_awesome: false});
User.gatherWhereNot({name: 'Aviv'});
// The whereNot QueryData is: {name: 'Aviv', is_awesome: false}
Adds to the class' gathered query select
User.gatherSelect(['id']);
// The select QueryData is: ['id']
Adds joins (inner right join) to the query
User.joins(Comment, 'JOIN ON users.id = purchases.customer_id')
// The joins QueryData is: ['JOIN ON users.id = comments.user_id', 'JOIN ON users.id = purchases.customer_id']
Executes the query, return Promise
and clears the backed up queryData (with where, select and joins):
User.execute().then((res) =>{
console.log(res);
}).catch((err)=> {
throw err
});
The SQL that was generated:
SELECT id FROM 'users' WHERE Name="Bar" AND is_awesome="t" AND NOT name="Aviv" AND NOT is_awesome="f"
Manually clears the class backup queries.
Best practice is not to use this. When running .execute the queryData is automatically cleared
Create new migration (will create new file with the time of creation and migration name):
$ orm migration new migration_name
Running migration (will run only the ones that haven't been executed before):
$ orm migration run
$ # OR just
$ orm migration
Using the DB config file the programer can separate different environments.
The config file always should be ./config/db.json
.
For example:
{
"development": {
"user": "postgres",
"password": "postgres",
"database": "database_development",
"host": "127.0.0.1"
},
"test": {
"user": "postgres",
"password": "postgres",
"database": "database_test",
"host": "127.0.0.1"
}
}
Expected keys (using pg by brainc, read more here):
user
- user namepassword
- user's password (NEVER PUBLISH YOUR PASSWORDS)database
- what database to pull fromhost
- what host to connect toport
- what port to connect toconnectionString
- all the configurations put in one string (for example:postgresql://dbuser:secretpassword@database.server.com:3211/mydb
)connectionTimeoutMillis
- number of milliseconds to wait before timing out when connecting a new clientidleTimeoutMillis
- number of milliseconds to wait before timing out when connecting a new client before it is disconnected from the back-end and discardedmax
- maximum number of clients the pool should contain
- Example project.
- Tests.
- Inner joins.
- Left joins.
- WhereOr.
- Check and prevent SQL injection.
- Add more features from pg like:
- Notifications using
LISTEN
andNOTIFY
. - Copying data using
COPY FROM
andCOPY
TO. - JSON
- Notifications using
- More data types (read more on pg data types on the pg documentation here).
- Fork
- Commit to new branch (with well document commit messages)
- Open PR
- ?
- Profit
Big thanks for @brianc, this pakage is based on node-postgres, without him all of this wasn't achievable.
Apache License 2.0, please see LICENSE for details.
Copyright (c) 2017 Bar Admoni.