Building dynamic applications often involves connecting to a database to store and retrieve data. Next.js provides an excellent platform for creating server-side rendered and statically generated applications. In this article, I'll walk you through the process of integrating MySQL with your Next.js app. From installing dependencies to executing raw queries, this comprehensive guide will show you how to connect your Next.js app to MySQL and harness the full potential of database-driven applications.
I'll be using Knex.js. At the time of this writing, Prisma is the hot ORM for JavaScript. While Prisma does have some mind-blowing advantages, I still prefer Knex.js. Mainly for three reasons:
I can write my queries in raw SQL, with parameter binding. Whenever I'm writing code to interact with a database, I tend to write the SQL first anyway (in something like DBeaver or MS Management Studio). From there I can use that raw SQL instead of porting it over into ORM-specific code.
While I could couple my database-accessing code to a library like
mysql2
, but Knex.js provides nice abstractions so I can more easily swap between the RDBMSs I typically use (SQLite, MariaDB, and MSSQL).I am familiar with it. It's tried and true. I can develop faster with it.
Prerequisites
A working Next.js app
A database set up in MySQL or MariaDB. See my Getting Started with MariaDB article.
Install Dependencies
This solution uses the knex
and mysql2
packages. Install them as dependencies.
npm install knex mysql2
Set Environment Variables
Set the following environment variables in whichever way makes sense for your situation. If you are connecting to a development database server from a development machine, you may create a file called .env.local
in the root directory of your app.
DB_HOST= 127.0.0.1
DB_PORT= 3306
DB_DATABASE= [database name]
DB_USER= [database user name]
DB_PASSWORD= [database user password]
You may need to change the host or the port depending on your setup.
Create a Database Connection File
In your Next.js app, create a new file called lib/db.ts
.
import knex from "knex";
export const db = knex({
client: "mysql2",
connection: {
host: process.env.DB_HOST,
port: process.env.DB_PORT,
user: process.env.DB_USER,
password: process.env.DB_PASSWORD,
database: process.env.DB_DATABASE,
},
});
We'll import this file into any file that needs to connect to the database.
Get Data from the Database Server-Side
Inside a Next.js page like pages/index.tsx
, import the db
function from the file we just created.
import { db } from "../lib/db";
Then create a getServerSideProps()
function. Next.js will use this to get data server-side and pass them as props to the page.
export async function getServerSideProps() {
const message = await db("Message").first("text").where({ id: 1 });
return {
props: { message },
};
};
The code above assumes you have a table named
Message
with columns forid
andtext
. Change the code to fit your data and what you're trying to get from it.
Now, you can use the prop in the page's render function.
export default function Home({ message }) {
return (
<main>
<h1>{message.text}</h1>
</main>
)
}
Simple!
Using a Raw Query
Knex.js has a built in raw()
function that allows you to execute raw SQL. Unfortunately, the format of the data returned by this function doesn't work well with Next.js.
However, we can write a simple wrapper around it to help us.
Add the following to lib/db.ts
.
export const raw = async (sql: string, bindings?: any) => {
const result = await db.raw(sql, bindings);
const data = result[0];
const parsed = JSON.parse(JSON.stringify(data));
return parsed.length === 1 ? parsed[0] : parsed;
};
Now on your page, you can import this function.
import { db, raw } from "../lib/db";
And you can use it to execute raw queries in getServerSideProps()
.
const message = await raw("SELECT text FROM Message WHERE id = 1 LIMIT 1;");
Now you know how to establish a reliable connection between your Next.js app and a MySQL database. You can interact with the database server-side, retrieve data, and present it to your clients. Whether you are building a small blog or a large-scale application, the ability to integrate databases into your Next.js projects opens up a world of possibilities for data-driven functionality.