Part 2: Adding a Database¶
Add PostgreSQL to your Express app with Knex.js migrations and learn the AppMecca database management commands.
What you'll learn¶
- Configuring Knex.js with a
DATABASE_URLenvironment variable - Writing a migration that creates the
todostable - Adding CRUD API routes for the todo contract
- Enabling the managed PostgreSQL database in
appmecca.yaml - Backing up, restoring, and downloading database bundles
What you'll need¶
- The hello-world app from Part 1 deployed and working
- PostgreSQL installed locally for development (optional but recommended)
Install dependencies¶
Configure Knex¶
Create knexfile.js at the project root:
module.exports = {
client: "pg",
connection: process.env.DATABASE_URL,
migrations: {
directory: "./migrations",
},
};
DATABASE_URL
AppMecca injects DATABASE_URL automatically when database.enabled is true, alongside the individual DB_HOST, DB_PORT, DB_NAME, DB_USER and DB_PASSWORD variables. Locally, set it yourself: export DATABASE_URL=postgres://localhost:5432/todo_dev.
Never hard-code the database or user name
AppMecca derives both as a deterministic hash — they look like am_9f3a1c4e88b2_db and am_9f3a1c4e88b2_user, not todo_app_db. Always read them from the environment.
The names are derived per branch, so main and a feature branch each get an entirely separate database. Data never crosses branches.
Write the migration¶
Edit the generated file in migrations/:
exports.up = function (knex) {
return knex.schema.createTable("todos", (table) => {
table.increments("id").primary();
table.string("title").notNullable();
table.boolean("done").defaultTo(false);
table.timestamp("created_at").defaultTo(knex.fn.now());
});
};
exports.down = function (knex) {
return knex.schema.dropTable("todos");
};
Add CRUD routes¶
Replace index.js with the full todo API:
const express = require("express");
const knex = require("knex")(require("./knexfile"));
const app = express();
app.use(express.json());
const PORT = process.env.PORT || 3000;
app.get("/health", (_req, res) => res.json({ status: "healthy" }));
app.get("/api/todos", async (_req, res) => {
const todos = await knex("todos").orderBy("created_at", "desc");
res.json(todos);
});
app.post("/api/todos", async (req, res) => {
const [todo] = await knex("todos").insert({ title: req.body.title }).returning("*");
res.status(201).json(todo);
});
app.put("/api/todos/:id", async (req, res) => {
const [todo] = await knex("todos")
.where({ id: req.params.id })
.update({ title: req.body.title, done: req.body.done })
.returning("*");
if (!todo) return res.sendStatus(404);
res.json(todo);
});
app.delete("/api/todos/:id", async (req, res) => {
await knex("todos").where({ id: req.params.id }).del();
res.sendStatus(204);
});
app.listen(PORT, () => console.log(`Listening on ${PORT}`));
Create an entrypoint script¶
The container must wait for the database to be reachable before running migrations. Create entrypoint.sh:
#!/bin/sh
set -e
echo "Waiting for database..."
until node -e "
const knex = require('knex')(require('./knexfile'));
knex.raw('SELECT 1').then(() => { knex.destroy(); process.exit(0); }).catch(() => process.exit(1));
" 2>/dev/null; do
sleep 2
done
echo "Running migrations..."
npx knex migrate:latest
echo "Starting server..."
exec node index.js
Update the Dockerfile¶
FROM node:20-alpine
WORKDIR /app
COPY package*.json ./
RUN npm ci --omit=dev
COPY . .
RUN chmod +x entrypoint.sh
EXPOSE 3000
CMD ["./entrypoint.sh"]
Update appmecca.yaml¶
Enable the managed database:
database:
enabled: true
containers:
- name: todo-app
dockerfile: Dockerfile
context: .
deploy:
- name: web
container: todo-app
port: 3000
health_check:
path: /health
Declare your connection pool size
database.max_connections (default 5, range 1-50) tells AppMecca how many connections each replica may open. The platform sums it across every database-enabled app and refuses deploys that would exhaust the shared PostgreSQL server. Match it to your pg.Pool max:
How the database appears¶
There is no create command. Setting database: enabled: true is the whole opt-in: on the first deploy AppMecca creates the database and its user on the managed PostgreSQL server, stores the generated password in the platform secret store, and injects DATABASE_URL into every container of the app. Re-deploying is idempotent.
Build and deploy — a deploy is queued automatically after a successful build, so the first command alone is enough:
If you built with --no-deploy, deploy separately:
There are no mecca db commands
The database lifecycle is entirely implicit — created on first deploy, dropped by mecca app delete. Backups and restores go through mecca app backup / mecca app restore; uploading a pg_dump you produced elsewhere is the dashboard's Database tab.
Test the API¶
The hostname always carries the service name and your group slug:
APP_URL=https://todo-app-web-yourgroup.dev.example.com
# Create a todo
curl -X POST "$APP_URL/api/todos" \
-H "Content-Type: application/json" \
-d '{"title": "Learn AppMecca"}'
# List todos
curl "$APP_URL/api/todos"
Backup and restore¶
A backup bundle captures the database and every declared drive in one manifest-verified archive.
Back up¶
List backups¶
mecca app backups todo-app # newest first; note the bundle prefix
mecca app backups todo-app --json
mecca app backups todo-app -n 50 # more than the default 20
Restore from a bundle¶
mecca app restore todo-app <bundle-prefix> --force
mecca app restore todo-app <bundle-prefix> --db-only # leave drives alone
Restore is destructive
Restoring replaces the current database contents entirely, and prompts for confirmation unless --force is passed. Always create a fresh backup before restoring an older one.
Download a bundle locally¶
Import an existing database¶
To bring an existing PostgreSQL database onto AppMecca, upload a pg_dump file directly (a custom-format dump, pg_dump -Fc, is recommended):
# Dashboard -> todo-app -> Database tab -> choose the file -> confirm
mecca queue status <action-id> # follow the queue row
What's next¶
In Part 3: Going Full-Stack, you will add a SvelteKit frontend and learn multi-container builds with service discovery.