This content originally appeared on DEV Community and was authored by Shravan Kumar B

This article mainly focuses on laying out our foundational approach to setting up the database migration system for Nixopus.

Database migrations are often the unsung heroes of software application development. They work silently in the background, ensuring that your database schema evolves safely alongside your application code. Yet for many teams, this is a major source of stress and uncertainty. In this blog, we will explore how we have set up the migration framework for Nixopus. This has become a pivotal step in our developmental and self-host workflow.

To start with, let us first explore and understand what we mean by database migration. As we always do, let us take an analogy.

We often use different database systems like MySQL, PostgreSQL, MongoDB, etc, for data persistence. These data are stored in the form of Tables having columns and rows.

As the application grows, the number of tables grows, and the data defined at each table might vary and change; hence, it is very important to keep track of these changes.

Tables are at the database system level, whereas at the programmatic level, we maintain them with something called schemas. Like a house blueprint that dictates where rooms, doors, and wiring go, a schema simply defines how the data is organized and connected.

Now, let’s imagine that you decide to change the blueprint mid-build, to add a new room or window, you need to carefully make changes to the existing blueprint such that you don’t end up collapsing the walls down.

This problem is addressed at the schema level to ensure the changes in the existing tables, which may be in the form of adding new columns, altering the type of existing columns, or adding new tables, are handled through what we call Database Migrations

When we started building Nixopus, we quickly realized that database schema management would be critical to our success in self-hosting and local development setups. We planned with an expectation set that eventually we would see multiple developers working on different features, frequent deployments, and the need to support both development and production environments, which would require a very streamlined process to handle database migration.

We had aligned on the following factors that our migration system would have to adhere to:

• Reliability: Migrations must execute consistently across all envs
• Automatic: No manual intervention should be required during deployments
• Forward & Backward Compatible: Ensure easy rollback scenarios
• Separations of Concerns: Organization of migrations by domain or feature

Many existing tools either lacked the flexibility we needed or came with unnecessary complexity, be it in terms of testability or adding rollbacks in case of errors, etc.

Hence, we decided to build our migration system tailored to our needs and requirements using a tool called Bun ORM.

To those who are not aware of the terminology ORM, let me give a glimpse of it; ORM, otherwise called as Object Relational Mapper, is like a waiter at a restaurant, to who you tell what you want, he/she goes to kitchen(I mean database), then brings back your order(data) as perfectly plated objects in ready to use form.

Without ORM, you would have been presented with raw vegetables and ingredients straight from the kitchen (of course, by that I mean the database), and you would have to do all the cooking yourself, which means writing and managing every query and data transformation.

Coming back, we have built our migration system around a simple yet powerful concept, where a pair of SQL files represents every database change:

1) Applying the change (up migrations)
2) Rolling it back (down migrations)

This approach has helped our system to easily take care of schema evolution and database versioning.

The migration system follows a defined life cycle:

1) Discovery: The system scans the api/migrations directory

2) Parsing: Migration files are parsed and paired (up/down migrations)

3) Ordering: Migrations are sorted by their numeric IDs

4) State Check: The system compares file system migrations with applied migrations in the database

5) Execution: Pending migrations are executed in transactions

6) Recordings: Successfully applied migrations are recorded in the migration table

Now that you know an overview of our setup and how it works, you might wonder what actually sets our approach apart? Fair enough!!!!

1) Domain Driven Structure: Instead of throwing all migrations into a single directory, we have organized them by domain:

api/migrations/
├── applications/   # App deployment features
├── audit/          # Audit logging and compliance
├── auth/           # Authentication & authorization
├── containers/     # Container management
├── domains/        # Domain and DNS management
├── feature-flags/  # Feature toggle system
├── integrations/   # Third-party integrations
├── notifications/  # Notification system
├── organizations/  # Multi-tenancy & organizations
├── rbac/           # Role-based access control
└── users/          # User management and profiles

This structure makes it easy for us to find and create migrations related to specific work or flow, helping us avoid confusion or conflicts.

2) Automatic migration execution on application startup: One of the key design decisions was to make migrations completely automatic. When the application starts, the migration system runs before any other initialization. This approach eliminated the need for separate deployment and ensures that the database schema is always up to date when the application starts.

3) Atomic, Transactional Migrations: Every migration runs inside a database transaction, ensuring atomicity. If any part of a migration fails, the entire migration is rolled back, ensuring that the schema remains fully consistent.

4) Bidirectional Migrations: Every migrations have 2 files:

• seqno_entity_up.sql (applies the change)
• seqno_entity_down.sql (rolls back the change)

This ensures that we can always roll back changes if something goes wrong.

As we are close to concluding the deep dive into the first of many articles of the Inside Nixopus series, I would like to highlight some of the major learnings and key takeaways:

1) Keep each migration small and focusedfor easier review or rollback.
2) Ensure to keep a down migration for every up migration, ensuring roll backs are easy.

The key insight is that sometimes the best tool is the one you build yourself. By understanding our specific needs and constraints, we were able to set up our migration system that fits perfectly into our development workflow and easy self-hosting.

The project, as we publish this article, is in the Alpha stage.

You can check it out on GitHub and see for yourself.

To sum it up, the approach helped us change schema changes from a headache to a reliable process.

If you would like to get involved or have questions, join our Discord community for real-time support and feedback. You can self-host Nixopus today, subscribe for updates, and stay tuned as we roll out new features and stability enhancements.

We have recently collaborated with HostUp, a reliable VPS provider based in Sweden, to bring you an exclusive deal of 10% off recurring on any VPS plan. Whether you choose to self-host Nixopus or deploy containerized apps, this is the perfect opportunity to secure rock-solid infrastructure at a Discord Community.

Stay tuned for more freshly brewed content.

That’s all for now. Thank you for reading.

Signing off until next time.

Thank you.

raghavyuva / nixopus

Nixopus streamlines your entire server workflow – ServerOps with no fuss

Nixopus

  </samp>
  <div class="markdown-heading" dir="auto">

Streamline Your Entire Server Workflow — ServerOps with No Fuss

Website | Documentation | Blogs

Project Overview

Nixopus is a powerful platform designed to simplify VPS management. Whether you’re a DevOps engineer, system administrator, or developer, Nixopus streamlines your workflow with comprehensive tools for deployment, monitoring, and maintenance.

Important Note: Nixopus is currently in alpha/pre-release stage and is not yet ready for production use. While you’re welcome to try it out, we recommend waiting for the beta or stable release before using it in production environments. The platform is still undergoing testing and development.

Features

• Simplified VPS management
  • 1 Click Application Deployment: Deploy applications effortlessly with a single click.
  • Integrated Web-Based Terminal: Access your server’s terminal directly from the browser.
  • Intuitive File Manager: Navigate and manage server files through a user-friendly interface.
  • Real Time Monitoring: Monitor your server’s CPU, RAM, containers usage in…

This content originally appeared on DEV Community and was authored by Shravan Kumar B