Kubernetes? Database Schema? Schema Management with Atlas Operator

[@DevOpsToolkit]

Which tool do you use to manage database schemas?

[@shibak4]

Depends on the company and its current processes, but it's been one of the tools that I mentioned. Liquibase has been around for ever and people with OLTP workloads (usually using Oracle/PostgreSQL/MySQL/SQL Server) tend to go for that or Flyway. In fact Redgate, a company that has a DB-oriented tool for managing DB versioning (not schema, DB itself) has a wrapper around Flyway with a very developer-oriented workflow. Modern data teams (those using Snowflake/Bigquery) tend to go for SqlDBM.

[@AaronHeld]

Flyway with the migrations built into a container. We use init containers for Dev and move to jobs as the schema stabilizes and we are in production. Init containers are great for running tests and blocking rollouts when they fail. Also used go-migrate, but most of my production stuff has been java lately. Another coupling that works for us is to have the app images look for the expected migration version in startup. My dream is to have a breaking schema change that is rolled out with zero downtime in a fully automated manner handled by k8s, but I haven't gotten there just yet.

[@dacam29]

We use the ORM’s code-first approach along with a k8s job + Argo + sync-wave annotation. Are there any other methods you’re aware of?

[@DevOpsToolkit]

@dacam29 there's either the approach you're using or something like Atlas or SchemaHero that provide CRDs to make it more Kubernetes-native.

[@RafaelDurelli]

These new video introduction are really nice lol 🎉🎉 I always watch with my daughter too

[@DevOpsToolkit]

That's great to hear. I wasn't sure whether it will be too distracting or a nice segway into the topic.

[@shibak4]

This seems to be a tool created by people who know databases exist but they've never been a part of a data team or don't understand how complex a database and its design can be. A solution developped by a newbie who has learned one language or tool and now thinks that EVERY problem has to be solved with the same language/tool. It's completely fair to look at a database, its resources (such as storage), its networking and to a certain extent its secrets from K8s perspective. After all, k8s is effectively an abstraction of OS and all those things are justified paradigms within the context of K8s. In fact you yourself had explained how many used to think that DBs don't belong to k8s because they didn't know how to use k8s the correct way. But defining a (e.g.) db table as a k8s resource is just an admission that one is obsessed with k8s and possibly doesn't know any tool other than that (or doesn't know about database design and data modeling enough). I'm sure many SRE folks used to think of Terraform as the right environment for expressing data elements, too. But that's as wrong as defining a table as a k8s resource! DB practitioners have had a portfolio of tools for schema management: Liquibase, Flyway, Alembic, Sqitch, DBMaestro, or modern tools like SqlDBM are built for that purpose and fit well within the workflow of DBAs, DB developers or analytic engineers. Managing a k8s resource has nothing to do with the day to day workflow of any of those three. AND all those tools have spent a huge amount of time adapting to the needs of DB developers: DBs are not made of just a handful of tables in one schema! You have to think about security design (i.e. multiple schemas, multiple users, privileges of the users, creator rights vs user rights, etc) and all sorts of different DB objects such as constraints, indexes, functions, stored procedures, triggers, views, materialised views, schedules (yes, many DBs have internal task schedulers), etc. Do you think a DB developer would be productive swimming in a sea of YAML definition to get to a db object definition in SQL?! This tool will only serve folks who are obsessed with K8s. No real development team which is building/maintaining/running a serious application would adopt this for maintain their data model.

[@DevOpsToolkit]

I do not see kubernetes as something designed to run containers exclusively. I see it as a universal extensible API though which we can plug resources that manage whatever we need to manage. From that perspective, and when thinking about a combination of resources (infra, services, DBs, schemas, apps, etc.), Kubernetes is probably the best place to put them all together. On the other hand, if one is focused only and exclusively on databases, there is no need to dive into kubernetes. What I'm really trying to say is that it all depends on whether we're trying to join dispare things together or keep them separate. If it's the latter, kubernetes is often not a good choice. Or, to put it differently, there is no good reason for DBAs to dive into kubernetes as long as they work in isolation (as is often the case in big systems).

[@shibak4]

@@DevOpsToolkitIf it doesn't fit within the workflow of database admins, designers and developers, it's not the right tool. A great deal of these folks' time is spent on thinking about CI. That wheel is already invented. What's the advantage of reinventing the wheel (which honestly, doesn't look circle, anyway. This looks like a proper square!) TF/Anible/Chef/Puppet practitioners have already learned that lesson. I guess it's time for k8s advocates to not learn from other people's mistakes and experience the failure. All you need to do is to talk with an analytics engineer and ask him/her whether they're willing to change their workflow and adopt this. Just show them your simple example and watch their facial expression.

[@shibak4]

K8s is an abstraction of OS, right? Would you be using Linux for DB schema management? Or you'd use a tool that's designed for schema management? Should a database schema (an internal paradigm of an application that's running with the help of OS) be a first degree citizen in Windows or Linux? If yes, what other internal paradigms of other applications that are running in an OS should be first degree citizens of the OS? Should any internal concept of a video editing application like FinalCutPro (such as a "sequence" which is analogous to a DB model in a database) be presented at OS level as a first degree citizen? We all use an IDE, right? Should the extension manager of VSCode be a first degree citizen of the (let's say) MacOs? Or is it enough that VSCode itself or FinalCutPro itself and the files that they operate on are the first degree citizens of the OS? @@DevOpsToolkit

[@DevOpsToolkit]

​@@shibak4 Kubernetes is, first and foremost, an extensible API. Those extensions (CRDs) are backed by controllers which are, in a way, processes that react when certain resources (CRs) are created/edited/deleted. Think of those reactions in a same way as an application with an API reacting to requests being sent to that API. That is the primary reason for existence of Kubernetes (extensible API backed by controllers). In Kubernetes, a controller is equivalent to a tool in an OS. Just as tools running in an OS need to follow specific rules of the OS, controllers in Kubernetes need to follow specific rules. In both cases we can bypass those rules, but that often results in an suboptimal experience.

[@shibak4]

We've all been taught that the success of IT missions depends on a number of things: Organisation, people, process, and finally tools. This tool completely ignores the first three! I'm sure it's following the principles of its own context (K8s) but without the first three, you'll never have success. The same way that TF/Ansible/Chef/Puppet never succeeded in providing a solution for managing DBs (other than from the perspective of running a process within the context of an operating system). They -like this tool- were too set on their own mentality and thought they could overcome the inertia of people, their processes, and the organisations. You can't win without having people on your side, especially when you're providing a replacement for an existing solution that's part of the day to day workflow. Maybe an intermediary component between the existing tools and k8s could help. @@DevOpsToolkit

[@shwanbekas1]

could you please talk about DevSecOps. Thank You

[@JordanCricketMoore]

Naming things is hard. Not to be confused with MongoDB Altas Operator

[@cowgod77]

Looks like a very nice solution. Does Atlas support data migrations as well? Sometimes my schema changes also require some UPDATE queries to adjust the data to match the new schema.

[@DevOpsToolkit]

It does support migrations 🙂

[@tonicebrian]

@@DevOpsToolkit I'd like to see how this is done because otherwise Flyway is the only tool to do real world schema+data migrations. All these examples sound like fine for greenfield projects but the moment you are in a production setup with production data in your DB I can help myself but I'm seeing shortcomings. Let me frame a simple problem. Let's say that I have a DB for a web scraper and there is a table with a "price VARCHAR" column. The data in there can be something like "12€", 24.95 dollars" etc. It is fine for displaying the price but after some months someone wants to perform aggregations so you need to split that column into 2 "amount DOUBLE, currency VARCHAR", and everything is good until the data team wants to unify currencies ('€', 'euros', etc.) and you need to have columns with "amount DOUBLE, currency ENUM". For this use case you can only work with a tool that allows schema AND data migrations, and the migrations need to be in a specific order. I don't know if current tools allow this but without this functionality they are (almost) useless. On the other hand and I don't know much about why a list of migrations in a predefined order cannot be seen as declarative and used in kubernetes tools. To me is like a hash from a commit in git, it is implicitly an ordered list of patches that leave the artefact or code in a predefined state. And hash commits are used everywhere in the kubernetes world.

[@DevOpsToolkit]

Atlas does support migrations which, if everything else fails, can be an ordered list of SQL statements. `CREATE TABLE` is not declarative (even though in case of Atlas it is since it always creates stuff in a temp DB and generates diffs). Outside Atlas, it works only if the table does not exist. It's a similar thing as why Shell scripts do not work for infra unless you create an infinite number of permutations with if/else statements do determine what should and what should not be done based on the current state. Now, that does not mean that declarative is the way to go. It often isn't. What I'm really trying to say is that if something is managed through Kubernetes (pending discussion whether it should be), then certain rules should be followed so that that something can interoperate with other tools in the ecosystem (e.g., policies with Kyverno). I do not necessarily say that schema management should be done in Kubernetes but, rather, that if it is done, it should be done right (CRDs, controllers, and other stuff that enables interoperatibility through standard expectations).

[@cowgod77]

That's great to hear! I definitely need to do more research, but it sounds like this might have the potential to be a good solution for us. We're in the process of migrating our in-house application suite to Kubernetes and adopting GitOps, and I'm very interested in managing the DB schema right alongside it, in a declarative way.@@DevOpsToolkit

[@ariga_io]

Hi, Atlas supports two modes of managing schemas: * Declarative migrations - which is demoed by Viktor in this video * Versioned migrations - where you provide SQL scripts for upgrading your database from version to version. The good news is that if you choose the latter, Atlas will still automatically plan your changes by diffing relative to your desired state. Currently, data migrations can be done in versioned migrations but not in the declarative mode. We are working on something pretty to support it, which will be out soon.

[@jonny.rubber]

At the moment we are using EF core to migrate databases for our own apps. After deploy, we call an API endpoint.

[@amirmirzababapour6016]

nice idea brother, thanks for sharing

[@jonny.rubber]

Completely agree 💯

[@SranSrepfler]

Need to watch it in full but many companies will stick to Liquibase and Flyway until there's a direct k8s support via an operator. I noticed LB and FW icons on Atlas, maybe they'll expand the operator so they reference those tools resources and apply the changes using the native tools devs have been using so far.

[@javisartdesign]

In the documentation there is a mention to other tools such as Liquibase, Flyway, etc.. It also support run Migrations (versioned scripts) so you can define the scripts in a sequence to be executed. Same as liquibase, you will need to append the latest migrations to the end of the main resource definition. As Viktor says the way you can define those scripts (SQL or declarative) is by using only one Kubernetes resource, or you can store those scripts in Atlas cloud or using ConfigMap. I think it would be more convenient to define a new priority flag or version inside the CDR or creating new custom CRDs for migrations so resources are dependent for each other... Maybe in the future we will see that feature.

[@mopsik4ever]

Great video but the fact that you need to manage multiple schemas inside the same CRD will eventually fail since every object in k8s has a size limit due to etcd limitations. Hopefully they will implement a queue for each CRD.

[@DevOpsToolkit]

Yeah. That's probably the biggest issue with it. Even if you do not reach the etcd limit, having everything in a single cr is not something I want to have.

[@kostiscodefresh]

Their commercial product offers an alternative where you store SQL files in a Git repo, and the CRD just points to Git Repo

[@ariga_io]

@@kostiscodefresh Thanks for mentioning this Kostis. This is currently supported for versioned migrations but is coming to declarative state definitions as well.

[@57skies]

wait. you guy use operators for this? helm hooks is what we use

[@DevOpsToolkit]

Helm hooks are, in my opinion, a workaround for badly designed kubernetes resources.

[@pavelpikat8950]

I knew it!!! :D

[@aguafria9565]

Lol what schema management. Literally all I did was run a job with an init script.

[@typicalaimster]

I was excited thinking this was for Mongo Atlas. Only to be disappointed.

[@DevOpsToolkit]

The name of the project is confusing and certainly not something I would choose.