Category Archives: Databases

MySQL: generate and insert a range of dummy rows

There are a lot of ways to do this, but the one below is the most straightforward to me.

How to safely update data in MySQL

CREATE TABLE IF NOT EXISTS my_tmp_copy LIKE my;

INSERT INTO my_tmp_copy
SELECT * FROM my;

UPDATE my_tmp_copy SET order_count = 5 WHERE customer_id > 0;

RENAME TABLE my TO my_old, my_tmp_copy TO my;

CREATE TABLE IF NOT EXISTS my_tmp_copy LIKE my;

INSERT INTO my_tmp_copy

SELECT * FROM my;

UPDATE my_tmp_copy SET order_count = 5 WHERE customer_id > 0;

RENAME TABLE my TO my_old, my_tmp_copy TO my;

Mock sql (sqlx) db on golang

I am using this library — https://pkg.go.dev/github.com/data-dog/go-sqlmock. That’s how you can use it for mocking db querying:

Postgresql index naming convention

Use the table name as the prefix. Use the following suffixes: _pkey for a Primary Key constraint _key for a Unique constraint _excl for an Exclusion constraint _idx for any other kind of index _fkey for a Foreign key _check for a Check constraint https://stackoverflow.com/a/4108266/801426 Also implicitly mentioned here — https://www.postgresql.org/docs/current/sql-altertable.html

Postgresql exercises

Basic querying — https://pgexercises.com/questions/basic/. Joins — https://pgexercises.com/questions/joins/. Data modification — https://pgexercises.com/questions/updates/. Aggregations — https://pgexercises.com/questions/aggregates/. That could be useful to refresh your memory. Not really up-to-date, but still pretty useful.

How to start with google bigquery

Here is the list of thing I noticed when started to work with bigquery. First you need to create a project — https://cloud.google.com/bigquery/docs/quickstarts/quickstart-web-ui. That’s how you create a service key for bigquery — https://cloud.google.com/docs/authentication/getting-started. Select your project on the very top of the page, and then create JSON key. And don’t forget to set is …

Pros and cons of DynamoDB

What the author suggests is to use it in cases when there are not so many writes to DB and where eventual consistency is fine for the app. The writes are pretty expensive after all and could lead to problems with eventual consistency. And the main issue from my side is that DynamoDB is a …

How to choose a partition key for DynamoDB

Here’s an article about how to do it right: https://aws.amazon.com/blogs/database/choosing-the-right-dynamodb-partition-key/. The main ideas — partition key should have a good cardinality, but at the same time it should be queried easily as all the queries to DynamoDB should start with PartitionKey=…, as they are hashes.

Checking restart problems with mysql on centos

You probable installed MySQL with a help of article like this — https://phoenixnap.com/kb/how-to-install-mysql-on-centos-7 So your mysql is controlled by systemctl. Check current status — sudo systemctl status mysqld. Start service — sudo systemctl start mysqld Logs for systemctl are here — sudo journalctl -xe Logs for mysql can be found here — /var/log/mysqld.log. Configs are …

DynamoDB videos list

AWS decided to start a series of videos on DynamoDB usage. First 5 episodes were published on Youtube, and I made a playlist for them: https://www.youtube.com/playlist?list=PLfgkuLYEOvGN95-dbdS_mT4bktQjkw2aT