PostgreSQL database initialization seems to fail

[mattock]

I attempted to switch from SQLite to PostgreSQL (16.4) and ran into issues during database initialization:

2024-09-10 08:11:11.354 UTC [64] ERROR:  syntax error at or near "user" at character 13
2024-09-10 08:11:11.354 UTC [64] STATEMENT:  ALTER TABLE user ADD COLUMN email_confirmed BOOLEAN;

This repeat over and over again. I attempted to run the same command manually from the postgresql:16.4 container and the error was exactly the same. Database initialization did succeed partially, however:

otterwiki=# \dt
            List of relations
 Schema |    Name     | Type  |   Owner   
--------+-------------+-------+-----------
 public | drafts      | table | otterwiki
 public | preferences | table | otterwiki
(2 rows)

So, the database connectivity as configured in settings.cfg did work as expected.

Is postgresql supported / tested?

[redimp]

Hey @mattock,

The _db_migrate() function is poorly implemented, I'm sorry for that. Will look into this and test it against at least postgres.

[mattock]

@redimp ok, thanks!

[redimp]

I've removed the obsolete and poorly implemented _db_migrate function, fixed some datatypes and lengths (that fortunately break nothing in SQlite.)

For testing I used this docker-compose.yaml:

---
services:
  web:
    build: .
    environment:
      SQLALCHEMY_DATABASE_URI: postgresql://postgres:example@db/postgres
    command:
      - /bin/bash
      - -c
      - |
        pip install psycopg2-binary
        /usr/bin/supervisord -c /etc/supervisor/supervisord.conf
    depends_on:
      db:
        condition: service_started
        restart: true
    ports:
      - 8080:80

  db:
    image: postgres:16.4
    restart: always
    shm_size: 128mb
    environment:
      POSTGRES_PASSWORD: example

  adminer:
    image: adminer:4.8.1
    restart: always
    ports:
      - 8082:8080

[mattock]

PostgreSQL 16.4 seems to work fine now, thanks @redimp !