Resolving Error

You are working on a project using Supabase, Postgres database, and GORM as your ORM. Everything seems to be set up correctly, everything works fine on development, your code looks good, and you're ready to run a simple database query. But suddenly, you're hit with this cryptic error:

ERROR: prepared statement 'S_1' already exists

That is what happened to me. I was trying to look up a user in my database with a simple query:

var user models.User
result := database.DB.Db.First(&user, "email = ?", body.Email)

But instead of getting my user data, I got this error that seemed to come out of nowhere.

My Setup

Before I dive into how I solved this issue, let me give you a quick overview of my setup:

Supabase: I'm using Supabase as my backend service. It's a great open-source alternative to Firebase, providing a PostgreSQL database with a powerful API.
PostgreSQL: This is the database engine that Supabase uses under the hood.
Go: My backend is written in Go, a language I love for its simplicity and performance.
GORM: For database operations, I'm using GORM, which is a fantastic ORM library for Go.

My connection URI looked something like this:

postgresql://postgres.xxxxxxx:[MY-PASSWORD]@aws-0-us-east-1.pooler.supabase.com:6543/postgres

My initial database connection was pretty standard:

dsn := "your_supabase_connection_string_here"
func connectToDb(dsn string) {
    db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{
        Logger: logger.Default.LogMode(logger.Info),
    })
    if err != nil {
        log.Fatal("Failed to connect to the database. \n", err)
    }
    log.Println("CONNECTED to the database")
    db.Logger = logger.Default.LogMode(logger.Info)
    MigrateDatabase(db)
    DB = Dbinstance{Db: db}
}

The Journey to a Solution:

As any developer would, I started by googling the error message. I found some discussions about prepared statements and connection pooling, but nothing that directly addressed my specific situation with Supabase, Go, and GORM.

I realized I needed to understand more about how GORM handles prepared statements and how this interacts with Supabase's connection pooling. This led me down a rabbit hole of database connections, prepared statements, and the intricacies of how these components work together.

Let me walk you through my troubleshooting process and what I learned along the way:

Enabling prepared statements globally

My first thought was that perhaps GORM wasn't properly managing prepared statements. So, I modified my database connection code to explicitly enable prepared statements:

func connectToDb(dsn string) {
    db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{
        Logger: logger.Default.LogMode(logger.Info),
        PrepareStmt: true,  // Added this line
    })
    if err != nil {
        log.Fatal("Failed to connect to the database. \n", err)
    }
    log.Println("CONNECTED to the database")
    db.Logger = logger.Default.LogMode(logger.Info)
    MigrateDatabase(db)
    DB = Dbinstance{Db: db}
}

This seemed to work for a while as the error disappeared and I thought I had solved the issue. However after a while the error occurred again.

Step 2: Investigating the Migration Process

Since the error seemed to be related to database operations, I decided to take a closer look at my database migration code. Here's what it looked like initially:

func MigrateDatabase(DB *gorm.DB) {
    fmt.Println("Running migration")
    DB.AutoMigrate(
        &models.User{},
        &models.Organisation{},
    )
}

The Solution

After much trial and error, I finally found a solution that worked consistently. The key was to use session-level prepared statements, especially during the migration process. Here's what I changed:

First, I kept the global prepared statement setting in the database connection

 func connectToDb(dsn string) {
     db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{
         Logger: logger.Default.LogMode(logger.Info),
         PrepareStmt: true,
     })
     // .. rest of the function remains the same
 }

Then I added :

 func MigrateDatabase(DB *gorm.DB) {
     fmt.Println("Running migration")
     DB.AutoMigrate(
         models.User{},
         models.Organisation{},
     )
     Session := DB.Session(&gorm.Session{PrepareStmt: true})
     if Session != nil {
         fmt.Println("Migration successful")
     }
 }

This combination finally resolved the error. The application started working without any "prepared statement already exists" errors, and it has been stable ever since.

Why This Solution Works

After diving deep into GORM documentation and researching database connection pooling, I came to understand why this solution works:

Connection Pooling: Supabase uses connection pooling, which means multiple connections are maintained and reused. This can sometimes lead to conflicts with prepared statements if they're not managed properly across connections.
Global vs Session-level Prepared Statements: By enabling prepared statements globally, we ensure that GORM tries to use them for better performance. However, the session-level prepared statement in the migration ensures that each database operation (including migrations) has its own isolated set of prepared statements.
Consistent State: The session-level approach helps maintain a consistent state for prepared statements within each database operation, reducing the chance of conflicts that can occur when statements are reused across different pooled connections.

How to Fix 'ERROR: prepared statement 'S_1' already exists' in Supabase, PostgreSQL, Go, and GORM

My Setup

The Journey to a Solution:

The Solution

Why This Solution Works