Tag: sql

How Golang DB migration tool work?

migrate

Database migrations written in Go. Use as CLI or import as library.

  • Migrate reads migrations from sources and applies them in correct order to a database.

The migration tool uses a schema_migrations table. One table exists for one DB.

mysql> select * from schema_migrations;
+---------+-------+
| version | dirty |
+---------+-------+
|      33 |     0 |
+---------+-------+
1 row in set (0.00 sec)

The command to apply all migrations to a DB would check-in schema_migrations table and pick migrations after the version (for the current case – 34 and onwards).

migration up

migration down 33

The down migration executes the down file for version 33 and updates the schema_migrations table.

Code

Migration files are read from 1..N if no version is specified.

migrate/migrate.go

// read reads either up or down migrations from source `from` to `to`.
// Each migration is then written to the ret channel.
// If an error occurs during reading, that error is written to the ret channel, too.
// Once read is done reading it will close the ret channel.
func (m *Migrate) read(from int, to int, ret chan<- interface{})

Troubleshooting

  1. no migration found for version
    Explanation: The migration version was updated in schema_registry marked dirty but the migration files are deleted/ missing. So maybe the previous migration was done with N and then Nth files were deleted. Now up or down would not work as the schema_registry has version N.
    So the solution is either to bring back Nth migrations or force the schema_regsitry version to N-1.

    migrate force N-1

  2. error: Dirty database version 32. Fix and force version.
    Explanation: The 33rd migration file was corrupted or had an error.

    mysql> select * from schema_migrations;
+---------+-------+
| version | dirty |
+---------+-------+
|      32 |     1 |
+---------+-------+
1 row in set (0.01 sec)

Manually undo the changes of version 33 (refer to 33 down migration file) and run migrate force 32

References

Written with StackEdit.

MySQL: Convert Character Set and Collation

The character set and collation changes for a DB have important changes on the storage requirements and query result behavior. The character set for a table can be changed in two ways:
a. Using CONVERT TO query
b. Using MODIFY <column name>

Methods

The CONVERT TO method makes sure that each column fits the new character set range after conversion. So a column type TEXT of character set Latin would not accommodate the character set of UTF8MB4. Since the latter needs 4 bytes for a character, while in Latin one byte is required for a character.

So CONVERT TO rounds of the column type to the nearest next size.

The next method of MODIFY does not make any change to the column type.

Conclusion

I prefer the first method because it’s assuring that the table column sizes are technically large enough and also it eliminates any manual changes for a column.

Examples

ALTER DATABASE <db name> CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;

ALTER TABLE `test_table` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;

ALTER TABLE `test_table` MODIFY `column1` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL;

ALTER TABLE `test_table` MODIFY `column2` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL;

References

Database Trigger Procedure in Postgres

An introduction to triggers in Postgres

Introduction

A trigger is an easy way to enforce a check or constraint, log mutations on a table. It is available for each row or a statement modification.
You can ask the DB to run a function triggered on an action (row append, update, delete) and a clause and timeframe (before/ after the operation).

Ingredients

  • Watch the Youtube video on Postgres trigger for 10 minutes (link)
  • Postgres psql client
  • Any text editor

Writing a Trigger Procedure is different than a Query

  • Find out Postgres datatypes
  • Typecasts using CAST() method
  • Learn Trigger Procedure Syntax

Sample Trigger Procedure

CREATE OR REPLACE FUNCTION time_range_check() RETURNS trigger AS
'
DECLARE
    acceptedTime timestamp;
    newTime timestamp;
    defaultTime timestamp;
  BEGIN
    acceptedTime = LOCALTIMESTAMP(0);
    defaultTime = to_timestamp(1577800000);
    newTime = to_timestamp(CAST(NEW.value as bigint));
	
    IF  newTime < defaultTime  THEN
		 raise EXCEPTION ''invalid past timestamp passed'';
         return NULL;
	END IF;
	IF  newTime > acceptedTime  THEN
		 raise EXCEPTION ''invalid future timestamp passed'';
         return NULL;
	END IF;
	RETURN NEW;
  END;
'LANGUAGE 'plpgsql';

Trigger Code

create trigger my_range_check 
before insert on my_table 
for each row 
when (NEW.item_id = 1)
execute procedure time_range_check();

How to Execute

After composing the trigger procedure and trigger, just paste the text on the psql command line.

How to Debug and Develop

You will get a hint of an error on pasting the trigger code on psql CLI. Every time the procedure code changes, we need to drop the trigger.

# drop trigger my_range_check on my_table;

List Triggers

# \d table_name

Reference

PostGres: Update Column with Array Values

Update an Array

UPDATE users SET topics = '{crows, cats}';

Append an Array

UPDATE users SET topics = topics || '{cats,mice}';

Append operation creates duplicates.

Reference