DEV Community: Kevin Tran

Tracking Email Activity from AWS Simple Email Service (SES)

Kevin Tran — Mon, 31 Jul 2023 16:00:24 +0000

Introduction

The email sending function is a common feature found in almost every website. For developers, AWS SES (AWS Simple Email Service) is a popular choice, particularly when the website is hosted on the AWS cloud. Although AWS SES doesn't directly support email activity tracking, it does offer the possibility to create custom tracking features by integrating with other AWS services. In this post, I will explain how to build an application that tracks email sending activities using SNS HTTPs endpoint.

SES Setup

Configure AWS SES

Implementing email communication with AWS SES is a straightforward task, thanks to the support of various programming languages provided by AWS developer tools.
To send an email using SES, we can directly call the AWS SDK. Here's an example using NodeJS:

  const client = new SESClient(config);
  AWS.config.loadFromPath('./credentials.json'); // for testing purpose

  const command = new SendEmailCommand({
    Destination: {
      ToAddresses: ['testemail@gmail.com'],
    },
    Message: {
      Subject: { Data: 'Test Subject' },
      Body: {
        Text: {
          Charset: 'UTF-8',
          Data: 'This is a test email',
        }
      }
    },
    Source: 'noreply@mydomain.com',
  });

  const response = await client.send(command);

For production ready, I am using the NodeMailer package instead, which provides a slightly better interface:

  const ses = new aws.SES({});

  const message = {
    from: 'noreply@mydomain.com',
    to: 'testemail@gmail.com',
    subject: 'Test Subject',
    text: 'body in text',
    html: '<p>HTML content</p>',
  };

  const transport = nodemailer.createTransport({
    SES: {ses, aws},
  });

  await transport.sendMail(data);

Rather than directly calling the SES Client, NodeMailer redirects the API call to SES through the Transport object, making it easy to switch to different transportation options.
This flexibility is particularly helpful for developers when running the code in development and test environments. We'll explore this further in the Testing section later on.

SES Dashboard

In the code example, the app only needs to pass the necessary data, such as fromEmail, toEmail, subject, body (and attachments), to the function.
AWS SES will then handle the entire process of sending out the email.

A pressing question arises: how can we determine whether the email has been successfully delivered to the user's inbox or rejected by the user's email server?

The SES Dashboard provides valuable insights into account usage, such as the total number of sent emails, historical sending data, rejected rate, bouncing rate, and complaint rate. However, these metrics do not provide specific details about each individual email activity. A complete email system requires this information to troubleshoot any potential bounces or rejections caused by external factors. Thankfully, the SES Notifications feature, integrated with AWS Simple Notification Service and AWS Simple Queue Service, helps bridge this gap and provides the necessary information for a complete email system.

Complete email system

Below is a draft design that outlines the components of an email system.

In our context, after observing how SES integration is set up in various applications, we have separated the Email sending system as a standalone application.
This approach allows other applications to avoid managing their individual AWS configurations for email communication. Instead, they can simply make an API call to the email system, which will handle the entire lifecycle of the emails.

Here are some more details about each component:

API Gateway: allows client applications, SNS Subscriber (HTTP/HTTPS) and dashboard interacts with the Email System.
Email Sending API: provides API to trigger email, it handles the Emails Service that responses for communicate with the AWS SES, and the Recipients Service that creates/updates the recipients's profiles.
Activity API: provides public endpoints for SNS subscriber to push the notification about the email's activities. The notification will be proceed later by a background job processor to the domain data.
Dashboard API: provides APIs for the Frontend Dashboard page to connect and render the email activities data.

AWS SES notification config

In each SES Domain Identity, there is a Notifications config, where it allows to setup the SNS topic for each feedback type. Here is an example:

Before setting up the SES notification, it's essential to create the required SNS Topics. In the example provided, separate topics are used for each notification type (bounce, complaint, and delivery), but a single topic can suffice for simpler use cases.

The subsequent step involves defining the subscription for the SNS Topic, for which we'll be using the HTTPs (or HTTP) protocol in this blog post.
AWS SNS supports various other subscription types, including SQS (event queue), email, and SMS subscriptions.

To create the HTTPs subscription, select the subscription type and provide the endpoint URL. Please note that the endpoint URL should be publicly accessible on the internet, as private IP addresses won't work.

In the example screenshot, the website supports creating an email hook, wherein each URL is assigned a unique identifier to distinguish email notifications from different identities. However, for simpler use cases, a general URL such as https://mydomain.local/ses_notifications will suffice.

After creating the subscription, the SNS will make a request call with subscription_confirm message to the subscription URL.
The application needs to store the request payload, which contains the Subscribe URL and the Subscription Token to confirm the subscription.

To confirm the subscription, we can open the SubscribeURL on the browser or implement an automated logic that calls the ConfirmSubscriptionCommand as follow:

  const input = { 
    TopicArn: "STRING_VALUE",
    Token: "STRING_VALUE",
    AuthenticateOnUnsubscribe: "STRING_VALUE",
  };

  const command = new ConfirmSubscriptionCommand(input);
  const response = await client.send(command);

To avoid the message be formatted, SNS subscription allows to send the raw message instead of the formatted message. However, there could be some issue when reading the request body with the raw payload if the option "Enable raw message delivery" was enabled. For example, if the project uses ExpressJS (NodeJS), this raw-body package can be used to parse the request body, the default access to request.body doesn't return any data.

Once the subscription is confirmed, whenever an email is sent using this SES domain identity, an HTTP request is triggered from SNS to the preconfigured endpoint. The email system captures the request payload and processes it to determine whether the email is delivered successfully.

The request payload may look like this:

{
   "notificationType": "Complaint",
   "complaint": {
      "complainedRecipients": [
         {
            "emailAddress": "richard@example.com"
         }
      ],
      "complaintFeedbackType": "abuse",
      "arrivalDate": "2016-01-27T14:59:38.237Z",
      "timestamp": "2016-01-27T14:59:38.237Z",
      "feedbackId": "000001378603177f-18c07c78-fa81-4a58-9dd1-fedc3cb8f49a-000000"
   },
   "mail": { // Content is trimmed }
}

In this example, the notificationType is "Complaint" which indicates that the email reached the recipient's mailbox but was marked as spam.

Core components' implementation

I hope that I have covered the main concepts and functionalities of the Email System which integrates with AWS SES.
In this section, we will explore the implementation of the core components in this email system.

Email Sending Controller

The controller exposes an API that allows other applications to make requests to send out emails.
gRPC is used as a default choice for microservice protocol, the gRPC proto file looks like this:

message TriggerEmailAttribute {
  string fieldName = 1;
  string value = 2;
}

message EmailAttachment {
  string name = 1;
  string remoteUrl = 2;
  optional string cid = 3;
}

message TriggerEmailRequest {
  string subject = 1;
  repeated string toEmails = 2;
  string mailBody = 3;
  repeated EmailAttachment attachments = 4;
  repeated TriggerEmailAttribute attributes = 5;
}

service EmailsServiceController {
  rpc triggerEmail(TriggerEmailRequest) returns (TriggerEmailResponse) {};
}

The triggerEmail call receives an input of type TriggerEmailRequest and returns a response with type TriggerEmailResponse. The TriggerEmailRequest definition includes 5 fields used to make the call to the AWS SES API. In the implementation, the controller interacts with two services: the EmailsService, responsible for handling the email submission to AWS SES, and the RecipientsService, which manages the creation of recipients based on the provided information. The notifications received from the SNS Subscription are later mapped to these recipients.

By providing the toEmails parameter in the request object, the service allows for specifying multiple email addresses to receive the same email in a single request.

Notification endpoints management

As the Email sending service is used by multiple applications, each application has a distinct notification endpoint for AWS SNS to publish the email notifications.

To demonstrate how to support separate endpoints for each application, the entity relationship diagram is as follows:

When an application makes an API call to the Email Sending Service, it includes the token in the meta fields to authorize the request, enabling the service to determine the email activity based on the hook id. Subsequently, when SNS subscription publishes the notification request to a hook, the system knows how to create the corresponding activity on the application's usage.

Background Job Processor

Background jobs are crucial in modern software development for two main reasons.
First, they handle long tasks without slowing down the main application, so users don't have to wait.
Second, they automatically try again if something goes wrong, minimize number of tasks gets lost or left undone.

The flow for the email sending and the hook notification processing looks like below:

Send Email flow
Process notification flow

Let's clarify the difference between another task scheduler system, where the processor acts as the master node distributing tasks to worker nodes, and the current case.
Here, the processor is a consumer class with methods that process jobs in the queue or listen to events on the queue. For more information on tasks queue documentation, you can refer to the bull documentation.

Data extraction from the notification can be done in the background as real-time updates are not required for analytic data. Parsing all the data from the payload may not be necessary, but we can store the notification for future data backfilling.

Testing strategy

Email testing can be expensive if actual emails need to be triggered. Fortunately, there are third-party services like MailCatcher or MailTrap that provide email sandbox solutions, rendering sent emails in a web interface. This eliminates the need to trigger actual emails to test purposes.

While third-party email sandbox services are often sufficient, I opted for a slightly improved solution with MailDev. Setting up MailDev for this project was straightforward, and I'll document the configuration process here.

The maildev can be run in Docker, here is the Docker Composer service definition:

  maildev:
    image: maildev/maildev
    expose:
      - "1025"
    ports:
      - "51080:1080"

Nothing is fancy here. The maildev service exposes the port 1025 for the Email App to connect, and allows the traffic to port 1080 (51080 from host) to access the dashboard.

Since we use NodeMailer package, it creates a Transport object to connect to AWS SES API.
To make the app send emails to MailDev in Dev environment, we need to create another transport object and hook it during the initialization.

The CreateTransportService takes in a driver parameter, which indicates the type of transport, the code looks like below:

enum MailDriver {
  ses,
  maildev,
}

export class DummyTransportation extends StreamTransport {}

export class CreateTransportService {
  constructor(private readonly options: TransportServiceOptions) {}

  create() {
    switch (MailDriver[this.options.driver]) {
      case MailDriver.ses: {
        const ses = new aws.SES(this.options.sesConfig);

        return createTransport({
          SES: { ses: ses, aws: aws },
        }).transporter;
      }
      case MailDriver.maildev: {
        return createTransport({
          ...this.options.mailDev,
        }).transporter;
      }
      default: {
        return new DummyTransportation({
          streamTransport: true,
        });
      }
    }
  }
}

The mailDev options object is simple with port and host, which can be passed into the app through a configuration file:

  maildev:
    port: 1025
    host: maildev

To speed up the test running time, we can defined the DummyTransportation to create mock Transportation when running test.

Capacity calculation

Understanding the traffic and data is important to determine the actual value of the platform we have developed.
The application primarily focuses on handling email sending requests from various applications within the organization. While it also offers a dashboard to monitor email activities, its main functionality revolves around efficiently managing the email communication.

To demonstrate, I will give an example how to calculate the capacity of the email system.

Sending rate: assume the app sends out 1 email per second, or 86k emails per day, 2.6M per month
For each email, the system stores the activities data and the recipients data The recipient data could be like this

    // TypeORM syntax
    @PrimaryGeneratedColumn('increment')
    readonly id: number;
    @Column({ unique: true })
    email: string;
    @Column('json')
    attributes: RecipientAttribute[];
    readonly createdAt: Date;
    // other columns

Each row recipient takes ~20bytes, we can assume if there are 10M of users in all managed applications, the total size of the recipients table is around 200MB, which is very affordable.

In the another side, the activities table could be a little bigger as the data is keep increasing.
Let's assume each activity takes around 30bytes, the size of the activities table would be

in a month: 2.6M * 30bytes = 78MB
in a year: 1GB
in 3 year: 3GB

While storage is not a concern, the sequential scanning of the activities table may result in slow performance. On a 1GB/sec (SSD disk) speed, generating complex dashboard data queries could take up to 3 seconds without optimization. Fortunately, the use of database indexes can greatly improve query times.

Overall, the application remains well-suited for the next three years, considering its current sending rate and user base.

Conclusion

In conclusion, I hope you found this blog post insightful and informative.
We've discussed some of the key aspects of the centralized Email service, though it's important to acknowledge that there are many other considerations, such as integrating it with different app clients, data tracking, and storage usage, among others.
As someone who has been involved in this project from its inception, I feel fortunate to have had the opportunity to design the application, define its features, and develop a comprehensive plan. Implementing a robust and efficient Email system can greatly enhance communication and efficiency. However, it's important to acknowledge the challenges that arise with the complexity of email configuration.

I'm thrilled to share this approach with you, and I hope it inspires you to enhance your organization's platform. Thank you for taking the time to read this!

Setup local domain with self-signed SSL certificate

Kevin Tran — Wed, 28 Jun 2023 20:30:59 +0000

Objective

The local domain are helpful to setup the connection between the frontend and backend on the development machine.
It is also a common solution when the actual address and port is difficult to remember.
In this post, I will explain how to set up the local domain and config the SSL certificate on the MacOS laptop.

Softwares required

In order to config the local domain name and issue the SSL cert, we'll need these tools installed:

openssl
MacOS Keychains app
Nginx: for configuring the proxy
Admin access
vi: to edit the host and create the configuration files

The nginx and vi are just my preference, you can use VSCode, emacs for editor and Apache, HA proxy for the proxy.

Create the local domain

Setup the domain is the most simple task, you can open the hosts file with the admin privillege.

  sudo vi /etc/hosts

Add a line for the new domain and map it to 127.0.0.1

127.0.0.1 mydomain.local

If you run the server inside a virtual network, for instance by running the proxy in the Virtual box or Docker, then use the IP of the virtual network interface instead.

After that, you may flush the DNS cache by using this command:

sudo dscacheutil -flushcache

Create the Root Certificate Authority (Root CA)

The SSL in the normal website are issued by a trusted Certificate Authority. However, for the local development, we don't need to purchase a SSL (and we don't own the domain), hence we have to setup the Root CA as well. During the handshake step, the browser checks whether the cert is valid by asking the Root CA about the cert, and if the Root CA doesn't confirm with the browser, the browser marks the site as unsafe.

To generate the Root CA, we can use the openssl tool.
The steps are:

Generate the root CA key
Generate the root CA cert

Personally, I suggest to create a directory to store all the files that are created during this process together, I usually create one in /tmp/ssl for this purpose. All the files we create after this are in the same directory.

To start with, we have to create the RootCA configuration file.

# fileName: config.conf

[req]
prompt = no
distinguished_name = req_distinguished_name
req_extensions = v3_req

[req_distinguished_name]
C = CA
ST = Kitchener
L = ON
OU = Org
CN = mydomain.local
emailAddress = dev@mydomain.local

[v3_req]
basicConstraints = CA:FALSE
keyUsage = nonRepudiation, digitalSignature, keyEncipherment, dataEncipherment
subjectAltName = @alt_names

[alt_names]
DNS.1 = mydomain.local
DNS.2 = api.mydomain.local
DNS.2 = www.mydomain.local

Please modify the content of the file to match with your information and the local domain.
The file references and examples can be found from IBM openSSL configuraton and OpenSSL document

After creating these 2 config files, we can generate the rootCA key and cert with these 2 commands

openssl genrsa -out rootCA.key 2048
openssl req -x509 -new -nodes -key rootCA.key -sha256 -days 1024 -out rootCA.pem -config config.conf

The values of each parameters can be altered by different values, such as the number of days can be change to 365, or the algorithm can be set to sha512. For the details of each parameters, checkout the OpenSSL manual.

Issue the Self-signed SSL cert

Create the ext file in the same directory with the content like below

# filename: mydomain.local.ext 

authoritykeyIdentifier = keyid,issuer
basicConstraints = CA:FALSE
keyUsage = nonRepudiation, digitalSignature, keyEncipherment, dataEncipherment
subjectAltName = @alt_names

[alt_names]
DNS.1 = mydomain.local
DNS.2 = api.mydomain.local
DNS.2 = www.mydomain.local

After this, we can generate the cert key file and the cert signing request file

openssl genrsa -out mydomain.local.key 2048
openssl req -new -key mydomain.local.key -out mydomain.local.csr -config config.conf

The last step is to issue the cert

openssl x509 \
        -in mydomain.local.csr -CA rootCA.pem -CAkey rootCA.key -CAcreateserial \
        -out mydomain.local.crt -days 1024 -sha256 -extfile mydomain.local.ext

After that, you should have the the cert (and other files) in the temporary directory. We don't need the config file as well as the ext file.
A common practice I usually take is to move these files to a more permanent place, such as /etc/ssl, so the files aren't gone after reboot.
To import the cert to the Apple Keychains, we can either use the Keychains UI or by running a command like below

sudo security add-trusted-cert -d -r trustRoot -k /Library/Keychains/System.keychain /etc/ssl/rootCA.pem

Noted that we don't have to import the cert for the local domain name.

Config the proxy

We can configure the SSL cert for the website from the proxy level instead of the app server. However, if you prefer to configure the cert from the app, please skip this section.

I prefer to use Nginx for the proxy because it is easy to setup and the document is excellent.

Here is how the nginx config look like

#filename: /opt/homebrew/etc/nginx/servers/mydomain.local.conf

upstream frontend {
   server localhost:3000;
   keepalive 100;
}

upstream backend{
   server localhost:8888;
   keepalive 100;
}

server {
  listen 443 ssl;
  server_name mydomain.local www.mydomain.local;

  ssl_certificate     /etc/ssl/mydomain.local.crt;
  ssl_certificate_key /etc/ssl/mydomain.local.key;

  # location config

  location / {
    proxy_http_version 1.1;
    proxy_pass http://frontend/;
  }

  location ~ /uploads/(file|image) {
    proxy_http_version 1.1;
    client_max_body_size 50M;
    proxy_pass http://frontend/;
  }
}

server {
  listen 443 ssl;
  server_name api.mydomain.local

  ssl_certificate     /etc/ssl/mydomain.local.crt;
  ssl_certificate_key /etc/ssl/mydomain.local.key;

  # location config

  location / {
    proxy_set_header Host $host;
    proxy_set_header X-Real-IP $remote_addr;
    proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
    proxy_set_header X-Forwarded-Proto $scheme;
    proxy_set_header Access-Control-Allow-Origin *;
    proxy_http_version 1.1;
    proxy_pass http://backend/;
  }
}

With Nginx, we can setup complicated routing rule to for the website traffic and configure the SSL without hassle.

A pro tips: If nginx is installed through **brew, we can use the brew services to make it starting automatically when the laptop is boot.

Recap

This short post is a summary for the steps to setup the local domain on the local machine.
I hope you find it useful for your works. And if you have set up the same stuffs in the Linux machine, please share the steps with me.

Rails Migration

Kevin Tran — Tue, 23 Jun 2020 14:28:51 +0000

Let's start by understanding what is Rails Migration.

What is Rails migration

Rails Migration is for changing the database structure.

It is different from the Model.

There are 2 types of language used to interact with the database, SQL and DDL.

SQL is used to CRUD data from/to database, DDL is to change the database design.

Rails Model (Active Record) works with SQL, and Rails Migration works with DDL.

Rails Model supports ways to interact to with the database, while Rails Migration changes the database structure.

A migration can change the name of a column in books table.

A migration can remove the table book_covers.

A migration can rename column sent_at to delivered_at in the table mail_histories.

A migration can add contraint to ensure field name in babies table isn't null.

Those are some examples of what a migration can do.

"Select those users who have more than 2 roles in the database" is done by Rails Model.

"Create a book with input fields, validate the isbn to be uniq before save" is the Model responsibility.

I hope this explanation works.

Rails migration supporting features

Change or up/down

Sometimes we see the method name is up and down and sometimes we see the method name is change in the migration file.

You can run rails db:migrate to run all pending migrations, and rails db:rollback to revert 1 migration.

When the rails db:migrate is executed, Rails will triggered the up or change method. When the command rails db:rollback method is executed, Rails will try to run the reversed change or down method.

There are behaviors that aren't reversible, such as change_column, change_table and manual SQL triggering.

If you are using those reversible behaviors, you can just use the change method.

And if so, you can define your own down migration to rollback the change. The other option is using the keyword reversible to define the up and down behaviors. Take a look at the example below.

  def change
    reversible do |dir|
      dir.up  { }
      dir.down { }
    end
  end

The information here you can find in the Rails guide. Please note that the migration can be reversed, but the data can't. When you perform one of those behaviors below:

Remove a table
Remove a column
Change the column type

The change will affect to existing data. This operation can't be reversed.

I want to ensure the column isn't accept null value. Hence I go ahead and add the constraint

  def change
    change_column_null :reminders, :message, false
  end

The migration fails in the first try. There is null value in the database. A common solution is adding a default value for the column, then adding the null check contraint. It may end up with 2 migrations instead one. It isn't a strict rule, you can write a script (a rake task) to set default value, which is totally fine. After adding the defalt value contraint, you can rerun the migration again

Rails has a better way to do so. The change_column_null actually has the fourth argument to set the default value for those rows with the column to add the null constraint are holding NULL value. This is the most elegant way I found so far to deal with this situation.

  def change
    change_column_null :reminders, :message, false, "default reminder message"
  end

Note that this won't add the default_value constraint to the column.

A similar situation, I want to add a unique constraint to a column, which may already contain duplication data, how should we deal with?

Unfortunately, there is no straight forward way to clean up duplication data, we have to clean it up separately before adding the index. Here is a blog post talking about a safe strategy to add unique index, check it out.

The race condition is also an annoying things while perform database migration.

While you try to run 2 steps migrations, for instance adding default value to a set of data, new data with null value comming in, and the migration to add the not_null constraint still remains failing. The same case can happen when the team are removing duplication data, more duplicated data is comming in.

Solving the race condition isn't easy too, hence for those risky operations, I think running the migration after working hours. Although we are looking at a zero time deployment, there are still places we have to take a risk assessment.

You may ask why don't add those constraint from the beginning. And the answer I can come up with: Well,... if we know all this, we can write a perfect software all the time, cheers.

I will talk about some Rails migration features in next sections.

Reference

  add_reference :books, :author

  # or

  create_table :books do |t|
    t.references :author
  end

This method adds a column name author_id to the books table.

However, it doesn't make author_id a foreign key to the authors table.

In fact, when I started with Rails, there is no add_reference. I thought the belongs_to :author, foreign_key: 'user_id' added the constraint to the source model. It turned out that the key word user_id here just mean the reference. It tells Rails (ActiveRecord) which column to join to the authors table.

Why so?

Check out this Hacker news thread and this Github issue

Because of some trade offs of the foreign key approach, such as performance issue, hard to combine data from many sources and extracting a subset of data, microservice approach, hence many team has decided to not go with foreign key approach to manage the relationship between models and rely on the ORM (app level) to handle this responsibility.

What are the disadvantages using the ORM to manage the integrity and relationship of the database? Compare to the foreign key approach, you don't have a reliable way to ensure the integrity on the database level. Most of the database is located in separate server. Usually there will be multiple instance of the web app which writting and reading to the same database simultaneously. From time to time, the race condition and network issue can create polution to the database.

However, if you need to use foreign_key, you can do that with an additional parameter foreign_key: true in the add_reference method.

  add_reference :books, :author, foreign_key: true

If you are wondering why need the add_reference method while what it does is adding a new column to table.

Both add_column and add_reference are both adding column to the table, plus add_reference does a bit more than that. Beside adding column, add_reference provides a more friendly syntax, and secondly it maps the model to column (reference from x to y mean adding column y_id to table x). It supports polymorphic association too. Thirdly, it adds an index for new column by default, unless you know what you are doing, just leave it. And lastly, you can add a foreign key constraint by specifying foreign_key: true.

Index

Database index is a big topic. In this post, I just cover an extra thin iceberg layer.

Generally database index speeds up database data retrieval operation.

SQL query checks if index for the lookup column exist then use it instead of sequently scanning through all rows of data.

If your project use email to find a user, it is definitely a good idea to have an index for the email column.

You can have index for multiple columns.

To add an index in Rails, you can use add_index method

  add_index :users, [:gender, :age], name: "user_index_by_gender_and_age"

I hope you are curious enough to ask why do we need multiple columns index, and I am happy to bring the question to the next stage: when the multiple columns index doesn't work, and what is difference between multiple column index and multiple indexes?

This article may answer your question.

Another common usecase of index is to ensure a column is unique.

The validation from model level can't ensure 100% as above discussion.

To add the unique index, adding the unique: true argument when create index.

I think it is an interesting topic, let me know if you feel the same way, then I will go more details in a separate post.

Postgresql extra data types

PostgreSQL Database supports additional data types.
By using these data type, we will depend on a specific database and the cost to migrate to different database is increased.

In this section, I share the 2 most common data types from Postgres.

Array
The array is useful to store an array of data like tags, scores, etc.
With array support, we don't have to create extra table to store those data.
Json & Jsonb
Extremely useful to store a complicated data while providing sufficient operation to retrieve and update the json data.
PostgreSQL support operators to directly access and update a key path.
Range
Define a range of data, for example age 0-18. With range type, you can perform some checking to see if ranges are overlap.

You don't have to design a complicated database models or adding a NoSQL Database while you can make it a column type in postgres.

For an example is a todo list application. There is no need to have different models for list and item, a todos table with a list of items, each contains detail, current state and complete date that is represent as a column with jsonb type.

Jsonb is faster when retrieving as well as it supports indexing, while json type is faster when writting to the database.

When use jsonb with Rails, the data you are getting back is just a hash. To convert the data from json column to Ruby object, we have to handle the mapping ourselves.

While writting this blog post, I find out Rails support serialize and store_accessor methods, which allows the model to access json attributes.

This blog post provides excellent explanation on how to use these 2 methods.

For the full list of postgres data type supported by Rails, check out the guide.

Multiple databases connection

After many years, Rails has supported multiple database connection.

This feature opens opportunity in many cases. Almost every medium project written on Rails are using more than 1 database.

What we can do with multiple database connection?

Switching connection from primary database and replica. Reading operators are using replica while writing's are using primary.

Depends on the nature of the app, some project needs heavy reading operations, By using replicas for reading, it is more ideal. The reason is because there is only 1 primary node while there can be many replicas node that copy the data from primary node and are used for reading purpose.

Distributing data to multiple databases

For example, user and profile can be stored separately. The app may not need to know about user profile when user login to the application. Another example is the product and orders. While the product data is used on the front page of the ecommerce, the order data is interests of the internal departments such as Sales, Finance and Delivery.

Of course, the foreign_key doesn't work with this database design approach.

Integrate with legacy application

Rails doesn't limit to use a database for only one reading or writting purpose. While working with legacy system, we may come up with new application for new features development while putting the old app in the maintaining mode. The legacy app is still in used without new feature instroduced. From the newer application, there are some data that the new app needs to retrieve from the legacy app's database.

Another approach is to use API to retrieve the data, it is quite depends on the current stage of the legacy app to decide which approach works better.

The complete guide to setup multiple database connection with Rails app is Rails guides - Multiple Databases with Active Record

Command line options

From Rails 5, you can call rails db:migrate and rails db:rollback instead of using rake db:migrate and rake db:rollback, they are still Rake tasks.

Rails makes it simple to just run the migrations. It will compare the last record of the table schema_migrations in your database and the version in db/schema.rb file to see if it is up-to-date. If the timestamp from the schema_migrations table is lower than the one in db/schema.rb, Rails will execute all migration files with the version after the version of the db/schema.rb.

The version is a timestamp follow format yyyymmddhhmmss, e.g 20200406022729.

Because it is easier to learn with example, here are some Rails migration commands I use often

    rails db:migrate
    rails db:rollback STEP=2                   # rollback by 2 steps, useful during development, not push yet.
    rails db:schema:load                       # load databse from schema file instead.
    rails db:seed                              # create seed data
    rails db:migrate:up VERSION=20200616035428 # run a migration file, quite useful when you need it 😉

I used to add this task to perform a migration way of resetting database. We can create a custom rake task that triggers multiple existing rake task like below

    # lib/tasks/db/rebuild_dev.rake
    namespace :db do
      desc "This task rebuild the db for development environment"
      task rebuild_dev: %i(drop create migrate seed)
    end

You can run it by rails db:rebuild_dev. It drops the databases (ensure the rails console and rails server are quited), recreate the databases, run migration and create seed data for you, all in once.

The built in rake db:reset does almost the same thing, except it loads the schema file to database instead of running migration.

However, you don't need this custom db:rebuild_dev task because you can use the built-in rails db:migrate:reset instead.

Sql Structure schema

It is generally same with exporting the Data definition language (DDL) from a database to a SQL file.
You can use the file to load into the a database to do some works without installing Rails just to run the migration.

In the Rails application, Rails allows us to choose between Ruby DSL and SQL to store the snapshot of the database.

Each time the migration command is executed, the db/schema.rb is updated as a snapshot of your database. It is written in Ruby, it is like a aggregation of all migration files. Some migrations you may add a new column, some migration you change the column name and drop the table, but the latest state is stored in the db/schema.

By running rails db:schema:load, all of the existing table are wiped out and the entire database is loaded up again.

Remember to not use this command on the production database.

Knowing the Database snapshot is written in Ruby, we know that Rails has to maintain the DSL of the schema to make it compatible with all supported Database. That requires a lot of efforts and definitely there are some lags to catch up, for instance the data type jsonb in Postgresql isn't reflected correctly for a long time.

When using those cutting edge features from DB or you prefered to use SQL schema since it is more natural to you, or you have to import the sql schema to the DB to perform some works and those servers aren't accessible from outside, the SQL Structure schema may be the only choice.

Conclusion

Rails migration helps interacting with the Database easier.

It is a thin layer but handle an extremely important component used by the rest of the app - the database.

By taking advantages of this layer, the app can perform very fast, hence we may consider to understand Rails migration and Database well.

The real database design is usually different from what we have taught in school. We should open mind to understand the rationale behind.

NoSQL Database and reactive programming also open new ways to fix some missing features of SQL database. With new technologies, it may outdate? the migration layer in Rails. Please let me know your thoughts.