Comment by simonw

I'd test them the same way I test other database stuff: run automated tests which spin up a database server for the duration, then run migrations to install the schema and stored procedures, then execute tests using your programming language of choice.

I still mostly use Django for PostgreSQL projects and the Django default test framework is great at running a temporary PostgreSQL (or MySQL or SQLite).