Can't do queries from another file (connection.query is not a function)

Question

im new in node.js and databases. Trying to do mysql query, but i getting this error TypeError: connection.query is not a function.

Im using Node MySQL 2 (https://www.npmjs.com/package/mysql2) package;

Connection and queries in app.js file works fine, but in another file it throws that error.

What can be wrong with my code?

app.js

const chalk = require('chalk');
const debug = require('debug')('app');
const morgan = require('morgan');
const path = require('path');
const mysql = require('mysql2');

const app = express();
const port = process.env.PORT || 3000;

const connection = mysql.createConnection({
  host: '...',
  user: '...',
  password: '...',
  database: '...',
});

connection.connect((err) => {
  if (err) {
    debug(`error connecting: ${chalk.red(err.stack)}`);
    return;
  }

  debug(`connected as id ${chalk.green(connection.threadId)}`);
});

app.use(morgan('tiny'));

app.use(express.static(path.join(__dirname, '/public/')));
app.use('/css', express.static(path.join(__dirname, '/node_modules/bootstrap/dist/css/')));
app.use('/js', express.static(path.join(__dirname, '/node_modules/bootstrap/dist/js/')));
app.use('/js', express.static(path.join(__dirname, '/node_modules/jquery/dist/')));
app.set('views', './src/views');
app.set('view engine', 'ejs');

const nav = [{ link: '/books', title: 'Books' },
  { link: '/authors', title: 'Authors' }];

const bookRouter = require('./src/routes/bookRoutes')(nav);

app.use('/books', bookRouter);
app.get('/', (req, res) => {
  res.render(
    'index',
    {
      nav,
      title: 'Library app',
    },
  );
});

app.listen(port, () => {
  debug(`listening on port ${chalk.green(port)}`);
});

module.exports = connection;

bookRoutes.js

const mysql = require('mysql2');
const debug = require('debug')('app:bookRoutes');

const connection = require('../../app');

const bookRouter = express.Router();

function router(nav) {
  const books = [
    {
      title: 'Nocturna',
      genre: 'Fantasy',
      author: 'Maya Motayne',
      read: false,
    },
    {
      title: 'Finale',
      genre: 'Fantasy',
      author: 'Stephanie Garber',
      read: false,
    },
  ];

  bookRouter.route('/')
    .get((req, res) => {
      connection.query('SELECT * FROM `books`', (error, results) => {
        debug(results);
        res.render(
          'bookListView',
          {
            nav,
            title: 'Library app',
            books,
          },
        );
      });
    });

  bookRouter.route('/:id')
    .get((req, res) => {
      const { id } = req.params;
      res.render(
        'bookView',
        {
          nav,
          title: 'Library app',
          book: books[id],
        },
      );
    });

  return bookRouter;
}

module.exports = router;

EDIT: Project tree

├── app.js
├── package-lock.json
├── package.json
├── public
|  ├── css
|  |  └── styles.css
|  └── js
└── src
   ├── routes
   |  └── bookRoutes.js
   └── views
      ├── bookListView.ejs
      ├── bookView.ejs
      └── index.ejs

Thanks for help.

dusthaines · Answer 1 · 2019-09-25T14:07:57.523

0

bookRoutes.js

const mysql = require('mysql2');
const debug = require('debug')('app:bookRoutes');

const connection = require('./../../app').connection; // identify the exported module

// the rest…

edited Sep 25 '19 at 14:07

answered Sep 25 '19 at 12:39

dusthaines

1,320
1
11
17

Now getting this error `TypeError: Cannot read property 'query' of undefined` – ArtisDev Sep 25 '19 at 12:47
Are you certain the relative path for your `app.js` file is correct? Might it need to be: `./../../app` ? If you'd like to edit your post and add your folder/document tree we can confirm. (handy module for quick tree printout: https://www.npmjs.com/package/tree-cli) – dusthaines Sep 25 '19 at 13:44
Done, I added project tree in post – ArtisDev Sep 25 '19 at 13:56
Very good, thanks. I do believe you'll want the leading single period and slash in your path. See my edited answer for example. Also not sure if you'll still need `.connection` of not. Been a while since I've used this approach as I generally make my db `global` to skip doing this on every file (see an example here: https://github.com/dusthaines/mysqljs_setup_snippet/blob/master/app.js) – dusthaines Sep 25 '19 at 14:11
Didn't help, tried to remove `.connection` too but still no luck. I will try to look at your link, thanks. – ArtisDev Sep 25 '19 at 14:18
Sorry to hear and good luck. There are many perspectives on this topic, you can read more here (and via links from within): [https://stackoverflow.com/questions/21229944/node-require-absolute-path] Regarding my example, note that I use the `mysqljs/mysql` module while you are using `mysql2` not sure if it matters for this example but wanted to make sure you're aware. – dusthaines Sep 25 '19 at 14:32

Can't do queries from another file (connection.query is not a function)

1 Answers1