SQL-Sync generates SQL migration code from a declarative JSON schema object that describes tables, views, routines, users, and permissions.

Use it to produce repeatable database deployment scripts online, from the command line, or inside CI/CD without maintaining a long migration history.

• The input is human readable and version control friendly.
• It can be integrated to CI/CD pipelines very easily.
• It does not require maintaining migration history.
• It generates output without access to the actual database.
• It supports managing the users and their permissions as well.
• It upgrades databases that are behind on schema changes in a single run.

All managed sections are optional. Include only the parts you want SQL-Sync to manage.

1. The root schema object describes the target database and optional managed sections.
   Schema description

   Input

   Schema receives one JSON schema object. The object contains the database name and optional schema sections, and Schema::replicate_sql() generates the SQL.

   Field Name	Required	Type	Description
   name	Yes	string	The database/schema name
   tables	No	array or null	The array of table objects
   views	No	array or null	The array of view objects
   routines	No	array or null	The array of routine objects
   users	No	array or null	The array of user objects

   Example:

   {
     "name": "demo",
     "tables": null,
     "views": null,
     "routines": null,
     "users": null
   }

   Optional section behavior is intentional and consistent:

   • An omitted section or a section set to null is ignored.
   • A section set to [] is reconciled as empty, so existing database objects for that section may be removed. The users section is different: MySQL accounts are server-level objects, so empty users removes database permissions for users with grants on this database; it does not drop user accounts.

   The report flag and dry-run flag are still separate function arguments.

2. Fill the tables section with table objects when you want to manage tables. Tables own columns, indexes, and foreign keys.
   Table description

   Table

   A table is an object that has the following fields:

   Field Name	Required	Type	Description
   id	Yes	string	A GUID generated solely for this table
   name	Yes	string	The name of the table
   engine	No	string	The engine of the table
   columns	Yes	array	The array of the column objects
   keys	No	array	The array of the key objects
   foreign-keys	No	array	The array of the foreign-key objects

   Example:

   {
       "name": "user",
       "id": "93B099B08D144B40BCC918FA24831669",
       "engine": "InnoDB",
       "columns": [
       ],
       "keys": [
       ],
       "foreign-keys": [
       ]
   }

   Column

   A column is an object that has the following fields:

   Field Name	Required	Type	Description
   id	Yes	string	A GUID generated solely for this column
   name	Yes	string	The name of the column
   type	Yes	string	The type of the column
   auto	No	boolean	The column is auto generated or no
   null	No	boolean	The column accepts null values or no
   default	No	string	The default value for the column

   Example:

   {
       "id": "76AC03C95026487AB55A590C48FE4C8F",
       "name": "id",
       "type": "int unsigned",
       "auto": true,
       "null": false,
       "default": ""
   }

   Key

   A key is an object that has the following fields:

   Field Name	Required	Type	Description
   name	Yes	string	The name of the key
   type	Yes	string	The type of the key
   columns	Yes	array	The name of the columns of the key

   Example:

   {
       "name": "PRIMARY",
       "type": "primary key",
       "columns": [
           "id"
       ]
   }

   Foreign Key

   A foreign key is an object that has the following fields:

   Field Name	Required	Type	Description
   name	Yes	string	The name of the foreign key
   delete	Yes	string	The delete option of the foreign key
   update	Yes	string	The update option of the foreign key
   columns	Yes	array	The name of the columns of the key
   table	Yes	string	The name of the foreign table
   keys	Yes	array	The name of columns in the foreign table

   Example:

   {
       "name": "fk_member_user",
       "delete": "RESTRICT",
       "update": "RESTRICT",
       "columns": [
           "user"
       ],
       "table": "user",
       "keys": [
           "id"
       ]
   }

3. Fill the views section with view objects containing a name and body when you want to manage views.
   View description

   View

   A view is an object containing the view name and the SQL body after AS. Put the SELECT command in body without a trailing semicolon. Sqlr adds CREATE OR REPLACE VIEW, qualifies the view name with the target database/schema, appends AS, and terminates the generated view statement with a semicolon.

   Example:

   [
       {
           "name": "project_account",
           "body": "SELECT `project`.`id`, `project`.`name` FROM `project`"
       }
   ]

4. Fill the routines section with routine objects containing type, name, and definition when you want to manage routines.
   Routine description

   Routine

   A routine is an object containing type, name, and definition. type must be FUNCTION or PROCEDURE. definition starts immediately after the routine name, usually with the parameter list. Sqlr adds the CREATE FUNCTION or CREATE PROCEDURE prefix and qualifies the name with the target database/schema while generating SQL.

   Example:

   [
       {
           "type": "FUNCTION",
           "name": "double_value",
           "definition": "(`input_value` int) RETURNS int DETERMINISTIC NO SQL BEGIN RETURN input_value * 2; END"
       }
   ]

   [
       {
           "type": "PROCEDURE",
           "name": "set_value",
           "definition": "(IN `input_value` int, OUT `output_value` int) MODIFIES SQL DATA BEGIN SET output_value = input_value; END"
       }
   ]

5. Fill the users section with user objects when you want to manage users. Each user has a name and a permissions array.
   User description

   User

   A user is an object that has the following fields:

   Field Name	Required	Type	Description
   name	Yes	string	The username
   permissions	Yes	array	The array of the permission objects

   Example:

   {
     "name": "Alice",
     "permissions": [
     ]
   }

   Permission

   A permission is an object that has the following fields:

   Field Name	Required	Type	Description
   type	Yes	string	The type of subject: table, function, or procedure
   subject	Yes	string	The name of the table/view, function, or procedure
   operations	Yes	array	The array of the operations that user is allowed to do on the subject

   Example permission for table:

   {
     "type": "table",
     "subject": "user",
     "operations": [
       "SELECT",
       "INSERT",
       "UPDATE",
       "DELETE"
     ]
   }

   Example permission for function:

   {
     "type": "function",
     "subject": "active_user_count",
     "operations": [
       "EXECUTE"
     ]
   }

1. This is a complete schema.json for the database:
   Schema example

   { "name": "sales", "tables": null, "views": null, "routines": null, "users": null }
2. This is the matching tables.json section example:
   Tables example

   [ { "name": "user", "id": "93B099B08D144B40BCC918FA24831669", "columns": [ { "name": "id", "id": "76AC03C95026487AB55A590C48FE4C8F", "type": "int unsigned", "auto": true }, { "name": "email", "id": "9B1B7A7BC7CF49B49A45E48E55B02669", "type": "varchar(255)", "null": true }, { "name": "password", "id": "410FE4351CD34E61AF4DAFE2B7AB7FE4", "type": "varchar(255)" } ], "keys": [ { "name": "PRIMARY", "type": "primary key", "columns": [ "id" ] }, { "type": "unique index", "name": "unique_email", "columns": [ "email" ] } ] }, { "name": "project", "id": "3D9C2B4ED6BA4AE39D3333FC5BBCC1FF", "columns": [ { "name": "id", "id": "7EA682D7A35E4398A7C782C5F177F4D8", "type": "int unsigned", "auto": true }, { "name": "name", "id": "C0D24606E108403D92397A3C0AEE219E", "type": "varchar(255)" }, { "name": "summary", "id": "0899EAFFFE7A41C7A9876DDDE0E01373", "type": "varchar(255)", "default": "\"\"" }, { "name": "stage", "id": "7E228CCA41014B51A73A444EEE34AD92", "type": "int unsigned" }, { "name": "round", "id": "16380EA4B54B4E8BB3CF78F36BC51AA2", "type": "int unsigned" }, { "name": "archived", "id": "ECF2AAA32B1A455595F7AF087089C005", "type": "tinyint(1)" } ], "keys": [ { "name": "PRIMARY", "type": "primary key", "columns": [ "id" ] }, { "name": "unique_name", "type": "unique index", "columns": [ "name" ] } ] }, { "name": "member", "id": "FC94A0BB4E9B422ABC399BBF79E6AC43", "columns": [ { "name": "id", "id": "197EF5B6132D4FC5A284EC950999BFD4", "type": "int unsigned", "auto": true }, { "name": "project", "id": "4426A25349774BE6A296440E10CD42BB", "type": "int unsigned" }, { "name": "user", "id": "0FB8B46DDF0348FBB5459592689E71C8", "type": "int unsigned", "null": true }, { "name": "admin", "id": "A6643F4E00B24616A21CB88193ABDAE8", "type": "tinyint(1)" }, { "name": "nickname", "id": "F27DEAD22DBB4256BA9B0B04147411EE", "type": "varchar(50)" }, { "name": "pin", "id": "B278231268FA4DBF8EF910285AA64776", "type": "int unsigned" } ], "keys": [ { "name": "PRIMARY", "type": "primary key", "columns": [ "id" ] }, { "name": "unique_member", "type": "unique index", "columns": [ "project", "user" ] }, { "name": "unique_name", "type": "unique index", "columns": [ "project", "nickname" ] } ], "foreign-keys": [ { "name": "fk_member_project", "delete": "RESTRICT", "update": "RESTRICT", "columns": [ "project" ], "table": "project", "keys": [ "id" ] }, { "name": "fk_member_user", "delete": "RESTRICT", "update": "RESTRICT", "columns": [ "user" ], "table": "user", "keys": [ "id" ] } ] } ]
3. This is the matching views.json section example:
   Views example

   [ { "name": "project_account", "body": "SELECT `project`.`id`, `project`.`name`, `user`.`email` FROM `project` JOIN `member` ON `member`.`project` = `project`.`id` JOIN `user` ON `user`.`id` = `member`.`user`" } ]
4. This is the matching routines.json section example:
   Routines example

   [ { "type": "FUNCTION", "name": "active_project_count", "definition": "() RETURNS int DETERMINISTIC READS SQL DATA BEGIN RETURN (SELECT COUNT(*) FROM project WHERE archived = 0); END" }, { "type": "PROCEDURE", "name": "archive_project", "definition": "(IN `project_id` int unsigned) MODIFIES SQL DATA SQL SECURITY INVOKER BEGIN UPDATE project SET archived = 1 WHERE id = project_id; END" } ]
5. This is the matching users.json section example:
   Users example

   [ { "name": "alice", "permissions": [ { "subject": "project", "operations": [ "SELECT", "INSERT", "UPDATE", "DELETE" ], "type": "table" }, { "subject": "user", "operations": [ "SELECT", "INSERT", "UPDATE", "DELETE" ], "type": "table" }, { "subject": "member", "operations": [ "SELECT", "INSERT", "UPDATE", "DELETE" ], "type": "table" }, { "subject": "project_account", "operations": [ "SELECT" ], "type": "table" }, { "type": "function", "subject": "active_project_count", "operations": [ "EXECUTE" ] } ] } ]

This is the generated output either from the command line or the online converter:

1. Single schema file, no dry-run, generated as SQL output.
   Single schema command

   curl -s \ -H "Content-Type: application/json" \ --data-binary @schema.json \ ${server}
2. Multiple input files merged into one schema request before posting.
   Separate files command

   printf '%s' '{ "name": "sales", "tables": '"$(cat tables.json)"', "views": '"$(cat views.json)"', "routines": '"$(cat routines.json)"', "users": '"$(cat users.json)"' }' | curl -s \ -H "Content-Type: application/json" \ --data-binary @- \ ${server}
3. Report and dry-run generated as SQL output.
   Dry-run report command

   curl -s \ -H "Content-Type: application/json" \ --data-binary @schema.json \ "${server}?report=1&dry-run=1" > dry-run-report.sql

Choose dry-run and report based on what you want the generated SQL to do:

• dry-run on, report off: validate the input only.
• dry-run on, report on: validate the input and print the planned commands. Use this as the first pass when you want to review changes before applying them; it is especially required for schemas with routines.
• dry-run off, report off: apply changes. The output shows only command results.
• dry-run off, report on: apply changes. The output includes the executed commands too.

1. For regular generated SQL, review the output and run it with mysql when ready. mysql < result.sql
2. For report and dry-run output, first run the generated report script through mysql to extract the planned commands: mysql -N -B --raw < dry-run-report.sql > deploy.sql Review deploy.sql, then apply it with mysql: mysql < deploy.sql
3. Update the password of the newly created users and unlock their accounts. ALTER USER 'alice' IDENTIFIED BY 'strong-password' ACCOUNT UNLOCK; When a new user is created, their account is locked by default and managing the passwords is left to database administrators.

SQL-Sync does not remove users. When a user is absent from the input, the tool only removes that user's permissions from the current database.