DailyNotes

DailyNotes copied to clipboard

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/

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.

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.

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?

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.

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