Database Migrations with API Maker

Effortless Multiple Database Types Migrations with API Maker

The Importance of Migrations

• Database migrations – the process of gradually upgrading database structure by writing SQL scripts or code, are essential whenever your application evolves. For example, adding a new feature often requires altering tables or columns in your databases or inserting new data. API Maker streamlines this by letting you write migration scripts in TypeScript (Node.js) with native SQL. These scripts can be run on demand to bring any database to the required schema state.

• When deploying code across development, staging, and production, keeping every database in sync is critical. Each update or new feature might need new tables or columns, and failing to apply those changes everywhere can break your app.

• Database migrations solve this by treating schema changes like version-controlled code. As API Maker’s documentation notes, migration scripts “bring databases to a specific stage” only if they aren’t already there.

• In other words, the system checks the current state and applies only the needed updates. This ensures that each environment – from a single developer’s sandbox to the live database – ends up with the correct structure.

API Maker’s Migration Features

API Maker’s migration tool is built for developers. Some of the key features include:

• Combined Node.js + SQL scripting: Write migrations in JavaScript code that can run raw SQL commands. This leverages developers’ existing Node.js skills without requiring a new DSL or tool.
• Multi-Database Support: A single migration script can target and update multiple database instances or types (MySQL, PostgreSQL, MongoDB, SQL Server, Maria DB, Oracle DB, TiDB, Percona XtraDB.) at once. For example, the same script can migrate MongoDB and MySQL databases.
• Condition-based Updates: Each script can check the current schema and apply only necessary changes. This “idempotent” style ensures that rerunning the migration won’t break anything – it simply detects if an update (like adding a column) is needed, and skips it if it’s already done.
• Integrated Testing UI: Developers get a built-in interface to write and test migration scripts. Each developer can use their own database, so experimenting with changes never affects other developer's work or database state.
• Unlimited Scripts & History: You can create, update, and delete any number of migration scripts with no hard limit. API Maker even lets you copy-paste SQL from database GUI tools, so switching to this system is frictionless.
• Manage Huge Size Of Migration Script: No worries, you can dynamically import that SQL script and run it step by step in API Maker's migration scripts.

These features mean teams and technical stakeholders get a reliable, repeatable process. Business teams benefit because migrations become part of the deployment workflow, database updates happen automatically when code is deployed (for example, on a Git pull) rather than through manual steps.

How It Works

Under the hood, an API Maker migration is essentially a Node.js function. The function uses API Maker’s system APIs to inspect and modify the database. For example, the code below (based on the API Maker docs) shows a simple migration that adds a description column if it’s missing:

import * as T from 'types';
import * as db from 'db-interfaces';
import * as _ from 'lodash';

async function main(g: T.IAMGlobal) {
    let order_transactions_meta = await g.sys.system.getTableMeta({
        instance: 'mysql8',
        database: 'inventory',
        table: 'order_transactions'
    });
    let hasDescriptionColumn = !!_.find(
        order_transactions_meta,
        { name: 'description' }
    );
    let isAlterTableScriptExecuted = false;

    if (!hasDescriptionColumn) {
        await g.sys.system.executeQuery({
            instance: 'mysql8',
            query: "ALTER TABLE `inventory`.`order_transactions`" +
                " ADD COLUMN `description` varchar(255) NULL AFTER `qty`"
        });
        isAlterTableScriptExecuted = true;
    }

    return { hasDescriptionColumn, order_transactions_meta };
};
module.exports = main;

• In this example, first we get meta data of current table using inbuilt function g.sys.system.getTableMeta provided by API Maker then we check if the order_transactions table already has a description column.
• If it doesn’t exist, it adds a new description column.
• Finally, it returns whether the column was already there and the original table metadata.

Getting Started migrations

• To use migrations in API Maker, you simply create a new Migration Script in the platform’s admin UI. Each script will be committed in git repository and can be executed on demand. You’ll find a list of saved scripts in the UI, and one-click “run script” options. The system will show you the results (success or errors) immediately.

• For businesses, this means database changes become auditable and repeatable. Instead of ad-hoc updates, every schema change is tracked as code. Technical team members can review that code or have confidence it runs the same way everywhere. Because API Maker lets you “maintain state of database” across environments, you get fewer deployment headaches and downtime.

• API Maker’s database migrations feature automates schema management: it uses familiar JavaScript/SQL scripts, supports all major databases, and provides a visual interface for testing. It keeps your production and development databases in sync effortlessly. Whether you’re a developer or a project manager, this means faster releases and more reliable updates with less manual work.

API Maker Migrations vs Other Frameworks Detailed Comparison

Managing database schema changes is a critical part of modern application development. API Maker’s database migration feature compares to others:

Why API Maker Wins for Modern Teams

• Low-code, high-power: Mix (JavaScript | TypeScript) with SQL in a friendly editor.
• Developer can migrate multiple database types in one migration scripts.
• We can migrate multiple installations of one database type in one migration script.
• Fail-safe: We can easily write migration scripts which does not break from middle. It is perfect for long migration scripts.
• Centralized control: Keep DB migrations, APIs, and business logic in one platform.
• No DevOps pain: No servers, no SSH, no cron. Just click and go.

Whether you're a developer, project manager, or running a team, API Maker makes migrations safe, scalable, and simple – without losing flexibility.

Most tools treat migrations as a side project. API Maker treats them as a first-class citizen, integrated into your development workflow, API logic, and UI and we can use common functions of APIs and Migrations together. It's ideal for startups looking to scale or enterprises looking for stability.

Ready to migrate without the migraine? Try API Maker.

Benefits of API Maker’s Database Migrations

• Zero Downtime, Instant Updates

  • Make schema changes without restarting your server or redeploying your app. Just click, apply, and you're done, your API and DB stays in sync.

• Smart & Safe Execution

  • In API Maker using TypeScript programming language support, we can write safe migration scripts which can skip already applied changes and avoids duplicate updates. No more “table already exists” errors!

• Visual Editor for Migrations

  • Create and edit your migration scripts from in built testing environment in web editor. No CLI, no SSH, no terminal, just browser and brains.
  • Many examples are also available, press ctrl + i and insert them.

• Human Readable History Logs

  • Track every migration run with full logs, success/failure status, script preview, timestamps, and user details. Know exactly what changed and when.

• Rollbacks & Version Control

  • Using inbuilt testing environment, you can quickly test, rollback, or revise your migration scripts. Perfect for debugging in dev or QA environments.

• Supports Native SQL + TypeScript

  • Mix raw SQL with TypeScript in the same migration file. Add logic, validations, or dynamic queries as needed.
  • Migrate multiple database types in one migration script.

• Multi-Environment Friendly

  • Migrations are intelligent. We can easily write that type migration script which they’ll only run if needed based on your DB state. So it’s safe to reuse the same scripts across dev, staging, and production.

• Works with Any Database

  • You can manage migrations across multiple databases from one platform.
  • Supports 8 Major Databases. One Unified Tool
    • PostgreSQL
    • MySQL
    • MariaDB
    • Microsoft SQL Server
    • Oracle DB
    • Percona XtraDB
    • TiDB
    • MongoDB

• Enterprise-Ready

  • To handle large data sets of SQL migrations, you can dynamically import that SQL file in API Maker's migration scripts. So API Maker's migrations scales with you, handle large datasets, legacy systems, and critical updates without risk.

No matter what database you're using, API Maker keeps your schema or data changes smooth, secure, and consistent across environments.

FAQ's – Database Migrations in API Maker

1. Can I create tables or alter columns using API Maker?

Yes! You can create new tables, modify existing ones, add or drop columns, change data types, and even manage indexes. You can validate those things are already done or not using native SQL scripts and them actually perform that.

2. Does API Maker support multiple databases for migrations?

Absolutely. API Maker supports 8 major databases: PostgreSQL, MySQL, MariaDB, Microsoft SQL Server, Oracle DB, Percona XtraDB, TiDB, MongoDB . You can write one migration script and it can perform migrations of multiple database types.

3. Do I need to write raw SQL for migrations?

Yes. API Maker is framework for developers so it is expected prerequisite that developer knows programming & basic database concepts and how SQL works.

4. Can I roll back a migration if something goes wrong?

Yes & No. It totally depends on how you have written migration script and what is your business requirement.

5. Can I test my migrations before applying them to production?

Definitely. You can preview, simulate, and test your migrations in a dev or staging environments, then promote the exact same script to production.

6. How are migration changes tracked?

Each migration has its own name. You can view the entire history of applied migrations.

7. Can I view or edit generated SQL?

You need to write migration script manually so you have total control over it.

8. Do I need to redeploy after applying a migration?

No redeployment is needed. Once you apply a migration from the Admin UI, the changes go live instantly on the connected database.

9. Who can manage migrations in API Maker?

Only users with the appropriate permissions can create, edit, or apply migrations.

10. Why should I use API Maker migrations instead of other tools?

• API Maker gives you a centralized and multi-database friendly way to manage migrations with power of TypeScript + Native SQL which you will not find in any other frameworks.
• Plus you can write migration script which gets data from one database type and migrate another database type.
• You can hit any AI or any other third party APIs and based on that also, you can perform migrations.
• So basically these migration scripts are very dynamic and powerful.