Database initialization and migration #16

Closed
opened 2022-05-19 16:47:00 +00:00 by skobkin · 1 comment
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
skobkin added a new dependency 2022-05-22 17:19:15 +00:00
skobkin removed a dependency 2022-05-22 17:19:52 +00:00
skobkin added this to the RSS Bot Kanban Perdoling project 2022-05-30 21:26:08 +00:00
Author
Collaborator
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
Sign in to join this conversation.
No milestone
No assignees
1 participant
Notifications
Due date
The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Depends on
Reference: Miroslavsckaya/tg_rss_bot#16
No description provided.