Jsqel is a lightweight framework that helps you to build APIs connected to a postgresql database.
Why Jsqel ?
As a full-stack freelance developer, I code webapps for companies : CRM, ERP, B2B e-commerce, automation.
My favourite stack to build them is NodeJS / Express / postgresql for the back-end, and React for the front-end.
There are many nodeJS frameworks : FeatherJS, Sails, LoopBack, NestJS, ... It is hard to choose the one that fit all our projects, and it is usually painfull to work with several frameworks beacause of their learning curve.
And I don't like ORMs. I think they turn the database engines into powerless softwares. I prefer to take advantages of the amazing built-in features of Postgresql, rather than encapsulating them in an ORM.
I've searched a lightweight Express framework, that simply exposes API endpoints based on parameterized SQL queries, with a build-in authentication process. I did not find it. That's the reason why I made jsqel
.
Jsqel in a nutshell
You must have a Postgresl instance running. For this example, let's consider that your postgresql database schema has a users
table.
Install jsqel :
yarn add jsqel
To create an API that returns the rows of users
, just write an index.js
file :
const jsqel = require("jsqel");
// Declare an API 'hello' that returns the rows of the Users table
// with a 'Public' access (no login required)
const hello = {
name: "hello",
sql: "SELECT * FROM users;",
restricted: ["Public"],
};
// Configure your database URI
const app = jsqel({
dbUri: "postgresql://user:pwd@postgresqlhost:5432/mydatabase",
secret: "anysecretkeyyouwant",
debug: process.env.NODE_ENV !== "production",
apiUrlBase: "",
});
app.register((namespace = "test"), [hello]);
app.run(5000);
Run node index.js
.
Now the API is available at : http://localhost:5000/test/hello :
curl -X POST http://localhost:5000/test/hello
Modules and namespace
Each route looks like :
https://<your host>/<apiUrlBase>/<namespace>/<api name>
-
<your host>
is the server that hosts the jsqel node process -
<apiUrlBase>
is the additionnal prefix you set in thejsqel
declaration -
<namespace>
is the name you've passed in theregister
call -
<api name>
is the name you've set in thename
attribute of the api object.
To keep all you APIs well organized, you may :
- have 1 javascript file for all the APIs of 1 domain (or 1 SQL table)
- set the
apiUrlBase
according to the configuration of yournginx.conf
file
Here is a sample :
// File backend/endpoints/faqs.js
// Get the list of the faqs
const faqs = {
name: "faqs",
sql:
"SELECT faqs.question, faqs.answer, category.label, category.id \
FROM faqs,category \
WHERE faqs.category_id=category.id ORDER by faqs.id ASC ;",
restricted: ["Public"],
};
const categories = {
name: "categories",
sql: "SELECT label, id FROM category ORDER by id ASC ;",
restricted: ["Public"],
};
module.exports = { queries: [faqs, categories] };
// File backend/index.js
const jsqel = require("jsqel");
const dbUri = "postgresql://user:pwd@postgresqlhost:5432/mydatabase";
const app = jsqel({
dbUri,
secret: "anysecretkeyyouwant",
debug: false,
apiUrlBase: process.env.NODE_ENV === "production" ? "/api" : "",
});
const auth = require("jsqel/modules/auth");
const faqs = require("./endpoints/faqs");
const initBackend = async () => {
// Init DB when backend is launched like : node index.js init
if (process.argv.length === 3 && process.argv[2] === "init") {
await app.migrate(__dirname + "/sql/schema.sql");
await app.migrate(__dirname + "/sql/functions.sql");
await app.migrate(__dirname + "/sql/triggers.sql");
}
// Migrate & register built-in modules
await app.migrateAndRegister("auth", auth);
// Migrate user-defined modules
await app.migrateAndRegister("faqs", faqs);
};
// Build the endpoints then launch server
initBackend()
.then(() => app.run())
.catch((e) => console.log("Something went wrong : ", e));
Parameters validation
Usually you want to pass some parameters to the SQL queries. You also expect to prevent any SQL injection.
You can add a params
object to the endpoint declaration. It contains the expected parameters by the API, and a function to validate them.
The validation function must look like :
const validationFunction = value => {
if (everythingIsOkWith(value)) {
return {success: true, value})
} else {
return {success: false, message: "Error" })
}
}
Example :
const check = {
name: "check",
sql: "SELECT * from users WHERE username=${username};",
restricted: ["Admin"],
params: {
username: (value) =>
value && value.length && value.length > 4
? { success: true, value }
: { success: false, message: "username should be longer" },
},
};
HTTP POST only
An API endpoint made with Jsqel accepts only POST requests, with :
- An authorization header "Bearer : token"
- A json body containing parameters
This is not compliant with the REST architectural style. No GET, nor PATCH or DELETE. The idea is to have a simplified way of using the APIs in the front-end (see "Connect to front-end" below).
Hooks
You can add hooks functions to a query :
- beforeQuery : to pre-process the parameters and returns a new set of parameters
- alterQuery : to compute a new query
- afterQuery : to post-process the results and returns a new set of results
Theses hooks must be pure functions without side-effects nor mutations.
Authentication
Each query that is restricted to an authenticated role has 2 additionnal parameters injected by Jsqel :
-
user_id
-
role
You can use theses parameters in your query.
const private_hello = {
name: "private_hello",
sql: "SELECT * FROM Hello where (user_id=${user_id} or ${role}='Admin') and message like ${filter}", // Auto inject user_id and role
restricted: ["Member", "Admin"], // private query, request need authentication bearer
params: {
filter: (value) => ({ success: true, value }),
user_id: (value) => ({ success: true, value }), // Injected paramter for an authenticated query (which does not contains 'Public' in restricted)
role: (value) => ({ success: true, value }), // Injected paramter for an authenticated query (which does not contains 'Public' in restricted)
},
beforeQuery: (query, params) => {
console.log("Filter : ", params.filter);
return params;
},
afterQuery: (query, params, results) => {
console.log("Got the result !");
return results;
},
};
Theses user_id
and role
are encoded and signed in the authentication token of the HTTP header.
You can use them to :
- restrict the API access. If the
restricted
array of the API description contains['Admin']
, the API reject with a 401 code any request with arole
that is notAdmin
in the token. - add a WHERE CLAUSE or anything else in the query. You may want to return all the rows for an Admin profile, but only 1 row where the user id matches the
user_id
injected by Jsqel.
Direct routes
Sometimes you need to implement something different from a SQL query. An upload controller for example. In this case, you can configure a direct route :
// Adding upload route
const directRoute = (app, namespace, apiUrlBase) => {
console.log("Registering direct route : ", apiUrlBase + "/" + namespace + "/upload");
app.post(apiUrlBase + "/" + namespace + "/upload", (req, res, next) => {
// Credentials are injected in req.paramsWithCredentials if needed
console.log("directRoute with credentials :", req.paramsWithCredentials);
res.send("This is my response");
});
};
const direct = {
name: "direct",
route: directRoute,
restricted: ["Admin"], // Mind the Capital
};
module.exports = { queries: [direct] };
Connect to front-end
In your React components, just use a custom hook to call you API :
const [{ results, error, loading }, refresh] = useJsqel("test/hello", { sendItNow: true });
A full boilerplate is available here, including :
- backend with auth and admin modules
- a sample front-end with react hook
- a react-admin back-office
Contributions
Feel free to open issues and submit PR !