Let's Build an ORM - PyOhio 2019

Applications rely on data, and relational databases are a convenient way to organize structured information. Object-relational mappers like SQLAlchemy and Django’s ORM are complex libraries, but they aren’t black magic. De-mystify some of the magic as we build the basics of an ORM in under an hour.

Transcript

1. Let's Build an ORM! Greg Back PyOhio 2019
2. $ whoami Sr Software Architect at /gtback @gtback
3. Object Relational Mapper
4. Object Relational Mapper
5. Object Relational Mapper
6. Object Relational Mapper
7. https://www.jetbrains.com/research/python-developers-survey-2018/ JetBrains Python Developers Survey 2018 Results
8. (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
9. 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
10. 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
11. 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
12. 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
13. 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
14. Let's Do This...