EdgeDB in a Fantasy Football Web App

I’m only a few months into my first software engineering role, but I’ve done a good amount of interacting with databases since 2018, when I first started in my actuarial analytics role. At the time, the company I worked for had some traditional SQL databases that most actuaries were expected to know their way around. We would use it to query for insights on the status of the insured population and transform the data to derive actuarial studies (metrics like actual vs. expected ratios of mortality and claim incidence).

A couple months back I heard of EdgeDB through an episode of talkpython.fm. Their website proclaims that the post-SQL era has arrived:

EdgeDB is an open-source database designed as a spiritual successor to SQL and the relational paradigm. It aims to solve some hard design problems that make existing databases unnecessarily onerous to use.

I’m definitely listening! The podcast episode stood out to me for two reasons:

• I know that at its age and level of pervasiveness, SQL will be a tough beast to bring down…so anyone taking a crack at it is noteworthy to me.
• The co-founder Yury conveyed a level of passion about the project that resonated with me, and called out quite a few pain points that I had also encountered in my day-to-day use of SQL.

SQL databases are certainly powerful…but when coupled with a bad design, things can get pretty wild, pretty quickly. Missing values, deceptive values, and duplicate records are all things I had encountered…this blog post on the EdgeDB website does a particularly good job of making the case against SQL’s handling of NULL values in particular.

I was wondering if EdgeDB had really come up with a better way, and one of my personal projects was due for a revamp this year, so I chose EdgeDB as the database layer and started the process of moving my data over. Here’s how it went…

Creating a Schema

I started off by installing EdgeDB locally and creating my first schema using their quick-start guide. For a fantasy football league, we need a to model few different objects:

• Seasons (represents one year of the fantasy football league)
• Teams (each team participates in exactly one season, and a season has included anywhere from 10–14 teams in our league)
• Owners (can have one team per year, so for example, players who have been around since the beginning have participated in 9 seasons and owned 9 teams)
• Games (played weekly between two teams)
• Notes (each Season can have multiple notes)

EdgeDB does a great job of making it feel natural to model the objects you’ll need in your application. Let’s take a closer look at what my EdgeDB schema looks like for the Team and Owner objects:

TODO left in for authenticity…

There’s a few things to call out here:

• One of the best superpowers that EdgeDB has is how links between objects are modeled, which we can see in the required link owner -> Owner on line 3. I no longer have to worry about matching keys between tables — EdgeDB abstracts that away! All I need to do when it comes time to insert a Team object is make sure I specify an associated Owner object that exists in the database already.
• Thinking ahead to the frontend, if I’m on the specific page of a single Team, I probably just need that Team’s owner information. The required link owner -> Owner will handle this for me. But what if I want to create a page for each Owner where all of their associated teams our listed? In this case, the backlink that’s included on the Owner object in line 21 handles this association. Now the link works both ways, so I can select a Team’s owner or an Owner’s teams depending on what I need to render.
• Computed fields are awesome. According to the EdgeDB docs, these are evaluated on-the-fly whenever you query the field. In this particular app, that means that when I add information about a new Game, each team’s win_total defined in line 23 should update automatically in our app, wherever the app is displaying that number. (This can certainly be handled on the frontend as well depending on your design, but it’s great to have the option to stick the calculation in the database.)

When converting my previous data models over to EdgeDB, I made a few changes that would take advantage of the natural relationships in the model (in other words, it pushed me to clean up my schema). This kind of hierarchical structure fits my application nicely, and it feels so rewarding to start visualizing frontend pages and how they’ll be populated with EdgeDB data. Going back to the Owner page example, if I want to create a page for every Owner in the league that lists their teams, that’s now as simple as pulling Teams with the fields and filters I want:

My SQL is a little rusty, but a rough equivalent would look something like:

EdgeDB has some more examples on their site that show how much they’ve cut down on SQL verbosity. And speaking of queries…

Querying for Data

EdgeDB has clients for multiple languages that you can use to interact with the database. (In the case of TypeScript there’s even a fully-typed query builder!) In my case, I used Python to do the parameterized data inserts that would get the database up and running. One example (the Season object is a very simple one with a single field called year):

Once you get around to grabbing data back out of the database, there’s a couple of things to be aware of. Here we’ll use some more examples of past teams from the 2020 season.

Use nested notation when you want to preserve nested object structure:

…and use dot notation when you just want fields directly:

Similarly, be aware of the difference between filtering nested fields and filtering the entire object! Here’s an example where I filter at the nested level (note how I still get the second Team object back, but the nested Owner object is empty):

But in a lot of cases you want your filter to apply across the entire object you want back, so you would do this (excluding the second object entirely):

The power to easily filter at any level is certainly one of EdgeDB’s strengths. The syntax takes a little getting used to, but in addition to being way less verbose than SQL, it also feels more composable. (Check out the with keyword for ideas on how to string queries together…you can go as deep as you like and create a whole query pipeline if you want!)

Side note: I switched to screenshots of my own terminal here because I love EdgeDB’s color highlighting. I think it’s a must-have layer of polish for any CLI app.

Looking Ahead

My first foray into EdgeDB was a successful one. Even though my personal project has a relatively puny dataset, I enjoyed using it enough that I would love to see EdgeDB in action on a much larger scale with a bigger business problem.

If you’re interested, EdgeDB 2.0 just landed this week, so there’s no better time to go check it out! The blog post for the release is totally stacked with new features:

• A built-in UI dashboard that looks CLEAN (if there’s one feature that I wish had existed during my initial journey, it’s this one!).
• GROUP statement, a.k.a. EdgeDB’s implementation of SQL GROUP BY .
• Object-level security (quick application in our example that I can think of: non-me owners would be able to update their own Owner objects…if I ever get around to adding that feature). This seems powerful at first glance.
• A Rust client (running out of reasons not to try more Rust…).

Good luck to the EdgeDB team in 2.0 and beyond! I’m looking forward to using it again.