Hand Coded Applications with SQLAlchemy

one or two more sentences from the person standing here hello so this is Mike Beier and this is his talk hand-coded applications with sequel alchemy this is a 45-minute talk so please don’t exit until the questions and at the end of the audio session thank you hi thanks for coming so pretty exciting I’m gonna talk about some sequel alchemy philosophy and actually those of you saw Brandon Rhoads talk just earlier kind of similar concept although coming from a maybe different perspective I really appreciated Brandon’s take on how disk access works that was really fantastic so we’re gonna start really basic here hmm what’s what’s a database so database has we probably know we can put data in and get it back out and when we store data it’s sort of some kind of record or row or a document that’s kind of what databases are doing for us within a record or row we’re gonna have sets of attributes nothing mysterious there and we’re gonna do queries that allow us to find records that have specific kinds of attributes so that applies to pretty much any database that I’ve ever seen relational database make you more specific the fundamental storage unit in a relational database is the column which is composed in two rows those are then composed into tables when we do queries we can take rows from multiple tables and transform them into new rows composed for multiple tables together using joins so that’s kind of a degree of flexibility that relational databases give us we can also make something called the derive table which is kind of like a sub query means they were going to take some rows within our query and that becomes something that we can then further select from which I find pretty cool relational databases give us lots of cool stuff like set operations and aggregate functions and groupings recursive queries and triggers and functions and stored procedures and all that stuff that we associate with relational databases and then the entire thing happens within the realm of transact guarantees which we know is the acid model of atomicity consistency isolation and durability and we’re going to talk a little bit later about the C and acid which I find to be fairly important how do we talk to relational databases so we know there’s in Python we have the Python database API which is kind of typically the lowest level that we use in python db-api is usually you know talk either to the network protocol or to a c api for that database then we have abstraction layers which is anything on top of a database API and to some degree within some DB api’s and then on top of that you have object relational mapper –is which we’re going to talk about mostly and here’s kind of a view of that to the left we have the lowest level of abstraction to the right we have more abstraction and DB api’s are kind of become start to become abstraction layers and know our ends maybe start to become more than the abstraction layers that’s kind of just a way of looking at it so what’s the know RM the first thing we do with our ends the most obvious things that it automates the persistence of our domain models which are like our object-oriented class hierarchies into a relational database schema so we all know that it provides the ability to query and compose sequel statements in terms of our object model our domain model and then when we get results from a database it translates those relational results back into instances of our classes domain model state so okay that’s basically the ground trip model and then something that Oren’s due to differing degrees is that it can mediate between object-oriented and relational geometries so a relationships kind of if you have two classes that have some linkage to each other like a collection an ORM will relate that to two tables or three tables with foreign keys between them some ORM can also do inheritance like if you have a class inheritance hierarchy it can represent that in a relational database as maybe one table with a discriminator or several tables so there are things that object-oriented that Owens can do for us so how much abstraction should have no RM provide and we’re talking here about abstraction is hiding first so what it concealed details of how the the data store and how its queried would it conceal that the database itself is even relational which means you know would it would the same interface be used to talk to MongoDB would we get non-relational data back from the same query interface as we do a relational store and these are questions that are asking to what degree we should be hiding things so there’s another way of looking at it well first we’ll say what are the problems that we might have if we consider ORM abstraction as to what degree it hides the database well as we just went over the sequel language is relational it has a lot of great things

that we like called like joins and unions and derive tables and all that other good stuff and the ability to use those things and to refer to them and as Brandon said to ask just one question of the database that was really terrific is kind of the primary feature of relational databases we really want to get at that stuff if we’re using an ORM that is hiding it it means you don’t have first-class access to those features which means the Orang might be using some of those features behind the scenes but it’s assuming that we don’t want to deal with it ourselves it’s gonna do it for us it’s kind of kind of guess so that’s why we have kind of second-class access with some some approaches so in that scenario the relational database is is you know underused or misused and I think this is where we get the term the object-relational impedance mismatch from when you read articles like that they’re talking about ORM s that are trying to make too many decisions for us so we don’t really want to hide things what we want the Olin to do for us is automate the work of dealing with the database so we’re best off when we design and control the schema in query side as well as how our object model interacts with it and we still need tools to automate this process so I’m gonna within the scope of this talk I’m going to say the concept of explicit decisions plus automation by tools is a quote/unquote hand coded kind of a buzzword so sequel alchemy in the the hand code approach now we’re going to go into what that is so hand coded what does that mean and here’s a little Twitter stream from Doug Hellman where he was like well what does that mean you know how how else did you make an application other than by hand so you know I googled around to find some of the tools that and some of us use when we used to work at banks and stuff you know this is an application called Oracle application Express which is where you get a wizard interface to something you get like these pie charts you can kind of select what you want you open up your cell spreadsheet and you cut and paste some data it’s basically just the Wizards gonna do it for us you push a button and this is formas creare from a mythical harry potter so it’s just magic it’s a wizard you know he just did his thing and then it’s web-scale so these are slides from an Oracle now actually Oracle application application Express is probably not a bad idea because it’s designed for executives who would otherwise be emailing spreadsheets to each other so it’s not terrible but they’re a little disingenuous when they say that their beauty of building your own web app but it’s not really quite the same thing so you know that’s a silly example because that’s really not at all we do in Python none of us use tools like that but that’s the that’s an extreme when we use tools that make schema design decisions for us they conceal the database and relational geometry and give us a pass on having to have having to obey acids some of the the letters within the acronym there you know it’s it’s I consider to be kind of a step in that direction you know we give up some control of architecture of our application to a third party so the hand coded approach means that we’re going to make decisions about everything we’re going to implement and automate those decisions with tools and we’re gonna retain control over the architecture of things hand coder does the opposite of apps written by Wizards you know that’s obvious it’s kind of the opposite of apps that rely very heavily on plugins meaning if you’re the way you write a program is you go and get you know six different plugins and string them all together and then you put them into a package and there’s your app you know that’s kind of not really Han code you’re kind of letting third parties design what you’re doing which is not a bad thing but you should just know that that’s what you’re doing it’s not it’s not the only approach you could be taking and then using AP is that make implementation decisions which is more subtle and that falls into the realm of trade-offs sometimes we do have to use api’s that make implementation decisions sometimes we should say you know the safety eye is really not worth it for what it’s going to do to us so hand coded doesn’t mean that we don’t use libraries we we use those as much as possible it doesn’t mean we don’t use frameworks because they’re great if they make things harder and it doesn’t mean that we don’t use defaults although what I’m going to show here is in cycle alchemy we kind of define our own defaults as far as how schemas are designed using self established conventions I’ll show an example of that in a minute so what are some examples of implementation decisions made by api’s and hiding so the first example I’ll show is the polymorphic Association pattern which is something that I first saw rails doing I think a lot of PHP ORM stew it and Django also does it and the next if I can get through it all will be querying approaches that oversimplify relational geometry so for polymorphic Association and actually for the second example we’re going to start with this kind of myth this this is not equality this is just any old object relational tool we’re gonna do a financial application where we have an attorney representing a bank account so if you have a checking account in a savings account you’d have two bank account records and a portfolio asset which is like you own 100 shares of Apple you’d have a portfolio asset if you own some shares of Google that’d be another

portfolio asset so that’s kind of two energies we’re gonna start with and they have you know a string name of the person who owns it which is probably not how we would do this in real life but just for the sake of example and they also have a like a symbol to identify which sock you own and an identifier which is a your bank account number so now we want to use some mythical library called magic library to add a collection to each of those objects called – an object called the financial transaction so this is what the polymorphic Association pattern is usually giving us it’s the ability to take a single class and associated with more than one kind of parent what financial transaction will do here it’s going to store how much money we have in these accounts and assets using an accounting model where you store the amount of money based on individual credits and debits so if I have three financial transactions one for $50.00 one for a hundred and one for negative 25 I’d have 125 in my account if I do the math right in front of all these people so that’s what that looks like so you says you know you have a bank account you add some transactions to it it’s got a time stamp and an amount and portfolio asset turns it so that’s what that looks like and then the generic reference behind the scenes without is really telling its who did this thing for us it did all the work for us you created a scheme like this and this actually is a pretty close to what Django is doing and rails does a similar thing we have our bank account and portfolio asset tables and they then contain a collection of financials financial transactions which are rolled up into one table which is not necessarily a bad thing there’s ways to do that for each row and financial transaction we have this column called content type ID which represents a discriminator so for each row the content type of ID will refer to one row or another in the other table over there called magic content type which will tell us for each row in financial transaction if we’re in the portfolio asset table or for in the bank account table so that’s a discriminator which is not necessarily a bad thing the discriminator for the magic library is actually storing the the module name and the class name of the bank account or portfolio asset class because that’s how it felt like doing things which you know is one way to do it and then for financial transaction has another column called object ID which is an integer that might be an entry within portfolio asset or it might be an entry within bank account and we don’t know which one points to unless we look at the discriminator and so in relational databases we have this concept of a foreign key which is an integer column that refers to the primary key of another table and the database will constrain the values within the foreign key to be only those of the primary keys that are available and that’s part of how the relational database maintains consistency which is the C and acid when you do this pattern they sometimes call this a generic foreign key the database is not able to constrain object ID it you could actually have rows in financial transaction that don’t point to anything which means if you’re going to do a report of how much money we have in our company you could get the wrong answer you could get its amoenus for in my view it’s good score it’s a corrupted database so it’s actually up to and and the usage of the model would look like this so magic content side gets these two rows one for bank account one for portfolio asset financial transaction gets these rows where the object ID points to one of the other and that’s what it looks like that’s what magic library is doing for us so the inclusive design sit-ins here as I started to talk about anyway it added this magic table to our schema which you know it that’s fine we don’t care I mean it’s adding a table it might not conform to the the conventions that our DBAs want us to use it’s just kind of this extra table the magic table is storing Python source code within it is storing the actual module and class name in there which means if we want to change our application structure with the change the data magic library gives us some command called update the database which takes care of it but still let’s it seems like why do we do we really need to have that going on the storage of transaction records run one model at the table which might be what we want but it’s a decision that was made for us we might want the bank account rows to be in a separate table than the portfolio assets if the portfolio assets table had millions of records and the bank account table had had thousands we’re using PostgreSQL it could make very different decisions about optimization based on if we’re joining to the bank accounts or the portfolio the portfolio assets so it you know it’s not you can use one table that’s fine also but the point is that the magic elaborate didn’t give us a choice and didn’t even ask us and then the biggest concern is the schema design here is not constrained abble so the application layer here is responsible for maintaining consistency and not the the database so sequel Alchemy’s approach on the other hand is to encourage us to specify how tables are designed in the haptic glasses explicitly we’re gonna use regular Python programming techniques to establish composable patterns and this approach will express our exact design fully and as I’ll illustrate can ill if we do it correctly can eliminate

boilerplate at the same time so this is a typical see quality model if you look at our tutorial we have you defined a class and the class has to create pretty much specify everything about the table that we need to know it doesn’t make any assumptions about the names of tables it doesn’t make assumptions about constraints or primary keys we have to tell it that the these two classes have a primary key called ID which is an integer it makes no assumption so let’s try to see if we can kind of trim this down and remove some of the redundancy what you can do with see quality declarative is you can you make this class called the base which is where your classes all extend from you can customize how the base works you can make what we’re doing here is is a base that will that are declarative base will derive from and we’re using a decorator called declared a true which is from the declarative extension it allows us to to define a class level method on a class that will act as one of the declared attributes such as table name so with this this is establishing a convention for all of our classes that they will have by default this is where we are making our own defaults a table name that’s derived from the name of the class basically converting from the camel case to words with underscores here using a regular expression and this only happens at module start of time this isn’t evaluated this is just when you first run your app those table names will be evaluated and that’s the only time it happens and then we’re going to give all of our mapped objects a single primary key column called ID which is an integer so then we make the declarative base and so we won’t have to do that anymore and then we’ll also make some patterns for classes having an owner like someone owns this account we’ll start out with a string column and we can later change this house owner mixed in to refer to another table and we’ll also remove the boilerplate of having to create columns that are unique and not knowable for like your bank account ID so with those conventions that we just create once we’ve put them in a util somewhere in our in our application and we never look at them again if we don’t have to we can now make that same model we saw before with just four lines of code so we so what we’re doing here is we’ve made our own defaults the ORM didn’t give us defaults but that didn’t mean we had to type out the table name every time we made our own defaults and we did that so continuing on that pattern will build the polymorphic Association using a convention called has transaction so where that will begin is we’re gonna make a class that will represent how we stored the transactions and and also maybe transaction was a poor choice here it’s a financial transaction not a database transaction so it has an amount of money and a timestamp so this is actually not a mapped object this is going to be kind of a a template for future classes that will be mapped to tables and then the one part that’s gonna be a little more dense here is this slide here this is the most dense code while later we’ll have some more but this this one this actually defines the polymorphic Association and this is the whole thing so you know using the magic library is fine but really and and this is just the only thing you really have to do to create to create the same functionality that the magic library did for us or similar functionality we’re defining a mixing called has transactions it has a declared attribute called transactions which is going to return for us a relationship to a new maps class called transaction and transaction is a class that we created anonymously using pythons type function so we have type and then we have you know we give it a name and then we give it a table name based on our convention and then we give it a parent ID which is a foreign key back to the parent object so then we apply that has transactions to the model and then we get bank account and portfolio asset okay rudimental usage is similar and the schema looks like this where we’re gonna have just bank account and portfolio asset and we just have these two tables bank account transaction and portfolio asset transaction we’re no longer doing the discriminator table we have kind of a cleaner design here so a summary is we’ve composed has transactions using a well understood recipe we’ve used our preferred naming and structural conventions we’ve used we’re using constraints and traditional normalized design properly and the data is in separate tables per parent and other schemes are possible as well and the data is not hard-coded to the application structure or source code so why don’t we improve the generic reference system the magic library to support these practices well generic reference would need to present various modes of behavior in the form of various options and flags you know it might lead to a more complicated configurational story and I would say that the polymorphic Association is actually not that hard to

do by ourselves we can just you know I consider to be kind of a trivial pattern it’s kind of self documenting and it’s actually easier just to have polymeric association on our own instead of kind of bringing in a third-party library that might have a complicated configurational story so that’s the polymorphic Association then it’s kind of a sequel Alchemy’s take on that kind of thing and then the second example is exposing relational geometry so what we’re going to talk about here is how we do queries with the ORM so take a mythological not really word take some hypothetical ORM and say we have some bank account and we want to get the balance of an account as of a certain date so we might say hey son bank account give me your transactions collection give me the sum of the amounts and the sum of the amounts will give us the balance for an account and then we’re going to filter those amounts to be those that are less than some particular date and that will give us the balance of an account as of a certain date and the obvious sequel is below you know select from bank account transactions and filter on those two criterion that’s pretty simple so any ORM you know the high the sequel system usually applied in aggregate field to a single field on a related collection with a simple filter it’s not very mysterious so then then what sequel alchemy will say well you know in the real world you probably will have more complicated queries than that that don’t map to the hide the sequel system so well we’re really going to want to get at relational geometry explicitly so suppose I want a report of the average balance per month across all accounts so with that one because our model is storing the the financial transactions as individual amounts we need some way to roll them up into balances and we need a way to roll them up into balances such that we can really distinguish among temporal data we need to group them into balanced per month basically so we probably need to use sub queries or window functions to produce the balances of some of the mouse this is assuming we’re trying to ask the database just one question as Brennan said in sequel we would build a query like this incrementally we’d make smaller parts of it first that represent a more engineer a general concept of data you know like a larger span of data but simpler and then we can kind of join them together to get more specifically the information that we’re looking for and if we can’t do that we have to bypass our tools doing this this is where we would either write a sequel statement as a string and just spin it out or we would not even bother asking the database one question we would go and just load all the bank accounts in and just sum all the amounts by hand which would work fine for about a month until a year later when your bank account table has grown to like a million rows and now it doesn’t scale anymore so you know we really do want to try to do this on the database side so the sequel Alchemy’s query model will explicitly it does explicitly expose the UM the geometry of the underlying relational structures that we’ve designed and it’s kind of like power steering for sequel doesn’t teach you how to drive you’re gonna steer the car but it’ll just kind of help you with that the developer should be aware of the sequel being emitted we have the echo flag which will show you everything that’s happening it’s that I mean one of these main focuses is being very very transparent about what it’s doing it’s just like your car has windows to see where you’re going so we’ll do this query in sequel first I’m going to show off what a window function looks like in a second so we’re gonna start with a query that’s going to extract all of these start and end dates of each month in the bank account transaction table so we’re gonna go through all the transact all the the financial transactions in the bank account transaction table we’re going to extract the year and the month from each timestamp and then we’re gonna make groups we’re gonna group on the month so we’re gonna have these groups of rows within these month buckets and then within each per month bucket we’re gonna get the lowest and the highest time stamp within that bucket so that we have we can it’s just extract from the database where the month starting be where the month start and ends relative to our data now if we could also do a query that just gave us the first date and the end date of every month that would work as well but that would mean we’d have to use some kind of calendar arithmetic to know that February 29th or 28th and March 1st through March 31st this way we just use the data that’s already there to give us the ranges of timestamps that we actually would care about which is are the ones that are actually there so this is one half of a two-part puzzle the data we get from that query looks like this so in March of 2009 the first transaction that month was on March 8th and the last one was on March 28th April 2009 we went from April 5th

to April 30th so that’s what that looks like that’s so that’s one half of the data we’re gonna work with the other half of the data we’re going to use something that I’ve been using more lately called a window function now a window function gives us this handy way to work with sequel aggregates but in such a way that we don’t have to group everything into one row and then just give you an aggregate for that group it’ll give you an aggregate for every row as it goes through the results so we’re gonna get the the idea of a each bank account and the timestamp of each financial financial transaction and the amount of each trend financial transaction and then we’re going to sum them over the partitioning of the bank account ID and order or the temp so what I’m skipping but what this means is that as we select rows the database will first partition them into groups based on on bank account ID so each Bank Kennedy will have its own list of financial transactions they’re gonna be ordered by timestamp within each group and then it’s going to sum give us the sum of the amount within each of those groups and it’ll give us kind of a running total for each bank account so this is what that data looks like so for bank account ID number one those timestamps are individual debits or credits to that bank account and those are the amounts seven thousand nine 20 25 146 and then on the right we have the sum as a running total as it goes and then you have bank account ID number two and three so what this query is giving us is every possible balance for every bank account that has ever been in the system so is every balance for every timestamp that the balance was unique so now we have these two sets of data we have ranges of all the months and then we have all the balances that have ever existed and then we can join them together where I’ve created this novel color coding system to try to make it quicker to see what’s happening is that the blue stuff the blue parts of the query are from our ranges of months and the red parts are from our our collection of every possible bank account balance there ever was and then we join them together at the bottom where you can see the balance the time stamp of each of each balance row is between the min and the max of the month ranges so this is a pretty fancy query and then we grew up a year a month and order to make it look better and then we get light data like that so that’s the average balance of per month within within our bank now that was kind of a weird query but you know I don’t think the example is too exact because it’s pretty common that accounting data is stored as credits and debits and it’s pretty common you’re going to want to have reports of balances so I don’t think it’s that crazy of a query so with sequel alchemy we’re gonna build this query in a similar way we’re going to think in terms of simpler parts and compose them together it provides the same inside out paradigm as sequel itself you think in terms of relations and joins the same way as when constructing plain plain sequel your brain is going to work the same way and then see quality because we’re staying within the ORM we can do all the other cool stuff that the Orang gives us like further you know generations upon that query limiting and and if you had objects being loaded you can either loading all the good stuff is seek walk me and other arms can do but we could stay so we’re basically staying in the object model and in the relational space at the same time both are exposed simultaneously which is really what seacock me the choice that we made early on so we’ll make the same query and if you know sequel alchemy syntax this kind of lays out the same way so we have first the month ranges sub query so we have the min of the timestamp and the max of the time stamp and we extract the year in the month and we group by the year in the month a it’s basically the same thing and we have the all balances and time stamps window function we’ve added window functions as of maybe Oh seven three oh seven four it’s just basically you say func sum of the amount and then you say over and you give it a partition by what you want to petition by and you order by what you want to order by and then we make a sub-query out of that and then we have these two units and then we compose them together so select average balance per month is the query of the year and the month of the month range is sub-query and the average of the balance from the all balances and time stamps sub-query select from the month range is selectable you know derive table join to the all balances and time stamps derive table and then we’re going to we’re going to do the join on the time stamp between the min and max of the month ranges we can group by year a month and we can order by year and month and we get the same result and then the sequel that it generates is more verbose but i’ve applied the same color coding to it

so you could see that geometrically it’s the same it’s got some more you know tsubaki’s going to layout each column with a label name if there’s a lot of other things that help it in its job of relating rows back to objects but it’s basically the same thing and the idea here is that we didn’t have to change how our brain works when we move from reading a straight query to working with sequel alchemy you know we did the exact same kind of thinking so once you know sequel can use query syntax you can really continue to think in terms of sets which is really what you should be doing when you work with the relational database so summary of good and what’s interesting here is that i when i time this this talk i was always right 45 minutes on the dot and now I’m like way ahead which is really weird which means I probably went too fast I’ll do the whole talk again it means I you know I right in the middle I was like oh boy I’m not explaining as well and I just went right through it so if maybe we can go back if some people had questions about some of the stuff I obviously went through too fast summary of hand coded the is retaining control over the relational form of the target data you know you’re gonna keep keeping control of what you’re doing I mean it means that when you’re given a problem to solve and you’re given the data that you’re dealing with you’re gonna actually design your schema you’re not going to piece together tools that are going to make the decisions for you so schema design decisions are made by the developer the tools are there to help you but they should not be make decisions see quality is then going to provide a rich and detailed vocabulary to express and automate these these decisions and then the developer will create patterns and conventions basically vocabulary we did the thing where we had the house owner convention and the unique ID and the base class with the table name and an ID and then the the relational geometry remains an explicit concept complementing complementing the object model which means as we work with C Qualicum II you know it’s not concealing anything except just some of the boilerplate of db-api and sequel rendering but this the geometry of relational algebra is always there for us in a very one-to-one way so some people will say this is what you call leaky abstraction meaning that you’ve had this abstraction layer but it’s leaking meaning there’s concepts from the lower level being exposed and this kind of speaks to the concept of abstraction meaning hiding and this term refers to what I just said so does sequel Agony’s schema design paradigm in query object exhibit this behavior for now I would just say you bet because it’s not really totally concerning to me I think the model we have works which is that you’re kind of doing the O and the AR and ORM at the same time and before Brandon Rhodes also had this term called the Oh problem which I just thought was awesome and I’m gonna totally take that on the O problem which is when you’re only looking at the ORM in terms of oh and not the are so it’s great so if you want to call that leaky abstraction you know I can’t really I can’t really help that part of it I just have this quote from Joel on software that all non-trivial abstractions to some degree are leaky but really the answer is I I can’t really care too much because I know that the approach works really well and a lot of other people like it so it’s leaky abstraction if you want to call it that sequel alchemy except this reality upfront to create the best balance possible and then we’ll have a little bit of a graphical display which is suppose we had this is this is norm from the new Yankee Workshop people know that show it used to be on PBS I used to like it because I used to like this before I was even doing Python I just liked watching a guy who really knew what he was doing and the things he didn’t know he was gonna learn he was really a master at his craft you know so this is software is craftsmanship right and here he is building like a like a nightstand there’s an end table or something and then the other side and this is a little bit Rolly sorry is uh you know doesn’t have a third party you know I couldn’t resist you know don’t don’t take this just kind of a joke haha you know you know but it’s like you know IKEA what you know it’s like modular furniture and it’s like mixing masks it’s actually fine I own that much IKEA furniture but like it’s like if some if you really want to build something amazing you know I would think you’d kind of want to do norms approach if you’re trying to build something amazing if you’re just trying to do something quickly and get it out the door fine but you know if you build software with what I’m calling the hand cutter approach you know you’re building much more specifically what you want accurately targeted and long lasting

designs that will resist technical debt and you know technical debt is means that you’ve made some trade offs as your design and you’ve accumulated them and then a year later you know you have this part of your app that doesn’t scale because you really didn’t write it in the way you wanted you accepted trade offs by in the name of using third-party systems that kind of did things are not the best way that you would want yourself so it’s about being specific but at the same time providing a way that it’s still not tedious you still can automate you still can have defaults you just kind of have to design these conventions on your own and having this part of your application it’s got a part of application design and that is the final slide yeah so what what I can do is because I went way too fast I can go back to the slide that I went too fast on the one with the polymorphic Association would that be good or do you want to have questions yeah go to that slide cuz alright cuz what happened on that slide was that I realized I was standing in front of a lot of people and I was like oh so let’s go to that slide so and this is um this is very slow alright if you download sequel alchemy we have a directory called examples where there’s a lot of examples of things and we have actually three examples of how to do polymorphic associations there’s three versions one is table per subclass one is one master table and the third one I can’t quite is some variants of that other one I forgot example it is but what we’re doing here is when you create a class that defined that includes has transactions mixin in its class hierarchy the declarative module will see this func this method called transactions and it will call it and then it’s going to evaluate what’s inside of it and then the return result will be returned as though you had just said transactions equals relationship to some class so what’s happening here is when transaction is called when you make a new class like portfolio asset declarative evaluates that method and then it will what we’re doing here is we’re actually creating a new class on the fly and what that and the class on the fly is a subclass of the transaction base which is this guy here and also our declarative base which is the base of all of our classes and you can see that where it’s calling type it has Transat uppal of transaction base and base those are the base classes so there’s basically a class that is inheriting two other classes and then we give the class a name which is based and also when this transactions method is called the CLS the class variable there is actually going to be the parent class that we’re building so it would be the bank account class or the portfolio asset class so that class comes in it’s called bank account this will create a new class called bank account transaction and then within the the new class you have this dictionary which is the class dictionary and it’ll define for it a table name that’s derived from the bank account table and it’ll give it a column that’s a foreign key back to the parent so this is basically it’s a little dense but the idea is you just do this once for your certain patterns and I have to do this all over the place for different kinds of pad you don’t have to deal with this on a daily basis this is just like a thing you do once up front for certain kinds of patterns you want to have and then you just stick it away and then it works and everyone uses it and if someone doesn’t like what it’s doing or you want to change it you just open up the source code it’s like you know fifteen lines I don’t think it’s that terrible and you see what it’s doing it’s trance it’s transparent and then at the bottom we return a relationship with this new class and we’ve also it’s attached the class to the parents so we have this thing called bank account dot transaction which is the map’s the table that’s the class that’s mapped to that table and in sequel alchemy in the examples there’s other variants of this there’s variants that will use one table for all the polymer of exfoliation similar to what rails does but it has an extra table to add a foreign key constraint so that’s what that is and I also have blog posts that show this and there’s a lot of background on this on this pattern but so that’s that’s a more advanced pattern but you know the important takeaway is at least do thing at least do things like this where you have you know common patterns are defined up front you know because it makes your code cleaner and more succinct and more specific to what you’re doing so that’s that’s that part so I guess now we can do the questions well if anybody has any questions please

step that’s a mitral yeah doesn’t make you son so your hand-coded approach assumes you have control over the schema yeah how does this apply when you don’t well it doesn’t a profit that’s a great question which I didn’t consider in this case in that case it might not apply as well because you’re gonna have to deal with the schema that you were given and that’s probably the flip side to the whole question that sequel of means again exposing the schema and letting you do exactly what you want so at least at the code level you can do exactly what you want and you don’t have to worry about third-party libraries that don’t know how to interact with that schema for a long time in the beginning there’s kind of this meme that sequel alchemy was for legacy schemas which you can imagine I didn’t like to hear that because I would try to say well it happens to work for legacy schemas but it also works for the schema that you exactly want to design so I would say well they worked for Legacy scheme is that it works also for your schema which is the one you want to build so the answer is you know it would be a half hand-coded approach where at least well the hand coded approach I’ve used with some very extreme cases yeah 14 or database tables with horrible composite keys across ten columns yeah is to build select statements and that’s the hand coded approach there right is to work with disliked in the sequel expression API switch that’s the only way I could have shift is thanks to seek welcome in sequel expression language right so a follow-up question and I’m sorry of the you guys behind me but this mixin pattern is really great and do you have access control access control examples with like an example of a mixin that does kind of it right like actually be protected access type of things that kind of thing I’m not sure if I have an example offhand there might have been people that have asked about that you there might be some things I came up with on the mailing list to do that you know it’s Python you know that’s kind of how I see it with Python you can you can use get attribute if you really want to put some central control so you I don’t have any I don’t have a specific example because I tend to use that too much but I mean sometimes I’ll use a custom validates if there’s some attribute that I really want to make sure no one’s doing the wrong thing I’ll put a validate decorator on it that’s in the doc so it’s kind of like there’s not a simple answer there’s like it depends on what you’re doing it’s that’s that is also hand coded that like there’s no simple answer well what’s your actionable use case that’s yeah who’s that so how do you do with schema changes when you have live data well it’s just schema changes in general right so like the example be like let’s say some reason that want to change instead of a table being make a transaction that would be called bake count trance change name the table you need sure yeah well okay so I mean from the point of view of the code you’d want to change your conventions and then from the point of view of your database you want to use a migrations tool early on I didn’t really get into migrations too much but I used seek alchemy migrate is your question about migrations basically or is that or you think more about since like I have like hundreds of gigs of live data and I want to it’s a live product if we’re using all the time yeah well if your scheme is changing you yeah we these days we use migrations tools so there’s a tool that I have called Alembic which does schema migrations and then there’s also seek alchemy migrate which is the the old-school package and it can detect I think see Papa migrate does it stupid Alembic does this as well it can detect what you’ve changed in your model versus what the schema looks like and then and it will generate for you a set of candidate upgrades like hey I think you want to change your database to look like this as you want and you get some code there and then you basically run that script or correct it is that what you’re asking her I think and I see more about like what’s the application code looked like during the migration it’s like let’s say the migration takes a week to run well alright so I well I think the model is you you change your applique or running and development first so you change your application to the way you want to look so assume that the database has already been migrated and then you create your migrations files that will do the database side of it and then you you run that side and then that catches up to where your model is that means you don’t have code that can talk to both schemas at the same time and that’s there’s really no way to avoid that well wouldn’t know that’s not totally true if you’re adding columns then it’s fine your your old app your app can talk to the your old code I’m getting confused but you know yeah generally the code goes first then the database is migrated second maybe we talk more later okay yeah can you talk a little bit about maybe some cases where see Gwathmey wouldn’t fit as well if such a thing exists or maybe only the lower where you wouldn’t you seek welcoming well obviously if you’re not using relational you wouldn’t be using it I think people that come to sequel alchemy

III think from the point of view of learning I think maybe you don’t want to use abstraction layers when you first are learning relational databases maybe you want to just you do it direct sometimes I’ve kind of toyed with this if you’re really doing a one-off and I’ve written someone off and I said hey let me not use C glog me for this one off and I find myself reinventing half C clock me anyway when that happens you know so I mean I kind of designed it that you would be able it has various levels of usage so that it really is pretty flexible so I don’t think I have a great answer I think if you if you if you find the ORM to beat I mean we have these two levels called the core and the or and the ORM is about mapping a domain model and the core is just about a DSL for sequel so if you’re writing your application in the none or object oriented fashion you probably would just use the core so you would use not the or in part so I would say that or if you’re writing a custom persistence layer that is really not at all like the ORM model you rent you might write a different kind of thing that uses just the core like reddit built their website using just the core so you know it’s it’s it’s a question of degrees kind of black and white hi I wonder can you expand when sicko sicko expresses it’s good for I mean what universe scenario we should consider you the question is sequel expressions sickly sir well you know what scenarios we should you consider using sicko you mean they’re using the core without the ORM uh-huh yeah yeah well originally the idea was he called me was that the ORM dealt with mapping classes there’s some history here the original erm dealt with mapping classes to tables and then the querying was done mostly in terms of the the core sequel expression so that allowed us early on when we didn’t do much at the Orang level to do everything that we could because the core Maps very closely to exactly how sequel looks so originally the model is that you’ve used the core expression language with the ORM and then as the years went by we kind of refine that so that the query this equality query object which we don’t even have any early versions could in fact do most of what the core does so if you’re using the ORM you probably don’t need to use the core anymore than to the degree that the ORM needs you to use it like when you use the ORM you could say filter you can use the and conjunction or the or conjunction with ORM concepts and that’s the same or an and that you have from the core so that it’s kind of this the core kind of blurs into the ORM so I’ve every question you guys I have like this kind of loosey-goosey blurry answer you know also when you use the query if you create a sub query out of a query that’s now a core select so this kind of just places where the ORM kind of naturally goes drops down to core so it kind of is as the use case calls for and if you’re not using the RM then you use the core completely so that’s kind of the best answer I can come up with yeah hi so in cases where you’ve got the parent-child relationship like you have with the transactions and the account with other ORMs that have experienced when you do a query to get a bunch of a child and you referenced the parent from the child each parent is an individual object as opposed to having one object for that parent of all the the children they’re like individual memory multiple parents right well so like one bank account transactions you do a video query and you get all the trends of the transactions and you go from a transaction object to the parent yeah if you reference the each parent individually from the child it’s actually a separate object in memory no it’s just no okay so that’s it’s the same object she called me when you work within as a session which I didn’t really describe too much here uses this pattern called the identity map which means that if you have a bank account with ID number five that object is given what’s called an identity key which consists of the bank account class and the number five and that’s a that’s the if that’s the only instance of bank account ID number five that can exist within that session so if you load tench financial transaction objects and you say dot parent it will every time it loads any identity from the database any row that comes in with an ID number five for the bank account table it will go into the identity map to see if it’s there first that’s so you get the same and that’s and that’s really key to the whole unit of work pattern that’s cool yeah other arms didn’t do that and it’s blown out of memory right well we were talking at the the Web Summit up to the starting to Jacob captain muscle a bit we kind of talked about possible sequel alchemy Jango some things we don’t know but he actually talked to me about it so I was

pleased I’m sorry we run out of time it’s not shower so I have to cut this great