Question about Broadsea default database

greshje.gmail · March 4, 2023, 8:40am

I was very quickly and very easily able to install Broadsea on my local windows laptop (thanks @lee_evans et al !!!).

It seems to have installed and/or connected to a default database: Atlas launches and I’m seeing 2694 patients in the Eunomia Demo Database data source when I go to http://127.0.0.1/atlas/#/datasources/EUNOMIA/dashboard.

So, a few questions:

What database is running (e.g. PostgreSql, MsSql, etc.)?
How do I connect to this database so I can do direct sql queries when developing/testing/etc.?
There are some posts that suggest an existing CDM is required but they are older (2017 for example: Fresh installation using BroadSea - #4 by lee_evans). I’m guessing the Broadsea install has been improved and this is no longer the case?

Thanks,
John

Sanjay_Udoshi · March 4, 2023, 7:48pm

This runs off of a Postgres db and you can connect to it using the standard port and user postgres.

greshje.gmail · March 5, 2023, 6:45pm

Thanks for the help @Sanjay_Udoshi !

I do have an instance of Postgres running on the default port. However, the information being shown in the Atlas instance Broadsea is running is not consistent with what is that database.

For example, the Broadsea instance of Atlas is showing a data source called “OHDSI Eunomia Demo Database” with 2,694 people. In my Postgres instance I do have a webapi database, but I do not have a datasource “OHDSI Eunomia Demo Database”. I also do not have a CDM with the Eunomia data (I do have several other CDM instances as shown in the screen shot).

I’m not sure if this is correct, please let me know if it is or where I’m getting it wrong: Looking at the .yml file (complete file is attached), it looks like the instance that is built out-of-the box might be pointing to a (shared) remotely hosted CDM (note the lines with “jdbc:postgresql://broadsea-atlasdb:5432/postgres”):

  ohdsi-webapi:
    container_name: ohdsi-webapi
    image: docker.io/ohdsi/webapi:latest
    restart: unless-stopped
    ipc: none
    read_only: true
    tmpfs:
      - /tmp
    privileged: false
    environment:
      DATASOURCE_DRIVERCLASSNAME: org.postgresql.Driver
      DATASOURCE_URL: jdbc:postgresql://broadsea-atlasdb:5432/postgres
      DATASOURCE_USERNAME: postgres
      DATASOURCE_PASSWORD: mypass
      DATASOURCE_OHDSI_SCHEMA: webapi
      SPRING_JPA_PROPERTIES_HIBERNATE_DIALECT: org.hibernate.dialect.PostgreSQLDialect
      SPRING_JPA_PROPERTIES_HIBERNATE_DEFAULT_SCHEMA: webapi
      SPRING_BATCH_REPOSITORY_TABLEPREFIX: webapi.BATCH_
      FLYWAY_DATASOURCE_DRIVERCLASSNAME: org.postgresql.Driver
      FLYWAY_DATASOURCE_URL: jdbc:postgresql://broadsea-atlasdb:5432/postgres
      FLYWAY_DATASOURCE_USERNAME: postgres
      FLYWAY_DATASOURCE_PASSWORD: postgres
      FLYWAY_LOCATIONS: classpath:db/migration/postgresql
      FLYWAY_PLACEHOLDERS_OHDSISCHEMA: webapi
      FLYWAY_SCHEMAS: webapi
      FLYWAY_BASELINEONMIGRATE: "true"
      FLYWAY_TABLE: schema_history
      flyway_baselineVersionAsString: "2.2.5.20180212152023"  # this env var is case sensitive
      FLYWAY_BASELINEDESCRIPTION: Base Migration
      SECURITY_CORS_ENABLED: "true"
      SECURITY_ORIGIN: "http://${BROADSEA_HOST}"
    depends_on:
      - broadsea-atlasdb
    labels:
      - "traefik.enable=true"
      - "traefik.http.routers.ohdsi-webapi.rule=Host(`${BROADSEA_HOST}`) && PathPrefix(`/WebAPI`)"
      - "traefik.http.routers.ohdsi-webapi.entrypoints=web"

docker-compose.yml.txt (4.2 KB)

Ajit_Londhe · March 6, 2023, 9:18pm

hi @greshje.gmail - likely you have 2 postgres instances running:

The Broadsea PG container on a port that is not 5432. Within docker, its host name is broadsea-atlasdb, but from the outside (your machine), it is at 127.0.0.1.
Your existing PG instance on port 5432

You don’t need to use Broadsea’s PG instance if you want to use a different one, but you would need to specify in your docker compose file to skip that.

greshje.gmail · March 6, 2023, 9:44pm

Thanks @Ajit_Londhe , How can I query (sql) the PostgreSql instance that is running within Docker?

If I try to connect using PgAdmin using postgres as the uid and either mypass or postgres as password I get the error shown below.

Ajit_Londhe · March 7, 2023, 2:56am

does docker ps show the atlasdb container running? What port is it on, has it mapped to 5432 or something else?

greshje.gmail · March 7, 2023, 12:54pm

It looks like ohdsi/broadsea-atlasdb:2.0.0 is running on port 5432:

C:\_YES\workspace\Broadsea>docker ps
CONTAINER ID   IMAGE                          COMMAND                  CREATED          STATUS                    PORTS                                            NAMES
385e540047ac   ohdsi/atlas:latest             "/docker-entrypoint.…"   37 seconds ago   Up 34 seconds             8080/tcp                                         ohdsi-atlas
9dc902be5dc2   ohdsi/webapi:latest            "/bin/sh -c 'exec ja…"   37 seconds ago   Up 35 seconds             8080/tcp                                         ohdsi-webapi
d2da045365bc   ohdsi/broadsea-atlasdb:2.0.0   "docker-entrypoint.s…"   38 seconds ago   Up 35 seconds (healthy)   0.0.0.0:5432->5432/tcp                           broadsea-atlasdb
7d0e1f503c62   nginx                          "/docker-entrypoint.…"   38 seconds ago   Up 36 seconds             80/tcp                                           broadsea-content
d85b6a044643   ohdsi/broadsea-hades:4.2.1     "/usr/bin/supervisor…"   38 seconds ago   Up 35 seconds             0.0.0.0:6311->6311/tcp, 0.0.0.0:8787->8787/tcp   broadsea-hades
dbbb94da31e8   traefik:v2.9.4                 "/entrypoint.sh --pr…"   38 seconds ago   Up 35 seconds             127.0.0.1:80->80/tcp                             traefik

C:\_YES\workspace\Broadsea>

However, if I try to connect using 0.0.0.0 and port 5432 I get the same error.

Ajit_Londhe · March 7, 2023, 1:29pm

Just to ensure no pgAdmin weirdness, can you try with R?

connectionDetails <- DatabaseConnector::createConnectionDetails(dbms = "postgresql", user = "postgres",
                                                                            password = "postgres", server = "127.0.0.1/postgres",
                                                                            port = 5432,
                                                                            pathToDriver = "<path to postgres driver>")
connection <- DatabaseConnector::connect(connectionDetails = connectionDetails)

greshje.gmail · March 7, 2023, 3:14pm

Hi Ajit,

This is what I get for the R code:

connectionDetails ← DatabaseConnector::createConnectionDetails(
+ dbms = “postgresql”,
+ user = “postgres”,
+ password = “postgres”,
+ server = “127.0.0.1/postgres”,
+ port = 5432,
+ pathToDriver = “C:\_YES\databases\postgres\drivers\42.3.3”)
connection ← DatabaseConnector::connect(connectionDetails = connectionDetails)
Connecting using PostgreSQL driver
Error in rJava::.jcall(jdbcDriver, “Ljava/sql/Connection;”, “connect”, :
org.postgresql.util.PSQLException: FATAL: password authentication failed for user “postgres”

Thanks again for all of the help!

Ajit_Londhe · March 7, 2023, 3:16pm

That is strange. Unless you changed the password, but looks like you’re using the default docker compose. Are you able to shut off the other PG instance?

greshje.gmail · March 7, 2023, 9:22pm

Huzzah!

That did the trick. I needed to shutdown the instance of PostgreSql that was running locally on port 5432. I then restarted the Docker instance of Broadsea (docker compose pull && docker compose up -d). I was then able to access the instance of PostgreSql running in the Docker container using PgAdmin (using localhost/postgres/postgres as the host/uid/pwd).

It looks like the database that is included with the distribution has some limited data, including only 444 records in the concept table.

What is that standard process for getting the Docker instance to use a different instance of the CDM. I imagine there are two options, is this correct?

Option 1: Create an instance of your CDM using the methods described at PostgreSQL Installation Guide · OHDSI/WebAPI Wiki (github.com) and then configure the existing Broadsea Docker application to point to that database.

Option 2: Create an instance of your CDM using the methods described at PostgreSQL Installation Guide · OHDSI/WebAPI Wiki (github.com) and then build a new Docker application that includes the new database (I’m not quite sure how to do this, is it documented anywhere)?

Option 3: ??? Is there a different approach ???

Thanks again for all of the help @Ajit_Londhe !!!

Sanjay_Udoshi · March 8, 2023, 12:22am

John,

Option 1 is the best and most straightforward. I’ve recently been through all these steps albeit with SQL Server as our CDM.

Read through that thread, I think you will find it helpful.
-Sanjay

Ajit_Londhe · March 8, 2023, 1:38am

I have been discussing with @lee_evans about adding a profile-based approach to Broadsea that allows users that want to “bring their own WebAPI postgres” rather than use the dockerized PG instance. One good reason would be for a production level deployment, where you want to use a platform like Amazon RDS for greater retention / backup capabilities.

If this is a non-production deployment, I’d go with using the default dockerized PG. But you can still use CDMs that live elsewhere within your network in any supported dbms.

greshje.gmail · March 8, 2023, 2:16am

Sounds great, we’ve been working some automation including automating the process to create a CDM instance including the automation of the entire process described at PostgreSQL Installation Guide · OHDSI/WebAPI Wiki · GitHub and optionally populate it with test data. We have a preliminary version that we’re tweaking. I’m hoping to have the new version by the beginning of next week. Documentation for the current version is at https://nachc-cad.github.io/fhir-to-omop/pages/navbar/getting-started/start-here/Ponos.html. I can easily see a version of this working very well with Broadsea and will incorporate this into our current efforts.

Thanks again for all of the help.

Sanjay_Udoshi · March 9, 2023, 12:09am

Ajit,

I am working with Roger Carlson on a project. He says you are a rockstar.

I agree.

Warmest Regards.
-S

kluivert · September 4, 2024, 1:59pm

Hi John,

I am following through with this guideline for the same problem I am getting. Please how were you able to access the instance of PostgreSQL running in the Docker container using PgAdmin (using localhost/postgres/postgres as the host/uid/pwd)?

I have shutdown the instance of PostgreSQL running locally on port 5432 but I am still getting this error. Connecting using PostgreSQL driver
Error in rJava::.jcall(jdbcDriver, “Ljava/sql/Connection;”, “connect”, :
org.postgresql.util.PSQLException: FATAL: password authentication failed for user “postgres”