Ubuntu – Spring Boot + Postgresql not creating table when running app

I’ve looked at a solution1, solution2, and tried the spring tutorial here.

I haven’t been able to resolve the problem. As you can see hibernate tries to create the table Hibernate: create table users (id bigserial not null, email varchar(255), name varchar(255), password varchar(255), role varchar(255), primary key (id)), but I never see it in my database. I’m able to manually create the table myself so it doesn’t seem to be an issue with postgresql. I have spring security in this project, I’m not sure if that might be causing a conflict.

Also, I think the problem is in the connection. I tried changing the credentials to a fake username/password and hibernate was able to run everything without an error.
Any suggestions would be appreciated.

system info:

psql (PostgreSQL) 14.9 (Ubuntu 14.9-0ubuntu0.22.04.1)

spring console:

  .   ____          _            __ _ _
 /\ / ___'_ __ _ _(_)_ __  __ _    
( ( )___ | '_ | '_| | '_ / _` |    
 \/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |___, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::                (v3.1.3)

2023-09-06T11:01:28.119-04:00  INFO 75478 --- [  restartedMain] c.a.springblog.SpringBlogApplication     : Starting SpringBlogApplication using Java 20.0.2.1 with PID 75478 (/home/skynet/IdeaProjects/spring-blog/target/classes started by skynet in /home/skynet/IdeaProjects/spring-blog)
2023-09-06T11:01:28.119-04:00  INFO 75478 --- [  restartedMain] c.a.springblog.SpringBlogApplication     : No active profile set, falling back to 1 default profile: "default"
2023-09-06T11:01:28.121-04:00  INFO 75478 --- [  restartedMain] .s.b.d.c.l.DockerComposeLifecycleManager : Using Docker Compose file '/home/skynet/IdeaProjects/spring-blog/compose.yaml'
2023-09-06T11:01:28.332-04:00  INFO 75478 --- [  restartedMain] .s.d.r.c.RepositoryConfigurationDelegate : Bootstrapping Spring Data JPA repositories in DEFAULT mode.
2023-09-06T11:01:28.334-04:00  INFO 75478 --- [  restartedMain] .s.d.r.c.RepositoryConfigurationDelegate : Finished Spring Data repository scanning in 1 ms. Found 1 JPA repository interfaces.
2023-09-06T11:01:28.369-04:00  INFO 75478 --- [  restartedMain] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat initialized with port(s): 8080 (http)
2023-09-06T11:01:28.369-04:00  INFO 75478 --- [  restartedMain] o.apache.catalina.core.StandardService   : Starting service [Tomcat]
2023-09-06T11:01:28.369-04:00  INFO 75478 --- [  restartedMain] o.apache.catalina.core.StandardEngine    : Starting Servlet engine: [Apache Tomcat/10.1.12]
2023-09-06T11:01:28.372-04:00  INFO 75478 --- [  restartedMain] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring embedded WebApplicationContext
2023-09-06T11:01:28.372-04:00  INFO 75478 --- [  restartedMain] w.s.c.ServletWebServerApplicationContext : Root WebApplicationContext: initialization completed in 92 ms
2023-09-06T11:01:28.377-04:00  INFO 75478 --- [  restartedMain] com.zaxxer.hikari.HikariDataSource       : HikariPool-2 - Starting...
2023-09-06T11:01:28.387-04:00  INFO 75478 --- [  restartedMain] com.zaxxer.hikari.pool.HikariPool        : HikariPool-2 - Added connection org.postgresql.jdbc.PgConnection@68990f0c
2023-09-06T11:01:28.388-04:00  INFO 75478 --- [  restartedMain] com.zaxxer.hikari.HikariDataSource       : HikariPool-2 - Start completed.
2023-09-06T11:01:28.388-04:00  INFO 75478 --- [  restartedMain] o.s.b.a.h2.H2ConsoleAutoConfiguration    : H2 console available at '/h2-console'. Database available at 'jdbc:postgresql://127.0.0.1:32790/mydatabase'
2023-09-06T11:01:28.398-04:00  INFO 75478 --- [  restartedMain] o.hibernate.jpa.internal.util.LogHelper  : HHH000204: Processing PersistenceUnitInfo [name: default]
2023-09-06T11:01:28.399-04:00  INFO 75478 --- [  restartedMain] o.h.b.i.BytecodeProviderInitiator        : HHH000021: Bytecode provider name : bytebuddy
2023-09-06T11:01:28.399-04:00  INFO 75478 --- [  restartedMain] o.s.o.j.p.SpringPersistenceUnitInfo      : No LoadTimeWeaver setup: ignoring JPA class transformer
2023-09-06T11:01:28.403-04:00  INFO 75478 --- [  restartedMain] o.h.b.i.BytecodeProviderInitiator        : HHH000021: Bytecode provider name : bytebuddy
2023-09-06T11:01:28.411-04:00  INFO 75478 --- [  restartedMain] o.h.e.t.j.p.i.JtaPlatformInitiator       : HHH000490: Using JtaPlatform implementation: [org.hibernate.engine.transaction.jta.platform.internal.NoJtaPlatform]
Hibernate: drop table if exists users cascade
2023-09-06T11:01:28.412-04:00  WARN 75478 --- [  restartedMain] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Warning Code: 0, SQLState: 00000
2023-09-06T11:01:28.412-04:00  WARN 75478 --- [  restartedMain] o.h.engine.jdbc.spi.SqlExceptionHelper   : table "users" does not exist, skipping
Hibernate: create table users (id bigserial not null, email varchar(255), name varchar(255), password varchar(255), role varchar(255), primary key (id))
2023-09-06T11:01:28.420-04:00  INFO 75478 --- [  restartedMain] j.LocalContainerEntityManagerFactoryBean : Initialized JPA EntityManagerFactory for persistence unit 'default'
2023-09-06T11:01:28.438-04:00  WARN 75478 --- [  restartedMain] JpaBaseConfiguration$JpaWebConfiguration : spring.jpa.open-in-view is enabled by default. Therefore, database queries may be performed during view rendering. Explicitly configure spring.jpa.open-in-view to disable this warning
2023-09-06T11:01:28.471-04:00  WARN 75478 --- [  restartedMain] .s.s.UserDetailsServiceAutoConfiguration : 

Using generated security password: e99bc0d2-86c6-4590-b802-58eb8b016bcc

This generated password is for development use only. Your security configuration must be updated before running your application in production.

2023-09-06T11:01:28.476-04:00  INFO 75478 --- [  restartedMain] o.s.s.web.DefaultSecurityFilterChain     : Will secure any request with [org.springframework.security.web.session.DisableEncodeUrlFilter@55209b90, org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter@7be130e3, org.springframework.security.web.context.SecurityContextHolderFilter@1f0658f2, org.springframework.security.web.header.HeaderWriterFilter@2a6b2b65, org.springframework.security.web.csrf.CsrfFilter@63322287, org.springframework.security.web.authentication.logout.LogoutFilter@3cf107f0, org.springframework.security.web.authentication.UsernamePasswordAuthenticationFilter@1b829270, org.springframework.security.web.authentication.ui.DefaultLoginPageGeneratingFilter@6f8dfae7, org.springframework.security.web.authentication.ui.DefaultLogoutPageGeneratingFilter@747f2cfd, org.springframework.security.web.authentication.www.BasicAuthenticationFilter@2f78c877, org.springframework.security.web.savedrequest.RequestCacheAwareFilter@6dd2678f, org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter@1c4eca08, org.springframework.security.web.authentication.AnonymousAuthenticationFilter@1d2d6975, org.springframework.security.web.access.ExceptionTranslationFilter@6782dde0, org.springframework.security.web.access.intercept.AuthorizationFilter@565f0619]
2023-09-06T11:01:28.480-04:00  INFO 75478 --- [  restartedMain] o.s.b.d.a.OptionalLiveReloadServer       : LiveReload server is running on port 35729
2023-09-06T11:01:28.484-04:00  INFO 75478 --- [  restartedMain] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat started on port(s): 8080 (http) with context path ''
2023-09-06T11:01:28.486-04:00  INFO 75478 --- [  restartedMain] c.a.springblog.SpringBlogApplication     : Started SpringBlogApplication in 0.378 seconds (process running for 18.874)
2023-09-06T11:01:28.487-04:00  INFO 75478 --- [  restartedMain] .ConditionEvaluationDeltaLoggingListener : Condition evaluation unchanged

application.properties:

spring.datasource.url=jdbc:postgresql://localhost:5432/spring_blog
spring.datasource.driver-class-name=org.postgresql.Driver
spring.datasource.username=postgres
spring.datasource.password=postgres
spring.jpa.database=POSTGRESQL
spring.jpa.show-sql=true


spring.datasource.hikari.connection-timeout=20000
spring.datasource.hikari.maximum-pool-size=5

spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
spring.jpa.properties.hibernate.jdbc.lob.non_contextual_creation=true

spring.jpa.hibernate.ddl-auto=create-drop
spring.jpa.generate-ddl=true

I also tried a simple config like this:

spring.datasource.url=jdbc:postgresql://localhost:5432/spring_blog
spring.datasource.username=postgres
spring.datasource.password=postgres
spring.jpa.hibernate.ddl-auto=create-drop
spring.jpa.show-sql=true

entity:

package com.myblogsite.springblog.entities;

import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;

@Entity(name = "users")
public class Users {

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

    private String name;
    private String email;
    private String password;
    private String role;

    public Users(){

    }

    public Users(Long id, String name, String email, String password, String role){
        this.id = id;
        this.name = name;
        this.email = email;
        this.password = password;
        this.role = role;
    }

    public Long getId() {
        return this.id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return this.name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getEmail() {
        return this.email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getPassword() {
        return this.password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getRole() {
        return this.role;
    }

    public void setRole(String role) {
        this.role = role;
    }
}

repository interface:

package com.myblogsite.springblog.repositories;

import com.myblogsite.springblog.entities.Users;
import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface IUserRepository extends CrudRepository<Users, Long> {
}

rest controller:

package com.myblogsite.springblog.http.controllers;

import com.myblogsite.springblog.repositories.IUserRepository;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
@RequestMapping("/api/users")
public class UserController extends BaseController{

    private final IUserRepository userRepository;

    public UserController(IUserRepository userRepository) {
        this.userRepository = userRepository;
    }

    @GetMapping
    public ResponseEntity<?> getAllUsers() {
        return ResponseEntity.ok(userRepository.findAll());
    }
}

Last but not least

pom.xml:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>3.1.3</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.myblogsite</groupId>
    <artifactId>spring-blog</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>spring-blog</name>
    <description>spring-blog</description>
    <properties>
        <java.version>20</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-mail</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-security</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.session</groupId>
            <artifactId>spring-session-core</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-docker-compose</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.security</groupId>
            <artifactId>spring-security-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

Edit:

I added this service to check if my connection with the database is working.

package com.myblogsite.springblog.database;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class DatabaseConnectionService {

    @Autowired
    private DataSource dataSource;

    public boolean isDatabaseConnectionValid() {
        try (Connection connection = dataSource.getConnection()) {
            // Execute a simple query to verify the connection.
            String sql = "SELECT 1";
            try (PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
                preparedStatement.executeQuery();
                return true; // Connection is valid.
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return false; // Connection is not valid.
        }
    }
}

when I call it in my controller and ping that endpoint it will return an empty array and in the console it says Database connection is valid.

this is what I added to the controller above:

@GetMapping
    public ResponseEntity<?> getAllUsers() {
        boolean isConnectionValid = databaseConnectionService.isDatabaseConnectionValid();

        if (isConnectionValid) {
            System.out.println("Database connection is valid.");
        } else {
            System.out.println("Database connection is not valid.");
        }
        return ResponseEntity.ok(userRepository.findAll());
    }

{insert going crazy emoji here}