Frappe and Laravel: Database Management and Migrations

Comments 0

Share to social media

In this second installment of our series comparing Frappe and Laravel, we probe into critical aspects of database management and migrations. When we talk about management in these frameworks, we’re referring to the practices that developers use to efficiently handle database operations throughout an application’s life cycle. To do this, we will be looking at the tools that are used to do this database management.

Both frameworks provide solutions for handling database operations, but their approaches differ in implementation and philosophy. We’ll explore how Frappe’s Doctype system and Laravel’s Eloquent ORM tackles schema definition, data manipulation and version control of database structures. By examining the migration process in both frameworks, we’ll uncover the strength and unique features it brings to the table.

Frappe uses a metadata-driven approach for database management where DocTypes acts as schema definitions and database models. DocTypes define fields, relationships and behaviors directly, and changes are applied using the bench migrate command. This approach simplifies schema updates, integrates tightly with the framework and ensures version control through Git.

Laravel manages database changes using Migrations, PHP files that programmatically define and modify database schema. Migrations are applied using Artisans commands like php artisan migrate and are version-controlled ensuring smooth updates across environments. This system offers flexibility enabling developers to handle schema changes and data seeding programmatically.

Database Management with Frappe’s DocTypes

Frappe’s DocTypes (as covered previously in a previous article) are the foundation of Frappe’s database modelling system. They serve as a high-level abstraction for defining data structures and relationships in your application. Here are some key points about DocTypes.

  • DocTypes define the structure of your data, including fields, their types and properties.
  • They automatically handle table creation, and schema updates in the underlying database.
  • DocTypes manage field properties such as validation, defaults and permissions.
  • They provide a user-friendly interface for developers to design and modify database schemas without writing SQL.

Frappe’s approach to database management significantly reduces the need for manual migrations and makes changes seamless for developers.

  • When you create or modify a DocType, Frappe automatically updates the database schema.
  • Adding, modifying, or removing fields in a DocType is reflected in the database without manual intervention.
  • Frappe handles data type changes and creates necessary relationships and indexes automatically.
  • The framework manages relationships between DocTypes, creating foreign key constraints as needed.
  • Developers can focus on defining the data structure rather than writing complex migration scripts.

Process of creating and modifying DocType

In the demo, we will be creating a DocType in Frappe and demonstrating automatic database changes. We will do this by creating a simple Customer DocType and then modify it to see how Frappe handles the database changes automatically.

Step 1: Creating the Customer DocType

The _ functions in these lines in the previous code block are wrapping the string labels for various fields. This typically says that the code is prepared for internalisation(i18n), allowing the strings to be easily translated into different languages without modifying the core code structure.

When this DocType is created, Frappe will automatically:

  • Create a new table in the database called `tabCustomer`.
  • Add columns for `customer_name`, `email`, and `phone`.
  • Set up appropriate data types and constraints (e.g., `customer_name` as NOT NULL).

Step 2: Modifying the Customer DocType

Let’s say we want to add a new field for the customer’s address and modify the phone field to be required. We can update our DocType definition:

When these changes are applied, Frappe will automatically:

  • Add a new `address` column to the `tabCustomer` table.
  • Modify the `phone` column to be NOT NULL (required).

All these changes happen without the need for manual migration scripts, although Frappe still allows manual migration scripts when operations become too complex for the automatic system to handle efficiently which is termed ‘patches’ in Frappe.

To create a manual migration script:

  • Create Python file in the `patches.txt` file of your Frappe app
  • Write your migration logic using Frappe’s database API
  • Add the patch to your app’s patch.txt file
  • Here’s an example of a patch.

While Frappe’s automatic migration handles most common scenarios, these manual patches provide the flexibility to perform more complex database operations when needed.

Laravel’s Migration System

Laravel’s migration system for managing database schema changes works in a version-controlled manner.

  • Migrations are like version control for your database, allowing you to define and share the application’s database schema.
  • Each migration file contains a class with two methods: `up()` for applying the changes and `down()` for reverting them.
  • Migrations are typically stored in the `database/migrations` directory of your Laravel project.
  • They are executed in the order of their timestamp prefixes, ensuring consistent application across different environments.

Step-by-step Process of Working with Migrations

This is an overview of the process for creating a migration win Laravel’s migration system.

  1. Writing Migration Files:
    • Use Artisan command to generate a new migration file: `php artisan make:migration create_users_table`
    • Define the schema changes in the `up()` method using Laravel’s Schema Builder.
    • Implement the reverse of those changes in the `down()` method for rollbacks.
  2. Running Migrations:
    • Execute migrations using the Artisan command: `php artisan migrate`
    • This applies all pending migrations in chronological order.
  3. Rolling Back Migrations:
    • Revert the last batch of migrations: `php artisan migrate:rollback`
    • Revert all migrations: `php artisan migrate:reset`
    • Rollback and re-run all migrations: `php artisan migrate:refresh`
  4. Versioning Changes:
    • Migrations are automatically versioned by their timestamp prefixes.
    • Laravel keeps track of which migrations have been run in the `migrations` table.

 

This next diagram provides the overview of the process, and what follows will step you through the process.

Let’s create a migration file to add a new `products` table and explain how Laravel’s Artisan commands handle migrations.

Step 1: Generate the migration file

This command creates a new file in `database/migrations` with a name like `2023_12_24_123456_create_products_table.php`.

Step 2: Define the schema changes

In this migration:

  • The `Illuminate` is a core Laravel component which forms the foundation of the Laravel framework and provide much of its functionality.
  • The up()` method creates a new `products` table with columns for id, name, description, price, stock, and timestamps.
  • The `down()` method drops the `products` table, allowing for easy rollback.

Creating a new table is a straightforward process in Laravel migrations. However, Laravel’s migration system is capable of handling much more complex scenarios.

For example: adding foreign keys and relationships.

This migration adds a foreign key relationship between the `products` and the `categories` table.

Step 3: Run the migration

To apply this migration, run:

How Laravel’s Artisan commands handles migrations:

  • When you run `php artisan migrate`, Laravel checks the `migrations` table in your database.
  • It compares the migrations in this table with the files in your `database/migrations` directory.
  • Any migration files that haven’t been run (i.e., aren’t in the `migrations` table) are executed in order.
  • For each migration, Laravel calls the `up()` method to apply the changes.
  • After successful execution, Laravel adds a record to the `migrations` table, marking that migration as complete.
  • If any errors occur during migration, Laravel will stop and rollback any changes made in that batch.

When you run php artisan migrate, Laravel checks the `migrations` table in your database. It compares the migrations in this table with the files in your `database/migration` directory. Any migration files that haven’t been run(i.e., aren’t in the `migration` table) are executed in order.

For each migration, Laravel calls the `up()` method to apply the changes. After successful execution, Laravel adds a record to the `migration` table, marking that migration as complete.

If any errors occur during migration, Laravel will stop the process at that point. Any migrations that were successfully completed before the error will remain applied but no further migrations in that batch will be executed. This allows developers to fix the issue and continue the migration process.

If you wanted to manually undo this migration:

This command will call the `down()` method of the most recent batch of migrations, effectively undoing the changes.

For more granular control, Laravel also provides:

  • `php artisan migrate:rollback --step=5` to rollback the last 5 migrations.
  • `php artisan migrate:reset` to rollback all migrations.
  • `php artisan migrate:refresh` to rollback all migrations and then migrate again.

Laravel’s migration system provides many ways to manage database schema changes, ensuring consistency across different environments and making it easier to work collaboratively on database-driven applications.

Comparisons of Database Flexibility and Control

Let’s compare the database flexibility and control offered by Frappe’s DocType system and Laravel’s migration system:

Aspect

Frappe’s DocType System

Laravel’s Migration System

Schema Definition

High-level, declarative approach using DocTypes

Explicit, code-based migrations

Ease of Use

Very easy for simple schema changes

Requires more manual coding for each change

Flexibility

Excellent for rapid prototyping and frequent adjustments

Highly flexible for complex schema evolutions

Version Control

Automatic, based on DocType changes

Manual, through migration files

Rollback Capability

Limited, mainly through DocType versioning

Robust, with explicit up() and down() methods

Complex Operations

May require custom scripts for very complex changes

Supports complex operations directly in migrations

Learning Curve

Shorter for basic operations

Steeper, requires understanding of migration concepts

Database Agnostic

Limited to supported databases

Highly database-agnostic

Now, let’s dive deeper into the trade-offs:

Ease of Schema Handling vs. Explicit Migration Control

When comparing Frappe and Laravel’s approaches to databases we encounter some philosophies: Frappe’s emphasis on ease of schema handling, and Laravel’s focus on explicit migration control. These approaches reflect different priorities in database management and cater to different development styles and project needs.

Frappe’s DocType System:

Frappe’s Doctype system prioritizes simplicity and rapid development. It abstracts away much of the complexity involved in database operations allowing developers to focus on defining data structures rather than writing SQL or migration scripts. This approach shines in scenarios where quick iterations and frequent schema changes are necessary, as it reduces the overhead of managing database migrations manually.

Pros:

  • Rapid development: Quickly define and modify database schemas without writing SQL or migration code.
  • Automatic schema updates: Changes to DocTypes are automatically reflected in the database.
  • Reduced cognitive load: Developers can focus on business logic rather than database management.

Cons:

  • Less granular control over the migration process.
  • Limited ability to perform complex database operations that fall outside the DocType paradigm.

Laravel’s Migration System:

Laravel’s migration system offers more granular control over database schema changes. While it requires a more explicit definition of changes, it requires developers with precise control over how and when schema modifications occur. This approach excels in situations where strict version control of database schema is crucial, especially in large teams or complex applications where coordinating database changes across different environments is critical.

Pros:

  • Fine-grained control over database changes.
  • Ability to perform complex schema modifications and data migrations.
  • Clear history of database changes through version-controlled migration files.

Cons:

  • More time-consuming for simple schema changes.
  • Requires more boilerplate code for each database modification.

Convenience for Frequent Schema Adjustments vs. Complex Migration Management

This is another philosophy in database management. While Frappe’s doctype system emphasizes convenience for frequent schema adjustment, Laravel’s migration system excels in managing complex migrations. These approaches cater to different development scenarios and project requirements.

Frappe’s DocType System:

Frappe’s doctype system is designed for agility and ease of use, particularly beneficial in rapidly evolving projects.

Pros:

  • Ideal for projects with evolving schemas and frequent adjustments.
  • Reduces the risk of migration conflicts in collaborative environments.
  • Simplifies the development process for non-database experts.

Cons:

  • May become challenging to manage in large, complex systems with intricate data relationships.
  • Less suitable for projects requiring strict control over database evolution.

Laravel’s Migration System:

This provides fine grained control over database schema changes which is particularly valuable in complex, large scale applications.

Pros:

  • Excellent for managing complex database evolutions over time.
  • Provides a clear audit trail of all database changes.
  • Allows for precise control over the timing and order of schema modifications.

Cons:

  • Can be overkill for simple projects or rapid prototyping.
  • Requires more effort to maintain and organize migration files as the project grows.

Choosing the best tool for the job

In the world of web development, one size rarely fits all. This is particularly true when it comes to database management approaches in Frappe and Laravel. As we’ve explored their philosophies and features, it becomes clear that choosing between them isn’t about determining which is universally better, but rather about identifying which tool is best suited for your specific project and team.

Choose Frappe’s DocType system if:

  • You need rapid development and frequent schema changes.
  • Your team includes members who are less comfortable with database management.
  • The project benefits from automatic schema updates and simpler version control.

Choose Laravel’s migration system if:

  • You require fine-grained control over database schema evolution.
  • Your project involves complex data migrations or schema changes.
  • You need a clear, version-controlled history of all database modifications.

The following diagram gives a brief overview of the factors that you can use when choosing between the two platforms to help support your arguments.: A diagram of a diagram

Description automatically generated

You can see the largest factor is how often you need to change your systems, and how much control you will need over your schema. The left/blue side represents Frappe’s DocType system, while the right/orange is Laravel’s migration system. Which you choose will ideally depend on the specific needs of your project, and hopefully these factors will help you decide..

Conclusion

Frappe’s approach offers superior convenience for projects with frequently changing schemas and a focus on rapid development. Laravel’s migration system, on the other hand, provides more significant control and flexibility for managing complex database evolutions over time, making it more suitable for large-scale, long-term projects with intricate data structures.

The best choice depends on your project’s specific requirements, your team’s expertise, and the expected trajectory of your application’s database needs over time.

Article tags

Load comments

About the author

Goodness Woke

See Profile

Goodness is a professional and enthusiastic writer and equally a software developer. In any space she finds herself, whether writing words or codes she is usually keyed into the aim for excellence. Asides her hard skills, kindness is her default setting.