tosane932

Posted on Aug 2

【Render】Migrating from SQLite to PostgreSQL: Fixing the `postgres://` Connection Error and the “Still Using SQLite” Problem [3/3]

#python #flask #postgres #render

Introduction

Hello from Japan! 🇯🇵

I am tosane932, a professional truck driver working in logistics while teaching myself Python.

At the time of writing, I had completed approximately 127 hours of programming study.

In the previous article, Part 2 of this three-part series, I removed the familiar db.create_all() approach and established a database migration foundation using Flask-Migrate.

This article records the next step in that work:

Completely moving from SQLite in the local-style setup to PostgreSQL in the Render production environment.

During this infrastructure transition, I encountered two unexpected problems:

A connection error caused by Render's postgres:// database URL format
A strange situation where the code had been corrected, but the production logs still showed SQLiteImpl

I will share the actual sequence of events, including the mistake that turned out not to be in the code at all.

https://github.com/tosane932/sales_data_app

1. Background and Purpose of the Migration

Until this point, the deployed application on Render had been using SQLite.

SQLite is simple and convenient, but the application was running without persistent disk storage.

Because of the behavior of the hosting environment, database data could be reset during events such as:

Redeployment
Service restart
Container replacement

That was not suitable for a sales-management application that needed to preserve records.

To build a more reliable production foundation, I decided to:

Create a PostgreSQL database on Render
Connect the Flask application to it
Apply schema migrations automatically during deployment
Preserve production data independently of the application container

The goal was to establish a production database environment that could support actual data-driven operation.

2. Implementation Steps and Observed Results

Step 1: Add Database URL Conversion Logic in `config.py`

The DATABASE_URL provided by Render began with:

postgres://

However, the SQLAlchemy configuration I was using expected:

postgresql://

Using the URL without conversion caused a connection error.

To handle the difference safely, I added the following logic to config.py.

import os


raw_db_url = os.environ.get("DATABASE_URL")

if raw_db_url:
    # Convert postgres:// into a format SQLAlchemy can interpret.
    if raw_db_url.startswith("postgres://"):
        SQLALCHEMY_DATABASE_URI = raw_db_url.replace(
            "postgres://",
            "postgresql://",
            1,
        )
    else:
        SQLALCHEMY_DATABASE_URI = raw_db_url
else:
    # Use local SQLite when DATABASE_URL is not set.
    SQLALCHEMY_DATABASE_URI = (
        f"sqlite:///{os.path.join(os.path.dirname(__file__), 'local.db')}"
    )

This configuration has two paths.

Production Path

When DATABASE_URL exists:

DATABASE_URL
→ Convert postgres:// if necessary
→ Connect to PostgreSQL

Local Fallback Path

When DATABASE_URL does not exist:

No DATABASE_URL
→ Use local.db
→ Connect to SQLite

I updated the code, committed it, and pushed it to GitHub.

However, production still did not behave as expected.

Step 2: Run Migrations Automatically During Container Startup

I wanted the production database schema to be upgraded automatically whenever a new version was deployed.

I therefore changed the Docker startup command to run:

flask db upgrade

before starting the Flask application.

CMD flask db upgrade && python app.py

The intended startup flow became:

Start the container
        ↓
Apply all pending database migrations
        ↓
Start the Flask application

This reduced the risk of deploying application code that expected a newer schema while the database was still on an older revision.

Step 3: The Invisible Problem—Production Still Reported SQLite

After completing both changes, I deployed the application with confidence.

However, the production logs showed:

INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.

The application was still using:

SQLiteImpl

even though I had added the PostgreSQL URL-conversion logic.

I first suspected that the code itself was wrong.

To confirm the actual contents of the file, I ran:

cat config.py

The expected conversion logic was present.

I then suspected that I might have forgotten to commit the change.

git commit -m "Fix: Replace postgres:// with postgresql:// for Render PostgreSQL connection"

Git returned:

On branch main
Your branch is up to date with 'origin/main'.

nothing to commit, working tree clean

The message:

nothing to commit

confirmed that:

The code had already been committed
The commit had already been pushed
The working tree matched the remote repository

The problem was not an uncommitted code change.

At that point, I changed my perspective.

Instead of asking:

Is the connection code wrong?

I asked:

Does the production PostgreSQL database actually exist?

That question revealed the real cause.

When I checked the Render dashboard, I discovered that I had never created the PostgreSQL database itself.

I had prepared the connection code before preparing the destination.

The application had no production DATABASE_URL, so the fallback logic worked exactly as written:

No DATABASE_URL
        ↓
Use SQLite

The SQLite log was not evidence that the new code had failed.

It was evidence that the fallback path was functioning.

Step 4: Create the PostgreSQL Database on Render

I created a new PostgreSQL database from the Render dashboard using the free plan.

I waited until its status changed to:

Available

Render then provided an:

Internal Database URL

I copied that value into the web service's environment variables as:

DATABASE_URL

Saving the environment variable triggered a new deployment automatically.

This time, the logs changed.

INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
==> Your service is live 🎉

The important difference was:

PostgresqlImpl

This confirmed that:

The application received DATABASE_URL
The connection URL was accepted
Alembic connected to PostgreSQL
The migration process ran against PostgreSQL
The web service started successfully

The application was no longer using the SQLite fallback in production.

What Actually Happened

The problem looked like this:

I added PostgreSQL connection code
        ↓
Production still used SQLite
        ↓
I suspected the code and Git history
        ↓
Both were correct
        ↓
The PostgreSQL database had never been created
        ↓
DATABASE_URL did not exist
        ↓
The application correctly fell back to SQLite

The code was not ignoring PostgreSQL.

There was simply no PostgreSQL environment available for it to use.

Why the SQLite Fallback Made the Problem Harder to Notice

The fallback was designed to make local development convenient.

else:
    SQLALCHEMY_DATABASE_URI = (
        f"sqlite:///{os.path.join(os.path.dirname(__file__), 'local.db')}"
    )

When no DATABASE_URL was available, the application still started successfully.

That was useful locally.

However, in production, it also meant that a missing database configuration did not cause startup to fail.

Instead, the application quietly used SQLite.

This creates a trade-off.

Advantage

Local development remains easy.

No PostgreSQL required
→ Application starts with SQLite

Risk

A missing production environment variable may remain hidden.

DATABASE_URL missing in production
→ Application starts anyway
→ SQLite is used unexpectedly

A stricter production configuration could fail immediately instead of falling back silently.

For example:

import os


environment = os.environ.get("APP_ENV", "development")
raw_db_url = os.environ.get("DATABASE_URL")

if environment == "production" and not raw_db_url:
    raise RuntimeError(
        "DATABASE_URL must be configured in production."
    )

if raw_db_url:
    SQLALCHEMY_DATABASE_URI = raw_db_url.replace(
        "postgres://",
        "postgresql://",
        1,
    )
else:
    SQLALCHEMY_DATABASE_URI = (
        f"sqlite:///{os.path.join(os.path.dirname(__file__), 'local.db')}"
    )

This would make the intended behavior explicit:

Development
→ SQLite fallback is allowed

Production
→ Missing DATABASE_URL stops startup

That follows the Fail-Fast principle more closely.

Automatically Applying Migrations During Startup

The Docker command used was:

CMD flask db upgrade && python app.py

This ensures that the application starts only after the migration command succeeds.

The && operator means:

Run the second command only if the first command succeeds

Therefore:

flask db upgrade succeeds
→ Start app.py

flask db upgrade fails
→ Do not start app.py

This provides a basic deployment safety mechanism.

The application is not started against a database schema that failed to upgrade.

However, running migrations during application startup also requires care.

Possible concerns include:

Multiple application instances attempting the same migration
Long-running migrations delaying startup
Schema changes that lock production tables
Failed migrations preventing the service from becoming available
Destructive migrations affecting existing data

For a small application running as a single service, this approach may be practical.

For a larger system, a separate migration job or release command may be safer.

SQLite and PostgreSQL Behave Differently

The log output also showed an important database difference.

SQLite

Will assume non-transactional DDL.

PostgreSQL

Will assume transactional DDL.

DDL refers to schema-changing operations such as:

Creating tables
Altering tables
Adding columns
Creating indexes

PostgreSQL can handle many schema operations inside transactions.

That can make it possible to roll back certain failed migration operations.

SQLite has different limitations and behavior.

This is one reason why a migration tested only against SQLite should not automatically be considered proven for PostgreSQL.

The production database engine must also be tested.

How I Verified the Migration

The production log gave several useful pieces of evidence.

Context impl PostgresqlImpl.

This proved that Alembic was connected using the PostgreSQL implementation.

Will assume transactional DDL.

This showed that Alembic recognized the database as PostgreSQL.

Your service is live

This confirmed that the startup process completed after the migration command.

However, a complete verification should also include checks such as:

Confirming that the expected tables exist
Confirming that the current Alembic revision is recorded
Confirming that existing application operations work
Creating and reading actual records
Confirming that records remain after redeployment

Useful commands include:

flask db current

and:

flask db history

The PostgreSQL database also contains an Alembic version table used to track the applied revision.

The Three-Part Environment Modernization Process

This series covered three related improvements.

Part 1: Synchronizing Dependencies

I discovered a mismatch between:

google-genai 2.10.0

and:

requirements.txt: google-genai 2.4.0

I aligned the versions and verified the application behavior with pytest and manual testing.

Part 2: Introducing Schema History Management

I removed:

db.create_all()

and introduced:

Flask-Migrate
Alembic
Migration history managed through Git

Part 3: Moving Production to PostgreSQL

Created a PostgreSQL database on Render
Configured DATABASE_URL
Converted postgres:// when necessary
Ran migrations during startup
Confirmed PostgresqlImpl in the deployment logs

Together, these changes created a more reproducible and production-oriented foundation.

Summary

Through this infrastructure migration, I established the following improvements.

1. Environment Synchronization

Corrected dependency-version mismatches
Updated requirements.txt
Verified behavior with pytest

2. Modernized Database Operations

Removed db.create_all()
Introduced Flask-Migrate
Added migration history management

3. More Reliable Production Infrastructure

Migrated from SQLite to PostgreSQL
Added dynamic URL conversion
Configured automatic migration execution
Confirmed the database implementation through logs

The most important lesson was:

Correct code and existing infrastructure are separate requirements.

The connection logic can be perfect.

However, if the database has not been created and the environment variable has not been configured, there is nothing to connect to.

The application code and production infrastructure must fit together.

Only then can the system provide persistent data and predictable deployment behavior.

This work moved the project closer to a practical development and operations cycle based on:

Declared dependencies
Version-controlled schema changes
Production database persistence
Automated deployment
Log-based verification

Thank you for reading.

This is Part 3 of a three-part series.

Part 1 covered dependency synchronization and pytest verification.

Part 2 covered moving from db.create_all() to Flask-Migrate.

Part 3 covered the production migration from SQLite to Render PostgreSQL.

DEV Community

【Render】Migrating from SQLite to PostgreSQL: Fixing the `postgres://` Connection Error and the “Still Using SQLite” Problem [3/3]

Introduction

1. Background and Purpose of the Migration

2. Implementation Steps and Observed Results

Step 1: Add Database URL Conversion Logic in `config.py`

Production Path

Local Fallback Path

Step 2: Run Migrations Automatically During Container Startup

Step 3: The Invisible Problem—Production Still Reported SQLite

Step 4: Create the PostgreSQL Database on Render

What Actually Happened

Why the SQLite Fallback Made the Problem Harder to Notice

Advantage

Risk

Automatically Applying Migrations During Startup

SQLite and PostgreSQL Behave Differently

SQLite

PostgreSQL

How I Verified the Migration

The Three-Part Environment Modernization Process

Part 1: Synchronizing Dependencies

Part 2: Introducing Schema History Management

Part 3: Moving Production to PostgreSQL

Summary

1. Environment Synchronization

2. Modernized Database Operations

3. More Reliable Production Infrastructure

sales_data_app Maintenance Series

Pre-Production Inspection Trilogy

Post-Deployment Maintenance Series

Top comments (0)

Introduction

1. Background and Purpose of the Migration

2. Implementation Steps and Observed Results

Step 1: Add Database URL Conversion Logic in config.py

Production Path

Local Fallback Path

Step 2: Run Migrations Automatically During Container Startup

Step 3: The Invisible Problem—Production Still Reported SQLite

Step 4: Create the PostgreSQL Database on Render

What Actually Happened

Why the SQLite Fallback Made the Problem Harder to Notice

Advantage

Risk

Automatically Applying Migrations During Startup

SQLite and PostgreSQL Behave Differently

SQLite

PostgreSQL

How I Verified the Migration

The Three-Part Environment Modernization Process

Part 1: Synchronizing Dependencies

Part 2: Introducing Schema History Management

Part 3: Moving Production to PostgreSQL

Summary

1. Environment Synchronization

2. Modernized Database Operations

3. More Reliable Production Infrastructure

sales_data_app Maintenance Series

Pre-Production Inspection Trilogy

Post-Deployment Maintenance Series

Step 1: Add Database URL Conversion Logic in `config.py`