Sails.js: Interfacing with postgreSQL
Sails is an awesome Node.js framework built on top of Express. I am currently working on two projects using the latest version 0.10.5 of this framework. I personally like Sails a lot due to it’s simplicity, ease of use, superfast development time and waterline ORM which is database agnostic while at the same time also provides you with support to write native database code for most of the popular databases like PostgreSQL, MySQL, MongoDB, etc.
You can easily find some really good tutorials on the web for creating a sails.js project. In this article we are going to talk about using postgreSQL as your underlying database and how to use native postgreSQL code just in case needed and some of the pitfalls that I faced during development.
I am assuming you already have postgreSQL server installed on your machine. If you need help with that, you may refer to the article below:
Please note that if you are trying to access a remote postgres server, you will have to configure the pg_hba.conf file of the postgres to allow it to accept remote connections. More on that in the link below:
Ok, now coming back to Sails configuration part. But first, you need to make sure you have installed the sails-postgresql adapter. For that, go to your project directory and type the command in terminal:
sudo npm install sails-postgresql Now coming to configuration. In the latest version of sails (0.10.5 ) , you need to add the configuration in the config/connections.js file. In earlier version (0.9.9), it used to be config/adapters.js file. Here is the sample file:
module.exports.connections = {
postgres: {
adapter: 'sails-postgresql',
host: 'localhost',
user: 'postgres',
password: '<postgresql password>',
database: 'mydatabase'
}
};To avoid any issues, please make sure the unix password and database password for user postgres are the same. That’s about it, you are ready to use postgres with your sails project. Congratulations! you have successfully configured your sails project to interface with postgres server.
May the force be with you!
Ok, now you have everything you ever wanted. Well almost everything, since there is always more to life. Waterline ORM supports associations as well but sometimes you might find yourself in a situation where you absolutely want to write a native SQL query since it may involve joining a lot of tables and/or some complicated logic. For that, you can use Model.query function of Sails. Here is an example:
var myQuery = "select id from users";
sails.log.debug("Query :", myQuery);
User.query(myQuery, function (err, users){
if(err || !users.rows.length){
return res.json({"status": 0, "error": err});
}
else{
return res.json(users);
}
});
Note: Name of the model does not matter when you are executing native query. If there is another model named Department and instead of User, I had used Department.query, I would have still got the same result. It does not matter what model name you give provided that model exists.
The query, on successful execution returns an object users which contains, among other keys, the rows array. This is the data that we are interested in. It will be in the form:
[ { "id": 1 }, { "id": 2 }, { "id": 3 } ]Just in case you don’t want the output in this way and want an array of user ids as the output, you can change your SQL query like this:
select json_agg(id) as user_ids from users;If you were wondering why I used postgres in the first place, well here is the reason why? Postgres is a relational database which supports all the RDBMS features like transactions and joins and also comes with an amazing datatype “JSON” which makes it extremely awesome to interface with server side javascript code. json_agg will return a JSON array. Postgres also has an array datatype and similarly and array_agg function. However, unlike JSON arrays, postgres arrays are homogeneous. Anyways, that’s another story. Now the users object will be different than before. To access the data, you need to refer to users.rows[0].user_ids. Directly referring to this might cause your server to crash in case no results are returned. So it is better if you check each property before referring to it. For example,
var result = users && users.rows && users.rows[0] && users.rows[0].user_ids || [];This way, you can ensure that the result is never undefined. Because postgres will return null and not return a blank array if it does not find any results. This is a dangerous pitfall that you must always be aware of while playing with native code. So, the output will be an array containing user ids or a blank array if no user ids exist. You can tweak your SQL query in different ways to observe the output and get a good grasp of how the interaction between waterline and postgres works with native queries. Hurray! you can now write native SQL queries in your project! Congratulations.
You can do a lot of things with what you have learnt so far. For many of the applications, that’s quite a lot of flexibility and power. However, if you are wondering if there is still something left, well read on . . .
There can be a situation where you would want to execute a really complex SQL code consisting of say 10 or more queries and then return the result of the computation. Also, you want to execute all this logic as a single transaction which will either commit completely or rollback if any query fails at any step. You may use postgres functions for the same. Please note that more you move towards native code, you start losing the benefit of waterline being an amazing database agnostic ORM and might face a lot of challenges if you plan on migrating your database to some other DBMS. Also, functions need to be compiled in database directly so they are not synced with your sails project. In order to do that, you can create a folder in your project directory, put all the stored functions in that directory and create a grunt task to compile all the projects in that directory. I was trying to figure out how to compile all the functions on sails lift but was facing issues and also not very familiar with grunt. However, I will share with you, how to do the basic setup. But first, lets take a look at how to work with native postgres functions in sails.
Let’s say there is a function named some_function(arg1, arg2) in postgres. To execute in psql, you simply write:
select some_function(arg1, arg2);Now, in sails, you need to treat your function like a simple SQL query and execute it in Model.query function like you did before.
var myQuery = "select generate_add_requests("+user_id+")";
Notification.query(myQuery, function (err, userIds){
if(err){
return res.json(err);
}
else{
userIds = userIds && userIds.rows && userIds.rows[0] && userIds.rows[0].generate_add_requests || [];
return res.json(userIds);
}
});Note how the result object of the function call is structured. It’s userIds.rows[0].generate_add_requests, where generate_add_requests is the name of the function. You need to be cautious about the pitfalls that we talked about in executing native queries.
Functions in postgres can have different types of return type. For example, integer, text, json, etc. However, most of the times if your function is returning a record or a set of records which you need to use in your sails application, your function should return a json object or a json array. To return a json object, you may use row_to_json function of postgres. But note that it will fail if there are more than a single row. To return a json array, use json_agg function of postgres. Here is an example of a function:
CREATE OR REPLACE FUNCTION return_single_user( p_user integer
)
RETURNS json AS
$$
BEGINRETURN row_to_json(users) from users where id = p_user;
END;
$$
LANGUAGE 'plpgsql';
To return a json array, the function changes to:
CREATE OR REPLACE FUNCTION return_multiple_users()
RETURNS json AS
$$
BEGINRETURN json_agg(users) from users;
END;
$$
LANGUAGE 'plpgsql';
That’s it. Now you have learned how to use native postgres functions with sails. Functions come with infinite power. But remember
With great power, comes great responsibility!
Now, you have learned pretty much everything you need to interface postgres with your sails project. There are pros and cons of using native functions. For example, if you find yourself executing a lot of queries in a single operation, then you will be connecting with database multiple times which will lead to multiple to and fro movement of data between database and your webserver. Also, if your web server and database server are not on the same machine this can cause network overhead. Functions give you the ability to execute complex logic in a single transaction. There are many more benefits of using functions. However, this may lead to difficulty in code management and version control since you can directly compile a function in the database without updating the code in your project. So, a good practice in my opinion would be to keep all the stored functions in separate files and put them under a specific directory in your project. For example sql/functions. Now you can create a grunt task to compile all the functions in this directory. You will need the npm module grunt-pg-utils for this. You can refer to this tutorial on how to set this up:
If you find any luck in configuring it so that functions are automatically compiled on sails lift, please do let me know . . .
That’s pretty much it finally. Happy coding!!! ☺
