[Question] Postgress database support

[hari-bo]

I've tried to use an Postgress database for DialyNotes as database engine, because sqlalchemy does support it.
But using the following connection string wont work, what i am doing wrong?
DATABASE_URI = username:password@host:port/database_name
https://www.geeksforgeeks.org/connecting-postgresql-with-sqlalchemy-in-python/

[deliberist]

I have a similar issue. I have:

DATABASE_URI=postgresql://postgres:postgres@postgres:5432

and DailyNotes appears to connect. But since tables schemas have not been set up I get these errors:

dailynotes_1  | Traceback (most recent call last):
dailynotes_1  |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
dailynotes_1  |     self.dialect.do_execute(
dailynotes_1  |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
dailynotes_1  |     cursor.execute(statement, parameters)
dailynotes_1  | psycopg2.errors.UndefinedTable: relation "note" does not exist
dailynotes_1  | LINE 2: FROM note
dailynotes_1  |              ^
dailynotes_1  |
dailynotes_1  |
dailynotes_1  | The above exception was the direct cause of the following exception:
dailynotes_1  |
dailynotes_1  | Traceback (most recent call last):
dailynotes_1  |   File "./verify_data_migrations.py", line 30, in <module>
dailynotes_1  |     main()
dailynotes_1  |   File "./verify_data_migrations.py", line 13, in main
dailynotes_1  |     first_note = Note.query.first()
dailynotes_1  |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 2823, in first
dailynotes_1  |     return self.limit(1)._iter().first()
dailynotes_1  |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 2907, in _iter
dailynotes_1  |     result = self.session.execute(
dailynotes_1  |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 1712, in execute
dailynotes_1  |     result = conn._execute_20(statement, params or {}, execution_options)
dailynotes_1  |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1705, in _execute_20
dailynotes_1  |     return meth(self, args_10style, kwargs_10style, execution_options)
dailynotes_1  |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 333, in _execute_on_connection
dailynotes_1  |     return connection._execute_clauseelement(
dailynotes_1  |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1572, in _execute_clauseelement
dailynotes_1  |     ret = self._execute_context(
dailynotes_1  |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1943, in _execute_context
dailynotes_1  |     self._handle_dbapi_exception(
dailynotes_1  |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2124, in _handle_dbapi_exception
dailynotes_1  |     util.raise_(
dailynotes_1  |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
dailynotes_1  |     raise exception
dailynotes_1  |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
dailynotes_1  |     self.dialect.do_execute(
dailynotes_1  |   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
dailynotes_1  |     cursor.execute(statement, parameters)
dailynotes_1  | sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedTable) relation "note" does not exist
dailynotes_1  | LINE 2: FROM note
dailynotes_1  |              ^
dailynotes_1  |
dailynotes_1  | [SQL: SELECT note.uuid AS note_uuid, note.user_id AS note_user_id, note.data AS note_data, note.title AS note_title, note.date AS note_date, note.is_date AS note_is_date
dailynotes_1  | FROM note
dailynotes_1  |  LIMIT %(param_1)s]
dailynotes_1  | [parameters: {'param_1': 1}]
dailynotes_1  | (Background on this error at: https://sqlalche.me/e/14/f405)

It appears we need some sql script that'll initialize the tables, I believe, according to the model classes in app/models.py.

[deliberist]

I spent a few hours trying to get this to work. I instantiated the latest Postgres Docker container (postgres:14.4-alpine3.16) with this file /docker-entrypoint-initdb.d/init.sql which will execute those SQL commands on first startup.

CREATE TABLE user_account (
        uuid CHAR(32) NOT NULL,
        username VARCHAR(64) NOT NULL,
        password_hash VARCHAR(128) NOT NULL,
        auto_save BOOLEAN,
        PRIMARY KEY (uuid),
        UNIQUE (username)
);
CREATE UNIQUE INDEX ix_user_uuid ON user_account (uuid);

CREATE TABLE IF NOT EXISTS "note" (
        uuid CHAR(32) NOT NULL,
        user_id CHAR(32) NOT NULL,
        data VARCHAR,
        title VARCHAR(128) NOT NULL,
        date TIMESTAMP DEFAULT (CURRENT_TIMESTAMP),
        is_date BOOLEAN,
        PRIMARY KEY (uuid),
        FOREIGN KEY(user_id) REFERENCES user_account (uuid)
);
CREATE UNIQUE INDEX ix_note_uuid ON note (uuid);

CREATE TABLE meta (
        uuid CHAR(32) NOT NULL,
        user_id CHAR(32) NOT NULL,
        note_id CHAR(32) NOT NULL,
        name VARCHAR,
        name_compare VARCHAR,
        kind VARCHAR,
        PRIMARY KEY (uuid),
        FOREIGN KEY(note_id) REFERENCES note (uuid),
        FOREIGN KEY(user_id) REFERENCES user_account (uuid)
);
CREATE UNIQUE INDEX ix_meta_uuid ON meta (uuid);

But there are a number of issues. First the user table needed to be renamed to user_account because USER is a Postgres keyword. This caused ripple effects in the code. I thought by adding into the model:

class User(db.Model):
  __tablename__ = 'user_account'
  # ...

I also needed to update

class GUID(TypeDecorator):
  cache_ok = True
  # ...

That didn't work, even after updating a few ForeignKey calls in the same file.

I also ran into a ton more issues when trying to put this behind an Nginx reverse proxy. The DailyNotes project doesn't use the BASE_URL properly in all places, and I never got it fully working.

I don't have enough time to continue debugging this. Which is unfortunate because it looked promising for my use cases, and was looking forward to adding features for my use cases.

[m0ngr31]

Yeah, I need to dig into why other databases aren't working properly, but SQLite has worked perfectly for me, so I haven't done it yet.

What issues are you having running it behind a reverse proxy?

[deliberist]

My initial assessment is that the static files are not being served under $BASE_URL. They get served under /.

My Docker Compose script:

---
version: "3.8"

services:

  postgres:
    image: "${IMAGE_POSTGRES}"
    environment:
      PGPORT: "${POSTGRES_PORT}"
      POSTGRES_DB: "${POSTGRES_DATABASE}"
      POSTGRES_USER: "${POSTGRES_USERNAME}"
      POSTGRES_PASSWORD: "${POSTGRES_PASSWORD}"
    logging:
      driver: syslog
      options:
        tag: "{{.Name}}/{{.ID}}"
    restart: unless-stopped
    volumes:
      - ./.volumes/postgres/:/var/lib/postgresql/data/
      - ./services/postgres/:/docker-entrypoint-initdb.d/:ro

  pgadmin:
    image: "${IMAGE_PGADMIN}"
    depends_on:
      - postgres
    environment:
      PGADMIN_LISTEN_PORT: "${PGADMIN_PORT}"
      PGADMIN_DEFAULT_EMAIL: "${PGADMIN_DEFAULT_EMAIL}"
      PGADMIN_DEFAULT_PASSWORD: "${PGADMIN_DEFAULT_PASSWORD}"
      PGADMIN_BASEURL: "${PGADMIN_BASEURL}"
    logging:
      driver: syslog
      options:
        tag: "{{.Name}}/{{.ID}}"
    restart: unless-stopped
    volumes:
      - ./services/pgadmin/servers.json:/pgadmin4/servers.json:ro

  dailynotes:
    build:
      context: ./services/DailyNotes
    depends_on:
      - postgres
    environment:
      BASE_URL: "${DAILYNOTES_BASE_URL}"
      DATABASE_URI: "${DAILYNOTES_DATABASE_URI}"
    logging:
      driver: syslog
      options:
        tag: "{{.Name}}/{{.ID}}"
    ports:
      - ${DAILYNOTES_PORT}:5000
    volumes:
      - ./.volumes/dailynotes:/app/config

  nginx:
    image: "${IMAGE_NGINX}"
    depends_on:
      - postgres
      - pgadmin
      - dailynotes
    environment:
      PGADMIN_PORT: "${PGADMIN_PORT}"
      DAILYNOTES_BASE_URL: "${DAILYNOTES_BASE_URL}"
      PGADMIN_BASEURL: "${PGADMIN_BASEURL}"
    logging:
      driver: syslog
      options:
        tag: "{{.Name}}/{{.ID}}"
    ports:
      - "443:443"
      - "80:80"
    restart: unless-stopped
    volumes:
      - ./services/nginx/default.conf.template:/etc/nginx/templates/default.conf.template:ro

The Nginx config:

log_format ssl_client_logger
    '$remote_addr [$ssl_client_s_dn] $remote_user [$time_local]'
    ' "$request" $status $body_bytes_sent "$http_referer"'
    ' "$http_user_agent"'
    ' "$upstream_response_time"'
    ;

server {
    listen 80;
    server_name nonsecure.local.denizen.net;

    # Route all logs to the container's /dev/stdout which is captured by Docker
    # Compose and sent to the hosts syslog server.
    access_log /dev/stdout ssl_client_logger;
    error_log /dev/stdout info;

    location ${PGADMIN_BASEURL}/ {
        proxy_pass http://pgadmin:${PGADMIN_PORT};
        proxy_http_version 1.1;

        proxy_set_header Connection         "upgrade";
        proxy_set_header Host               $http_host;
        proxy_set_header Upgrade            $http_upgrade;

        proxy_set_header X-Forwarded-For    $proxy_add_x_forwarded_for;
        proxy_set_header X-Forwarded-Proto  $scheme;
        proxy_set_header X-Nginx-Proxy      true;
        proxy_set_header X-Real-IP          $remote_addr;
        proxy_set_header X-Script-Name      ${PGADMIN_BASEURL};

        proxy_redirect off;
    }

    location ${DAILYNOTES_BASE_URL} {
        proxy_pass http://dailynotes:5000;
        proxy_http_version 1.1;

        proxy_set_header Connection         "upgrade";
        proxy_set_header Host               $http_host;
        proxy_set_header Upgrade            $http_upgrade;

        proxy_set_header X-Forwarded-For    $proxy_add_x_forwarded_for;
        proxy_set_header X-Forwarded-Proto  $scheme;
        proxy_set_header X-Nginx-Proxy      true;
        proxy_set_header X-Real-IP          $remote_addr;

        proxy_redirect off;
    }
}

My Docker Compose .env file:

IMAGE_NGINX=nginx:1.23.1-alpine
IMAGE_PGADMIN=dpage/pgadmin4:6.14
IMAGE_POSTGRES=postgres:14.4-alpine3.16

################################################################################
# Postgres Configuration.

# The port to bind Postgres to inside the container.
POSTGRES_PORT=5432

# The database for Daily Notes.
POSTGRES_DATABASE=dailynotes

# The username+password credentials for the database.
#       The password should be changed in production!
POSTGRES_USERNAME=postgres
POSTGRES_PASSWORD=postgres

################################################################################
# pgAdmin Configuration.

# The port to bind pgAdmin to INSIDE and OUTSIDE the container.
PGADMIN_PORT=5050

# Initial "admin" user.
#       The password should be changed in production!
[email protected]
PGADMIN_DEFAULT_PASSWORD=p@55w0rd

# Set the backend URI.
#       This is "BASEURL" variable that should not contain a trailing slash!
PGADMIN_BASEURL=/pgadmin

################################################################################
# Daily Notes.

# The poinr to bind Daily Notes to OUTSIDE the container.
DAILYNOTES_PORT=5000

# Set the backend URI.
#       This is "BASEURL" variable that MUST contain a trailing slash!
DAILYNOTES_BASE_URL=/dailynotes/

# THe URI to the Postgres SQL database.
DAILYNOTES_DATABASE_URI=postgresql://postgres:postgres@postgres:5432

# The database encryption key.
DAILYNOTES_DB_ENCRYPTION_KEY=0123456789ABCDEF

# Prevent signups??
DAILYNOTES_PREVENT_SIGNUPS=False

################################################################################
# Nginx Configuration.

# nothing yet.

[m0ngr31]

I'll have to look at that and see what's going on