DB Migration is a process used to manage and implement changes to the database of a software application. This process can be used in many scenarios such as database schema updates, data migrations and changes in data storage technologies.
Uses of DB Migration
Database Schema Changes:
Adding new columns.
Rename existing tables.
Creating new indexes.
Making edits to the table structure.
Database Restructuring:
Changing the database management system used by the application (for example, from SQL to NoSQL).
Changing the database to a more optimized structure.
Data Migration:
Maintaining data integrity when moving data from one environment to another.
Prevent data loss and optimize database performance.
DB Migration Operations on Kuika Platform
Kuika platform offers a streamlined process to manage DB Migration operations in a systematic way. You can follow the steps below to perform these operations:
Start DB Migration Process:
Click on the Rocket icon in the Header.
Click on Download Source Code or Publish button in the window that opens.
Generated backend and frontend codes will be sent to you via e-mail.
You need to upgrade your subscription plan to download the source code. Visit the Pricing Page for more information.
Backend Codes and SQL Scripts:
Backend codes contain the SQL scripts required for the database that the application is connected to.
SQL scripts:some text
It enables the creation of structures such as foreign key, primary key and index in tables in other databases.
It creates the database structures required for the application to run on-premise or in a different environment.
Running Scripts:
The generated SQL scripts are embedded in the backend files but are not executed automatically.
These scripts can be run using the DB Migration API.
DB Migration APIs
On the Kuika platform, the following APIs are used during DB Migration operations:
1. Checksum API:
GET /v1/api/DbMigration/checksum/current
Represents the current state of the database. This represents the current schema and data structure.
GET /v1/api/DbMigration/checksum/source
Refers to the source database or schema in the DB Migration process. This represents the data where the changes will be made.
2. IsSame (Comparison):
The “IsSame” API compares the differences between the current database schema or data and the target database schema or data. This process determines which changes are required.
3. Execute Migration:
The Execute Migration API runs SQL scripts sequentially to implement the determined changes.
In the Swagger documentation, this API is located under DbMigration/executeMigration.
Details of the DB Migration Process
Analysis of Database Schema and Data Status:
Current: Current database schema and data status.
Source: Source database schema and data state.
Comparison (IsSame):
The current state and the source state are compared and differences are detected.
Execute Migration Scripts (ExecuteMigration):
SQL scripts are executed in order and the target database structure is created.
Each table or column is assigned a UNIQUE ID and comparisons are made over these IDs.
Result:
The database schema and data are transformed into the target structure.
Features of SQL Scripts
SQL scripts perform the following operations in the database structure:
Create new tables.
Update existing tables.
Adding or removing indexes.
Assigning and comparing IDs to tables and columns.
Checksum:
A unique digest of each table and column in the database.
Rename Operations: If the name of a column or table has been changed during the comparison, this difference is detected and the script is edited accordingly.