API Routes Get Data from Database

As promised in our last article, this article focused on retrieving the data from the database (in the Next.js api routes) and return the JSON object to the browser. It proves Next.js is absolutely a full-stack framework.

We'll quickly set up the database (MySQL/MariaDB) for this article. And then see how to retrieve the data. In this article, you must have at least a basic familiarity with MySQL or MariaDB.

Create the database nextjsDemo and create the table users.

create database nextjsDemo;
create table users(
    user_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL
);

The next step is to insert few lines of data into it.

INSERT INTO users (user_id, name, email) 
    VALUES 
(1, 'Michael Foo', 'michael@foo.com'), 
(2, 'John Cena', 'john@example.com'), 
(3, 'Zoho S', 'zoho@example.com');

Install the Node MySQL package

npm install mysql

In our last article, we created a file getuser.js under the api folder. Delete all the contents of the file and add the following:

const mysql = require('mysql');
let connection = mysql.createConnection({
    host: 'localhost',
    user: 'your_db_user',
    password: 'your_db_password',
    database: 'nextjsDemo'
});


connection.connect(function(err) {
  if (err) {
    return console.error('error: ' + err.message);
  }
  console.log('MySQL Connection Successful.');
});

function getUsers(req, res) {
    res.status(200).json({ users: 'Will return user object'})
}


export default getUsers;

If you visit the page http://localhost:3000/api/getuser and, you'll see "{"users": "Will return user object"}" And on the command line, you'll see 'MySQL Connection Successful.'

We have successfully set up the MySQL connection. It already proves that Next.js is a Full Stack React Framework. The next task is to retrieve the data from the database.

We need to use the query function to retrieve the data from the database.

Update the function code as following:

function getUsers(req, res) {
    const user_query = "select * from users";
    connection.query(user_query, function (error, result, fields){
        if (error) throw error;
        res.status(200).json({ users: result})
    })
}

If you visit the page again http://localhost:3000/api/getuser and, you'll see the user object.

But there is one catch, in the console, there is a warning 'API resolved without sending a response for /api/getuser, this may result in stalled requests'. It's due to the async nature of NodeJs. You can fix by using a promise and make the export function async.