Database migrations in NextJS - Mini Project: Build & Apply

Database Migrations with Next.js

📖 Scenario: You are building a Next.js app that needs to store user profiles in a database. To keep your database organized and up to date, you will create a simple migration system to add a users table.

🎯 Goal: Build a basic database migration script in Next.js that creates a users table with id, name, and email columns.

📋 What You'll Learn

Create a migration file with SQL commands to create the users table

Add a configuration variable for the database connection string

Write a function to run the migration using the connection string

Export the migration function for use in the Next.js app

💡 Why This Matters

🌍 Real World

Database migrations help keep your app's database structure consistent and up to date as your app evolves.

💼 Career

Understanding migrations is essential for backend and full-stack developers working with databases and modern web frameworks like Next.js.

1

Create the migration SQL string

Create a constant called migrationSQL that contains the SQL command to create a users table with columns id (integer primary key), name (text), and email (text). Use a template string for the SQL.

NextJS

# Your code here

Need a hint?

Use backticks ` to create a multi-line string for the SQL command.

2

Add the database connection string

Create a constant called connectionString and set it to the string 'postgresql://user:password@localhost:5432/mydb' to represent the database connection URL.

NextJS

const migrationSQL = `
CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT,
  email TEXT
);
`;

# Your code here

Need a hint?

Use single quotes for the connection string.

3

Write the migration function

Write an async function called runMigration that connects to the database using connectionString, runs the migrationSQL command, and then closes the connection. Use the pg library's Client class. Import { Client } from 'pg' at the top.

NextJS

import { Client } from 'pg';

const migrationSQL = `
CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT,
  email TEXT
);
`;

const connectionString = 'postgresql://user:password@localhost:5432/mydb';

// Your code here

Need a hint?

Remember to use await for asynchronous calls and to close the client connection.

4

Export the migration function

Add an export statement to export the runMigration function as a named export from the module.

NextJS

import { Client } from 'pg';

const migrationSQL = `
CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT,
  email TEXT
);
`;

const connectionString = 'postgresql://user:password@localhost:5432/mydb';

async function runMigration() {
  const client = new Client({ connectionString });
  await client.connect();
  await client.query(migrationSQL);
  await client.end();
}

// Your code here

Need a hint?

Use a named export to make the function available to other files.