Theo's Typesafe Cult•5d ago

[SOLVED] New to Docker, can't connect from localhost to DB on container

I am running Docker on Windows via Docker Desktop. I am trying to follow a tutorial and one of the first steps is connecting to a Postgres DB on Docker with Drizzle, but my I can't connect to it and I get an error saying the DB doesn't exist. I have this compose.yml file:

services:
  db:
    image: postgres:17
    environment:
      - POSTGRES_USER=${DB_USER}
      - POSTGRES_PASSWORD=${DB_PASSWORD}
      - POSTGRES_DB=${DB_NAME}
    volumes:
      - pgdata:/var/lib/postgresql/data
    ports:
      - "5432:5432"

volumes:
  pgdata:

services:
  db:
    image: postgres:17
    environment:
      - POSTGRES_USER=${DB_USER}
      - POSTGRES_PASSWORD=${DB_PASSWORD}
      - POSTGRES_DB=${DB_NAME}
    volumes:
      - pgdata:/var/lib/postgresql/data
    ports:
      - "5432:5432"

volumes:
  pgdata:

This script for testing the connection:

import { drizzle } from 'drizzle-orm/node-postgres';
import { Pool } from 'pg';
import env from '@/env';

const pool = new Pool({
    connectionString: env.DATABASE_URL,
});

export const db = drizzle(pool);

// Test the connection
const result = await db.execute('SELECT 1');
console.log(result);

import { drizzle } from 'drizzle-orm/node-postgres';
import { Pool } from 'pg';
import env from '@/env';

const pool = new Pool({
    connectionString: env.DATABASE_URL,
});

export const db = drizzle(pool);

// Test the connection
const result = await db.execute('SELECT 1');
console.log(result);

And this is my DB URL on env:

DB_USER=postgres
DB_PASSWORD=postgres
DB_NAME=betternewsdb

DATABASE_URL=postgresql://${DB_USER}:${DB_PASSWORD}@localhost:5432/${DB_NAME}

DB_USER=postgres
DB_PASSWORD=postgres
DB_NAME=betternewsdb

DATABASE_URL=postgresql://${DB_USER}:${DB_PASSWORD}@localhost:5432/${DB_NAME}

Error stack:

bun run .\server\db\index.ts
40 |     res = resolve
41 |     rej = reject
42 |   }).catch((err) => {
43 |     // replace the stack trace that leads to `TCP.onStreamRead` with one that leads back to the
44 |     // application that created the query
45 |     Error.captureStackTrace(err)
               ^
error: database "betternewsdb" does not exist
     length: 98,
   severity: "FATAL",
     detail: undefined,
       hint: undefined,
   position: undefined,
 internalPosition: undefined,
 internalQuery: undefined,
      where: undefined,
     schema: undefined,
      table: undefined,
   dataType: undefined,
 constraint: undefined,
       file: "postinit.c",
    routine: "InitPostgres",
       code: "3D000"

bun run .\server\db\index.ts
40 |     res = resolve
41 |     rej = reject
42 |   }).catch((err) => {
43 |     // replace the stack trace that leads to `TCP.onStreamRead` with one that leads back to the
44 |     // application that created the query
45 |     Error.captureStackTrace(err)
               ^
error: database "betternewsdb" does not exist
     length: 98,
   severity: "FATAL",
     detail: undefined,
       hint: undefined,
   position: undefined,
 internalPosition: undefined,
 internalQuery: undefined,
      where: undefined,
     schema: undefined,
      table: undefined,
   dataType: undefined,
 constraint: undefined,
       file: "postinit.c",
    routine: "InitPostgres",
       code: "3D000"

If I connect on my local psql terminal, I can't connect to the DB and get a similar error. If I open docker's bash, I can connect to it. Am I doing something wrong?

Solution:

you can try running this on windows to see if anything is also taking up that port. netstat -an | find "5432", cuz its possible that an existing postgres instance is already running but your docker compose just couldn't exposed it.

Jump to solution

11 Replies

darfdxOP•5d ago

The tutorial also had something about networks on the compose.yml file. I have tried with and without it and get the same result:

services:
  db:
    image: postgres:17
    environment:
      - POSTGRES_USER=${DB_USER}
      - POSTGRES_PASSWORD=${DB_PASSWORD}
      - POSTGRES_DB=${DB_NAME}
    volumes:
      - pgdata:/var/lib/postgresql/data
    networks:
      - betternews-network
    ports:
      - "5432:5432"

networks:
  betternews-network:
    driver: bridge

volumes:
  pgdata:

services:
  db:
    image: postgres:17
    environment:
      - POSTGRES_USER=${DB_USER}
      - POSTGRES_PASSWORD=${DB_PASSWORD}
      - POSTGRES_DB=${DB_NAME}
    volumes:
      - pgdata:/var/lib/postgresql/data
    networks:
      - betternews-network
    ports:
      - "5432:5432"

networks:
  betternews-network:
    driver: bridge

volumes:
  pgdata:

Bored Student•5d ago

I don't know drizzle specifically, but it says like "database 'betternewsdb' does not exist", that looks like a postgres problem not a network problem. Did you tell postgres to create Postgres to create that database and did you check if your permissions are configured correctly? Per default afaik there is only a database called "postgres" you have do execute something like "create database 'betternewsdb' with owner '{USER}';"

darfdxOP•5d ago

If i check inside the container, I can see the DB was indeed created. Running docker-compose exec db psql -U postgres -l

darfdxOP•5d ago

I tried giving privileges to postgres user, but I get the same error

Rivenris•5d ago

So with that, we can safely assume the db is created and variable interpolation on all levels work. What I would check next is if you have actually exposed the port to your host machine. can you connect to the db by other means outside of docker? E.g. by cli client, or from IDE plugin to browse databases? Another thing I'd try is to connect to the DB server, select the db and run query from within docker. Just in case user can list, but cannot use the db. Yet another debug idea would be to delete the volume (e.g. via docker compose down -v) and start the thing from scratch. Just in case you had different password in there initially, or something else changed after the db was created. Sorry if that sounds patronizing, but whenever I stumble on something irregular, I simply check the simplest things. That is why I recommended the above to narrow down the place where problem lies

darfdxOP•5d ago

I tried removing the volume (docker compose down -v) with no luck. I can also connect to the DB from inside and perform queries:

docker compose exec db bash
>> 
root@28491d05b842:/# psql -U postgres \betternewsdb
psql (17.2 (Debian 17.2-1.pgdg120+1))
Type "help" for help.

betternewsdb=# select 1;
 ?column? 
----------
        1
(1 row)

betternewsdb=#

docker compose exec db bash
>> 
root@28491d05b842:/# psql -U postgres \betternewsdb
psql (17.2 (Debian 17.2-1.pgdg120+1))
Type "help" for help.

betternewsdb=# select 1;
 ?column? 
----------
        1
(1 row)

betternewsdb=#

Checking on docker desktop, i can see this. Let me know if there is another way to check if the port is actually exposed

Sheape•5d ago

Im guessing you dont have any other ports with 5432 open (like installing postgres as a background service and running it). There's probably a windows command equivalent to lsof -i :5432 which outputs the apps that have that ports exposed.

Solution

Sheape•5d ago

darfdxOP•5d ago

bruh I had a Postgres server running Welp, that was it Thanks!

Sheape•5d ago

see, that was always my problem when trying to do docker with postgres XD. always happy to help

Gaming

Programming

[SOLVED] New to Docker, can't connect from localhost to DB on container

Did you find this page helpful?