EdgeDB & Python @ PyBay 2019

Transcript

1. EdgeDB & Python Yury Selivanov for PyBay 2019
2. ABOUT ME Python Core Developer for ~7 years. async/await, asyncio, and a few PEPs. uvloop & asyncpg. Co-founder of EdgeDB. @1st1
3. DATABASE TYPES Relational: PostgreSQL, MySQL, Oracle. Document: MongoDB. Graph: neo4j. Key-value: Redis. Also: columnar, time series, search, ... RDBMS Other
4. Simple math foundation. Powerful query language: SQL. RELATIONAL DATABASES Strongly typed. Fast.
5. PROBLEM 1970: Relational databases are introduced. 2019: Productivity-wise relational databases are still in the 70s.
6. PROBLEM Developers do not "think in tables". ORMs — opaque abstraction. SQL is a hard language to master.
7. ORMs Huge API surface to learn. Language specific. Can generate extremely inefficient SQL queries.
8. IDEA Python made writing software 10x faster. What if we could make a database that offers a similar boost in productivity?
9. EdgeDB A new relational database. Focus: make developers productive. Based on PostgreSQL. Novel relational data model & query language. Open source!
10. Rich & strict type system. Maps naturally to Python. EdgeDB: Schema No need for an ORM. Support for indexes, constraints, JSON, user-defined functions, etc. type Person { required property name -> str; required property bio -> str; } type Movie { required property title -> str; required property year -> int16; required property desc -> str; multi link cast -> Person; multi link directors -> Person; }
11. Easy to learn. Strictly typed & functional. EdgeDB: EdgeQL Fetch deep data hierarchies. Aims to be more powerful than SQL. SELECT
 Movie {
 title, desc,
 
 cast: { 
 name,
 bio
 } ORDER BY .name,
 } 
 FILTER
 Movie.year IN {2018, 2019};
12. SELECT
 Movie {
 title, year, 
 cast: { 
 name
 }
 } FILTER
 .year = 2019; SELECT Movie.title, Movie.year, array_agg((SELECT Person.name FROM Person INNER JOIN Movie_Cast ON Person.id = Movie_Cast.person_id WHERE MovieCast.movie_id = movie.id )) FROM Movie WHERE Movie.year = 2019; EdgeQL SQL
13. SELECT Movie.title, Movie.year, array_agg((SELECT Person.name FROM Person INNER JOIN Movie_Cast ON Person.id = Movie_Cast.person_id WHERE MovieCast.movie_id = movie.id )) FROM Movie WHERE EXISTS( (SELECT FROM Movie_Cast INNER JOIN Person ON Movie_Cast.person_id = Person.id WHERE Movie_Cast.movie_id = Movie.id AND Person.name = 'Tom Hardy' ) ); SELECT
 Movie {
 title, year,
 cast: { 
 name
 }
 } FILTER
 .cast.name = 'Tom Hardy'; EdgeQL SQL
14. SELECT
 Movie {
 title, year,
 cast: { 
 name
 }, NR := count(.reviews)
 } FILTER
 .cast.name = 'Tom Hardy'; EdgeQL SELECT count( (SELECT
 Movie {
 title, year,
 cast: { 
 name
 }, NR := count(.reviews)
 } FILTER
 .cast.name = 'Tom Hardy') ); SELECT math::mean( (SELECT
 Movie {
 title, year,
 cast: { 
 name
 }, NR := count(.reviews)
 } FILTER
 .cast.name = 'Tom Hardy' ).NR );
15. WITH MovieReviewCount := Movie { number_of_reviews := count(.reviews) }, TomHardyMovies := ( SELECT MovieReviewCount FILTER .cast.name = 'Tom Hardy' ), ZoeSaldanaMovies := ( SELECT MovieReviewCount FILTER .cast.name = 'Zoe Saldana' ) SELECT ( tom_hardy_avg := math::mean(TomHardyMovies.number_of_reviews), zoe_saldana_avg := math::mean(ZoeSaldanaMovies.number_of_reviews), ); EdgeQL # Task: # # Compute average rating of # movies starring Tom Hardy # and Zoe Saldana.
16. SQL ON (Movie.id = Review.movie_id) GROUP BY Movie.id ), TomHardyMovies AS ( SELECT movie_id, count FROM MovieReviewCount WHERE EXISTS ( SELECT FROM Movie_Cast INNER JOIN Person ON (Movie_Cast.person_id = Person.id) WHERE MovieReviewCount.movie_id = Movie_Cast.movie_id AND Person.name = 'Tom Hardy' ) ), ZoeSaldanaMovies AS ( SELECT movie_id, count FROM MovieReviewCount WHERE EXISTS ( SELECT FROM Movie_Cast INNER JOIN Person ON (Movie_Cast.person_id = Person.id)
17. First class Python support. Supports both sync & async. EdgeDB: Python Same EdgeQL query can return results as Python objects or as JSON. movie = conn.fetchone( 'SELECT Movie {title}') print(movie.title) # or movie_json = conn.fetchone_json( 'SELECT Movie {title}') print(movie_json)
18. Let's build an app! Person last_name first_name bio image Movie title year description image directors cast Review movie body rating author User name image
19. Let's build an app! movies movies movies Movie Page Cast, directors, details, reviews. Actor Page Movies & reviews, bio. User Page Last 10 reviews.
20. Django directors = [rel.person for rel in movie.directors_rel. select_related('person')] cast = [rel.person for rel in movie.cast_rel. select_related('person')] result = { 'id': movie.id, 'image': movie.image, 'title': movie.title, 'year': movie.year, 'description': movie.description, 'directors': [{ 'id': person.id, 'full_name': person.get_full_name(), 'image': person.image, } for person in directors], # 20 more lines } return json.dumps(result)
21. SQLAlchemy baked_query = bakery(lambda sess: (sess.query(m.Movie) .options( orm.subqueryload(m.Movie.directors_rel) .joinedload(m.Directors.person_rel, innerjoin=True), orm.subqueryload(m.Movie.cast_rel) .joinedload(m.Cast.person_rel, innerjoin=True), ) ) ) baked_query += lambda q: q.filter_by(id=sa.bindparam('id')) movie = baked_query(sess).params(id=id).first() directors = [rel.person_rel for rel in sorted(movie.directors_rel, key=sort_key)] # 20 more lines
22. EdgeDB return conn.fetchone_json(''' SELECT Movie { id, image, title, avg_rating, directors: { id, full_name, image, } cast: { id, full_name, image, } ), } FILTER .id = <uuid>$id ''', id=id) return await conn.fetchone_json(''' SELECT Movie { id, image, title, avg_rating, directors: { id, full_name, image, } cast: { id, full_name, image, } ), } FILTER .id = <uuid>$id ''', id=id) OR
23. Benchmarks
24. EdgeDB First-class support for migrations. Soon: advanced analytics. GraphQL! Soon: Access Control policies at the schema level.
25. FUN FACT EdgeDB is written in Python 3.7. Uses asyncio & uvloop. You should contribute!
26. EdgeDB Read more at edgedb.com/blog. Star github.com/edgedb/edgedb. Follow @edgedatabase. Thank you! Questions?