How to create tables with App Platform Dev databases?

Hi there,

There should be no need to escalate the user privileges in order to create tables.

I have a demo Laravel project that uses a Dev Postgres database and here is how I supply the credentials:

https://github.com/thedevdojo/wave/blob/main/.do/deploy.template.yaml#L34-L53

    # DATABASE
    - key: DB_CONNECTION
      scope: RUN_TIME
      value: "pgsql"
    - key: DB_HOST
      scope: RUN_TIME
      value: ${dev-db.HOSTNAME}
    - key: DB_PORT
      scope: RUN_TIME
      value: ${dev-db.PORT}
    - key: DB_DATABASE
      scope: RUN_TIME
      value: ${dev-db.DATABASE}
    - key: DB_USERNAME
      scope: RUN_TIME
      value: ${dev-db.USERNAME}
      type: SECRET
    - key: DB_PASSWORD
      scope: RUN_TIME
      value: ${dev-db.PASSWORD}
      type: SECRET

After that the database migrations work as expected.

Can you share how exactly are you utilizing the ${db.DATABASE_URL} connection string in your code?

The DATABASE_URL env var, already has the username and the password in there, so there should be no need to specify them again, eg.:

postgres://${db.USERNAME}:${db.PASSWORD}@${db.HOSTNAME}:${db.PORT}/${db.DATABASE}

UPDATE: The issue was that the dev database permissions will not allow you to create databases or schemas; instead, you will need to use the default database and the public schema.

You will get permissions denied if you were to try and create a new schema, eg:

dev-db=> create schema test;
ERROR:  permission denied for database dev-db

-- But creating tables will work as expected:

dev-db=> create table delete_me_just_testing (id int);
CREATE TABLE

- Bobby