Heroku Postgresql DB - Unable to update database once deployed

Question

I am using Heroku to deploy my postgresql database project. The site I created using PHP, HTML5 and CSS works fine on localhost with phpmyadmin and the equivalent MySQL database. But, when I transferred it over to Heroku only the SELECT queries work (inserts, updates, and deletes are no good).

The only changes from my localhost to heroku site lie in my .env and file to create the php data object which I pasted below. Can anyone tell me where to look next to solve this issue? Heroku error logs don't show any error. I'm using pgAdmin4 for the postgres db.

.env file

DATABASE_URL="pgsql://postgres:mypassword@localhost:5432/dbname"

other file

require __DIR__ . '/vendor/autoload.php';

$dbopts = parse_url(getenv('DATABASE_URL'));

$dbopts["path"] = ltrim($dbopts["path"], "/");

$db = new PDO("pgsql:" . sprintf(
"host=%s;port=%s;user=%s;password=%s;dbname=%s",
$dbopts["host"],
$dbopts["port"],
$dbopts["user"],
$dbopts["pass"],
ltrim($dbopts["path"], "/")
));

I wonder if it would be worth having another variable to store the result of the sprintf(), so you can easily echo it. Would you show us what is in it, minus any security-sensitive info? (Don't assume what's in it - check what it actually resolves to in PHP). — halfer
– halfer, Commented Apr 30, 2018 at 17:46
Hmm, are you reading the URL from config, parsing it, and then putting it all back together again? Can you just feed getenv('DATABASE_URL') into the connection string directly? — halfer
– halfer, Commented Apr 30, 2018 at 17:48
It looks like you're doing ltrim($dbopts["path"], "/") twice too - is that deliberate? — halfer
– halfer, Commented Apr 30, 2018 at 17:48
Thanks so much for the help!! @Chris was correct in that the issue was with the database language migration. Turns out Postgres normalizes attribute names by making them all lowercase, so my php functions (ie array_column) didn't recognize the table attributes with uppercase letters. Noob mistake. — Alicia
– Alicia, Commented Apr 30, 2018 at 23:29
That's great if you fixed it! If you think it might help someone else, please post a summary of the solution below, in an answer proper (or ask @Chris to, so they are appropriately credited). — halfer
– halfer, Commented Apr 30, 2018 at 23:36

Alicia · Accepted Answer · 2018-05-01 04:25:17Z

1

Solution to problem:

phpmyadmin only supports MySQL, while I used the free Postgres add-on for Heroku. Migration from phpmyadmin locally to Heroku revealed slight differences in syntax between the two languages.

My particular error had to do with uppercase letters in MySQL attributes being reverted to all lowercase in Postgres. Example in one of my functions:

MySQL Version:

$sql = "SELECT DISTINCT ID FROM Doctors";
$query = $db->prepare($sql); 
$query->execute(); 
$results = $query->fetchAll(\PDO::FETCH_ASSOC); 
$did = array_column($results, 'ID');
$docid = implode(",", $did);

Postgres Version:

$sql = "SELECT DISTINCT ID FROM Doctors";
$query = $db->prepare($sql); 
$query->execute(); 
$results = $query->fetchAll(\PDO::FETCH_ASSOC); 
$did = array_column($results, 'id');   // difference
$docid = implode(",", $did);

If not for a uni project, I would use phpPgAdmin instead of phpmyadmin to avoid migration discrepancies. Thanks @Chris and @halfer!

edited May 1, 2018 at 4:25

answered May 1, 2018 at 2:14

Alicia

464 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Chris Over a year ago

Slight clarification: Heroku uses PostgreSQL by default, but there are compatible MySQL providers as well. If you have a good reason for running MySQL on Heroku you can. See elements.heroku.com/addons

Alicia Over a year ago

@Chris True! PostgreSQL is just the only RDBMS that can be used without giving out your credit card info (unfortunately).

Collectives™ on Stack Overflow

Heroku Postgresql DB - Unable to update database once deployed

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related