sqlbundler

SQL script bundler for MySQL — merge table init scripts and versioned patch scripts into deployable SQL files.

将分散的 SQL 脚本（建表、初始化数据、版本增量更新）合并为可直接部署的 init.sql 和 patch.sql，专为 MySQL 数据库版本管理设计。

Installation / 安装

npm install --save-dev sqlbundler

Quick Start / 快速开始

1. Project structure / 基础项目结构

your-project/
├── package.json             # version is auto-read by sqlbundler from package.json
├── sqlbundler.config.js     # Config file
├── src/
│   ├── init/
│   │   ├── table/           # Table creation scripts (*.sql)
│   │   └── data/            # Initial data scripts (*.sql)
│   └── versions/            # Versioned patch directories
│       ├── 1.0.0/
│       │   ├── schema.sql   # DDL for this version
│       │   └── data.sql     # DML for this version (optional)
│       ├── 1.1.0/
│       │   └── schema.sql
│       └── ...

• src/init is the initial script directory — scripts here are bundled into a single init.sql file for initializing an empty database.
• src/versions is the version iteration directory — scripts here are bundled into a patch.sql file for updating a production database.
• The file structure inside src is not fixed; the above is just the default layout. You can configure your own structure in sqlbundler.config.js.
• The bundled output files are placed in the auto-generated dist directory by default.

2. Config file / 配置文件 (sqlbundler.config.js)

module.exports = {
    // version: "1.0.0",        // optional, auto-read from package.json if omitted
    remark: "Release notes...",  // optional remark written into version view
    patch: {
        versions: [
            "1.0.0",
            { version: "1.1.0", files: ["schema.sql", "data.sql"] },
            "1.2.0",
        ]
    }
}

patch.versions specifies which version iterations will be bundled into the final patch.sql file.

3. Build / 构建

# Production build / 生产构建
npx sqlbundler-build

# With custom config / 使用自定义配置
npx sqlbundler-build --config=sqlbundler.config.dev.js

Or configure it in package.json and run with npm run:

{
  "name": "your_project",
  "version": "1.0.0",
  "description": "",
  "scripts": {
    "build": "sqlbundler-build",
    "build-dev": "sqlbundler-build --config=sqlbundler.config.dev.js",
  },
  ...
}

Output:

• dist/init.sql — full database initialization script
• dist/patch.sql — incremental update script with version checks

4. Dev database / 开发数据库

# Apply patch to dev database / 应用增量更新到开发库
npx sqlbundler-dev --which=patch --config=sqlbundler.config.dev.js

# Full init / 全量初始化
npx sqlbundler-dev --which=init --config=sqlbundler.config.dev.js

# Drop dev database / 删除开发库
npx sqlbundler-dev --clean --config=sqlbundler.config.dev.js

Or configure it in package.json and run with npm run:

{
  "name": "your_project",
  "version": "1.0.0",
  "description": "",
  "scripts": {
    "build": "sqlbundler-build",
    "build-dev": "sqlbundler-build --config=sqlbundler.config.dev.js",
    "dev": "sqlbundler-dev --which=patch --config=sqlbundler.config.dev.js",
    "dev-init": "sqlbundler-dev --which=init --config=sqlbundler.config.dev.js",
    "dev-clean": "sqlbundler-dev --clean --config=sqlbundler.config.dev.js",
  },
  ...
}

CLI Commands / 命令行

sqlbundler-build

| Argument | Description | |----------|-------------| | --config=<path> | Custom config file path (default: sqlbundler.config.js) |

sqlbundler-dev

| Argument | Description | |----------|-------------| | --config=<path> | Custom config file path (default: sqlbundler.config.js) | | --which=init|patch | Run init or patch (default: patch) | | --clean | Drop the dev database only |

Configuration Reference / 配置参考

| Key | Type | Default | Description | |-----|------|---------|-------------| | version | string | auto-read from package.json | Target database version | | remark | string | "" | Remark attached to the version view | | init.files | string[] | ["./src/init/table/*.sql", "./src/init/data/*.sql"] | Glob patterns for init SQL files | | init.target | string | "init.sql" | Output filename for init script | | patch.path | string | "./src/versions" | Directory containing versioned patch directories | | patch.versions | (string|{ version:string, files:string[] })[] | [] | Version list to include (ordered) | | patch.files | string[] | ["schema.sql", "data.sql"] | Default filenames to look for in each version dir | | patch.target | string | "patch.sql" | Output filename for patch script | | output.path | string | "./dist" | Output directory | | output.clean | boolean | true | Clean output directory before build | | output.charset | string | "utf-8" | Output file charset | | dev.* | — | — | Dev database connection config (see below) |

Dev database config / 开发数据库配置

dev: {
    user: "root",                  // MySQL user
    password: "",                  // MySQL password
    db: "myapp_dev",               // Dev database name
    recover: {
        user: "",                  // Recovery source user (defaults to dev.user)
        password: "",              // Recovery source password (defaults to dev.password)
        db: "",                    // Recovery source database (empty = use dump file)
        path: "./dev/recover.sql", // Recovery dump file path
        cache: true                // Cache the recovery dump file
    },
    execBefore: ["./dev/before.sql"],  // SQL files to run before update
    execAfter:  ["./dev/after.sql"]    // SQL files to run after update
}

• The sqlbundler-dev command executes the generated scripts against the local database specified in the dev config.
• If recover is configured, each sqlbundler-dev run restores the local dev database from the source database or recovery script specified in recover.

How It Works / 工作原理

Init SQL generation / 初始化脚本生成

1. Resolve all SQL files matching init.files glob patterns
2. Concatenate them with filename headers
3. Append a version view: CREATE VIEW 'version' AS SELECT '{version}', '{remark}'
4. Write to {output.path}/init.sql

Patch SQL generation / 增量脚本生成

For each version in patch.versions:

1. Resolve SQL files in {patch.path}/{version}/
2. Wrap each version's SQL in a version-check guard:

IF DeployFun_IsLowVersion('{version}') THEN
    -- patch SQL here
END IF;

3. Combine all version guards into a single stored procedure DeployProc_Main()
4. Append the final version view update
5. Write to {output.path}/patch.sql

When deployed, DeployProc_Main() checks the current database version against each patch version and only applies patches that are newer than the current version.

Version Convention / 版本规范

sqlbundler supports semantic versioning format: X.Y.Z or X.Y.Z-N (with pre-release number).

Examples: 1.0.0, 2.3.1-5

The DeployFun_ResolveVersion() function in patch.sql converts version strings into comparable integers for the IsLowVersion() check during deployment.

License / 许可证

MIT