EasyDBMigrator

<br />

<a href="https://www.buymeacoffee.com/adehbanr" target="_blank"><img src="https://cdn.buymeacoffee.com/buttons/default-orange.png" alt="Buy Me A Coffee" height="41" width="174"></a> <br /> <img alt="MIT" src="https://img.shields.io/github/license/AliDehbansiahkarbon/EasyDBMigrator"> <img src="https://img.shields.io/github/forks/AliDehbansiahkarbon/EasyDBMigrator" alt="forks"> <img src="https://img.shields.io/github/stars/AliDehbansiahkarbon/EasyDBMigrator" alt="stars"> <img src="https://img.shields.io/github/watchers/AliDehbansiahkarbon/EasyDBMigrator" alt="watchers"> <a href="https://github.com/AliDehbansiahkarbon/EasyDBMigrator/issues"><img src="https://img.shields.io/github/issues-closed/AliDehbansiahkarbon/EasyDBMigrator" alt="issues"></a> <br /> <a href="https://github.com/AliDehbansiahkarbon/EasyDBMigrator/pulls"><img src="https://img.shields.io/github/issues-pr-closed/AliDehbansiahkarbon/EasyDBMigrator" alt="pulls"></a> <img src="https://img.shields.io/github/last-commit/AliDehbansiahkarbon/EasyDBMigrator.svg" alt="last-commit">

<br />

Are you fed up with developing a hundred lines of SQL scripts to keep your database updated/synced with your application?

Here is the solution!

* EasyDbMigrator is a database migration library designed for Delphi.

* It simplifies database evolution and is available in both 32-bit and 64-bit versions. (more or less similar to FluentMigrator in C#, DbUp in C#, Liquibase in Java, or ActiveRecord Migrations in Ruby).

* Migrations are structured objects designed to alter your database schema. They provide an alternative to creating numerous SQL scripts that would require manual execution by every developer involved.

* When dealing with multiple databases, such as the developer's local database, test database, and production database, migrations are a helpful solution for evolving a database schema. These changes to the schema are recorded in Delphi classes, which can then be committed to a version control system.

Covered databases and available examples:

|Name | Simple | Advanced | ORM | LargeScript Execution| |---|---|---|---|---| | Microsoft SQL SERVER | ✅ | ✅ | ✅ | ✅ | | MySQL | ✅ | ✅ | ✅ | ✅ | | MariaDB | ✅ | ✅ | ✅ | ✅ | | PostgreSQL | ✅ | ✅ | - | - | | Oracle | ✅ | ✅ | - | - | | Firebird | ✅ | ✅ | - | - |

Supported Delphi Versions

Delphi XE5
Delphi XE6
Delphi XE7
Delphi XE8
Delphi 10 Seattle
Delphi 10.x (10.1 Berlin, 10.2 Tokyo, 10.3 Rio, 10.4 Sydney)
Delphi 11.x Alexandria
Delphi 12.x Athens

Available on Getit Package Manager-ClickMe!

How does it work?

To use the library, simply incorporate the units into your projects, implement migrations, and execute the migrator. It's that simple.

(If you want to see some demo videos, please visit here or click on the below image👇👇)

<a href="https://www.youtube.com/playlist?list=PLsToBC7EKBNSkD-mwMN18TQbJ-lzistS7" target="_blank"><img src="https://github-production-user-asset-6210df.s3.amazonaws.com/5601608/259202139-98eda93e-6e60-4469-b1ab-d538e371cfd3.png" width = "700" height = "400" /></a>

Can it be utilized with other databases?

Certainly! You can seamlessly integrate it with your environment. Kindly refer to the integration section for more details.

How to use it?

To get started quickly, please take a look at the sample codes provided. These examples showcase how to use the library and include extra details.<br> To use this library, you must add the library paths to your project's search path or Delphi's library path. It is necessary to include lines that are similar to the following:

..\..\..\lib\Logger;
..\..\..\lib\ConnectionManagers;
..\..\..\lib\Core;
..\..\..\lib\Runners;
..\..\..\lib\ORM

There are various kinds of samples available.

• <ins>Simple</ins>: Suitable for small projects (using on-demand classes with anonymous methods).

• <ins>Advanced</ins>: Suitable for large projects (using versioned classes with attributes). Instead of creating some on-demand classes you can create one unit per entity and implement versioned classes.

• <ins>ORM</ins>: Suitable for both small and large projects.

• <ins>Large Script</ins>: This is a script executor that allows you to use your existing old scripts as a starting point and continue working with this library from now on.

Dependencies

To work with certain database engines such as Mysql(libmysql32.dll) or PostgreSQL(libpq.dll), you will need to have the appropriate Dlls. There are no other prerequisite libraries or components required. It is important to ensure that you provide the correct Dll or class library depending on the specific version of the database engine installed in your environment. Please note that this library does not include the necessary DLLs, but rather provides a template for each database engine.

• Note:

A condition definition has been added to provide additional features such as additional overridden constructors and functions. This is to prevent compiler prompts/hints for unused functions, if you want access to all functionalities, you can use "FullOptions" as a conditional definition of your project.

---

I have provided some samples and templates for your reference.👇

• Simple

<details> <summary> 🟢 SQL SERVER Sample </summary> <br> - Initializing

uses
  EasyDB.Core,
  EasyDB.Migration,
  EasyDB.MSSQLRunner,
  EasyDB.Logger;

var
  Runner: TSQLRunner;
  ConnectionParams: TConnectionParams;
begin

  with LvConnectionParams do // Could be loaded from ini, registry, or somewhere else.
  begin
    Server := '127.0.0.1'; // SQL Server address
    LoginTimeout := 30000;
    Username := 'sa';
    Pass := '1';
    DatabaseName := 'Library';
    Schema := 'dbo'; //Optional
  end;

  {Use this line if you need a local log}
  TLogger.Instance.ConfigLocal(True, 'C:\Temp\EasyDBLog.txt').OnLog := OnLog; // Logger must be configured before creating the Runner.

  {Use this line if you don't need a local log}
  // TLogger.Instance.OnLog := OnLog;

  Runner := TSQLRunner.Create(LvConnectionParams);
  Runner.AddConfig.LogAllExecutions(True).UseInternalThread(True).SetProgressbar(pbTotal).RollBackAllByAnyError(True); //each part This line is Optional
end

• Add migrations

Runner.MigrationList.Add(TMigration.Create('TbUsers', 202301010001, 'Alex', 'Create table Users, Task Number #2701',
  procedure
  var sql: string;
  begin
    sql := 'If Not Exists( Select * From sysobjects Where Name = ''TbUsers'' And xtype = ''U'') ' + #10
     + '    Create Table TbUsers( ' + #10
     + '    	ID Int Primary key Identity(1, 1) Not null, ' + #10
     + '    	UserName Nvarchar(100), ' + #10
     + '    	Pass Nvarchar(50) ' + #10
     + '    );';

    Runner.SQLConnection.ExecuteAdHocQuery(sql);
  end,
  procedure
  begin
    Runner.SQLConnection.ExecuteAdHocQuery('DROP TABLE TbUsers');
  end
  ));

  //============================================
  Runner.MigrationList.Add(TMigration.Create('TbUsers', 202301010002, 'Ali', 'Task number #2701',
  procedure
  begin
    Runner.SQLConnection.ExecuteAdHocQuery('ALTER TABLE TbUsers ADD NewField2 VARCHAR(50)');
  end,
  procedure
  begin
    Runner.SQLConnection.ExecuteAdHocQuery('ALTER TABLE TbUsers DROP COLUMN NewField2');
  end
  ));

  //============================================

  Runner.MigrationList.Add(TMigration.Create('TbUsers', 202301010003, 'Ali', 'Task number #2702',
  procedure
  begin
    Runner.SQLConnection.ExecuteAdHocQuery('ALTER TABLE TbUsers ADD NewField3 INT');
  end,
  procedure
  begin
    Runner.SQLConnection.ExecuteAdHocQuery('ALTER TABLE TbUsers DROP COLUMN NewField3');
  end
  ));

  //============================================

  Runner.MigrationList.Add(TMigration.Create('TbCustomers', 202301010003, 'Alex', 'Task number #2702',
  procedure
  var sql: string;
  begin
    sql := 'If Not Exists( Select * From sysobjects Where Name = ''TbCustomers'' And xtype = ''U'') ' + #10
     + '    Create Table TbCustomers( ' + #10
     + '    	ID Int Primary key Identity(1, 1) Not null, ' + #10
     + '    	Name Nvarchar(100), ' + #10
     + '    	Family Nvarchar(50) ' + #10
     + '    );';
    Runner.SQLConnection.ExecuteAdHocQuery(sql);
  end,
  procedure
  begin
    Runner.SQLConnection.ExecuteAdHocQuery('DROP TABLE TbCustomers');
  end
  ));

//...
//Add other migrations here
//...

• Run the Migrator

  Runner.UpgradeDatabase; // Do upgrade
  // Or 
  Runner.DowngradeDatabase(202301010001); // Do downgrade to a specific version.
  //This version and lower versions of the database will remain and any version above this will be reverted.

</details> <details> <summary> 🟠 MySQL Sample </summary> <br> - Initializing

uses
 EasyDB.Core,
 EasyDB.Logger,
 EasyDB.Migration,
 EasyDB.MySQLRunner;

var
 LvConnectionParams: TMySqlConnectionParams;
begin
 with LvConnectionParams do // Could be loaded from ini, registry, or somewhere else.
 begin
   Server := '127.0.0.1';
   LoginTimeout := 30000;
   Port := 3306;
   Username := 'ali';
   Pass := 'Admin123!@#';
   Schema := 'Library';
 end;

 Runner := TM