Object Relational Mapping for Node.js and PostgreSQL insisted on Eloquent from php.
The Nightmare ORM provides a simple implementation of object relational mapping, for working with your database. Each database table has a corresponding Model (ECMAScript 6 class) which is used to interact with that table. Models allow you to query for data in your tables, as well as insert and update new records into the table. It is also safe because it prevents SQL injection.
-
using npm:
npm install nightmare-orm --save
-
using yarn:
yarn add nightmmare-orm
This module depends of a pool connection generated by the npm module "pg" also known as node-postgres.
To get started, all models must extend or inherit from Nightmare. In their construction method, they must receive an "data" parameter in case they want that model with all the information in the database mapped.
Also in the same constructor must execute the constructor of the father super, which must receive two parameters, first the pool of connections of PostgreSQL, second the name of the table to which that model belongs and finally the attribute "data" of constructor of the model itself.
Let's see in the following example we will see how the model is defined Profile:
const { Pool } = require('pg');
const Nightmare = require('nightmmare-orm');
//PostgreSQL connection pool
const pool = new Pool({
user: 'postgres',
host: 'localhost',
database: 'database',
password: '1',
port: 5432
});
//Model
class Profile extends Nightmare {
constructor(data = false) {
// table_name
super(pool, 'profiles', data);
this.fillable = [
'fullname', 'age', 'description'
];
}
//...
}
To create a new record in the database, create a new model instance, set attributes on the model, then call the save method
const profile = new Profile();
profile.fullname = 'john doe';
profile.age = 22;
await profile.save();
The save method may also be used to update models that already exist in the database. To update a model, you should retrieve it, set any attributes you wish to update, and then call the save method.
const profile = new Profile();
profile.find(1).then(myProfile => {
myProfile.age = 25;
myProfile.save();
});
// or use async/await
await profile.find(1);
profile.age = 26;
await profile.save();
To delete a model, call the delete method on a model instance:
await profile.find(1);
profile.delete();
Nightmare's query builder provides a convenient, fluent interface to creating and running database queries. It can be used to perform most database operations in your application.
const profile = new Profile();
try {
const profiles = await profile.select('*').execute();
console.log(profiles);
} catch(error) {
console.log('Error: ', error);
}
the "execute" method in which it performs the query based on the methods and parameters previously used in the instance. This method ends with the operation of the queryBuilder and returns a Promise with the result.
Specifying A Select Clause
Of course, you may not always want to select all columns from a database table. Using the select method, you can specify a custom select clause for the query.
const profiles = await profile.select('fullname', 'age', 'description').execute();
You may use the where method on a query builder instance to add where clauses to the query. The most basic call to where requires three arguments. The first argument is the name of the column. the second argument is the value to evaluate against the column. Finally, the third argument is an operator, which can be any of the database's supported operators. if the operator is equal ("=") the third parameter can be omitted
let profiles = await profile.select('fullname').where('age', 22, '!=').execute();
profiles = await profile.select('fullname').where('age', 20).execute();
profile = await profile.select('*').where('id', 20).limit(1).execute();
Of course, you may use a variety of other operators when writing a where clause:
const user = new User();
const chat = new Chat();
const userId = 22;
const users = await user.where('votes', 100, '>=').execute();
const users = await user.where('votes', 100, '<>').execute();
// json colunms
const message = await chat
.select("content->'messages'")
.where("content->'users'", userId, '@>')
.execute();
// WHERE AND
const data = await profile
.where('age', 20)
.where('fullname', 'john doe', '!=')
// you can use any numbers of where's
.orderBy('age', 'desc')
.limit(10)
.execute();
Inner Join Clause
The query builder may also be used to write join statements. To perform a basic "inner join", you may use the join method on a query builder instance. The first argument passed to the join method is the name of the table you need to join to, while the remaining arguments specify the column constraints for the join. Of course, as you can see, you can join to multiple tables in a single query:
const data = await user
.select('users.*', 'contacts.phone', 'orders.price')
.join('contacts', 'users.id', 'contacts.user_id', '=')
.join('orders', 'users.id', 'orders.user_id', '=')
.execute();
if the operator is equal ("=") this parameter can be omitted
You can get the data of an instance in json format using the toJson method, and thus leave aside all the information of methods and properties that the instance contains:
let user = new User();
user = await user.find(2);
user = user.toJson();
If we have the chats table, which is structured as follows:
chat | |
---|---|
id | int |
created_at | timestamp |
content | jsonb or json |
generate a javascript object I have installed it directly in an orm query:
const newChat = new Chat();
newChat.created_at = '2004-10-19 10:23:54';
newChat.content = {
users: [1, 7, 6],
messages: [
{ text: 'hello', user: 1 },
{ text: 'world', user: 7 }
],
messageCounter: 2
};
await newChat.save();
const chatToUpdate = await chat.find(1);
chatToUpdate.content.messages.push({ text: 'yeah!', user: 6 });
chatToUpdate.content.messageCounter += 1;
await chatToUpdate.save();
the documentation is not yet ready, we are working on it