Postgres with neon not working

i generated the default migrations and when i try to migrate:

$ php artisan migrate:fresh

   INFO  Preparing database.

  Creating migration table ........................................................................................................... 240.69ms DONE

   INFO  Running migrations.

  0001_01_01_000000_create_users_table ............................................................................................... 240.31ms FAIL

   Illuminate\Database\QueryException 

  SQLSTATE[25P02]: In failed sql transaction: 7 ERROR:  current transaction is aborted, commands ignored until end of transaction block (Connection: pgsql, Host:
 ep-wandering-math-agv6071k-pooler.c-2.eu-central-1.aws.neon.tech, Port: 5432, Database: neondb, SQL: alter table "users" add constraint "users_email_unique" uni
que ("email"))

  at C:\Users\elton\Documents\programming\taskr\backend\vendor\laravel\framework\src\Illuminate\Database\Connection.php:831
    827▕             $exceptionType = $this->isUniqueConstraintError($e)
    828▕                 ? UniqueConstraintViolationException::class
    829▕                 : QueryException::class;
    830▕
  ➜ 831▕             throw new $exceptionType(
    832▕                 $this->getNameWithReadWriteType(),
    833▕                 $query,
    834▕                 $this->prepareBindings($bindings),
    835▕                 $e,

  1   C:\Users\elton\Documents\programming\taskr\backend\vendor\laravel\framework\src\Illuminate\Database\Connection.php:577
      PDOException::("SQLSTATE[25P02]: In failed sql transaction: 7 ERROR:  current transaction is aborted, commands ignored until end of transaction block")    

  2   C:\Users\elton\Documents\programming\taskr\backend\vendor\laravel\framework\src\Illuminate\Database\Connection.php:577
      PDOStatement::execute()

I don't know whats the problem, i mean the default migrations should be correct?

EltonRexha

3 months ago

Level 1

fixed it by:

In your .env file, change from:

DB_HOST=ep-wandering-math-agv6001k-pooler.c-2.eu-central-1.aws.neon.tech

To (remove '-pooler'):

DB_HOST=ep-wandering-math-agv6001k.c-2.eu-central-1.aws.neon.tech

itsmill3rtime

3 months ago

Level 1

I had this same issue a long time ago...

In short... it is because you are using a pooled connection.

In long... Database migrations often fail on a pooled connection because connection poolers are designed for short, stateless transactions, while migrations require long-running, stateful connections and exclusive access to the database schema. Also some connection poolers use a transaction-based pooling mode (like transaction_mode in Supavisor/PgBouncer). In this mode, a client gets a connection for the duration of a single transaction. Migrations, however, often run multiple commands in sequence that require session-level state to persist between queries, which breaks the pooler's assumptions.

My recommendation is creating a second connection in your app, and put the non-pooled connection string in it. then in your migrations put $connection = '...your second connection...' so they will utilize that while the rest of your app uses the pooled connection.

My fix was i actually hooked into the events and checked for when a migration is running and i dynamically changed the connection on the fly

1 like

Please or to participate in this conversation.