DailyNotes
DailyNotes copied to clipboard
m0ngr31
[Question] Postgress database support
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.