Let's Build an ORM! Greg Back PyOhio 2019

$ whoami Sr Software Architect at /gtback @gtback

Object Relational Mapper

Object Relational Mapper

Object Relational Mapper

Object Relational Mapper

https://www.jetbrains.com/research/python-developers-survey-2018/ JetBrains Python Developers Survey 2018 Results

(Potential) Responsibilities of an ORM ● create database ● define tables ● define fields ○ size/length ○ nullable ○ default values ● define foreign keys ● define indices ● define relationships ○ one-to-many ○ many-to-many ○ one-to-one ○ inverse relationships ● define stored procedures ● class inheritence ● caching ● transactions ○ commit ○ rollback ○ nested transactions ● support sqlite ● support MySQL ● support PostgreSQL ● support Oracle ● support MS SQL Server ● prevent SQL injection ● remain performant ● support multiple clients ● use multiple databases ● error handling ● logging ● create tables ● insert data ○ convert data types ○ validate data ● retrieve data ○ support joins ○ order results ○ subqueries ○ nested queries ○ lazy loading ○ eager loading ● update data ○ by ID ○ using filters ● delete data ○ by ID ○ using filters ● cascading operations ● bulk operations

Responsibilities of our ORM ● create database ● define tables ● define fields ○ size/length ○ nullable ○ default values ● define foreign keys ● define indices ● define relationships ○ one-to-many ○ many-to-many ○ one-to-one ○ inverse relationships ● define stored procedures ● class inheritence ● caching ● transactions ○ commit ○ rollback ○ nested transactions ● support sqlite ● support MySQL ● support PostgreSQL ● support Oracle ● support MS SQL Server ● prevent SQL injection ● remain performant ● support multiple clients ● use multiple databases ● error handling ● logging ● create tables ● insert data ○ convert data types ○ validate data ● retrieve data ○ support joins ○ order results ○ subqueries ○ nested queries ○ lazy loading ○ eager loading ● update data ○ by ID ○ using filters ● delete data ○ by ID ○ using filters ● cascading operations ● bulk operations

Responsibilities of our ORM ● create database ● define tables ● define fields ○ size/length ○ nullable ○ default values ● define foreign keys ● define indices ● define relationships ○ one-to-many ○ many-to-many ○ one-to-one ○ inverse relationships ● define stored procedures ● class inheritence ● caching ● transactions ○ commit ○ rollback ○ nested transactions ● support sqlite ● support MySQL ● support PostgreSQL ● support Oracle ● support MS SQL Server ● prevent SQL injection ● remain performant ● support multiple clients ● use multiple databases ● error handling ● logging ● create tables ● insert data ○ convert data types ○ validate data ● retrieve data ○ support joins ○ order results ○ subqueries ○ nested queries ○ lazy loading ○ eager loading ● update data ○ by ID ○ using filters ● delete data ○ by ID ○ using filters ● cascading operations ● bulk operations ● create database ● define tables ● define fields

Responsibilities of our ORM ● create database ● define tables ● define fields ○ size/length ○ nullable ○ default values ● define foreign keys ● define indices ● define relationships ○ one-to-many ○ many-to-many ○ one-to-one ○ inverse relationships ● define stored procedures ● class inheritence ● caching ● transactions ○ commit ○ rollback ○ nested transactions ● support sqlite ● support MySQL ● support PostgreSQL ● support Oracle ● support MS SQL Server ● prevent SQL injection ● remain performant ● support multiple clients ● use multiple databases ● error handling ● logging ● create tables ● insert data ○ convert data types ○ validate data ● retrieve data ○ support joins ○ order results ○ subqueries ○ nested queries ○ lazy loading ○ eager loading ● update data ○ by ID ○ using filters ● delete data ○ by ID ○ using filters ● cascading operations ● bulk operations ● create database ● define tables ● define fields ● define foreign keys

Responsibilities of our ORM ● create database ● define tables ● define fields ○ size/length ○ nullable ○ default values ● define foreign keys ● define indices ● define relationships ○ one-to-many ○ many-to-many ○ one-to-one ○ inverse relationships ● define stored procedures ● class inheritence ● caching ● transactions ○ commit ○ rollback ○ nested transactions ● support sqlite ● support MySQL ● support PostgreSQL ● support Oracle ● support MS SQL Server ● prevent SQL injection ● remain performant ● support multiple clients ● use multiple databases ● error handling ● logging ● create tables ● insert data ○ convert data types ○ validate data ● retrieve data ○ support joins ○ order results ○ subqueries ○ nested queries ○ lazy loading ○ eager loading ● update data ○ by ID ○ using filters ● delete data ○ by ID ○ using filters ● cascading operations ● bulk operations ● create database ● define tables ● define fields ● define foreign keys ● create tables ● insert data ● retrieve data

Responsibilities of our ORM ● create database ● define tables ● define fields ○ size/length ○ nullable ○ default values ● define foreign keys ● define indices ● define relationships ○ one-to-many ○ many-to-many ○ one-to-one ○ inverse relationships ● define stored procedures ● class inheritence ● caching ● transactions ○ commit ○ rollback ○ nested transactions ● support sqlite ● support MySQL ● support PostgreSQL ● support Oracle ● support MS SQL Server ● prevent SQL injection ● remain performant ● support multiple clients ● use multiple databases ● error handling ● logging ● create tables ● insert data ○ convert data types ○ validate data ● retrieve data ○ support joins ○ order results ○ subqueries ○ nested queries ○ lazy loading ○ eager loading ● update data ○ by ID ○ using filters ● delete data ○ by ID ○ using filters ● cascading operations ● bulk operations ● create database ● define tables ● define fields ● define foreign keys ● create tables ● insert data ● retrieve data ● support sqlite

Let's Do This...