Database Configuration

Overview

The Library API uses PostgreSQL as its primary database and Spring Data JPA for object-relational mapping. This guide covers database setup, configuration, and schema management.

PostgreSQL Setup

Install PostgreSQL

Install PostgreSQL on your system:

sudo apt update
sudo apt install postgresql postgresql-contrib

Start PostgreSQL service

Ensure PostgreSQL is running:

sudo systemctl start postgresql
sudo systemctl enable postgresql
sudo systemctl status postgresql

Create the database

Connect to PostgreSQL and create the library database:

sudo -u postgres psql

In the PostgreSQL prompt:

CREATE DATABASE library;
CREATE USER libraryuser WITH ENCRYPTED PASSWORD 'your_secure_password';
GRANT ALL PRIVILEGES ON DATABASE library TO libraryuser;
\q

Replace your_secure_password with a strong password. Keep this password secure as you’ll need it for the application configuration.

Verify database creation

List databases to confirm creation:

sudo -u postgres psql -c "\l"

You should see library in the list.

Application Properties Configuration

Configure the database connection in src/main/resources/application.properties:

Basic Configuration

application.properties

spring.application.name=libraryapi

# Database Connection
spring.datasource.url=jdbc:postgresql://localhost:5432/library
spring.datasource.username=postgres
spring.datasource.password=your_password
spring.datasource.driver-class-name=org.postgresql.Driver

# JPA/Hibernate Configuration
spring.jpa.hibernate.ddl-auto=update

Configuration Properties Explained

spring.datasource.url

string

required

JDBC connection URL for PostgreSQL. Format: jdbc:postgresql://host:port/database

host: Database server address (default: localhost)
port: PostgreSQL port (default: 5432)
database: Database name (default: library)

spring.datasource.username

string

required

Database username. Default PostgreSQL user is postgres.

spring.datasource.password

string

required

Database password. Leave empty if no password is set (not recommended for production).

spring.datasource.driver-class-name

string

required

JDBC driver class. For PostgreSQL: org.postgresql.Driver

spring.jpa.hibernate.ddl-auto

string

Controls database schema generation. Options:

update: Updates schema without dropping existing data (recommended for development)
create: Drops and recreates schema on each startup (use with caution)
create-drop: Creates schema on startup, drops on shutdown
validate: Validates schema without making changes
none: Disables automatic schema management

Advanced JPA Configuration

For more control over JPA behavior, add these optional properties:

application.properties

# Show SQL queries in logs (useful for debugging)
spring.jpa.show-sql=true

# Format SQL output for readability
spring.jpa.properties.hibernate.format_sql=true

# SQL dialect optimization for PostgreSQL
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect

# Connection pool settings
spring.datasource.hikari.maximum-pool-size=10
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.connection-timeout=30000

# Enable batch processing for better performance
spring.jpa.properties.hibernate.jdbc.batch_size=20
spring.jpa.properties.hibernate.order_inserts=true
spring.jpa.properties.hibernate.order_updates=true

Set spring.jpa.show-sql=false in production to avoid logging sensitive data and improve performance.

Database Schema

The Library API automatically creates the following tables based on JPA entities:

Users Table

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    firstname VARCHAR(255),
    lastname VARCHAR(255),
    email VARCHAR(255) UNIQUE NOT NULL,
    password VARCHAR(255),
    google_id VARCHAR(255),
    display_picture VARCHAR(255)
);

Books Table

CREATE TABLE books (
    isbn VARCHAR(255) PRIMARY KEY,
    title VARCHAR(255),
    author_id BIGINT REFERENCES authors(id),
    stock INTEGER,
    cover_image VARCHAR(255)
);

Authors Table

CREATE TABLE authors (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255),
    bio TEXT
);

Rentals Table

CREATE TABLE rentals (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT REFERENCES users(id),
    book_isbn VARCHAR(255) REFERENCES books(isbn),
    rental_date TIMESTAMP,
    return_date TIMESTAMP,
    status VARCHAR(50)
);

With spring.jpa.hibernate.ddl-auto=update, Hibernate automatically creates these tables on application startup if they don’t exist.

Entity Relationships

The database schema includes the following relationships:

Users and Rentals

One-to-Many relationship: A user can have multiple rentals

UserEntity.java

@OneToMany(mappedBy = "user")
private Set<RentalEntity> borrowedBooks;

Books and Authors

Many-to-One relationship: Multiple books can have the same author

BookEntity.java

@ManyToOne(cascade = CascadeType.ALL)
@JoinColumn(name = "author_id")
private AuthorEntity author;

Books and Rentals

One-to-Many relationship: A book can be rented multiple times

BookEntity.java

@OneToMany(mappedBy = "book", orphanRemoval = true)
private Set<RentalEntity> rentals;

Environment-Specific Configuration

For different environments (development, staging, production), use Spring profiles:

Development
Production
Testing

Create application-dev.properties:

spring.datasource.url=jdbc:postgresql://localhost:5432/library_dev
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true

Run with:

mvn spring-boot:run -Dspring-boot.run.profiles=dev

Create application-prod.properties:

spring.datasource.url=jdbc:postgresql://prod-db-server:5432/library
spring.datasource.username=${DB_USERNAME}
spring.datasource.password=${DB_PASSWORD}
spring.jpa.hibernate.ddl-auto=validate
spring.jpa.show-sql=false

Run with environment variables:

export DB_USERNAME=prod_user
export DB_PASSWORD=secure_password
java -jar target/libraryapi-0.0.1-SNAPSHOT.jar --spring.profiles.active=prod

Create application-test.properties:

spring.datasource.url=jdbc:postgresql://localhost:5432/library_test
spring.jpa.hibernate.ddl-auto=create-drop
spring.jpa.show-sql=false

Tests automatically use this profile.

Database Migration

For production environments, consider using a database migration tool instead of ddl-auto=update:

Using Flyway

Add Flyway dependency to pom.xml:

<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
</dependency>

Update application.properties:

spring.jpa.hibernate.ddl-auto=validate
spring.flyway.enabled=true
spring.flyway.baseline-on-migrate=true

Create migration scripts in src/main/resources/db/migration/:

V1__create_tables.sql

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    firstname VARCHAR(255),
    lastname VARCHAR(255),
    email VARCHAR(255) UNIQUE NOT NULL,
    password VARCHAR(255)
);

Troubleshooting

Connection refused

Error: Connection to localhost:5432 refusedSolutions:

Verify PostgreSQL is running: sudo systemctl status postgresql
Check if PostgreSQL is listening on port 5432: sudo netstat -plnt | grep 5432
Verify firewall settings allow connections to port 5432

Authentication failed

Error: FATAL: password authentication failed for user "postgres"Solutions:

Verify username and password in application.properties
Check PostgreSQL authentication configuration in pg_hba.conf

Try resetting the password:

ALTER USER postgres WITH PASSWORD 'new_password';

Database does not exist

Error: FATAL: database "library" does not existSolutions:

Create the database: sudo -u postgres createdb library
Verify database exists: sudo -u postgres psql -c "\l"

Schema validation failed

Error: Schema-validation: missing table [users]Solutions:

Change spring.jpa.hibernate.ddl-auto to update or create
Manually create the required tables
Run database migrations if using Flyway/Liquibase

Connection pool exhausted

Error: Unable to acquire JDBC ConnectionSolutions:

Increase connection pool size:

spring.datasource.hikari.maximum-pool-size=20

Check for connection leaks in your code
Reduce connection timeout if connections are being held too long

Best Practices

Use Environment Variables

Never commit database credentials to version control. Use environment variables or external configuration.

Connection Pooling

Configure appropriate pool sizes based on your application load and database capacity.

Schema Management

Use validate or migration tools in production instead of update or create.

Monitoring

Enable SQL logging in development, but disable in production for security and performance.

Get Started

Core Concepts

Guides

Database Configuration

Overview

PostgreSQL Setup

Application Properties Configuration

Basic Configuration

Configuration Properties Explained

Advanced JPA Configuration

Database Schema

Users Table

Books Table

Authors Table

Rentals Table

Entity Relationships

Environment-Specific Configuration

Database Migration

Using Flyway

Troubleshooting

Best Practices

Use Environment Variables

Connection Pooling

Schema Management

Monitoring

Next Steps

Security Configuration

API Reference

Build docs developers (and LLMs) love

Get Started

Core Concepts

Guides

​Overview

​PostgreSQL Setup

​Application Properties Configuration

​Basic Configuration

​Configuration Properties Explained

​Advanced JPA Configuration

​Database Schema

​Users Table

​Books Table

​Authors Table

​Rentals Table

​Entity Relationships

​Environment-Specific Configuration

​Database Migration

​Using Flyway

​Troubleshooting

​Best Practices

Use Environment Variables

Connection Pooling

Schema Management

Monitoring

​Next Steps

Security Configuration

API Reference

Build docs developers (and LLMs) love

Overview

PostgreSQL Setup

Application Properties Configuration

Basic Configuration

Configuration Properties Explained

Advanced JPA Configuration

Database Schema

Users Table

Books Table

Authors Table

Rentals Table

Entity Relationships

Environment-Specific Configuration

Database Migration

Using Flyway

Troubleshooting

Best Practices

Next Steps