After we dockerized our development area from the „Learn Next.js“ tutorial in my last tutorial at https://niklas-stephan.de/blog/learn-next-js-chapter-18/ we will learn how to switch from Vercel’s database offering to a local PostgreSQL database.
At first we navigate to filder /docker/dev inside our project.
docker-compose.yml:
version: "3.9"
services:
nextjs-db:
image: postgres:15
restart: unless-stopped
volumes:
- ../../db:/var/lib/postgresql/data
env_file:
- .env
healthcheck:
test: ["CMD", "pg_isready", "-U", "${POSTGRES_USER}", "-h", "${POSTGRES_HOST}", "-d", "${POSTGRES_DB}"]
interval: 10s
timeout: 5s
retries: 5
networks:
- default
nextjs-dashboard:
build: .
ports:
- 3000:3000
restart: unless-stopped
depends_on:
nextjs-db:
condition: service_healthy
volumes:
- ../../:/usr/src/app/
- ./app-startup.sh:/usr/src/app/app-startup.sh
networks:
- default
env_file:
- .env
nextjs-pgadmin:
image: dpage/pgadmin4:latest
ports:
- 5050:80
restart: unless-stopped
networks:
- default
environment:
- PGADMIN_DEFAULT_EMAIL=admin@admin.com
- PGADMIN_DEFAULT_PASSWORD=postgres
.env
POSTGRES_URL="postgres://USER:PASSWORD@nextjs-db:5432/verceldb" POSTGRES_PRISMA_URL="postgres://USER:PASSWORD@nextjs-db:5432/verceldb?pgbouncer=true&connect_timeout=15" POSTGRES_URL_NON_POOLING="postgres://USER:PASSWORD@nextjs-db:5432/verceldb" POSTGRES_USER="USER" POSTGRES_HOST="nextjs-db" POSTGRES_PORT=5432 POSTGRES_PASSWORD="PASSWORD" POSTGRES_DB="verceldb" POSTGRES_DATABASE="verceldb" # `openssl rand -base64 32` AUTH_SECRET=HASH
app-startup.sh
#!/bin/bash echo Installing node modules from package.json npm install --no-progress echo Create DB schema and example data if not exisiting npm run seed echo Starting your Node.js application npm run dev # Keep the script running in case of errors to be able to inspect the container tail -f /dev/null
In directory data.ts
import { Pool } from 'pg';
const pool = new Pool({
user: process.env.POSTGRES_USER,
password: process.env.POSTGRES_PASSWORD,
host: process.env.POSTGRES_HOST,
port: process.env.POSTGRES_PORT,
database: process.env.POSTGRES_DB,
});
import {
CustomerField,
CustomersTableType,
InvoiceForm,
InvoicesTable,
LatestInvoiceRaw,
User,
Revenue,
} from './definitions';
import { formatCurrency } from './utils';
import { unstable_noStore as noStore } from 'next/cache';
export async function fetchRevenue() {
// Add noStore() here prevent the response from being cached.
// This is equivalent to in fetch(..., {cache: 'no-store'}).
noStore();
try {
console.log('Fetching revenue data...');
const data = await pool.query(<Revenue>`SELECT * FROM revenue`);
return data.rows;
} catch (error) {
console.error('Database Error:', error);
throw new Error('Failed to fetch revenue data.');
}
}
export async function fetchLatestInvoices() {
noStore();
try {
const data = await pool.query(
<LatestInvoiceRaw>`
SELECT DISTINCT
invoices.id,
invoices.date,
invoices.amount,
customers.name,
customers.image_url,
customers.email
FROM
invoices
JOIN customers ON invoices.customer_id = customers.id
ORDER BY invoices.date DESC
LIMIT 5`,
);
const latestInvoices = data.rows.map((invoice) => ({
...invoice,
amount: formatCurrency(invoice.amount),
}));
return latestInvoices;
} catch (error) {
console.error('Database Error:', error);
throw new Error('Failed to fetch the latest invoices.');
}
}
export async function fetchCardData() {
noStore();
try {
// You can probably combine these into a single SQL query
// However, we are intentionally splitting them to demonstrate
// how to initialize multiple queries in parallel with JS.
//const invoiceCountPromise = sql`SELECT COUNT(*) FROM invoices`;
const invoiceCountPromise = pool.query(`SELECT COUNT(*) FROM invoices`);
//const customerCountPromise = sql`SELECT COUNT(*) FROM customers`;
const customerCountPromise = pool.query(`SELECT COUNT(*) FROM customers`);
//const invoiceStatusPromise = sql`SELECT
// SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) AS "paid",
// SUM(CASE WHEN status = 'pending' THEN amount ELSE 0 END) AS "pending"
// FROM invoices`;
const invoiceStatusPromise = pool.query(`SELECT
SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) AS "paid",
SUM(CASE WHEN status = 'pending' THEN amount ELSE 0 END) AS "pending"
FROM invoices`);
const data = await Promise.all([
invoiceCountPromise,
customerCountPromise,
invoiceStatusPromise,
]);
const numberOfInvoices = Number(data[0].rows[0].count ?? '0');
const numberOfCustomers = Number(data[1].rows[0].count ?? '0');
const totalPaidInvoices = formatCurrency(data[2].rows[0].paid ?? '0');
const totalPendingInvoices = formatCurrency(data[2].rows[0].pending ?? '0');
return {
numberOfCustomers,
numberOfInvoices,
totalPaidInvoices,
totalPendingInvoices,
};
} catch (error) {
console.error('Database Error:', error);
throw new Error('Failed to fetch card data.');
}
}
const ITEMS_PER_PAGE = 6;
export async function fetchFilteredInvoices(
query: string,
currentPage: number,
) {
noStore();
const offset = (currentPage - 1) * ITEMS_PER_PAGE;
try {
const invoices = await pool.query(
<InvoicesTable>`
SELECT
invoices.id,
invoices.amount,
invoices.date,
invoices.status,
customers.name,
customers.email,
customers.image_url
FROM invoices
JOIN customers ON invoices.customer_id = customers.id
WHERE
customers.name ILIKE '%${query}%' OR
customers.email ILIKE '%${query}%' OR
invoices.amount::text ILIKE '%${query}%' OR
invoices.date::text ILIKE '%${query}%' OR
invoices.status ILIKE '%${query}%'
ORDER BY invoices.date DESC
LIMIT ${ITEMS_PER_PAGE} OFFSET ${offset}
`,
);
return invoices.rows;
} catch (error) {
console.error('Database Error:', error);
throw new Error('Failed to fetch invoices.');
}
}
export async function fetchInvoicesPages(query: string) {
try {
noStore();
const count = await pool.query(`SELECT COUNT(*)
FROM invoices
JOIN customers ON invoices.customer_id = customers.id
WHERE
customers.name ILIKE '%${query}%' OR
customers.email ILIKE '%${query}%' OR
invoices.amount::text ILIKE '%${query}%' OR
invoices.date::text ILIKE '%${query}%' OR
invoices.status ILIKE '%${query}%'
`);
const totalPages = Math.ceil(Number(count.rows[0].count) / ITEMS_PER_PAGE);
return totalPages;
} catch (error) {
console.error('Database Error:', error);
throw new Error('Failed to fetch total number of invoices.');
}
}
export async function fetchInvoiceById(id: string) {
noStore();
try {
const data = await pool.query(
<InvoiceForm>`
SELECT
invoices.id,
invoices.customer_id,
invoices.amount,
invoices.status
FROM invoices
WHERE invoices.id = '${id}';
`,
);
const invoice = data.rows.map((invoice) => ({
...invoice,
// Convert amount from cents to dollars
amount: invoice.amount / 100,
}));
return invoice[0];
} catch (error) {
console.error('Database Error:', error);
throw new Error('Failed to fetch invoice.');
}
}
export async function fetchCustomers() {
try {
const data = await pool.query(<CustomerField>`
SELECT
id,
name
FROM customers
ORDER BY name ASC
`);
const customers = data.rows;
return customers;
} catch (err) {
console.error('Database Error:', err);
throw new Error('Failed to fetch all customers.');
}
}
export async function fetchFilteredCustomers(query: string) {
noStore();
try {
const data = await pool.query(
<CustomersTableType>`
SELECT
customers.id,
customers.name,
customers.email,
customers.image_url,
COUNT(invoices.id) AS total_invoices,
SUM(CASE WHEN invoices.status = 'pending' THEN invoices.amount ELSE 0 END) AS total_pending,
SUM(CASE WHEN invoices.status = 'paid' THEN invoices.amount ELSE 0 END) AS total_paid
FROM customers
LEFT JOIN invoices ON customers.id = invoices.customer_id
WHERE
customers.name ILIKE '%${query}%' OR
customers.email ILIKE '%${query}%'
GROUP BY customers.id, customers.name, customers.email, customers.image_url
ORDER BY customers.name ASC
`,
);
const customers = data.rows.map((customer) => ({
...customer,
total_pending: formatCurrency(customer.total_pending),
total_paid: formatCurrency(customer.total_paid),
}));
return customers;
} catch (err) {
console.error('Database Error:', err);
throw new Error('Failed to fetch customer table.');
}
}
export async function getUser(email: string) {
try {
const user = await pool.query(`SELECT * FROM users WHERE email=${email}`);
return user.rows[0] as User;
} catch (error) {
console.error('Failed to fetch user:', error);
throw new Error('Failed to fetch user.');
}
}
dd actions.ts
'use server';
import { signIn } from '@/auth';
import { AuthError } from 'next-auth';
import { z } from 'zod';
import { revalidatePath } from 'next/cache';
import { redirect } from 'next/navigation';
import { Pool } from 'pg';
const pool = new Pool({
user: process.env.POSTGRES_USER,
password: process.env.POSTGRES_PASSWORD,
host: process.env.POSTGRES_HOST,
port: process.env.POSTGRES_PORT,
database: process.env.POSTGRES_DB,
});
const FormSchema = z.object({
id: z.string(),
customerId: z.string({
invalid_type_error: 'Please select a customer.',
}),
amount: z.coerce
.number()
.gt(0, { message: 'Please enter an amount greater than $0.' }),
status: z.enum(['pending', 'paid'], {
invalid_type_error: 'Please select an invoice status.',
}),
date: z.string(),
});
const CreateInvoice = FormSchema.omit({ id: true, date: true });
export type State = {
errors?: {
customerId?: string[];
amount?: string[];
status?: string[];
};
message?: string | null;
};
export async function createInvoice(prevState: State, formData: FormData) {
// Validate form using Zod
const validatedFields = CreateInvoice.safeParse({
customerId: formData.get('customerId'),
amount: formData.get('amount'),
status: formData.get('status'),
});
// If form validation fails, return errors early. Otherwise, continue.
if (!validatedFields.success) {
return {
errors: validatedFields.error.flatten().fieldErrors,
message: 'Missing Fields. Failed to Create Invoice.',
};
}
// Prepare data for insertion into the database
const { customerId, amount, status } = validatedFields.data;
const amountInCents = amount * 100;
const date = new Date().toISOString().split('T')[0];
// Insert data into the database
try {
await pool.query(`
INSERT INTO invoices (customer_id, amount, status, date)
VALUES ('${customerId}', ${amountInCents}, '${status}', '${date}')
`);
} catch (error) {
// If a database error occurs, return a more specific error.
console.error('Database Error:', error);
return {
message: 'Database Error: Failed to Create Invoice.',
};
}
// Revalidate the cache for the invoices page and redirect the user.
revalidatePath('/dashboard/invoices');
redirect('/dashboard/invoices');
}
const UpdateInvoice = FormSchema.omit({ id: true, date: true });
export async function updateInvoice(
id: string,
prevState: State,
formData: FormData,
) {
const validatedFields = UpdateInvoice.safeParse({
customerId: formData.get('customerId'),
amount: formData.get('amount'),
status: formData.get('status'),
});
if (!validatedFields.success) {
return {
errors: validatedFields.error.flatten().fieldErrors,
message: 'Missing Fields. Failed to Update Invoice.',
};
}
const { customerId, amount, status } = validatedFields.data;
const amountInCents = amount * 100;
try {
await pool.query(`
UPDATE invoices
SET customer_id = '${customerId}', amount = ${amountInCents}, status = '${status}'
WHERE id = '${id}'
`);
} catch (error) {
return { message: 'Database Error: Failed to Update Invoice.' };
}
revalidatePath('/dashboard/invoices');
redirect('/dashboard/invoices');
}
export async function deleteInvoice(id: string) {
try {
await pool.query(`DELETE FROM invoices WHERE id = '${id}'`);
revalidatePath('/dashboard/invoices');
return { message: 'Deleted Invoice.' };
} catch (error) {
return { message: 'Database Error: Failed to Delete Invoice.' };
}
}
export async function authenticate(
prevState: string | undefined,
formData: FormData,
) {
try {
await signIn('credentials', formData);
} catch (error) {
if (error instanceof AuthError) {
switch (error.type) {
case 'CredentialsSignin':
return 'Invalid credentials.';
default:
return 'Something went wrong.';
}
}
throw error;
}
}
dff delete db.js
Schreibe einen Kommentar