Mike Bayer Pycon 2014 W Wr ri it ti in ng g t th he e A Ap pp p Mike Bayer mike_mp@zzzcomputing.com 1 of 42

W Wh ha at t''s s t th hi is s t ta al lk k a ab bo ou ut t? ? Writing larger, longer lived applications on top of SQLAlchemy The code that we write when we first plug in our frameworks and libraries - the "foundation" Having a healthy amount of this "up front" code is a good thing, rather than a failure of libraries/ frameworks to do everything possible SQLAlchemy is designed with "foundations" in mind 2 of 42

D De ef fi in ni in ng g " "F Fo ou un nd da at ti io on n" " Technologies to use, libraries, frameworks Directory structures, file naming schemes Architectural patterns Build / Configuration / Test approaches The decisions we make here will determine: what new developers need to grok the specific steps to go from “Use case” into “Executed code” 3 of 42

T Th he e F Fo ou un nd da at ti io on n I Is s. .. .. . Source files at the base of our application The basis for patterns and idioms that are repeated throughout use-case code Possibly reusable in other apps The adaptation of the frameworks and libraries we’ve chosen to the task at hand 4 of 42

T Th he e F Fo ou un nd da at ti io on n I Is s N No ot t. .. .. . A replacement for existing, well-documented and solid frameworks or libraries The start of “our company’s new app platform!” A reinvention of the wheel …unless we’re talking about some very mediocre wheels 5 of 42

L Le et t''s s S Se ee e a a F Fo ou un nd da at ti io on n! ! Web framework: Pyramid! Things I like about Pyramid: view / renderer model - separates response data from templates opinionated in the right places great emphasis on testability huge attention to detail, mega-mature components curmudgeonly like me! Use whatever framework! 6 of 42

S Sa am mp pl le e A Ap pp pl li ic ca at ti io on n https://bitbucket.org/zzzeek/pycon2014_atmcraft/ 7 of 42

O Ov ve er rv vi ie ew w the "meta" folder conventions / recipes session scoping constraint naming migrations testing 8 of 42

T Th he e M Me et ta a F Fo ol ld de er r atmcraft/ model/ meta/ __init__.py base.py orm.py schema.py types.py 9 of 42

T Th he e M Me et ta a F Fo ol ld de er r __init__.py - provides names as from atmcraft.model.meta import base.py - fundamental units like declarative_base, Session orm.py - object-relational mapping helpers schema.py - database schema generation/definition helpers types.py - types and SQL constructs specific to our application 10 of 42

C Co on nv ve en nt ti io on ns s / / R Re ec ci ip pe es s - - O Ov ve er rv vi ie ew w Goal is to reduce as much repetition as possible without taking it too far Key strategies: plain Python functions, mixins, events, @compiles, custom types SQLAlchemy is continuously trying to add more and more capability and predictability Some conventions/recipes eventually become SQLAlchemy features 11 of 42

W Wh hy y N No ot t P Pa ar rt t o of f S SQ QL LA Al lc ch he em my y? ? Naming - once it needs names, SQLAlchemy usually wants you to do it Some conventions d do o become features, or at least partial features Composable components, rather than pre-packaged defaults and patterns, provides more long-term value and use case potential "Give someone a fish and feed them for a day..." ... "give someone an @event.listens_for('fish_available') hook and..." ... you get it 12 of 42

C Co on nv ve en nt ti io on ns s - - M Mi ix xi in ns s a an nd d F Fu un nc ct ti io on ns s Add an integer primary key to classes, refer via foreign key: c cl la as ss s S Su ur rr ro og ga at te eP PK K(object): id = Column(Integer, primary_key=True) d de ef f ReferenceCol(tablename, nullable=False, **kw): r re et tu ur rn n Column(ForeignKey("%s.id" % tablename), nullable=nullable, **kw) 13 of 42

C Co on nv ve en nt ti io on ns s - - M Mi ix xi in ns s a an nd d F Fu un nc ct ti io on ns s Usage: c cl la as ss s A Ac cc co ou un nt tB Ba al la an nc ce e(SurrogatePK, Base): __tablename__ = 'account_balance' balance_type_id = ReferenceCol('balance_type') balance_type = relationship("BalanceType") 14 of 42

C Co on nv ve en nt ti io on ns s - - M Mi ix xi in ns s a an nd d F Fu un nc ct ti io on ns s Slicker version that's in the example app: c cl la as ss s A Ac cc co ou un nt tB Ba al la an nc ce e(SurrogatePK, Base): __tablename__ = 'account_balance' balance_type = many_to_one("BalanceType") 15 of 42

C Co on nv ve en nt ti io on ns s - - U Us si in ng g @ @c co om mp pi il le es s Define SQL idioms that vary across backends: f fr ro om m s sq ql la al lc ch he em my y. .e ex xt t. .c co om mp pi il le er r i im mp po or rt t compiles f fr ro om m s sq ql la al lc ch he em my y. .s sq ql l i im mp po or rt t functions f fr ro om m s sq ql la al lc ch he em my y. .t ty yp pe es s i im mp po or rt t DateTime c cl la as ss s u ut tc cn no ow w(functions.FunctionElement): key = 'utcnow' type = DateTime(timezone=True) @ @c co om mp pi il le es s(utcnow, 'postgresql') d de ef f _pg_utcnow(element, compiler, **kw): r re et tu ur rn n "(CURRENT_TIMESTAMP AT TIME ZONE 'utc')"\ "::TIMESTAMP WITH TIME ZONE" @ @c co om mp pi il le es s(utcnow, 'mssql') d de ef f _ms_utcnow(element, compiler, **kw): r re et tu ur rn n "GETUTCDATE()" 16 of 42

C Co on nv ve en nt ti io on ns s - - U Us si in ng g @ @c co om mp pi il le es s Use cases: # in SQL statements result = connection.execute(select([utcnow()])) # in table defs Table('orders', metadata, Column('created_at', DateTime(timezone=True), default=utcnow()) ) 17 of 42

R Re ec ci ip pe es s - - U Us si in ng g e ev ve en nt ts s Adds auto-generating "created_at" / "updated_at" columns to all tables @ @e ev ve en nt t. .l li is st te en ns s_ _f fo or r(Table, "after_parent_attach") d de ef f timestamp_cols(table, metadata): i if f metadata i is s Base.metadata: table.append_column( Column('created_at', DateTime(timezone=True), nullable=False, default=utcnow()) ) table.append_column( Column('updated_at', DateTime(timezone=True), nullable=False, default=utcnow(), onupdate=utcnow()) ) 18 of 42

R Re ec ci ip pe es s - - C Cu us st to om m T Ty yp pe es s Persist a password transparently using bcrypt: c cl la as ss s P Pa as ss sw wo or rd d(str): d de ef f __eq__(self, other): i if f n no ot t isinstance(other, Password): other = bcrypt.hashpw(self, self) r re et tu ur rn n str.__eq__(self, other) d de ef f __ne__(self, other): r re et tu ur rn n n no ot t self.__eq__(other) c cl la as ss s B Bc cr ry yp pt tT Ty yp pe e(TypeDecorator): impl = String(128) d de ef f process_bind_param(self, value, dialect): r re et tu ur rn n bcrypt.hashpw(value, bcrypt.gensalt(4)) d de ef f process_result_value(self, value, dialect): r re et tu ur rn n Password(value) 19 of 42

R Re ec ci ip pe es s - - C Cu us st to om m T Ty yp pe es s Use Case: # runs "ImASecret" through bcrypt before INSERT session.add(Client(identifier='snowflake', secret='ImASecret')) session.commit() c1 = session.query(Client).\ filter_by(identifier == 'snowflake').first() # runs "ImASecret" through bcrypt w/ correct salt # before comparison operation r re et tu ur rn n c1.secret == 'ImASecret' 20 of 42

S Se es ss si io on n S Sc co op pi in ng g The SQLAlchemy ORM defines database access through a central object known as the S Se es ss si io on n. When writing a web app, we need to set up two things: Where is the session during a request? 1. How does it get there and how does it leave? 2. 21 of 42

S Se es ss si io on n S Sc co op pi in ng g - - W Wh he er re e i is s i it t? ? Method One - scoped to the current thread f fr ro om m s sq ql la al lc ch he em my y. .o or rm m i im mp po or rt t sessionmaker, scoped_session Session = scoped_session(sessionmaker()) # usage Session.query(Foo).all() 22 of 42

S Se es ss si io on n S Sc co op pi in ng g - - W Wh he er re e i is s i it t? ? Method Two - scoped to the current request f fr ro om m s sq ql la al lc ch he em my y. .o or rm m i im mp po or rt t sessionmaker, scoped_session f fr ro om m p py yr ra am mi id d. .t th hr re ea ad dl lo oc ca al l i im mp po or rt t get_current_request Session = scoped_session(sessionmaker(), scopefunc=get_current_request) # usage Session.query(Foo).all() 23 of 42

S Se es ss si io on n S Sc co op pi in ng g - - W Wh he er re e i is s i it t? ? Method Three - attached to the current request f fr ro om m s sq ql la al lc ch he em my y. .o or rm m i im mp po or rt t sessionmaker f fr ro om m p py yr ra am mi id d. .e ev ve en nt ts s i im mp po or rt t NewRequest, subscriber Session = sessionmaker() @ @s su ub bs sc cr ri ib be er r(NewRequest) d de ef f new_session(event): event.request.db_session = Session() # usage request.db_session.query(Foo).all() 24 of 42

S Se es ss si io on n S Sc co op pi in ng g - - H Ho ow w d do oe es s i it t l le ea av ve e? ? Use the web framework's system for ending a request f fr ro om m p py yr ra am mi id d. .e ev ve en nt ts s i im mp po or rt t NewRequest, subscriber @ @s su ub bs sc cr ri ib be er r(NewRequest) d de ef f cleanup_session(event): d de ef f remove(request): Session.remove() event.request.add_finished_callback(remove) 25 of 42

C Co on ns st tr ra ai in nt t N Na am mi in ng g Database constraints like FOREIGN KEY, UNIQUE, INDEX, etc. all have names, so they can be targeted for CREATE / DROP Controlling these names is a good idea, though tedious SQLAlchemy generally provides no default names We can make names automatically using schema events The new naming_convention feature provides this for us 26 of 42

C Co on ns st tr ra ai in nt t N Na am mi in ng g S Se et tt ti in ng g New in SQLAlchemy 0.9.2 - 0.9.4 f fr ro om m s sq ql la al lc ch he em my y. .e ex xt t. .d de ec cl la ar ra at ti iv ve e i im mp po or rt t declarative_base Base = declarative_base() Base.metadata = MetaData(naming_convention={ "pk": "pk_%(table_name)s", "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s", "uq": "uq_%(table_name)s_%(column_0_name)s", "ix": "ix_%(table_name)s_%(column_0_name)s", "ck": "ck_%(table_name)s_%(constraint_name)s" }) # ... later ... Base.metadata.create_all(some_engine) 27 of 42

C Co on ns st tr ra ai in nt t N Na am mi in ng g SQL Output: C CR RE EA AT TE E T TA AB BL LE E account_balance ( id SERIAL N NO OT T N NU UL LL L, balance NUMERIC(8, 2), last_trans_id UUID, created_at T TI IM ME ES ST TA AM MP P W WI IT TH H TIME Z ZO ON NE E N NO OT T N NU UL LL L, updated_at T TI IM ME ES ST TA AM MP P W WI IT TH H TIME Z ZO ON NE E N NO OT T N NU UL LL L, account_id INTEGER, balance_type_id INTEGER, C CO ON NS ST TR RA AI IN NT T pk_account_balance P PR RI IM MA AR RY Y K KE EY Y (id), C CO ON NS ST TR RA AI IN NT T fk_account_balance_account_id_account F FO OR RE EI IG GN N K KE EY Y(account_id) R RE EF FE ER RE EN NC CE ES S account (id), C CO ON NS ST TR RA AI IN NT T fk_account_balance_balance_type_id_balance_type F FO OR RE EI IG GN N K KE EY Y(balance_type_id) R RE EF FE ER RE EN NC CE ES S balance_type (id) ); 28 of 42

G Ge en ne er ra at ti in ng g a an n A Al le em mb bi ic c M Mi ig gr ra at ti io on n Generating a migration: classic$ .venv/bin/alembic -c development.ini \ revision -m "initial rev" --autogenerate [alembic.migration] Context impl PostgresqlImpl. [alembic.migration] Will assume transactional DDL. [alembic.autogenerate.compare] Detected added table 'balance_type' [alembic.autogenerate.compare] Detected added table 'client' [alembic.autogenerate.compare] Detected added table 'account' [alembic.autogenerate.compare] Detected added table 'auth_session' [alembic.autogenerate.compare] Detected added table 'account_balance' [alembic.autogenerate.compare] Detected added table 'transaction' Generating ./migrations/versions/52caef8c4043_initial_rev.py ... done 29 of 42

C Co on ns st tr ra ai in nt t N Na am mi in ng g i in n M Mi ig gr ra at ti io on ns s Autogenerate will place our naming conventions into the file: revision = '52caef8c4043' down_revision = None d de ef f upgrade(): op.create_table('account_balance', sa.Column('id', sa.Integer(), nullable=False), sa.Column('balance', sa.Numeric(precision=8, scale=2), nullable=True), # ... sa.ForeignKeyConstraint(['account_id'], [u'account.id'], name=op.f('fk_account_balance_account_id_account')), sa.ForeignKeyConstraint(['balance_type_id'], [u'balance_type.id'], name=op.f('fk_account_balance_balance_type_id_balance_type')), sa.PrimaryKeyConstraint('id', name=op.f('pk_account_balance')) ) # ... d de ef f downgrade(): op.drop_table('account_balance') # ... 30 of 42

U Up pc co om mi in ng g A Al le em mb bi ic c F Fe ea at tu ur re es s Multiple version directories Automatic branch resolution Versions as a directed graph, rather than linear 31 of 42

T Te es st ti in ng g For testing database-enabled models, two approaches are: Transactional Mock 32 of 42

T Tr ra an ns sa ac ct ti io on na al l T Te es st ti in ng g Simplified structure c cl la as ss s S So om me eT Te es st t(TestCase): d de ef f setUp(self): self.connection = engine.connect() self.trans = connection.begin() self.session = Session(bind=self.connection) d de ef f _fixture(self): self.session.add(Foo()) d de ef f test_something(self): self._fixture() foo = self.session.query(Foo).first() a as ss se er rt t foo d de ef f tearDown(self): self.session.close() self.trans.rollback() self.connection.close() 33 of 42

T Tr ra an ns sa ac ct ti io on na al l T Te es st ti in ng g In practice f fr ro om m . .. .m mo od de el l i im mp po or rt t Client, AuthSession f fr ro om m . .. .v vi ie ew ws s i im mp po or rt t auth_on_token f fr ro om m p py yr ra am mi id d i im mp po or rt t testing d de ef f _auth_fixture(self, created_at=None): auth_session = AuthSession(Client(identifier="ident"), created_at=created_at) Session.add(auth_session) r re et tu ur rn n auth_session d de ef f test_auth_timeout(self): auth_session = self._auth_fixture( created_at=datetime.datetime.utcnow() - datetime.timedelta(seconds=800)) request = testing.DummyRequest() request.params={"auth_token": auth_session.token} self.assertRaises(exc.HTTPForbidden, auth_on_token(l la am mb bd da a req: "hi"), request) 34 of 42

T Tr ra an ns sa ac ct ti io on na al l T Te es st ti in ng g Advantages: Easy Well suited towards integration tests Disadvantages: Slow Tests are too generalized Need a database 35 of 42

M Mo oc ck k T Te es st ti in ng g Using MockSession in atmcraft/tests/_mock_session.py > >> >> > f fr ro om m a at tm mc cr ra af ft t. .t te es st ts s. ._ _m mo oc ck k_ _s se es ss si io on n i im mp po or rt t MockSession > >> >> > session = MockSession() > >> >> > session.query(Foo).filter_by(id=5).first.return_value = 5 > >> >> > session.query(Foo).filter(Foo.id == 7).first.return_value = "blah" > >> >> > session.query(Foo).filter_by(id=12).one.side_effect = NoResultFound > >> >> > session.query(Foo).filter_by(id=5).first() 5 > >> >> > session.query(Foo).filter(Foo.id == 7).first() "blah" > >> >> > session.query(Foo).filter_by(id=12).one() Traceback ... sqlalchemy.orm.exc.NoResultFound 36 of 42

M Mo oc ck k T Te es st ti in ng g Simplified structure f fr ro om m . ._ _m mo oc ck k_ _s se es ss si io on n i im mp po or rt t MockSession c cl la as ss s S So om me eT Te es st t(TestCase): d de ef f setUp(self): self.session = MockSession() d de ef f _fixture(self): self.session.query(Foo).first = Foo() d de ef f test_something(self): self._fixture() foo = self.session.query(Foo).first() a as ss se er rt t foo 37 of 42

M Mo oc ck k T Te es st ti in ng g In practice (part 1 of 2) - the fixture is much more complicated... f fr ro om m . .. .m mo od de el l i im mp po or rt t Client, AuthSession f fr ro om m . .. .v vi ie ew ws s i im mp po or rt t auth_on_token f fr ro om m p py yr ra am mi id d i im mp po or rt t testing d de ef f _auth_fixture(self, created_at): auth_session = AuthSession(Client(identifier="ident"), created_at=created_at) validate_session_q = Session.query(AuthSession).\ filter_by(token=auth_session.token).\ filter(AuthSession.created_at > utcnow() - datetime.timedelta(seconds=360)) i if f created_at i is s n no ot t None a an nd d datetime.datetime.utcnow() - \ created_at > datetime.timedelta(seconds=360): validate_session_q.one.side_effect = NoResultFound e el ls se e: validate_session_q.one.return_value = auth_session r re et tu ur rn n auth_session 38 of 42

M Mo oc ck k T Te es st ti in ng g In practice (part 2 of 2) - ... but the test itself is exactly the same d de ef f test_auth_timeout(self): auth_session = self._auth_fixture( created_at=datetime.datetime.utcnow() - datetime.timedelta(seconds=800)) request = testing.DummyRequest() request.params={"auth_token": auth_session.token} self.assertRaises(exc.HTTPForbidden, auth_on_token(l la am mb bd da a req: "hi"), request) 39 of 42

M Mo oc ck k T Te es st ti in ng g Advantages: No database needed Tests can be focused to small units Encourages code to be modular Fast Disadvantages fixtures can be very tedious to create won't catch ORM configurational/SQL/query issues can turn into "mirrors" of tested code 40 of 42

A An n e ex xa am mp pl le e a ap pp p! ! run the pyramid app run the "client" 41 of 42

T Th ha an nk ks s! !! ! 42 of 42