Database initialization and migration #16

New Issue

Closed

opened 2022-05-19 16:47:00 +00:00 by skobkin · 1 comment

skobkin commented

2022-05-19 16:47:00 +00:00

Collaborator

We need to have database the code which will:

Initialize the database if it's empty
Migrate the database if it's structure was changed

I propose to add several methods for the database wrapper class:

__initialize(). It's called each time database wrapper is created (from the constructor). In case of SQLite it can create the file if needed and initialize the driver. Then it tries to get current version from the database using __get_current_version. Then it calls __migrate(version) where version is the current version from the database (or None?).
__migrate(). Depending on the version it calls several blocks of statements consequentially to upgrade the database schema. If the current version is 1, it executes only statements for versions 2 and above if they're present.
__get_current_version(). It goes to migrations table and selects the latest version.

Simple example of migrations table:

CREATE TABLE IF NOT EXISTS migrations (
  version INT NOT NULL UNIQUE
)

Selecting current version:

SELECT MAX(version) FROM migrations

Each time database changes are made during the development, new version should be added to the code so other team members could automatically get their database migrated to the latest version.

An example of __migrate() implementation:

class Database:
    conn: SomeDatabase.Connection
    log: SomeLogger.Logger
    
    def __init__(self, log: SomeLogger.Logger, dsn: string):
        self.log = log
    	// Doing some stuff if needed
        // For SQLite probably creating the file and/or checking write permissions?
        // For PostgreSQL most likely nothing
        
        // Initializing connection
        self.conn = SomeDatabase.Connection(dsn)
        
        self.__initialize()
        version = self.__get_current_version()
        self.log
    	self.__migrate(version)
    
    def __get_current_version() -> int:
    	// do version retrieval here
    
    def __migrate(version: int) -> None:
    	if version is None:
            // First migration here
            self.conn.execute('CREATE TABLE blahblah(id INT);')
            self.conn.execute('CREATE TABLE migrations(version INT NOT NULL UNIQUE);')
            self.conn.insert('INSERT INTO migrations VALUES(1)')
            version++
            
        if version <= 1:
            // Second migration
            self.conn.execute('ALTER TABLE blahblah ADD name VARCHAR(32)')
            self.conn.insert('INSERT INTO migrations VALUES(2)')
            version++
            
        // Other migrations if needed
    // It's also a good thing to extract the 'INSERT INTO MIGRATIONS' to a separate method (`def __add_migration(version: int) -> None:` for example)

We need to have database the code which will: * Initialize the database if it's empty * Migrate the database if it's structure was changed I propose to add several methods for the database wrapper class: * `__initialize()`. It's called each time database wrapper is created (from the constructor). In case of SQLite it can create the file if needed and initialize the driver. Then it tries to get current version from the database using `__get_current_version`. Then it calls `__migrate(version)` where `version` is the current version from the database (or `None`?). * `__migrate()`. Depending on the version it calls several blocks of statements consequentially to upgrade the database schema. If the current version is `1`, it executes only statements for versions `2` and above if they're present. * `__get_current_version()`. It goes to `migrations` table and selects the latest version. Simple example of `migrations` table: ```sql CREATE TABLE IF NOT EXISTS migrations ( version INT NOT NULL UNIQUE ) ``` Selecting current version: ```sql SELECT MAX(version) FROM migrations ``` Each time database changes are made during the development, new version should be added to the code so other team members could automatically get their database migrated to the latest version. An example of `__migrate()` implementation: ```python class Database: conn: SomeDatabase.Connection log: SomeLogger.Logger def __init__(self, log: SomeLogger.Logger, dsn: string): self.log = log // Doing some stuff if needed // For SQLite probably creating the file and/or checking write permissions? // For PostgreSQL most likely nothing // Initializing connection self.conn = SomeDatabase.Connection(dsn) self.__initialize() version = self.__get_current_version() self.log self.__migrate(version) def __get_current_version() -> int: // do version retrieval here def __migrate(version: int) -> None: if version is None: // First migration here self.conn.execute('CREATE TABLE blahblah(id INT);') self.conn.execute('CREATE TABLE migrations(version INT NOT NULL UNIQUE);') self.conn.insert('INSERT INTO migrations VALUES(1)') version++ if version <= 1: // Second migration self.conn.execute('ALTER TABLE blahblah ADD name VARCHAR(32)') self.conn.insert('INSERT INTO migrations VALUES(2)') version++ // Other migrations if needed // It's also a good thing to extract the 'INSERT INTO MIGRATIONS' to a separate method (`def __add_migration(version: int) -> None:` for example) ```

skobkin added this to the MVP 0.1 milestone 2022-05-19 16:47:00 +00:00

skobkin added the

enhancement

label 2022-05-19 16:47:00 +00:00

Miroslavsckaya was assigned by skobkin

2022-05-19 16:47:00 +00:00

skobkin added a new dependency 2022-05-19 16:48:13 +00:00

#6 Persistence module (database interaction)

skobkin referenced this issue

2022-05-19 16:51:22 +00:00

Persistence module (database interaction) #6

skobkin referenced this issue

2022-05-19 18:37:05 +00:00

database_module #17

skobkin added a new dependency 2022-05-22 17:19:15 +00:00

#18 Refactor/Optimize transactions

skobkin removed a dependency 2022-05-22 17:19:52 +00:00

#18 Refactor/Optimize transactions

skobkin added this to the RSS Bot Kanban Perdoling project 2022-05-30 21:26:08 +00:00

skobkin commented

2022-05-30 23:38:29 +00:00

Author

Collaborator

Library options:

And even interesting external language-agnostic tool:

https://github.com/amacneil/dbmate

Library options: * https://ollycope.com/software/yoyo/latest/ * https://pypi.org/project/db-migrator/ And even interesting external language-agnostic tool: * https://github.com/amacneil/dbmate

Miroslavsckaya referenced a pull request that will close this issue

2022-08-21 13:06:47 +00:00

Database migration #45

Miroslavsckaya closed this issue

2022-08-21 20:43:36 +00:00

Miroslavsckaya referenced this issue from a commit

2022-08-21 20:43:36 +00:00

Database migration (#45)