Preql is a library to make writing SQL in javascript bearable, inspired by Yesql.
##Rationale##
Sometimes, when accessing a SQL database in Javascript, you don't neccesarily want or need an ORM. But, writing something like
function getPost(id){
client.query('SELECT * FROM posts WHERE id=$1',[id]);
}
Not only gets repetitive for multiple models, it's hard to read, there's no syntax highlighting, and multiline strings are annoying to format.
Enter Preql
##Usage##
To use Preql, have a file with your sql query:
SELECT *
FROM posts
WHERE author = 'John Doe'
LIMIT 10;
(Note that while this example uses postgres syntax, preql works with any sql database).
Then, in your javascript file, call preql and now it can be used as a regular javascript function.
var preql = require('preql');
var pg = require('pg');
var getPosts = preql.makeQuery('./some/directory/get_posts.sql');
getPosts(client.query.bind(client), function(err,result){
console.log(result);
});
The functions generated by preql take three parameters. The first is the query function, typically generated by your database client. So where you would normally say
client.query('SELECT * FROM posts',callback);
with preql you would write
getPosts(client.query.bind(client),callback);
The reason you have to call .bind(client) is to compensate for javascript's lexical scoping, allowing the query function to act normally in a closure.
Preql also allows you to recursively require an entire directory of files, creating queries out of the .sql files in that directory. For example, if you had a directory like
/queries
/users
find.sql
getPosts.sql
createPost.sql
file.txt
when you call
preql.makeQueries('./queries');
it will return { getPosts: [Function], createPosts: [Function], users: { find: [Function] } }
To install: npm install preql
Presql uses mocha and should for tests, run npm test to run the test suite.
##Todo##
- Allow for multiple queries per file
Written by Michael Fine