Building the App

Mike Bayer
Pycon 2014
W
Wr
ri
it
ti
in
ng
g t
th
he
e A
Ap
pp
p
Mike Bayer [email protected]
1 of 42

View Slide

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

View Slide

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

View Slide

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

View Slide

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

View Slide

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

View Slide

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

View Slide

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

View Slide

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

View Slide

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

View Slide

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

View Slide

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

View Slide

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

View Slide

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

View Slide

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

View Slide

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

View Slide

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

View Slide

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

View Slide

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

View Slide

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

View Slide

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

View Slide

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

View Slide

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

View Slide

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

View Slide

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

View Slide

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

View Slide

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

View Slide

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

View Slide

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

View Slide

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

View Slide

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

View Slide

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

View Slide

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

View Slide

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

View Slide

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

View Slide

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

View Slide

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

View Slide

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() -
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

View Slide

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() -
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

View Slide

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

View Slide

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

View Slide

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

View Slide

Building the App

Building the App

mike bayer

More Decks by mike bayer

Other Decks in Programming

Featured

Transcript