Spring Boot PostgreSQL CRUD API Starter

A Spring Boot 4.1 CRUD API starter in Java 21 that stores one resource, items, in PostgreSQL through Spring JDBC's JdbcClient and a HikariCP pool, built with the Maven wrapper. It is for Java developers who want controller, service and repository layers with Bean Validation, JSON errors, a database health check, Swagger UI at /docs and MockMvc tests.

by Light CloudCRUD APIs

Build a CRUD API with Spring Boot and PostgreSQL

Spring Boot PostgreSQL CRUD API Starter: Swagger UI at /docs listing the items endpoints

Features

  • GET /items?limit=20&offset=0 lists items oldest first, with @Min and @Max keeping limit to 1 to 100
  • POST /items answers 201 and DELETE answers 204; an id that is not a positive whole number is a 404
  • Bean Validation on an ItemRequest record whose constructor trims name before the 1 to 200 character check
  • A Jackson coercion rule makes a number or boolean sent as name or description a 400 instead of a string
  • One @RestControllerAdvice answers 400, 404, 405, 415 and 500 as { "error": "..." } and logs unexpected failures
  • Swagger UI at /docs and the OpenAPI schema at /v3/api-docs from springdoc-openapi 3.2
  • schema.sql creates the items table on every start; GET /health answers 503 when SELECT 1 fails
  • Six @WebMvcTest tests with a @MockitoBean service, so ./mvnw test needs no database

Tech stack

What you need

  • A paid plan: deploying creates a PostgreSQL database, which the Free plan does not include
  • To run it locally: a JDK for Java 21 and Docker for a local PostgreSQL (the Maven wrapper downloads Maven)
  • No API keys: it starts with nothing to configure
  • Outside Light Cloud: a PostgreSQL database set through DB_HOST, DB_NAME, DB_USER and DB_PASSWORD, with DB_SSLMODE=disable if it has no TLS

About

Plain SQL through JdbcClient keeps every query visible in ItemRepository, while application.properties reads the DB_* variables straight into spring.datasource.url and caps HikariCP at 5 connections. Spring Boot runs schema.sql before the app takes traffic, so an unreachable database shows up at deploy time; because the script only creates a missing table, changing the shape of items later means one ALTER TABLE, or adding Flyway once you need real migrations. JSON goes through Jackson 3, the version Spring Boot 4 uses, which is why JsonConfig imports from tools.jackson, and responses use snake_case fields such as created_at. On Light Cloud the Maven project is built and run as a container on PORT with the database variables set, and every push to your copy redeploys.

FAQ

Is there a Spring Boot CRUD template with PostgreSQL and Swagger UI?

Yes. This MIT-licensed GitHub template runs Spring Boot 4.1 on Java 21 with springdoc-openapi, so Swagger UI is at /docs as soon as it starts. Deploying it on a paid Light Cloud plan creates the PostgreSQL database and sets the DB_* variables.

How do I add a new resource to a Spring Boot API?

Copy the item/ package, rename the record, controller, service and repository, and add its CREATE TABLE IF NOT EXISTS to schema.sql. Component scanning picks up the new controller, so there is nothing to register.

Why JdbcClient and not Spring Data JPA?

Each query is a few lines of SQL, which keeps the starter small and shows exactly what runs against the database. You can add Spring Data JPA later; the controller only talks to ItemService.

Do the Spring Boot tests need a PostgreSQL database?

No. ItemControllerTest loads only the web layer with @WebMvcTest and mocks ItemService. That also means the SQL and the health check are not tested, so add an integration test against a real PostgreSQL when you need that cover.

Can I run this Spring Boot app without Light Cloud?

Yes. Build the jar with ./mvnw package and start java -jar target/items-1.0.0.jar with PORT and the DB_* variables set, on any host with Java 21 that can reach PostgreSQL.

Price
Free
Framework
Spring Boot · Java
Updated