database/setup.sh fails on fresh PostgreSQL: CREATE DATABASE inside DO $$ block
Nobody has claimed this yet.
- Dominant language
- JavaScript
- Stars
- 211
- Forks
- 82
- PR merge metrics
- No merged PRs in 30d
Description
## Summary
`database/setup.sh` fails on a fresh PostgreSQL instance when running `database/seeds/001-install.sql`.
The `marcus` role is created, but the `application` database is not. The next steps in `setup.sh` then fail because the database does not exist.
## Steps to reproduce
```bash
git clone https://github.com/metarhia/Example.git
cd Example
docker run --rm --name pgtest -e POSTGRES_PASSWORD=test -d postgres:17-alpine
until docker exec pgtest pg_isready -U postgres; do sleep 1; done
docker cp database/seeds/001-install.sql pgtest:/tmp/001-install.sql
docker exec pgtest psql -U postgres -v ON_ERROR_STOP=1 -f /tmp/001-install.sql
```
## Actual result
```
DO
psql:/tmp/001-install.sql:15: ERROR: CREATE DATABASE cannot be executed from a function
CONTEXT: SQL statement "CREATE DATABASE application OWNER marcus"
PL/pgSQL function inline_code_block line 4 at SQL statement
```
## Cause
PostgreSQL does not allow `CREATE DATABASE` inside a PL/pgSQL `DO $$` block. Reproduced on PostgreSQL 12–17.
## Context
Related to #297: seeds were added for docker-compose, where `POSTGRES_DB=application` created the database before init scripts ran, so the failing block in `001-install.sql` was masked. After #305 (docker removed), `database/setup.sh` is the main manual path, but it fails on a clean database.
## Suggested fix
Replace the second `DO $$` block in `database/seeds/001-install.sql` with:
```sql
DO $$
BEGIN
IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'marcus') THEN
CREATE ROLE marcus LOGIN PASSWORD 'marcus';
END IF;
END
$$;
SELECT 'CREATE DATABASE application OWNER marcus'
WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'application')\gexec
```
(`\gexec` is a psql meta-command; `setup.sh` already uses `psql -f`.)
Contributor guide
No contributing guide indexed for this repository
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
Start with database/seeds/001-install.sql, focusing on the second DO $$ block, then review database/setup.sh and reproduce the failure with the provided PostgreSQL Docker and psql commands. Update the seed flow so role creation remains valid and database creation works on a fresh PostgreSQL instance. Done means the seed file and setup.sh complete successfully without the missing application database error.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- postgresql, shell
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 2/5
- Estimated time
- 1-3 hours
- Activity status
- Quiet
- Clarity
- Clearly specified
- Newbie friendliness
- 84/100