database – Simplify Complexities

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

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

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

How to: MySQL Get Database Name and Use in Another Query

The following SQL query get the database name and use the name to run another query.

SET @dbname = DATABASE();

ALTER DATABASE @dbname CHARACTER SET utf8 COLLATE utf8_unicode_ci;

References

Database Collation and UTF8MB4

Database collation defines how characters are compared and hence the order of rows in query results.

Popular Encodings

UTF8: Uses 3 bytes for a character
UTF8MB4: Uses 4 bytes for a character, so allows more characters.

How to Decode Collation

utf8mb4_unicode_520_ci

UTF8MB4
Unicode 5.2.0 comparison for characters
ci: Case Insensitive comparisons

Reference

Written with StackEdit.

Go, gorm, nested JSON and associations

Go, gorm, nested JSON and associations

gorm provides a clean way to store a nested JSON with the relation of associations among tables. The following code creates a DB with three levels of nesting.

package main

import (
	"encoding/json"
	"fmt"

	"github.com/jinzhu/gorm"
	_ "github.com/lib/pq"
)

const (
	host     = "localhost"
	port     = 5432
	user     = "postgres"
	password = ""
	dbname   = "postgres"
)

type Page struct {
	ID     int64  `sql:"auto_increment" json:"-"`
	Number int64  `json:"number"`
	Book   Book   `gorm:"foreignkey:book_id" json:"-"`
	BookID int64  `json:"book_id"`
	Text   string `json:"text"`
}

type Book struct {
	ID          int64  `sql:"auto_increment" json:"-"`
	ShelfPlace  int64  `json:"shelf_place"`
	Shelf       Shelf  `gorm:"foreignkey:shelf_id" json:"-"`
	ShelfID     int64  `json:"shelf_id"`
	Author      string `json:"author" gorm:"unique;not null"`
	Publisher   string `json:"publisher"`
	PagesAmount int64  `json:"pages_amount"`
	Pages       []Page `json:"pages"`
}

type Shelf struct {
	ID          int64  `sql:"auto_increment" json:"-"`
	Number      int64  `json:"number"`
	BooksAmount int64  `json:"books_amount"`
	Book        []Book `json:"books"`
}

func main() {
	psqlInfo := fmt.Sprintf("host=%s port=%d user=%s "+
		"password=%s dbname=%s sslmode=disable",
		host, port, user, password, dbname)

	db, err := gorm.Open("postgres", psqlInfo)
	if err != nil {
		panic(err)
	}
	defer db.Close()

	// Create
	//db.Create(&Shelf{
	record := `{
			"number": 1,
			"books": [
			  {
				"shelf_place": 5,
				"author": "Lewis Carroll",
				"publisher": "EA",
				"pages_amount": 2,
				"pages": [
				  {
					"number": 2,
					"text": "lorem ipsum"
				  },
				  {
					"number": 4,
					"text": "dolor sit amet"
				  }
				]
			  },
			  {
				"shelf_place": 7,
				"author": "Mark Twain",
				"publisher": "Activision",
				"pages_amount": 3,
				"pages": [
				  {
					"number": 1,
					"text": "this is"
				  },
				  {
					"number": 3,
					"text": "a test"
				  },
				  {
					"number": 6,
					"text": "of json"
				  }
				]
			  }
			]
		  }`
	var shelf Shelf

	err = json.Unmarshal([]byte(record), &shelf)
	fmt.Printf("err=%v\n", err)

	db.DropTableIfExists(&Shelf{})
	db.DropTableIfExists(&Page{})
	db.DropTableIfExists(&Book{})

	// Migrate the schema
	db.AutoMigrate(&Shelf{})
	db.AutoMigrate(&Page{})
	db.AutoMigrate(&Book{})

	db.Create(&shelf)
	
	// Fails because author is a unique attribute
	//db.Create(&shelf)

    // Preload is necessary to query nested structure.
	db.Preload("Book").Where("author = ?", "Mark Twain").Find(&shelf)
	fmt.Printf("shelf=%v", shelf)
}

References

Written with StackEdit.

Design Pattern: Active Record

Active Record

An Active Record Object represents a row in a DB table. The object has fields mapping to a column in the table and methods to access table data.

Active Record connects classes to relational database tables to establish an almost zero-configuration persistence layer for applications.
The library provides a base class that, when subclassed, sets up a mapping between the new class and an existing table in the database.
In the context of an application, these classes are commonly referred to as models.
Models can also be connected to other models; this is done by defining associations.

Source

Active Record objects don’t specify their attributes directly, but rather infer them from the table definition with which they’re linked.
Adding, removing, and changing attributes and their type is done directly in the database. Any change is instantly reflected in the Active Record objects.
The mapping that binds a given Active Record class to a certain database table will happen automatically in most common cases but can be overwritten for the uncommon ones.

Source

Use Cases

Golang gorm library is the implementation of the Active Record design pattern.
Ruby gem ActiveRecord

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

How to Get PSQL Client on Alpine Linux

How to Install?

$ apk --update add postgresql-client

How to Login?

$ psql -h host_name -d db_name -U user_name

Keep the user password ready 🙂

Reference

https://gist.github.com/petrkohut/2ed5dc83ca4afd508e9b

Written with StackEdit.

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

https://makandracards.com/makandra/36097-postgresql-how-to-add-remove-modify-array-values-and-how-to-replace-1-value-with-multiple-values

Mongo DB: Good to know things

Mongo DB is a No-SQL, free, open-source solution that is highly scalable, highly available and high performance solution.
Engine is coded in C++
Works in a client-server model
Major components:
- mongod: The storage server
- mongos: The sharding server
- config server(s):
  - Stores metadata that accomplish sharding
  - Is actually a mongod process
Mongo provides write operations durability with journaling (write ahead logging)
User data is seen as a database of collection of records
- Collection is roughly similar to a table in RDBMS
- Record could be map to a row in a table (incorrect but helps understanding)
Mongo stores data in BSON format (on-wire and on-disk)

migrate

Code

Troubleshooting

References

Share this:

Methods

Conclusion

Examples

References

Share this:

How to: MySQL Get Database Name and Use in Another Query

References

Share this:

Popular Encodings

How to Decode Collation

Reference

Share this:

References

Share this:

Active Record

Use Cases

Share this:

Introduction

Ingredients

Writing a Trigger Procedure is different than a Query

Sample Trigger Procedure

Trigger Code

How to Execute

How to Debug and Develop

List Triggers

Reference

Share this:

Share this:

Update an Array

Append an Array

Reference

Share this:

Share this: