CODEWITHSUNDEEP
javaspringpostgresqlspring-bootdocker
rray
rray

Reputation: 480

Wiring Up pre-existing Docker container running Postgres with Spring Boot

My goal is: Use a Docker container running a Postgres database. Use this container for building the Spring Services and data access layer. All of this is within my local environment.

I am working through setting up a Postgres database within a Docker container. I have the container up and running. I am have created the tables, views, triggers, sequences, etc. It works fine from all I can tell. I am able to connect using pgAdmin.

I have now moved to setting up Spring Boot, the Repository, the Model, and the unit test for a single table. The test only inserts a couple of records into the table. I have tried following several examples, tutorials and blogs.

When I build and test using maven for the project, I received the below error.

2020-08-10 16:01:49.407  INFO 83573 --- [           main] DeferredRepositoryInitializationListener : Triggering deferred initialization of Spring Data repositories…
2020-08-10 16:01:49.771  INFO 83573 --- [           main] DeferredRepositoryInitializationListener : Spring Data repositories initialized!
2020-08-10 16:01:49.789  INFO 83573 --- [           main] n.c.p.s.repository.VendorRepositoryTest  : Started VendorRepositoryTest in 6.623 seconds (JVM running for 7.917)
2020-08-10 16:01:49.932  INFO 83573 --- [           main] o.t.d.DockerClientProviderStrategy       : Loaded org.testcontainers.dockerclient.UnixSocketClientProviderStrategy from ~/.testcontainers.properties, will try it first
2020-08-10 16:01:50.660  INFO 83573 --- [           main] o.t.d.UnixSocketClientProviderStrategy   : Accessing docker with local Unix socket
2020-08-10 16:01:50.661  INFO 83573 --- [           main] o.t.d.DockerClientProviderStrategy       : Found Docker environment with local Unix socket (unix:///var/run/docker.sock)
2020-08-10 16:01:50.816  INFO 83573 --- [           main] org.testcontainers.DockerClientFactory   : Docker host IP address is localhost
2020-08-10 16:01:50.871  INFO 83573 --- [           main] org.testcontainers.DockerClientFactory   : Connected to docker: 
  Server Version: 19.03.12
  API Version: 1.40
  Operating System: Docker Desktop
  Total Memory: 1991 MB
2020-08-10 16:01:51.966  INFO 83573 --- [           main] org.testcontainers.DockerClientFactory   : Ryuk started - will monitor and terminate Testcontainers containers on JVM exit
2020-08-10 16:01:51.966  INFO 83573 --- [           main] org.testcontainers.DockerClientFactory   : Checking the system...
2020-08-10 16:01:51.967  INFO 83573 --- [           main] org.testcontainers.DockerClientFactory   : ✔︎ Docker server version should be at least 1.6.0
2020-08-10 16:01:52.131  INFO 83573 --- [           main] org.testcontainers.DockerClientFactory   : ✔︎ Docker environment should have more than 2GB free disk space
2020-08-10 16:01:52.154  INFO 83573 --- [           main] 🐳 [postgres:9.6.12]                     : Creating container for image: postgres:9.6.12
2020-08-10 16:01:52.254  INFO 83573 --- [           main] 🐳 [postgres:9.6.12]                     : Starting container with ID: 0c1ea5de4bc47651ac2ce02d1e85590a9165d9c31b7584ea1924d88fc85eeaad
2020-08-10 16:01:52.614  INFO 83573 --- [           main] 🐳 [postgres:9.6.12]                     : Container postgres:9.6.12 is starting: 0c1ea5de4bc47651ac2ce02d1e85590a9165d9c31b7584ea1924d88fc85eeaad
2020-08-10 16:01:57.539  INFO 83573 --- [           main] 🐳 [postgres:9.6.12]                     : Container postgres:9.6.12 started in PT7.619S
Hibernate: select nextval ('vendor_tbl_vendor_id_seq')
2020-08-10 16:01:57.587 ERROR 83573 --- [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : ERROR: relation "vendor_tbl_vendor_id_seq" does not exist
  Position: 17
Hibernate: select vendor0_.id as id1_0_, vendor0_.vendor_env as vendor_e2_0_, vendor0_.lpl_client_id as lpl_clie3_0_, vendor0_.public_key as public_k4_0_, vendor0_.secret_key as secret_k5_0_, vendor0_.vendor_name as vendor_n6_0_ from investor.vendor_tbl vendor0_
[ERROR] Tests run: 3, Failures: 0, Errors: 2, Skipped: 0, Time elapsed: 15.57 s <<< FAILURE! - in net.clouddeveloper.plaid.services.repository.VendorRepositoryTest
[ERROR] addVendor  Time elapsed: 8.052 s  <<< ERROR!
org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
        at net.clouddeveloper.plaid.services.repository.VendorRepositoryTest.addVendor(VendorRepositoryTest.java:84)
Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
        at net.clouddeveloper.plaid.services.repository.VendorRepositoryTest.addVendor(VendorRepositoryTest.java:84)
Caused by: org.postgresql.util.PSQLException: 
ERROR: relation "vendor_tbl_vendor_id_seq" does not exist
  Position: 17
        at net.clouddeveloper.plaid.services.repository.VendorRepositoryTest.addVendor(VendorRepositoryTest.java:84)

[ERROR] validateDatabaseRunning  Time elapsed: 0.005 s  <<< ERROR!
java.lang.IllegalStateException: Mapped port can only be obtained after the container is started
        at net.clouddeveloper.plaid.services.repository.VendorRepositoryTest.performQuery(VendorRepositoryTest.java:56)
        at net.clouddeveloper.plaid.services.repository.VendorRepositoryTest.validateDatabaseRunning(VendorRepositoryTest.java:46)

From what I understand from the error, Spring Boot can not find the sequence for the table and the test can not prove can not prove the container is running.

Docker Environment Here is how I create the image:

docker run --name plaid_postgres2 --rm -d -p 54321:5432 -e POSTGRES_PASSWORD=postgres  -e POSTGRES_DB=ach  postgres

This is more of the environment settings. Port & Mount

Here is how I have the application properties setup:

# database connectivity
spring.jpa.open-in-view=true
spring.jpa.database=POSTGRESQL
spring.datasource.platform=org.hibernate.dialect.PostgreSQLDialect
spring.datasource.url=jdbc:postgresql://localhost:54321/ach
spring.datasource.username=postgres
spring.datasource.password=postgres
spring.datasource.driver-class-name=org.postgresql.Driver
spring.jpa.show-sql=true
spring.jpa.generate-ddl=true
spring.jpa.hibernate.ddl-auto=none
spring.jpa.hibernate.use-new-id-generator-mappings=false
spring.jpa.properties.hibernate.jdbc.lob.non_contextual_creation=true

This is the Entity declaration. Note I am using a sequence in Postgres for the specific table.

@Entity
@Table(schema = "investor", name="vendor_tbl")
public class Vendor  implements Serializable {


    private static final long serialVersionUID = -2343243243242432341L;
    @Id
    @Column(name="vendor_id")
    @SequenceGenerator(schema="investor",
                       name="vendor_tbl_vendor_id_seq",
                       sequenceName="vendor_tbl_vendor_id_seq",
                       allocationSize=1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE,  generator="vendor_tbl_vendor_id_seq")
    private long vendor_id;

This is my Test Class:

public class VendorRepositoryTest {

    @Autowired
    private VendorRepository repository;


   @Container
    private static final PostgreSQLContainer postgresqlContainer = new PostgreSQLContainer()
            .withDatabaseName("ach")
            .withUsername("postgres")
            .withPassword("postgres");


    @Test
    @DisplayName("Validate Database is Running")
    void validateDatabaseRunning() throws Exception {
        ResultSet resultSet = performQuery(postgresqlContainer, "SELECT 1");
        resultSet.next();
        int result = resultSet.getInt(1);
        assertEquals(1, result);

        assertTrue(postgresqlContainer.isRunning());
    }

    private ResultSet performQuery(PostgreSQLContainer protgres, String query) throws SQLException {

        String jdbcURL = postgresqlContainer.getJdbcUrl();
        String userName = postgresqlContainer.getUsername();
        String password = postgresqlContainer.getPassword();
        Connection conn = DriverManager.getConnection(jdbcURL, userName, password);
        return conn.createStatement().executeQuery(query);

    }


    @Test
    @DisplayName("Test Find All Vendors")
    public void find_all_vendors() {
        Iterable<Vendor> vendors = repository.findAll();

        int numOfCities =4;
        assertThat(vendors).isEmpty(); //.hasSize(numOfCities);
    }

    @Test
    @DisplayName("Test Add Vendor")
    public void addVendor(){
        String clientID = "blahblahblah";
        String secret_key = "blahblah";
        String public_key = "blahblah";
        String environment = "blah";
        String vendor_name = "Russ Test";
        Vendor vendor1 = new Vendor(clientID, secret_key, public_key, environment, vendor_name);
        Vendor vendor2 = new Vendor(clientID, secret_key, public_key, environment, vendor_name);
        this.repository.save(vendor1);
        this.repository.save(vendor2);
    }


}

After of two days of searching for answers, I have lost direction on how to resolve. Any suggestions or guidance would be greatly appreciated.

Thanks,

Russ

Upvotes: 2

Views: 654

Answers (1)

asgarov1
asgarov1

Reputation: 4098

You don't have to use Dockerfile but you need to define and pass to Docker container username, password and database name. You could do that also in the run command:

docker run --rm --name postgresContainer -d -p 54320:5432 -e POSTGRES_PASSWORD=postgres -e POSTGRES_DB=yourDatabase postgres

default username is postgres so you don't need to specify that. Last postgres word in command is the name of the image

I also called yourDatabase differently because I think there is a default database in postgres called postgres.

After running the above command you can execute the following to check that your database is running:

docker exec -it postgresContainer bash

su postgres

psql

\list

This will show you all of the databases available. Now type:

\c yourDatabase

and you are connected to your database in postgres Docker. Feel free to run create tables/selects or whatever.

to exit just type \q and exit like 2 times. Container will keep running.

Don't forget to update you spring properties to match

spring.datasource.url=jdbc:postgresql://localhost:54320/yourDatabase
spring.datasource.username=postgres
spring.datasource.password=postgres

also I think you should remove the schema parameter from your java code, I never used it and I am afraid it will start searching for 'investor' database.

Upvotes: 1

Related Questions