database-schema-operator's Introduction

Database Migration Schema Operator

This is a DBaaC (Database as a Code) using Kubernetes Operator pattern.

There will be some CRDs

Database
Table

Roadmap

Sample Database:

apiVersion: db.pedag.io/v1
kind: Database
metadata:
  name: adventureworks
spec:
  name: AdventureWorks
  server: localhost
  port: 1433
  user: sa
  password: itssecret

Sample Table:

apiVersion: db.pedag.io/v1
kind: Table
metadata:
  name: users-table
spec:
  name: Users
  databaseRef:
    name: adventureworks
    kind: Database
  columns:
  - name: Id
    type: int not null identity(1,1)
  - name: Name
    type: varchar(50) not null
  - name: Email
    type: varchar(255) not null
  - name: Active
    type: bit not null default(0)
  - name: Blocked
    type: bit not null default(0)

Every Table must have a Database reference.

Database will be something like SQLDatabase, PostgresDatabase, MySQLDatabase, etc.

Not sure if we will use any orm (like xorm.io) or pure SQL.

Controller logs:

2020-08-06T15:48:53.625-0300	INFO	controller-runtime.metrics	metrics server is starting to listen	{"addr": ":8080"}
2020-08-06T15:48:53.626-0300	INFO	setup	starting manager
2020-08-06T15:48:53.626-0300	INFO	controller-runtime.manager	starting metrics server	{"path": "/metrics"}
2020-08-06T15:48:53.626-0300	INFO	controller-runtime.controller	Starting EventSource	{"controller": "table", "source": "kind source: /, Kind="}
2020-08-06T15:48:53.626-0300	INFO	controller-runtime.controller	Starting EventSource	{"controller": "database", "source": "kind source: /, Kind="}
2020-08-06T15:48:53.727-0300	INFO	controller-runtime.controller	Starting Controller	{"controller": "database"}
2020-08-06T15:48:53.727-0300	INFO	controller-runtime.controller	Starting workers	{"controller": "database", "worker count": 1}
2020-08-06T15:48:53.727-0300	INFO	controller-runtime.controller	Starting Controller	{"controller": "table"}
2020-08-06T15:48:53.727-0300	INFO	controller-runtime.controller	Starting workers	{"controller": "table", "worker count": 1}
2020-08-06T15:48:53.739-0300	DEBUG	controller-runtime.controller	Successfully Reconciled	{"controller": "database", "request": "default/adventureworks"}
2020-08-06T15:48:53.781-0300	INFO	controllers.Table	Creating table [Addresses] on database.	{"table": "default/addresses-table"}
2020/08/06 15:48:53 create table [Addresses] ([Street] varchar(50),[Number] varchar(50),[UserId] int,)
2020-08-06T15:48:53.810-0300	DEBUG	controller-runtime.controller	Successfully Reconciled	{"controller": "table", "request": "default/addresses-table"}
2020-08-06T15:48:53.853-0300	INFO	controllers.Table	Creating table [Users] on database.	{"table": "default/users-table"}
2020/08/06 15:48:53 create table [Users] ([Id] int not null identity(1,1),[Name] varchar(50) not null,[Email] varchar(255) not null,[Active] bit not null default(0),[Blocked] bit not null default(0),)
2020-08-06T15:48:53.876-0300	DEBUG	controller-runtime.controller	Successfully Reconciled	{"controller": "table", "request": "default/users-table"}

database-schema-operator's People

Contributors

Stargazers

Watchers

database-schema-operator's Issues

Support for migrations like Liquibase

It would be interesting to support a migration mechanism like Liquibase, Flyway, etc...
Including schema versionig

Support to preload scripts

It would be desirable if there could be a mechanism to populate tables with static data

Support to create system_versioning tables in MariaDB

It would be interesting to support the creation of tables of type SYSTEM_VERSIONING including the declaration of partitions.
Perhaps this issue should be broken down into more issues as just creating partitions would require a series of parallel works.
Here is an example of create table SYSTEM_VERSIONING on mariadb:

CREATE TABLE IF NOT EXISTS _operator_.tag_sequence_events (
    toll_company_id     numeric(10)  NOT NULL,
    operator_id         numeric(10)  NOT NULL,
    series              numeric(10)  NOT NULL,
    sequence_id         numeric(19)  NOT NULL,
    event_id            varchar(36)  NOT NULL,
    event_timestamp     varchar(24)  NOT NULL,
    event_source_type   varchar(20)  NOT NULL,
    event_status        varchar(20)  NOT NULL,
    headers             JSON,
        CHECK (headers IS NULL OR JSON_VALID(headers)),
    content_raw         BLOB,
    content_serialized  BLOB,
    PRIMARY KEY (
        toll_company_id,
        operator_id,
        series,
        sequence_id
    )
)
WITH SYSTEM VERSIONING
	PARTITION BY SYSTEM_TIME LIMIT 100000 (
	PARTITION hist_0 HISTORY,
	PARTITION hist_1 HISTORY,
	PARTITION current CURRENT
);

Recommend Projects