2

I have a PostgreSQL schema that resides in a schema.sql file that gets run each time a database connection is initiated in Python. It looks something like:

CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    facebook_id TEXT NOT NULL,
    name TEXT NOT NULL,
    access_token TEXT,
    created TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

The app is deployed on Heroku, using their PostgreSQL and everything works as expected.

Now, what if I want to change a bit the structure of my users table? How can I do this the easiest and the best way? I thought of writing an ALTER... line in schema.sql for each change I want to produce in the database, but I don't think this is the best approach, since after some time the schema file will be full of ALTERs and it will slow down my app.

What's the indicated way to deploy changes made to a database?

Improve this question

2 Answers 2

Reset to default
3

Running a hard-coded script on each connection is not a great way to handle schema management.

You need to either manage the schema manually, or use a full-fledged tool that keeps a schema version identifier in the database, checks that, and applies a script to upgrade to the next schema version if it's different to the latest one. Rails calls this "migrations" and it kind-of works. If you're using Django it has schema management too.

If you're not using a framework like that, I suggest just writing your own schema upgrade scripts. Add a "schema_version" table with a single row. SELECT it when the app first starts after a redeploy and if it's lower than the current version the app knows about, apply the update script(s) in order, eg schema_1_to_2, schema_2_to_3, etc.

I don't recommend doing this on connect, do it on app start, or better, as a special maintenance command. If you do it on every connection you'll have multiple connections trying to make the same changes and you'll land up with duplicated columns and all sorts of other mess.

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

2 Comments

It's a good point. I am using Flask as framework and I don't see any extension for handling schemas. Probably I'll write a module of my own. Thank you!
@linkyndy Try searching first. A 30s google search for "flask migrations" found enough info that it's clear other people have tried to do the same thing already. Don't reinvent the wheel, learn from them. See, eg, mattupstate.com/python/databases/2012/11/15/…
0

I support several django apps on heroku with Postgres. I just connect via PgAdmin and run my scripts when changes are required. I don't see any need for running a script every time a connection is made.

Improve this answer

5 Comments

I haven't got PgAdmin installed. What other solutions are available?
You can you the psql client, but if you are going to be using Postgres, I recommend installing pgAdmin. Its free and is available for windows, Linux and Mac OSX.
@DavidS Funny how preferences differ - I use psql exclusively and find that PgAdmin-III drives me kind of nuts.
@CraigRinger I use psql for some things. But, I find that if you have a complex database will multiple schema and functions, etc it's just easier for me to navigate around it. To each his/her own. I made the recommendation of pgAdmin because my gut feeling was they OP is not a database expert and that they UI might help. That was just my gut feeling.
@CraigRinger - a while back, I started making a conscience effort to learn more of the psql commands (e.g. \dt) and I have to say that now, I use it a lot more than pgAdmin. It's definitely worth the small investment to learn.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.