Skip to content

Part 2: Adding a Database

Add PostgreSQL persistence to your Spring Boot todo app with JPA entities and Flyway migrations.

What you'll learn

  • Configuring Spring Data JPA with AppMecca's managed PostgreSQL
  • Writing Flyway migrations for schema management
  • Backing up and restoring your app's data
  • Reading the DATABASE_URL environment variable injected at deploy time

What you'll need

  • A deployed hello-world app from Part 1
  • APPMECCA_ENV set (or -e <env> on each command)

Add Spring dependencies

Update your build.gradle to include JPA, Flyway, and PostgreSQL:

dependencies {
    implementation 'org.springframework.boot:spring-boot-starter-web'
    implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
    implementation 'org.flywaydb:flyway-core'
    implementation 'org.flywaydb:flyway-database-postgresql'
    runtimeOnly 'org.postgresql:postgresql'
}

Configure the database connection

Spring Boot reads DATABASE_URL which AppMecca injects automatically when database is enabled.

# src/main/resources/application.properties
server.port=8080

# AppMecca injects DATABASE_URL as: postgresql://user:pass@host:5432/dbname
spring.datasource.url=jdbc:${DATABASE_URL:postgresql://localhost:5432/todo}
spring.jpa.hibernate.ddl-auto=validate
spring.flyway.enabled=true

Connection format

AppMecca provides DATABASE_URL in the format postgresql://user:pass@host:5432/dbname. Spring's JDBC driver expects the jdbc: prefix. The expression jdbc:${DATABASE_URL} handles this cleanly.

The individual DB_HOST, DB_PORT, DB_NAME, DB_USER and DB_PASSWORD variables are injected alongside it if you prefer them.

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 Flyway migration

Create the initial schema migration:

-- src/main/resources/db/migration/V1__create_todos.sql
CREATE TABLE todos (
    id BIGSERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    done BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

Flyway naming

Flyway requires migrations to follow the pattern V{version}__{description}.sql. The double underscore is mandatory. Flyway runs migrations in version order and tracks which have been applied.

Create the JPA entity

// src/main/java/com/example/todo/Todo.java
package com.example.todo;

import jakarta.persistence.*;
import java.time.LocalDateTime;

@Entity
@Table(name = "todos")
public class Todo {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false)
    private String title;

    @Column(nullable = false)
    private boolean done = false;

    @Column(name = "created_at", nullable = false, updatable = false)
    private LocalDateTime createdAt = LocalDateTime.now();

    // Getters and setters
    public Long getId() { return id; }
    public String getTitle() { return title; }
    public void setTitle(String title) { this.title = title; }
    public boolean isDone() { return done; }
    public void setDone(boolean done) { this.done = done; }
    public LocalDateTime getCreatedAt() { return createdAt; }
}

Create the repository

// src/main/java/com/example/todo/TodoRepository.java
package com.example.todo;

import org.springframework.data.jpa.repository.JpaRepository;

public interface TodoRepository extends JpaRepository<Todo, Long> {
}

Create the REST controller

Replace the hello-world controller with a full CRUD API:

// src/main/java/com/example/todo/TodoController.java
package com.example.todo;

import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;

import java.util.List;
import java.util.Map;

@RestController
public class TodoController {

    private final TodoRepository repository;

    public TodoController(TodoRepository repository) {
        this.repository = repository;
    }

    @GetMapping("/health")
    public Map<String, String> health() {
        return Map.of("status", "healthy");
    }

    @GetMapping("/api/todos")
    public List<Todo> list() {
        return repository.findAll();
    }

    @PostMapping("/api/todos")
    public Todo create(@RequestBody Todo todo) {
        return repository.save(todo);
    }

    @PutMapping("/api/todos/{id}")
    public Todo update(@PathVariable Long id, @RequestBody Todo updates) {
        Todo todo = repository.findById(id)
            .orElseThrow(() -> new RuntimeException("Todo not found"));
        todo.setTitle(updates.getTitle());
        todo.setDone(updates.isDone());
        return repository.save(todo);
    }

    @DeleteMapping("/api/todos/{id}")
    public ResponseEntity<Void> delete(@PathVariable Long id) {
        repository.deleteById(id);
        return ResponseEntity.noContent().build();
    }
}

Update appmecca.yaml

Enable the managed database:

database:
  enabled: true

containers:
  - name: todo-app
    dockerfile: Dockerfile
    context: .

deploy:
  - name: web
    container: todo-app
    port: 8080
    health_check:
      path: /health
      startup_timeout: 300

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 Hikari's maximumPoolSize:

database:
  enabled: true
  max_connections: 5

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.

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.

Build and deploy

mecca build trigger -g git@github.com:yourorg/todo-app.git
mecca app deploy todo-app

Test the API

Once deployed, test with curl. 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.

mecca app backup todo-app -d "after initial setup"
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

mecca app restore todo-app <bundle-prefix> --force
mecca app restore todo-app <bundle-prefix> --db-only   # leave drives alone
mecca app download todo-app <bundle-prefix>

Restore is destructive

Restore replaces the current database entirely and prompts for confirmation unless --force is passed. Always create a fresh backup before restoring an older one.

Importing 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

Command reference

Command Purpose
(none) The database is provisioned automatically on first deploy
mecca app backup <app> [-d LABEL] Create a backup bundle
mecca app backups <app> [--json] [-n N] List available bundles
mecca app restore <app> <prefix> --force Restore from a bundle
mecca app download <app> <prefix> Get download URLs for a bundle
Dashboard → Database tab Import an existing pg_dump

What's next

In Part 3: Going Full-Stack, you will add a Vue 3 frontend that talks to this API, turning your project into a multi-container deployment.