Beyond Alembic and Django Migrations

Speaker 1 [00:07]

So, my name is Rotem. I am, for the past four years, I've been the CTO and co-founder of a company called Arriga. As part of my job, I have the pleasure of being a co-maintainer of two fairly large open-source projects that we will touch upon them today. The first is called Ant. Ant was started by my co-founder when he worked at Facebook. Today, it's part of the Linux Foundation. It's an entity framework for the Go programming language. So an ORM for building Go applications. The second is called Atlas. Atlas lets you manage your database schema as code. So it's a database schema management tool based on modern DevOps principles. And let's jump into it. So if you're building an application, a backend application that is reading or writing data from the database, you might be using an ORM. As a quick recap, ORM, an object relation mapper, is the thing that maps between your Python classes and the database software component that lets you define data models in your programming language and has some layer that can read and write from the database making your life potentially easier. ORMs are a highly contentious topic. It's one of these things in the industry that we cannot seem to come to consensus around. They have been called the Vietnam of computer science. So it's a metaphor for something that we think we can easily solve. And as the years progress, we seem to sink deeper and deeper into the mud, never reaching final victory. Others say, if you don't use an ORM, you will eventually end up writing your own. So as an ORM maintainer, I'm more in this camp, but opinions vary. Today I don't want to talk about ORMs in general, and if this is the best or most efficient way to query or write to your database. I want to focus on something that's been bothering me personally for the past 10 years of my career and more so in the past four years as a founder of a company. That's dealing in this space It's the migration tools that are shipped with standard ORMs as a reminder Why we need migration tools? So So when our data model evolves, our database schema must follow suit. So if we have a user model and we're adding a birthday attribute because we want to track birthdays of our users, before deploying our code, we have to run an alter table statement to update the underlying database. Otherwise, when our server comes up, starts issuing queries to the database, we'll start getting errors. The database will not know what is this birthday concept that you're talking about. So we need tools to manage that. Let's see a quick example of what this looks like today in a popular Python ORM called SQLAlchemy. The examples here, because this is not an AI talk, I did use an AI agent to record the examples so you have something fun to look at. So we're using GitHub Copilot in agent mode. With SQLAlchemy, we define our classes as simple Python classes, a user and a match for tracking some game. And migrations are written in this DSL of create table. So these are scripts for instructions for upgrading the database. We ask Copilot to add a point attribute to the match model. And it's going to go ahead and generate. I've become really lazy, can't type this on my own anymore. So I'm going to keep this change. And now my co-pilot knows that it needs to run the Alembic program to generate the migration script. This is not AI, it's just normal program. And it runs. And it detects the diff between the model and the migrations. And it generates the upgrade script to add the points column. Very, very good. The Python community is actually among the luckiest, because we have pretty good schema management tools for our ORMs. If you compare this with things that are available in JavaScript or in Go, you will see that this idea that you can just define your database schema as code and then generate migrations for them is a pretty awesome experience. So why still, here we're at the Python conference, should we consider, or when should we consider using more specialized tools? So I'll try to give you three cases where you should consider, and happy to discuss this with you afterwards. To explain this, I want to talk about something that I call sometimes the hidden bias of ORM native migration tools. So let's think from the perspective of an ORM maintainer and see if we can uncover this issue. So if I'm building an ORM, I must provide a minimally useful, if I want to provide a minimally useful product, I must ship with some schema management solution. So imagine downloading a shiny new ORM that promises to make efficient queries on the database, and then you define the Python class, and then it tells you, okay, where's the database? So you need to give the user some way to manage the database. Even if it's just a minimal scaffolding of a local environment, you have to ship something. I had a chat with my co-founder, Ariel, about this, and he said something like this. So as an ORM maintainer, I see schema management as a necessary evil. I want to spend my time on runtime API and performance optimization, and not generating alter table statements. So in the words of Jeff Bezos, products should focus on what makes their beer taste better. The differentiating features in the competitive space of ORMs, this means a nicer API, this means better performing database queries. It does not mean the way in which the operations, the IT operations happen typically. The second point is that ORMs are abstractions. Their goal is to provide a Python interface to something that is not Python. So they try to provide a unified interface of working with very different database management systems. Postgres, MySQL, SQLite all operate very differently and have different features. Yet still, ORMs are expected to provide a unified experience. The only way they can do this is by providing the lowest common denominator between the different databases. This means that as an ORM user, you only get the tip of the iceberg, tables, foreign keys, very basic indexes that are supported across the board. But you skip functions, stored procedures, triggers, views, materialized views, extensions, level security and more cool things that your database can do. I can hear some of you moving uncomfortably in your seat, and when I was a young engineer many years ago, my seniors told me that I shouldn't program on the database, that the database should be, I should follow the repository pattern, and the database is just a container for data, and all of the logic should reside in the application code because it's better, cleaner, whatever. But as those of us have been around know, the pendulum swings, right? We move between different things, go back and forth. And as someone who's observing database schema management and working with lots of companies in this space, I can tell you that in the wild, database programming does exist, and it exists a lot. In fact, it's even making a comeback with systems like Superbase, where you build a full application just with SQL and something that generates a REST API or GraphQL on top of that. So what can we gain by programming on the database? So if we use functions and stored procedures, we can run application logic on the database. We can save round trips, giving better performance for our users. We don't need to serialize our data, throw it across the wire 10 times to satisfy a request. We can do all of it on the database. If we use triggers, we can do things like audit logs or maintaining counters, things like that, and just enjoy the database's native transaction support. Our comments counter and the comments table are always in sync, so there is no way they can get out of sync, because if something isn't committed, then the counter is not updated. With views, we can encapsulate queries that we commonly use and define specific access control to them. With materialized views, we can pre-compute expensive queries to improve things. If we have our game application that we showed for SQLAlchemy, we can have a materialized views to pre-calculate the leaderboard so we don't need to do a full table scan whenever we want to know who the top 10 players are. With extensions, we can use things like PostGIS for geospatial applications or PGVector for doing AI in our backend. We don't want to read everything into memory and process it in the application if the database can do it efficiently with really, really great support. Finally, advanced indexes. So your ORM will typically help you out with very, very simple indexing schemes. But if you really want to get the best performance out of your database, you're going to start tweaking indexing. So you want a way to manage that. Let's see what happens when we ask our AI co-pilot to create a materialized view if we're using SQLAlchemy. So let's try add a leaderboard materialized view for tracking user wins and points. OK, it goes to work. And what it will tell us is that it's not trivially possible. OK, I'm going to recap what it says. So it says there's no native way for defining the materialized view. You can create a custom migration with the create materialized view statement, but it's not going to be part of the diffing in the future. So the whole magic of I modify the definition, and then Alembic will understand when it needs to regenerate the view, that is lost. You are now manually tracking some of the database schema. So if we want to do this schema as code thing, we need a general purpose migration tool, something that is designed without the bias of an ORM that says, I'm only going to focus on the lowest common denominator between the different databases. I want something that allows me to extend my database schema and still work in this cool declarative workflow. So part of the reason we created Atlas is to solve this problem. We open sourced its core engine in 2021. It's used by thousands of projects on GitHub, thousands of companies, big and small, in production. And we're having a great time building it. I want to do a really, really quick demo of what it looks like to work with Atlas, very similar to what we saw with Alembic with some small differences. So with Atlas, we are still reading our user model. We can still read the Python. We have standard SQL scripts for migrations, no DSL. We have the migrate diff command. When we run it, we compare the schema to the migrations. Currently, everything is synced, so I'm not creating a new file. However, if I go ahead and make some change to my Python code, For example, I'm going to add a column here named hello. Atlas, when I run migrate diff, is going to pick up on this change and generate the alter table statement to reconcile between the schema and the migration directory. So this is kind of nothing very fancy up until now. It doesn't give us anything new. But it is cool that it's a general purpose tool that can read the Python for us. So now, back to our co-pilot. Let's try to add a materialized view and see. Thanks, man. Co-pilot goes to work. And what it tells us now is that it's going to update our schema SQL file. So we are using something called the composite schema that layers on top of the Python a simple SQL file so we can extend our schema with database objects that are not supported by the ORM. Copilot suggests that we are going to now run the migrate diff command and then it will generate the migration for us containing the thing. So if you run migrate diff again, no new because Atlas builds the graph containing all of the objects in the database. Now let's see something else that's cool. Let's generate a test. Atlas has a unit test framework for testing database objects. There's lots of logic in this materialized view that we want to make sure that it works correctly. So I'm going to ask my co-pilot to generate this. It will spit out this test. What it does is it seeds some users into the table, inserts some matches. now we need to refresh the materialized view so the data is up to date and then it's going to run a query and we have some assertion here about what we expect the output to look like so this is a unit test for a materialized view using atlas we run this and we see that we have some some error there is some mistake copilot says ah okay charlie actually has 155 points It goes on to correct the unit test when we hit continue. Now the test should pass. And we have a framework for testing logic on the database. We went through this really quickly, so just to understand how this happens or how is it possible that a program that's written in Go is reading the schema from a Python class. So it's not magic. It's just not AI. It's simple software engineering. We have a program called the Atlas Provider for SQLAlchemy. This is Python code that knows a little bit about SQLAlchemy. And what it does is it prints to the screen the create table statement. So what is the desired schema from the SQLAlchemy perspective? We have something called a composite schema. So we can compose a graph that's built of two components. We have the SQL alchemy component, and on top of that, we layer our schema. So now we have a graph that describes our Python code plus the SQL stuff. Finally, we define this environment that stitches everything together. It tells Atlas where the migrations are, where the source is, the desired state. And when we run migrate diff, we compare our schema graph with the migration directory, and if needed, we generate a new migration. So benefits of using Atlas here would be you still define the data model with your Python ORM. You can layer extra SQL objects to utilize your database features. Diffing takes into account all database objects, so not just what's supported by the ORM natively. And one cool feature, among others, you can write unit tests for your database code. If you're starting to program on your database, your database schema is code, so you should be able to test it like your Python code. However, building applications doesn't end with code. Code is only the beginning of the journey. We need to take into account how we're going to get this code live into production. So let's talk a little bit about modern CICD, the deployment pipelines for databases. As a recap, CI is the stuff that we do to make sure stuff doesn't break before deployment. So if you have unit tests, you run them on the pull request phase. If your security team wants to scan for vulnerability, typically that will happen before code is merged to the master branch. So this is all of the security or the safety checks that we run to make sure that our code quality is okay before merging. CD is about making sure we can deploy without human intervention whenever we want. It doesn't necessarily have to mean that whenever I merge code to master, it immediately goes live. But I want to be able to deploy the code I want, when I want. If I want to roll back, I want to be able to go back. So this is about the important part about CD, continuous delivery or deployment, depending on which camp you're in. This is important because migrations are risky. So if you have worked on a team with a busy database, you know that sometimes migrations don't go as planned. Schema changes don't go as well as you planned. Some of the ways that things break are databases allow us to do destructive changes. So we can drop table, drop column. Unfortunately, they don't give us an undo button in case that happened to us. This can be an issue sometimes. It might sound to you like something that only happens to, or should never happen, but it's something that happens every day. Incident report from Resand, famous email API company. 4.56 AM, database migration started. A minute later, notice database tables being dropped. Took us something like an hour to recover from a backup, and still what this report doesn't say is what happened to the records that were written to the database before, between the last snapshot and the recovery. So there's no great way to recover from this. Best to avoid it in CI if we can. Constraint violations, we can add, for example, a unique constraint. It will always work on an empty database. When we run it in prod, maybe we have a duplicate value on a large table. So now we have a broken deployment. We did some steps. Some of them didn't work. Now we're in some limbo. We should find a way to roll back. Best to avoid this during CI. Breaking changes, just like we can break the contract between our iOS application and the back end, we can break the contract between the back end and the database. For example, if we rename a column in the wrong way, we're going to start to get a very high error rate because the database doesn't understand what we are asking from it. Finally, this is a bit more advanced topic. Certain changes you're going to ask your database for are going to cause the database to acquire a lock for the duration of the transformation. It will need to rewrite values on disk. It can be a slow operation. It could take hours, sometimes days. For example, if you're using Postgres and you're adding a unique constraint, to satisfy that request, the database will take an access exclusive log of the table, meaning no reads and no writes during this period. It now is going to scan the table, full table scan, an expensive one, because it's going to do some sort to figure out if we have any unique values. And only once these checks are completed will it allow you to finish the DDL statement. This can take minutes or hours, depending on how busy the database is, and it's a very nasty outage. So it would be very good if we had something in CI that can prevent this stuff from being ever merged to reduce the chances that we have a reliability issue. Atlas comes with a command called atlas migrate lint. Lint maybe is underselling it a little bit. What this does is similar to static code analysis. It's going to look at the statements that were written in the recent migration and give you a warning about them. So for example, this might fail because adding a unique index can fail depending on the data. or in our example that we talk about, it's going to acquire and access exclusive lock. Having this locally as a developer is very informative. It's very valuable. But where it becomes really valuable is if we add this to our code review step. So here we see Atlas running as a GitHub action. Whenever developers make a change, they get the feedback. What are the potential issues with the migrations that they have authored? And the best part is that the pull request is read. It's not mergeable. Depending on team policy, you can decide what is allowed, what is not allowed. But basically, it provides you with guardrails where you can prevent risky changes from being merged. Much, much more important today, where a large portion of our code base is not written by humans anymore, perhaps we should have some guardrails to check this code before it modifies stuff on the database. Another point is that ORMs are typically not concerned with deployments. They see themselves as runtime libraries. So they don't care about if you use Kubernetes, if you use Terraform, you use whatever Jenkins pipeline. Unopinionated, and that's fine. With Atlas, one of our goals for building a modern CICD platform is to meet the DevOps team or the platform team with whatever tooling they are using. So we have a Kubernetes operator to make the database schema just another resource as part of the Kubernetes application. We have a Terraform provider, so you can use the database schema as a resource in the infrastructure as code graph. And of course, integrations with all the common CI platforms like GitHub Actions and GitLab, et cetera. Final point of when you should consider not using your ORM migration tool is if your company is building a platform and the platform is polyglot. Polyglot means speaks multiple languages. This is very common in larger companies today. Some code is written in Go, some of it is written in Python. If you want to standardize stuff and you want to have these safety checks applied no matter what database we're using, no matter what programming language we're using is very helpful to have one tool to rule them out, so one standard solution for your company. Atlas does this by providing providers, so plugins native to the ORM where we can read the schema that the user is describing. And we have drivers, so we have the database side that can talk to and is aware of the different risks of the different databases. Key takeaways, when to use Atlas or a specialized database schema management tool instead of your ORM. First of all, you might not need it. And if you kind of pay attention or thought about this, Atlas is actually inspired by some of the aspects of migration tools that came from the Python community. So if it works for you, don't touch it. it's one of the most important principles in software engineering. However, it might be holding you back in ways that you didn't consider. If you want to use advanced database features, like materialized views, and stored procedures, et cetera. If you need a more robust CI-CD pipeline, consider using something that has native support for that. And if you're building a platform that needs to support polyglot teams, consider using something that can speak multiple languages and not need to reinvent the wheel for every permutation of database and programming language. That's it. Thanks for your time.

Speaker 2 [26:03]

We do have a few questions in slido.com and if we can't get to all of them

Speaker 1 [26:07]

I'll respond on Discord, sure. Yes.

Speaker 2 [26:10]

So first question is, is Atlas capable of handling downgrades in the same way that Alembic does?

Speaker 1 [26:19]

Great question. It's always the first question in every conference. Usually I even have a special slide for gotcha. So, yeah, we've done a lot of research into this, interviewed hundreds of companies. Migration tools offer this thing called a down migration, telling you to pre-plan the down migration. What we found is typically these things are used locally when you're developing, but in production, most of the time, or very rarely, are they used. Why? Down migrations are an optimistic plan for an unoptimistic future. Failure is unexpected. One implicit assumption of down migrations is that all of the up statements succeeded. If we have a create table, create table, our down script is drop table, drop table. Now, if there was a problem somewhere, then our down migration is going to fail as well. Double trouble. We don't want that. Instead, what Atlas does, it uses its diffing mechanism to do ad hoc down migrations based on the real state of the target database. So you have a migrate down command. What Atlas does, it looks at the real schema. It looks at the real statements. It understands which of them were applied and not. And it does the reverse operation in the reverse order to bring you back to the right state. So it kind of uses the same engine it uses for diffing in local dev, but applies it in production. Our commercial product has approval flows for this stuff so you can control it more carefully, but generally this is how it works

Speaker 2 [27:54]

Thank you, next question is with rust Becoming a language often used with Python are there plans to support rust or or RM definitions in Atlas?

Speaker 1 [28:05]

We're very community oriented. When we have demand, then we support it. We actually didn't get any so far. The implementation of the plugins is super simple. You just need a program that prints out the create table statements. So if you want to work on this with us, it's really like a one or two day fun project. Happy to support you in the contribution.

Speaker 2 [28:28]

As you use native SQL in migrations, how can I maintain different RDMs in parallel?

Speaker 1 [28:36]

So, you have two approaches. One is we have something called declarative migrations, which is kind of like a terraform for database. There are no migrations. You just schema apply, and then the calculation happens at runtime. The other way to do that is to have separate migration directories for the different dialects, which happens anyway. If you look at big open source projects, you can see they have separate migration directory for Postgres, separate one for MySQL, because as much as we'd like the abstraction to work, it is leaky. It's not 100% bulletproof. Sometimes we need different details for different databases.

Speaker 2 [29:13]

Maybe one more. This might be a bit involved, but how to switch an existing app from the Django ORM into Atlas? Yeah.

Speaker 1 [29:21]

Yeah, great. So typically what we do is we realize that most projects are brownfield projects. So it's very fun to start a project with Atlas, but most of our users did come from an existing project. For this, we have a command called Atlas Schema Inspect. It allows you to basically extract the current schema into a SQL representation, if you like. And then you use that as the baseline migration. Basically, you have a file that represents this was the schema when we started the project. You run the command once, and the first time that you run Atlas, basically you baseline the database. So you tell Atlas, the database is already at this state. And from this point on, you just continue using Atlas.

Speaker 2 [30:07]

Maybe one more Last one. I promise the examples show SQL alchemy. Mm-hmm. Is there a support for SQL model a layer on top of SQL alchemy?

Speaker 1 [30:18]

I'm not aware of it actually if we have a discord community you can join on our website and ping me personally I will happy to check if it's if it works

Speaker 2 [30:30]

Awesome. Let's give a round of applause.