MySQL vs PostgreSQL

What is Mysql?

MySQL is the world's most popular DBMS - it is used by 39% of developers in 2019. MySQL is a fast, reliable and versatile relational database management system. Although it lacks the richness of PostgreSQL, it works well with a wide range of applications, especially web applications.

In fact, MySQL is the best choice for scalable web applications. One of the reasons for this is that it is included as standard in the LAMP stack (a group of open source web applications consisting of Linux, Apache HTTP Server, MySQL, and PHP). Additionally, popular content management systems such as Drupal, Joomla, and WordPress rely on MySQL, which is why MySQL is everywhere on the web.

MySQL Features

• Open Source: MySQL is a free and open source relational database management system (RDBMS).
• Long history: MySQL has been around since 1995.
• Maintained by Oracle: Oracle owns and maintains MySQL and offers a premium version (for a fee) with additional services, proprietary plugins, extensions, and user support.
• Supportive community: A dedicated community of volunteers to help troubleshoot as needed.
• Stable and Reliable: MySQL is widely accepted by users to be a very stable RDBMS as long as you keep your database "tidy" and maintain it regularly.
• MVCC functionality: MySQL now offers multi-version concurrency control (MVCC) functionality. This feature is more widely known in PostgreSQL (more on that later).
• Frequent Updates: MySQL benefits from frequent updates with new features and security improvements.

Notable users of MySQL are:

• Facebook
• Google
• Flickr
• GitHub
• NASA
• Netflix
• Spotify
• Tesla
• Twitter
• Uber
• US Navy
• WeChat
• Wikipedia
• YouTube
• Zendesk

What is PostgreSQL?

PostgreSQL is recognized as the best solution for complex, high-volume data manipulation. This is because PostgreSQL excels at handling special database challenges.

PostgreSQL "has more features" than other database management systems. PostgreSQL is also extensible "because its operations are catalog driven". In other words, you can define data types, index types, and functions as well as simply store table and column information.

Other things that make PostgreSQL stand out are that it's object-relational, ACID compliant, highly concurrency, and supports NoSQL.

Features of PostgreSQL

• Open Source: PostgreSQL is a free and open source Object-Relational Database Management System (ORDBMS). Being an ORDBMS rather than an RDBMS, PostgreSQL allows for both object-oriented and relational database functionality.
• Customizable: PostgreSQL can be customized to adapt the DBMS to your requirements by developing plugins. PostgreSQL also allows you to embed custom functions written in other programming languages ​​such as C/C++ and Java.
• Long history: PostgreSQL has been around since 1988.
• Frequent updates
• Liberal Open Source License: PostgreSQL features a liberal open source license, allowing you to use, modify and distribute the DBMS however you like.
• MVCC Features: PostgreSQL was the first DBMS to implement multi-version concurrency control (MVCC) functionality.
• Supportive Community: A dedicated community of developers and volunteers available for support when needed. Private third-party support services are also available. The same community supports PostgreSQL and updates the platform through the PostgreSQL Global Development Group.

Notable uses of PostgreSQL:

• Apple
• BioPharm
• Cisco
• Debian
• Facebook
• Fujitsu
• IMDB
• Instagram
• Macworld
• Red Hat
• Skype
• Spotify
• Sun Microsystem
• Yahoo

Why would a developer choose one over the other?

Why would a developer choose MySQL?

Flexibility and scalability: MySQL allows you to choose from a wide range of storage engines. This allows you to flexibly integrate data from different table types. MySQL 8.0 supports the following storage engines:

• InnoDB
• MyISAM
• Memory
• CSV
• Archive
• Blackhole
• NDB/NDBCLUSTER
• Merge
• Federated
• Example

Focus on Speed and Reliability: By not including specific SQL features, MySQL prioritizes speed and reliability. MySQL's speed is especially noticeable when it comes to high concurrency read-only functions. This makes it an excellent choice for certain business intelligence purposes. But if you need to run many complex queries under heavy load, PostgreSQL may be a better choice.

Options for server optimization: MySQL provides many options for fine-tuning and optimizing the MySQL server by adjusting variables such as sort_buffer_size, read_buffer_size and max_allowed_packet.

Easy to use and popular: MySQL's popularity means that it's easy to find database administrators with experience with MySQL. Others report that it is easy to set up and does not require tweaking like other DBMS solutions. Additionally, many managing tools (MySQL Workbench, HeidiSQL, dbForge Studio, etc.) add graphical interfaces to MySQL to provide a more user-friendly experience.

Cloud-ready DBMS: MySQL is cloud-ready, and many cloud platforms offer a MySQL feature that lets you install and maintain a MySQL database for a fee.

Multi-version concurrency control (MVCC) and ACID compliance available with InnoDB engine: InnoDB is the default engine in the current version of MySQL.

Why would a developer choose PostgreSQL?

it is not just an RDBMS: PostgreSQL is an object-relational programming language (ORDBMS), so it bridges object-oriented programming with relational/procedural programming (like C++). This allows you to define inheritance for objects and tables, allowing you to transform them into more complex data structures. ORDBMS excels when dealing with data that doesn't lend itself to a strictly relational model.

Best for Complex Queries: PostgreSQL is an excellent choice when you need to perform complex read/write operations while working with data that requires validation. But ORDBMS can be slow when doing read-only operations (which is where MySQL excels).

Supports NoSQL and wide variety of data types: PostgreSQL is a popular choice for NoSQL functionality. It natively supports rich data types such as JSON, hstore, and XML. You can also define your own data types and set custom functions.

Designed for ultra-large database management: PostgreSQL does not limit database size. According to Adjust.com's database administrator, his company uses PostgreSQL to manage "approximately 4PB [petabytes] of data." This equates to 4,000 terabytes.

Multiversion Concurrency Control (MVCC): MVCC is one of the most important reasons enterprises choose PostgreSQL. MVCC allows different readers and writers to simultaneously interact with and manage a PostgreSQL database. This improves efficiency by not requiring a read-write lock every time someone needs to interact with the data. MVCC achieves this through "snapshot isolation". A snapshot represents the state of data at a moment.

ACID Compliant: PostgreSQL prevents data corruption and preserves data integrity at the transaction level.

What programming languages are supported?

What programming languages are supported by MySQL?

• C/C++
• Delphi
• Erlang
• Go
• Java
• Lisp
• Node.js
• Perl
• PHP
• R
• Ruby

What programming languages are supported by PostgreSQL?

• C/C++
• Delphi
• Erlang
• Go
• Java
• Lisp
• .Net
• Python
• R
• Tcl
• Ruby

How to install rbenv and ruby? [MacOS]

What is rbenv?

It is a tool for Ruby version control.

Manually installing and managing Ruby in the development environment or in the production environment is very troublesome.

But with rbenv it became easy task because you can manage multiple versions of Ruby by using rbenv.

How to install rbenv?

First step, is to open the terminal and install Homebrew.

  /bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"

It may ask you to enter your login password for sudo permission please enter it. Then it will ask you to press Enter to continue. Also it will install Xcode command line tools in the process.

You may get the error "homebrew-core is a shallow clone. To fix it run the following command. It will take few minutes to run.

git -C /usr/local/Homebrew/Library/Taps/homebrew/homebrew-core fetch --unshallow

The running result of the command above.

After finishing, re-run the following command

  /bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"

Then use Homwbrew to install rbenv

  brew install rbenv

Add eval "$(rbenv init - bash)" to ~/.bash_profile

$ vi ~/.bash_profile 

eval "$(rbenv init - bash)"
:wq

And run ~/.bash_profile contents in your current terminal session

$ . ~/.bash_profile

Install ruby using rbenv

In the following steps we will use rbenv to install the latest version of ruby which at the time or writing this post, v3.1.2.

$ rbenv install 3.1.2

Then set active version of ruby to v3.1.2 locally in the current directory

$ rbenv local 3.1.2
$ ruby -v
ruby 3.1.2p20 (2022-04-12 revision 4491bb740a) [x86_64-darwin20]

This command will generate a file called .ruby-version in the current directory containing the version of ruby 3.1.2

What is Cross-Site Scripting (XSS)? | Explanation of attack mechanism and countermeasures

Cross-site scripting (XSS) is one of the most popular attacks in recent years. Website operators are required to take countermeasures against XSS vulnerabilities, as attacks can be carried out relatively easily if the attacker has some knowledge. In this article, we will explain the overview of cross-site scripting (XSS), its techniques, and countermeasures.

What is cross-site scripting (XSS)?

Cross-site scripting (XSS) is an attack that uses vulnerabilities in websites to embed malicious scripts in HTML.

Sites that generate web pages based on user input, such as survey sites, site searches, blogs, bulletin boards, and web applications such as Facebook and Twitter, are prone to cross-site scripting if no proper XSS countermeasures are taken. When a user enters or submits information on a form placed on the site, the embedded malicious HTML script is executed, and in addition to the entered information, cookie information is also sent to the attacker.

Cross-site scripting (XSS) attack flow and mechanism

Cross-site scripting (XSS) attacks mainly consist of the following three steps.

1. Attackers enter content, including links with scripts, into input forms to set traps in web applications (e.g. bulletin board sites).
2. The victim uses the corresponding web application
3. When a link is clicked and a script is executed, it transits (crosses) to another website and executes malicious content (the script)

As a result, malicious web pages will be displayed, to the victim machine will be infected with malware, etc. For example, you need to be careful when unfamiliar pop-up modals or input forms are displayed.

These are designed to prompt the user to enter important information such as personal information and then steal the entered information, and the user may suffer damage such as the leakage of personal information or malware infection.

In order to avoid becoming a victim, one of the countermeasures is to pay attention to the displayed content even if it is a web application that you use regularly and not to click or enter information carelessly.

Difference between XSS and Cross Site Request Forgery (CSRF)

Cross-site scripting (XSS) countermeasures

There are three effective countermeasures against cross-site scripting (XSS).

Keep in mind that these measures alone will not completely prevent cross-site scripting attacks. It is important to implement multi-layered defense by introducing all measures, not just one, to further increase the security of web applications.

Countermeasure #1: Santizing inputs:

A cross-site scripting (XSS) attack is an attack that injects executable code into web application forms to perform malicious operations in the web application user's environment. It is effective to implement sanitization of scripts to prevent this code from being executed.

For example, if content containing the strings <script> through %lt;/script%gt; is posted to be embedded in a non-sanitized web application, JavaScript or other scripts will be executed when a button or link displayed as the posted content is clicked.

Therefore, focusing on escaping (encoding) the 5 special characters &, <, %gt;, ”, ' that are necessary for script execution will make the script inexecutable and it will be displayed as string on the screen, and render the script harmless (sanitization). By doing this substitution, even if a script is entered, the screen of the web application will display the source of the script and not what the attacker intended.

Countermeasure #2: Limiting input values:

Restricting the values that users can enter is another countermeasure against cross-site scripting (XSS).

For example, when entering a postal code, disallowing non-numeric entries prevents script insertion.

Also, even if the character type cannot be restricted, setting a restriction on the length of the input value makes it possible to suppress the insertion of scripts that allow attacks to some extent.

Countermeasure #3: Using WAF (Web Application Firewall)

Both of the above-mentioned two countermeasures are performed at the stage of Web application creation.

However, even the most meticulously crafted web applications can be vulnerable to cross-site scripting attacks.

In order to prepare for an emergency, it is recommended to use a WAF (Web Application Firewall). Even if a cross-site scripting attack is possible due to absence of the input content check of the web application, it is possible to prevent the attack by introducing a WAF.

This is because WAF monitors the content of requests sent by users. WAF is an abbreviation for Web Application Firewall, and is a tool that protects web applications, including websites, from cyberattacks. It sits in front of the web server and inspects and analyzes all communications to your web applications. Then, when it detects that there is an unauthorized attack from an attacker, it will act to block that communication.

The skills needed for a backend engineer

The following are the most required skills for a backend engineers from my point of view.

Programming Languages

• Java
• .NET (C# and VB.NET)
• Python
• Ruby
• PHP
• TypeScript
• Golang
• Kotlin (As a server side language)
• Scala
• Elixir

Java, .NET, Kotlin (Compile type languages)

Python, Ruby, PHP (Inpterpreted type languages)

Package Managers

At the moment, a lot of languages use a package management method that combines ◯◯ File and ◯◯ File.lock, so I think it's enough to get used to this method to some extent. (Recently, a package manager called Pipenv, which uses the above method for Python, seems to be gradually spreading.)

However, regarding npm, considering the importance of JavaScript (TypeScript) in the web industry (used for server-side, front-end, and application development), I think it would be beneficial to know about it. I think it's better to study personally even if you don't have a chance to use it for business.

Regarding Scala's sbt and Java (Kotlin)'s gradle, if you are not particularly interested in these languages, I don't think you need to study them privately.

Security Measures

I think that it is impossible to understand everything about security, but it is enough to take the countermeasures against basic vulnerability attacks such as SQL injection, XSS, CSRF, etc.

Important information should not be stored in plaintext in databases, etc. (always encrypted)

Do not upload confidential information to source control services such as Github (use .env and environment variables)

On the cloud (AWS and GCP), perform access control using IAM etc. as finely as possible

Always use and enforce SSL

If the security is not properly set, it will lead to the bankruptcy of the service or the company. On the other hand if the security requirements are too strict, the development efficiency will be very bad (it will also lead to the departure of engineers), so It is necessary to set security settings according to the importance of the service. For example a security measure for a blog should not be the same as for online banking service.

Testing

• Rspec (Ruby on Rails)
• unittest (Python)
• testing (Golang)
• ExUnit (Elixir)
• ScalaTest (Scala)

Try to understand as much as possible how to use the standard test frameworks of various languages that are used in the project you are in charge of (for example, how to set fixtures, the scope of setup and teardown, etc.) However, all test frameworks basically have similar parts, so if you are familiar with one test framework, it will be relatively easy to use test frameworks for other languages.

Besides that, it is important to understand the difference between unit test and integration test, and I think that the concept of DI (Dependency Injection) and the test method using it are essential education for engineers. I think you should have some understanding of Test Doubles, mocks, stubs.

RDBMS

I think even today, with the emergence of many NoSQL services, but relational databases are still extremely important, and MySQL in particular will continue to play an important role in the future. I think that it is highly likely that it will continue to be an important RDB, so I think that it is necessary to know the basic management methods and commands of MySQL.

As for SQL, at least

• Basic DDL (CREATE TABLE, ALTER, DROP etc.)
• Basic DML (SELECT/INSERT/UPDATE/DELETE)
• Basic DCL (GRANT, etc.)

It is necessary to understand the above areas, and I think it is necessary to understand also encodings, indexes, transactions, deadlocks, the meaning of master/slave, etc.

Recently, I think that most people use fully managed RDB such as RDS, so I don't think it is necessary to understand the very detailed parts of RDB, and there are few opportunities to need such knowledge. However, compared to before, it seems that young engineers' knowledge of RDB and SQL has declined considerably, so it seems that there are more and more cases where RDB is used rather wastefully. Or, I often think that I want you to study a little more about the basic part of RDB.

Web Frameworks

• Ruby on Rails (Ruby)
• Django, Flask (Python)
• Revel (Golang)
• Phoenix (Elixir)
• Finch (Scala)
• Vert.x (Kotlin)

I think that sticking too much to one framework is a big disadvantage, so avoid a such attitude like "I only want to do Ruby on Rails" and stick to a "modern framework." If that's the case, then let's try anything," I think it's better to try various frameworks with a flexible attitude, and you'll be able to obtain more knowledge.

Cache/NoSQL

• memcached
• Redis
• DynamoDB
• Cassandra

I think it is better to have some knowledge about NoSQL services on various clouds.

REST API

I think it's good to understand the REST-like API definition method and the tools used there, and the API definition method that almost follows the REST guidelines.

The relationship between Activerecord and the generated SQL [Ruby on Rails]

What is RDBMS?

It is the abbreviation for Relational Data Base Management System. As the name suggests, it is a general term for software that manages relational databases, and it organizes data into columns and records (rows) and expresses them side by side in a two-dimensional table. The SQL language is used for procedures such as data acquisition and creation. Typical examples include Mysql and Postgresql.

What is ORM?

it is the abbreviation for Object Relational Mapping, a programming technique that allows you to handle tables and data on RDBMS that cannot be directly handled by object-oriented languages such as Ruby by mapping them into classes.

What is ActiveRecord?

ActiveRecord used in Rails is the layer that plays the core of MVC's M, or Model, and mainly interacts with the database. It is a library that implements the "Active Record pattern" described in ORM systems and bears the same name as this pattern. All classes created as Rails models inherit the ApplicationRecord class, which inherits from ActiveRecord::Base, so you can use this ActiveRecord method in all classes generated by Rails.

Preparations

In the example used below, we see a User model. Let's say we have the following columns.

User Model

Column	Type
ID	integer
First Name	string
Last Name	string
Age	integer

ActiveRecord methods and generated SQL statements

The ActiveRecord methods and the corresponding SQL generated to retrieve data are shown below. (The SQL statement is slightly different from what appears on the rails console, but it is shown in a form that can be executed on an actual RDBMS.)

	
# 1
User.all
# SELECT * FROM users;

# 2
User.find(1)
# SELECT * FROM users WHERE id = 1 LIMIT 1;

# 3
User.find_by(first_name: "John")
# SELECT * FROM users WHERE first_name = 'John' LIMIT 1;

# 4
User.create(first_name: 'John', last_name: "Smith", age: 30)
# INSERT INTO users (first_name, last_name, age) values ('John', 'Smith', 30);

# 5
User.first.update(first_name: "Michael")
# SELECT * FROM users ORDER BY id ASC LIMIT 1;
# UPDATE users SET first_name = 'Michael' updated_at = NOW() WHERE id = 1;

1. User.all

SELECT * FROM users;

It is a method that is often executed at the index method of the controller. As you know, all is an ActiveRecord method to get all users. * is a wildcard and means all (= all columns defined in the users table here). In other words, this SQL means "get all the records defined in the users table". The acquired data is mapped to the (array of) User class.

2. User.find(1)

SELECT * FROM users WHERE id = 1 LIMIT 1;

This SQL means "get all the columns of the first data of the user whose id is 1 from the users table". The fetched record is mapped to User model object returned by the method find.

3. User.find_by(first_name: "John")

SELECT * FROM users WHERE first_name = 'John' LIMIT 1;

When specifying the condition by id and getting the user, simply specifying 1 was enough, but if you want to specify the condition on other columns, use the find_by method to specify the column and condition at the same time.

However, looking at the issued SQL, the WHERE id = 1 part only changed to WHERE first_name = 'John'. Not limited to ActiveRecord, there are many ORM systems that provide simplified methods for id searches like this.

4. User.create(first_name: 'John', last_name: "Smith", age: 30)

INSERT INTO users (first_name, last_name, age) values ('John', 'Smith', 30);

This method generates insertion query and execute it.

5. User.first.update(first_name: "Michael")

SELECT * FROM users ORDER BY id ASC LIMIT 1;

UPDATE users SET first_name = 'Michael' updated_at = NOW() WHERE id = 1;

With update, two SQL statements are generated to first get the data column to be updated then rewrite it. The first method used here is a method that retrieves the data with the smallest id on the database, but in SQL, the condition is to "arrange the data in ascending order of id and retrieve the first data''. As you can see, the update method is slightly different from insert in how to specify columns and values.

Docker vs Virtual Machines

Advantages of Virtualization

Before virtualization technology became widespread, it was common practice to build one server environment for one server, and as the number of server environments required increased, so did the number of servers.

In recent years, a server environment construction technology that is not bound by a physical configuration has emerged. With the advent of "server virtualization technology," it is now possible to build multiple server environments on a single server. In addition, those server environments can run different server operating systems on the same server.

In some cases, different versions of Windows, Linux, and other operating systems can be mixed and matched on a Windows server. Let's take a look at the benefits of this server virtualization technology.

Let's take a look at the three advantages of server virtualization.

Reduction of Installation Costs

In the conventional system development, when the OS selected depending on the development time and its version differed, a server was procured each time, but with the use of virtualization technology, different OSes can be installed on the same server. It is now possible to stand up and operate each system. As a result, not only hardware procurement costs but also human costs such as setting work can be reduced.

Reduction of server maintenance costs

Server maintenance costs (rental costs, electricity costs, maintenance costs, etc.) can be reduced if a large number of systems can be operated with a small number of servers using server virtualization technology.

Realization of BCP measures by facilitating replication

BCP is a business continuity plan, and it determines in advance how to continue business in the event of an emergency such as a disaster. Once the server virtual environment is in place, the encapsulated virtual server environment can be replicated to a backup site in a remote location along with the capsule, enabling quick switching to the backup site in an emergency and minimizing business continuity risks.

Advantages of Docker

Docker is a platform for building a containerized virtualization environment, actually it is one of the methods of virtualization.

In this article, we will explain Docker in an easy-to-understand manner, focusing on its advantages. We hope you will read this article to the end, as this knowledge is definitely useful for all engineers.

Easy environment construction

One of the advantages of "Docker" is that the difficulty of building an environment is low. For the container environment, you can build the desired environment by downloading the "Docker image" from "Docker Hub" and making some changes.

In addition, "Docker" consumes less resources, and a container can be created in no time just by typing the command to create a "Docker container" from the command prompt.

Easy to share and unify environments

If you install "Docker" in each environment, you can run the application simply by downloading the same "Docker image" and creating a container.

This allows developers to easily share and standardize the development environment. Another advantage is that the production environment can be created in the same environment as the environment created in the development process, so the transition to the production environment can be performed smoothly.

Low resource consumption

Compared to traditional virtual environments, container virtual environments consume less resources such as disk and memory. This is because the hosted virtualization and hypervisor types require a guest OS, while the container type does not require a guest OS and the containers only run the applications.

The disadvantages of Docker

Dependence on a single OS limits flexibility

Since Docker containers run using the kernel part of one OS, the flexibility of the OS is limited. For example, in order to run Windows containers and Linux containers on the same machine, it is necessary to prepare a hypervisor-type virtual environment

Higher security risk compared to virtual machines

Docker containers run multiple containers on one OS, so if a third party deprives privileges, all containers will be affected. Assuming that Docker containers pose a higher security risk than virtual machines, it is necessary to take solid security measures.

Kubernetes vs Docker

Today, a variety of companies and services continue to emerge, and several applications are developed every day. It has become complicated to manage multiple hosts. It would be nice if application publishing and modification could be automated. There are times when you feel like this.

Kubernetes is gaining attention as a platform for managing and automating multiple containers. In this article, we will explain the basics of Kubernetes, its differences from Docker, and the concrete benefits of using Kubernetes.

What is Kubernetes

Kubernetes is open source software designed to manage and automate container operations. In Greek, it means pilot. It is also abbreviated as "k8s".

Kubernetes operates on Docker or other containers. Containers run various applications, but they cannot manage themselves or cooperate with other servers. This makes management complicated when you want to run containers on multiple hosts.

Kubernetes is a system that solves this problem by allowing you to manage networking, storage, etc. in a coordinated manner when running containers on multiple hosts. Such a system is also called "container orchestration.

As we will see later, Kubernetes also offers other advantages, such as self-healing, trouble tolerance, and flexible scaling to reduce CPU waste.

What is a container

Containers are a technology for building a virtual environment for running applications, which is a collection of applications, libraries, etc. on a host OS.

Although the technology is very similar to virtualization software, containers can be separated from the system as a single unit. With conventional virtualization software, it is necessary to build all resources within a single virtual environment. This is fine when you want to use different OS environments, but in actual development, there are many cases where "the OS should be the same," wasting resources to build the OS each time.

However, with containers, systems running multiple OSes can be combined into one, and while the OS is shared, the CPU and memory for running applications is managed on a container-by-container basis, so resources are saved.

What is difference between Docker and Kubernetes?

While Docker is a containers runtime, Kubernetes is a platform for running and managing containers using several container runtimes. kubernetes supports Docker, containerd, CRI-O, and Kubernetes CRI (Container Runtime Interface), including any implementation of the Container Runtime Interface.

Containers are grouped into Pods, the basic unit of Kubernetes, which automatically manages service discovery, load balancing, resource allocation, isolation, and expansion of pods. Embraced by the open source community and is now part of the Amazon, Microsoft, and Google. All of them offer managed Kubernetes on their cloud computing platforms.

Organizations use Kubernetes to automate the deployment and management of containerized applications. Rather than separately managing each container in a cluster, a DevOps team can instead tell Kubernetes how to allocate the necessary resources for all the required containers in advance.