Does Prefect silently fall back to SQLite when things go wrong?

[AmagicalFishy]

I'm struggling to get Prefect to connect to a PSQL DB; no matter what environment variable I set, what I put in a profile, what configuration I change, it always creates SQLite.db files and quietly goes on. There's no indication that it sees the PSQL settings or any other setting.

Is there a log level more verbose than DEBUG? Is there something I can do to absolutely force PSQL and disable SQLite entirely?

[cicdw]

Hi @AmagicalFishy, can you share specifics of what you've tried? Prefect should not silently fall back to sqlite if a DB connection fails.

[AmagicalFishy]

I've tried:

• Setting PREFECT_API_DATABASE_CONNECTION_URL=postgresql+asyncpg://... through the environment, through profiles, and through prefect config set. I've tried setting this to both valid, actual DB URIs and totally nonsensical URIs; this has not resulted in any indication that prefect server start ... notices this environment variable (although prefect config view sees it correctly).
• Setting PREFECT_API_DATABASE_HOST, PREFECT_API_DATABASE_PASS, etc. in the same way with the same result
• Setting PREFECT_API_DATABASE_ECHO to true; the generated logs are much too large to send (an immediate ~60k lines), but it's connecting to SQLite

I'm running a pretty simple Dockerfile:

FROM prefecthq/prefect:3.3.5-python3.9

WORKDIR /home/prefect

RUN apt update; apt install -y curl

RUN mkdir -p /home/prefect/.prefect

COPY ./entrypoint.sh .
COPY ./debug.sh .
RUN chmod +x ./entrypoint.sh
RUN chmod +x ./debug.sh

ENTRYPOINT ["./entrypoint.sh"]

... where entrypoint.sh just sets environment variables and then runs the Prefect server (I've also set PREFECT_HOME to match). It's just entirely unresponsive to changes in database settings, which I believe indicates I'm doing something incorrect w.r.t. actually running the server

(Also: Apologies for not doing this earlier. I expected an AI response and hoped it would fire out some ideas I could run through 😂 )

[cicdw]

Can you share your entrypoint.sh? I'm guessing this is an issue with how your environment variables are being set. prefect server start may spawn subprocesses and if the environment variables are not set properly across all processes then they are reset to defaults. Ideally your env vars are set in your docker file but if not, they should use export to ensure they persist across subprocesses.

[AmagicalFishy]

Yeah, they're all set using export. Right now (after cleaning it up) it's just:

#!/bin/bash

export PREFECT_API_DATABASE_CONNECTION_URL=postgresql+asyncpg://$DB_USER:$DB_PASS@/$DB_NAME?host=$DB_HOST
export PREFECT_API_DATABASE_ECHO=true

echo $(prefect config view)
prefect server start --log-level DEBUG --host 0.0.0.0 --port 8080

I've gone through a ton of different URL permutations. With this thread, I basically wanted to ensure that Prefect wasn't supposed to silently fall back to SQLite, so if it does I know something is going terribly wrong (but at least I know now where to look).

I can take it from here! Thanks for your responses ❤️ (no need to take up your time!)

[cicdw]

Yup sounds good! And just to avoid any doubt, here's something I ran locally that fails immediately:

> export PREFECT_API_DATABASE_CONNECTION_URL=postgresql+asyncpg://BAD_USER:BAD_PASS@/BAD_NAME?host=BAD_HOST
> prefect server start

socket.gaierror: [Errno 8] nodename nor servname provided, or not known

ERROR:    Application startup failed. Exiting.
Server stopped!

You might consider one-lining the whole thing:

PREFECT_API_DATABASE_CONNECTION_URL=postgresql+asyncpg://$DB_USER:$DB_PASS@/$DB_NAME?host=$DB_HOST \
PREFECT_API_DATABASE_ECHO=true \
prefect server start --log-level DEBUG --host 0.0.0.0 --port 8080