There is a local repo git which is deployed to the hosting, how can you also deploy the database?

    2 answers 2

    There is no such thing as a "database deployment". Either the entire database is restored from the backup (this operation is performed by system administrators during the initial deployment of the site, or when moving the production server to another location, less often when the site is restored, then you can restrict yourself to only a few tables) or change the data scheme.

    The above was an example for C # based on the Entity Framework , similar frameworks exist in other programming languages.

    For example, in PHP you can use phinx . Installed via composer , easy to configure and use.

    New migration is created using the command

     php vendor/bin/phinx create MyNewMigration

    Each migration contains a method for applying changes and reversing them, for example:

     use Phinx\Migration\AbstractMigration; class ExternalAuthentification extends AbstractMigration { public function up() { $storage = $this->table('ai_storage',array('id' => 'ID')); $storage ->addColumn('CREATED', "timestamp", array("default" => "CURRENT_TIMESTAMP")) ->addColumn('REQUEST_TOKEN', 'string', array('limit' => 256)) ->addColumn('REQUEST_SECRET', 'string', array('limit' => 256)) ->addColumn('REQUEST_VERIFY', 'string', array('limit' => 256)) ->addColumn('SESSION_ID', 'string', array('limit' => 63)) ->save(); } public function down() { $this->dropTable('ai_storage'); } }

    (Migrations can be not only at the level of creating tables, but also manipulating individual columns)

    Then you either apply the migration

     phinx migrate -e development

    or (less often) roll back)

     phinx rollback -e development

    And now all these migrations are stored in the git-repository, along with other project code.

      As a rule, no one copies the database from one server to another, including because there are test data in the developers database, and real data in the combat database, which cannot be copied.

      What developers really need is to make changes to the database schema and upload reference data (a list of countries, telephone codes of cities, etc.). The migration mechanism is used for this.

      Under different platforms, this can be done in different ways. From my experience I will tell you how this is done in the Entity Framework.

      Firstly, to avoid duplication, the database structure is not stored as DDL scripts ( CREATE TABLE and others), but as .NET classes.

       [Table("Users")] public class User { [Key] public Guid Id { get; set; } public string Login { get; set; } [MaxLength(512)] public byte[] PasswordHash { get; set; } }

      All tables that are included in the database are listed in a dedicated class, which is called the database context :

       public class BlogDbContext : DbContext { public DbSet<User> Users { get; set; } }

      At the beginning of work we have to create the first database copy for work (first migration). In essence, the Entity Framework code explores BlogDbContext and User and prepares scripts to generate the Users table and the necessary indices. To create a migration, use the PowerShell-command Add-Migration .

       Add-Migration Initial

      The script will look something like this:

       public partial class Initial : DbMigration { public override void Up() { CreateTable( "dbo.Users", c => new { Id = c.Guid(nullable: false), Login = c.String(nullable: false), PasswordHash = c.ByteArray(maxlength: 512), }) .PrimaryKey(t => t.Id); } public override void Down() { DropTable("dbo.Users"); } }

      As you can see, the script includes two branches: to move forward in the list of migrations ( Up method) and to roll back ( Down method).

      Now, note, the database schema is included in the rest of the project source code and is distributed with it.

      If we need to make changes to the schema, we do this, for example, add a new table class, or a new field, and run the Add-Migration again. The Entity Framework again generates a class that updates the schema. In most cases, you do not need to manually edit the code, but you can do it, for example, if you need a non-standard index.

      Now what? You publish your code on the battle server. The next time you access your server, the schema is checked, and, if necessary, all updates of the schema are rolled transparently (methods Up are called).

      As a result, the database is updated along with the rest of the code, and, by the way, not only on the combat server, but also between the developers.