13

I'm using express 3 in my node application and I've broken my routes into separate files...

app.use('/', routes);
app.use('/users', users);

The problem is that I need a database connection in many of these routes. Should I be connecting to the database in each of the route files or can I connect in my main app file and somehow pass the connection to the includes?

I used express-generator to create a skeleton app. In app.js the routes are included like this...

app.use('/', routes);
app.use('/users', users);

And in each other these files there are routes as follows...

var express = require('express');
var router = express.Router();

router.get('/', function(req, res) {
    res.render('index');
});
michael
  • 4,427
  • 6
  • 38
  • 57
  • can you show how `routes` or `users` is defined? – Setthase Sep 04 '14 at 16:15
  • Possible duplicate of [How to properly pass mysql connection to routes with express.js](http://stackoverflow.com/questions/16800418/how-to-properly-pass-mysql-connection-to-routes-with-express-js) – dev Mar 28 '16 at 23:20

2 Answers2

32

I simply keep the database connection in the app:

app.set('db',new MyDAO(config)); <-- use where you define your routes.

then, inside the route, in get() or post() I simply do

req.app.get('db').usercollection.find()

This way you keep your database connection pool attached to the gloabl application context.

Alternative common approach is to extend req on every request, but it is executed every time:

app.use(function(req,res,next){
    req.db = db; //this db comes from app.js context where you define it
    next();
});
Alex Pakka
  • 9,466
  • 3
  • 45
  • 69
  • What is new `MyDAO(config)`? If I was using monk and did `var db=require('monk')('localhost/dbname');` would that be the same? I mean it works what I'm doing, just not sure if it's the same as you described. – Jens Bodal Mar 08 '16 at 04:03
  • 2
    Yes. Monk is kind of a universal Data Access Object (DAO) for Mongo. `MyDAO` here is simply an abstraction around backend to retrieve and store objects in readable and maintainable way. It typically has `getUserById()` or `getTodaysBlogEntries()` or `addChatMessage()` or similar, business-logic specific methods. If you change backend from MySQL to Mongo, you don't have to change the code in upper layers, you can just replace DAO. See e.g. https://en.wikipedia.org/wiki/Data_access_object – Alex Pakka Mar 08 '16 at 04:13
  • great! save my day! – Amos Apr 23 '19 at 10:42
0

The prevailing answer didn't work for me because I used the typescript-express-starter project which places the database functionality one level deeper into the project from the controller level to the services level. And short of using doing service.call(req.app.get('db'), deleteParams) to every service call there had to be another strategy.

Instead I just used a static db.ts file to declare and initialize the database value:

import { Knex } from 'knex';

const db: Knex = require('knex')({
  // debug only in development
  // this will cause knex to print extra helpful lines in the console
  debug: process.env.NODE_ENV === 'development',
  client: 'pg',
  version: '13.2',
  connection: {
    host: process.env.RDS_HOSTNAME,
    user: process.env.RDS_USERNAME,
    password: process.env.RDS_PASSWORD,
    database: process.env.RDS_DB_NAME,
  },
});

// perform a test query to verify the connection to the DB was successful
db.select('*')
  .from('auth_group')
  .then(rows => {
    console.log(rows);
  });

export default db;

And then in the service-level code I just imported it directly:

import db from '../db';
...
db.select()
...
fIwJlxSzApHEZIl
  • 11,861
  • 6
  • 62
  • 71