Foreign Key: Postgres

SQLAlchemy: Postgres vs. SQLite

When running on Postgres, the pragma command is not only unnecessary, but it also raises an error through SQLAlchemy. This is a shortcoming of SQLAlchemy. It’s supposed to provide a seamless interface that works across databases. Mostly, it is successful, but here the illusion breaks down. One reasonable option is to keep track of whether the connection is SQLite or Postgres, and run the pragma command only when it is SQLite. Another option is to always run the pragma but put it in a try-except clause and ignore any ProgrammingError exception.

Another difference between SQLite and Postgres is the explanatory text given when a foreign constraint violation is attempted. To compare in Postgres, we drop the pragma line and use the Postgres connection string.

connection_string = "postgresql://localhost:5432/books"

The new outcome still involves the IntegrityError, but the explanatory text is different and longer, as you can see below.

Note: The connection string we’ve given below is a bit different on our platform due to different configurations.