How to ship stored procedures (PL/PGSQL) to be used by Pyramid web app to the environment?

Question

I am working on a web app written using Pyramid web application. Using MySQL to store the relational stuff. But the web app is also a data storing facility and we use Postgres for that purpose.

Note that each user's account gets its own connection parameters in Postgres. The hosts running Postgres is not going to be the same for users.

We have a couple of stored procedures that are essential for the app to function. I was wondering how to ship the procedures to each Postgres database instance. I would like to make sure that it is pretty easy to update them as well.

Here is what I could come up with so far.

I have a file in the app's code base called procedures.sql

CREATE FUNCTION  {procedure_name_1} (text, text,
                      max_split integer) RETURNS text AS $$
BEGIN
-- do stuff --
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION  {procedure_name_2} (text, text,
                      max_split integer) RETURNS text AS $$
BEGIN
-- do stuff --
END;
$$ LANGUAGE plpgsql;

Whenever a user wants to talk to his DB, I execute _add_procedures_to_db function from the python app.

procedure_name_map = {
    'procedure_name_1': 'function_1_v1',
    'procedure_name_2': 'function_2_v1'
}

def _add_procedures_to_db(connection):
    cursor = connection.Cursor()

    with open(PROCEDURE_FILE_PATH) as f:
        sql = f.read().format(**procedure_name_map)
        try:
            cursor.execute(sql)
            connection.commit()
        except:
            pass

Note that connection params will be obtained when we want to do some transaction within web response cycle from MySQL DB.

Strategy is to change function_1_v1 to function_1_v2 in case I update the code for the procedure.

But this seems like such an expensive way to do this as each time I want to connect, I will get an exception that has to be handled after first time.

So here is my question:

Is there another way to do this from within the web app code? Or should I make procedure updates a part of deployment and configuration rather than an app layer thing?

Answer 1

If you are looking how to change the database (tables, views, stored procedurues) between different Pyramid web application version deployments that's usually called migration.

• If you are using SQLAlchemy you can use automated migration tools like Alembic

• If you are using raw SQL commands, then you need to write and run a custom command line script each time you deploy an application with different version. This command line script would prepare the database for the current application version. This would include running ALTER TABLE SQL commands, etc.