AMA ● Please, ask questions. ● Suggestions: Postgres, DB operations, Python app development, Mozilla webdev, GSoC, Outreachy (formerly OPW), Open Source/Free Software, Vim vs Emacs, memes involving setting things on fire, release engineering, messy diagrams, power lifting ● Can I get a scribe volunteer?
I work on Socorro. http://github.com/mozilla/socorro http://crash-stats.mozilla.com PyLadies PDX, Founder Python Software Foundation, Director Ada Initiative, Advisor PostgreSQL, Major Contributor
My environment: ● Schema migrations are frequent. ● Automated schema migration is a goal. ● Stage environment is enough like production for testing. ● Writing a small amount of code is ok.
Process with Alembic: 1.Make changes to model.py or raw_sql files 2.Run: alembic revision –-auto-generate 3.Edit revision file 4.Commit changes 5.Run migration after auto-deploy of a release
Process with Alembic: 1.Make changes to model.py or raw_sql files 2.Run: alembic revision -–auto- generate 3.Edit revision file 4.Commit changes 5.Run migration after auto-deploy of a release 5.Have jenkins and travis-ci run downgrade/upgrade as part of test suite. 6.Run migration automatically.
A good ORM provides: ● Schema defined in one place ● Reusable components ● Integration with useful tools ● Database version independence ● Ability to use raw SQL
And: ● Gives you a new way to think about schemas ● Develops compassion for how horrible ORMs can be ● Gives you developer-friendly vocabulary for discussing why ORM-generated code is often terrible
https://alembic.readthedocs.org Vocabulary: revision: a single migration down_revision: previous migration upgrade: apply 'upgrade' change downgrade: apply 'downgrade' change offline mode: emit raw SQL for a change
Ignore certain schemas or partitions? In env.py: def include_symbol(tablename, schema): return schema in (None, "bixie") and re.search(r'_\d{8}$', tablename) is None
Manage User Defined Functions? Chose to use raw SQL files 3 directories, 128 files: procs/ types/ views/ def load_stored_proc(op, filelist): procs_dir = os.path.normpath(os.path.join( __file__, '../../', 'external/postgresql/raw_sql/procs' )) for filename in filelist: sqlfile = os.path.join(sqlfile,filename) with open(myfile, 'r') as stored_proc: op.execute(stored_proc.read())
Always roll forward. 1.Put migrations in a separate commit from schema changes. 2.Revert commits for schema change, leave migration commit in-place for downgrade support.
Store schema objects in the smallest, reasonable, composable unit. 1.Use an ORM for core schema. 2.Put types, UDFs and views in separate files. 3.Consider storing the schema in a separate repo from the application.
Write tests. Run them every time. 1.Write a simple tool to create a new schema from scratch. 2.Write a simple tool to generate fake data. 3.Write tests for these tools. 4.When anything fails, add a test.
1.Understand partitions 2.Never apply a DEFAULT to a new column 3.Help us manage UDFs better 4.INDEX CONCURRENTLY 5.Prettier syntax for multi-commit sequences
Other tools: Sqitch http://sqitch.org/ Written by PostgreSQL contributor Erwin http://erwin.com/ Commercial, popular with Oracle South http://south.aeracode.org/ Django-specific, well-supported
Sane Schema Management with Alembic and SQLAlchemy Selena Deckelmann Mozilla @selenamarie http://www.whitecells.org https://speakerdeck.com/selenamarie/sane -schema-management-with-alembic
selenamarie
sat
nikkei_engineer_recruiting
masa5555
karamem0
sinsoku
yuj1osm
yoshikiiida
minorun365
niftycorp
orgachem
naohachi89
rakus_dev
pauljervisheath
kevinliebholz
phodgson
malarkey
tammyeverts
sstephenson
maltzj
chrisshort
dougneiner
robhawkes
jrom
reverentgeek