Correct Database usage

Need some advice on an api application built using:

- express

- knexjs

- postgres

My question is if im handling database operations correctly or if im doing it completely wrong.. See example

var knex = require('knex');
app.get('/example/:param',
loadWithParam(),
updateWithParam(),
render.staticOK()
function loadWithParam() {
return function(req, res, next) {
var db = knex({conConfig});
db(tableName).where(field, req.params.param).select().then(function(rows) {
db.destroy(function() {res.locals[tableName] = rows;next();
});
}).catch(function(err) {
...
});
};
}
function updateWithParam() {
return function(req, res, next) {
if(res.locals[tableName].length === 0) {
return next();
}
var db = knex({conConfig});
db(tableName).where(field, req.params.param).update({field:value}).then(function() {
db.destroy(function() {
next();
});
}).catch(function(err) {
...
});
};
}

In short my question is if its good or bad practise opening up and closing database connections per operation required to run? Ive been told setting up a database connection on res.locals upon receiving a request is better, but i disagree..

I'm not 100% sure about knex, but by default the node Pg library caches connections, so it's not an issue. Might be worth checking the docs or the source though to make sure it's using that code path.

Opening and closing database connection per request is a very bad idea. Database connection should be opened permanently and re-used across many different requests, this way you save milliseconds establishing it.

 

knex allows connection pooling. Use it.

Any reason not using pg-native?

You can load your config into Knex in, for example, db/client.js

Then export that knex instance out into your data access objects

Thanks for the replies. The reason I built it this way was beacuse of these reasons:

- Each user has its own Database with a list of schemes. (IE new connection every time, can't reuse a mass pool on a specific database)

- Spamming API requests would open up a massive amount of connections, that are never killed off, resulting in the postgres instance to eventually be smocked full.

This is why I thought opening and closing was good. How would you recommend handling the above issues?

Hmm, there are two things that feels funny here.

1) Each user has its own Database with a list of schemes

2) Spamming API requests would open up a massive amount of connection.

What do you think postgres says about millions of databases? Normally you share your users in a single database and selects the correct "view" using a "where" clause. I think you should log in to your database once and use connection pooling. Put a HAProxy infront of your outward facing servers and limit the number of simultaneous connections per ip.

I prefer knexjs because of simplicity and the amount of features it supports. Also allows for neater code, in the sense that it's a lot easier to read.

For instance I've made an external JS file included in most of my routes that load data from the database. I simply call a function load(tableName) and that JS file will find a connection, parse all the req.params / req.query properties and add them to the whereclause, including other things as well like limit/offset/orderby/etc. All done in less than ~30 lines of code, which is great.

I guess I wasn't clear enough.

1) Each User has a Db (Master) containing schemes per user (UserScheme) all containing the same type of tables, and datastructure. This is to make sure all data is split correctly.

2) Limiting the IP is dumb, in my opinion. An API is an API, should work without applying limits in response to somewhat "broken" code. Hence the question above regarding my code snippet, whether it was right or wrong.

I rewrote most of it already and it's working much better now. I create pools for each User/Scheme that when idled for too long (5+min) will be destroyed. Everythings working flawlessly now so I guess this discussion can be considered solved.

I'm really curious about why your data needs to be 'split correctly'. If the table structures are the same, couldn't you include everything together and just put Master/Userscheme info in WHERE clauses?

Think of it this way.. Let's say you have and are maintaining 4 different hosted forums, but they all use the same source and structure (let's say vBulletin). Would you prefer to keep tables & data of all these forums on the same Database/Schema, or would you rather divide it into several databases?

I don't know how better to describe it. It may be similar structures, but the data is unique to each user, hence why I want to keep it divided. And it worked out in the end.

I can't speak for this person, specifically, but at my work we have legal requirements for keeping customer data separate. We also have SLAs for delivering analytics on this data and so keeping them on separate DB instances (and physical machines) allows us to actually fulfill these guarantees.

Sometimes there are valid reasons to split very similar databases.

I'm not him, but I've used the split schema pattern before. It helps a lot in a few cases.

a) You want to provide a full export for the customer, you can dump the schema really simply.

b) You don't want the serial columns to jump drastically between users. Like an Order ID.

c) You want to upgrade customers on a rolling basis - you can apply migrations to each schema separately. 

d) You may move users to a dedicated server in the future, or shard them across more machines.

These can all be handled in other ways of course, but sometimes it makes it a lot simpler.