Handling IoT (Internet of Things) data in PostgreSQL involves designing a database schema that accommodates the unique characteristics of IoT data. Here are some general guidelines and considerations, along with a basic ER (Entity-Relationship) diagram for an IoT database.

Guidelines for Handling IoT Data in PostgreSQL:

1. Identify IoT Devices:

• Create a table to store information about the IoT devices (e.g., device_id, device_type, manufacturer, etc.).

CREATE TABLE devices (
    device_id SERIAL PRIMARY KEY,
    device_type VARCHAR(50),
    manufacturer VARCHAR(50),
    -- Other device attributes
);

2. Capture Sensor Data:

• Create a table to store sensor data readings. This table can be partitioned by time to improve query performance over time-series data.

CREATE TABLE sensor_data (
    reading_id SERIAL PRIMARY KEY,
    device_id INT REFERENCES devices(device_id),
    timestamp TIMESTAMP,
    sensor_type VARCHAR(50),
    value NUMERIC,
    -- Other sensor data attributes
);

3. Location Tracking:

• If your IoT devices have location data, include a table to store this information.

CREATE TABLE device_location (
    location_id SERIAL PRIMARY KEY,
    device_id INT REFERENCES devices(device_id),
    timestamp TIMESTAMP,
    latitude NUMERIC,
    longitude NUMERIC,
    -- Other location attributes
);

4. Event Logging:

• Capture events and logs related to device activities.

CREATE TABLE device_events (
    event_id SERIAL PRIMARY KEY,
    device_id INT REFERENCES devices(device_id),
    timestamp TIMESTAMP,
    event_type VARCHAR(50),
    -- Other event attributes
);

5. Security and Authentication:

• If security is a concern, implement a table for user authentication and authorization.

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    password_hash VARCHAR(255),
    -- Other user attributes
);

6. Relationships:

• Establish relationships between tables using foreign keys to maintain data integrity.

Ideal ER Diagram:

In the diagram:

• Each box represents a table.
• Lines with diamonds indicate relationships, and the cardinality is marked on each side (e.g., 1:M for one-to-many relationships).
• Primary keys are underlined.
• Foreign keys are denoted with an arrow pointing to the referenced table.

Handling large volumes of data and ensuring that queries remain performant over time

Handling large volumes of data and ensuring that queries remain performant over time is a common challenge in database management, especially for IoT applications with continuous data streams. Here are some strategies to address this issue:

Data Partitioning:

• Partition large tables into smaller, more manageable pieces based on a chosen partition key, such as time or device ID. This helps distribute data across multiple storage locations, making queries on a subset of the data more efficient.

CREATE TABLE sensor_data (
    reading_id SERIAL PRIMARY KEY,
    device_id INT REFERENCES devices(device_id),
    timestamp TIMESTAMP,
    sensor_type VARCHAR(50),
    value NUMERIC,
    -- Other sensor data attributes
) PARTITION BY RANGE (timestamp);

Indexing:

• Proper indexing is crucial for fast query performance. Identify columns frequently used in WHERE clauses and create indexes on those columns. However, be cautious not to over-index, as it can impact write performance.

CREATE INDEX idx_timestamp ON sensor_data(timestamp);
CREATE INDEX idx_device_id ON sensor_data(device_id);

Materialized Views:

• Use materialized views for pre-aggregated or pre-joined data that is queried frequently. This can significantly improve query response times by avoiding the need to perform complex calculations on the fly.

CREATE MATERIALIZED VIEW daily_sensor_avg AS
SELECT
    device_id,
    date_trunc('day', timestamp) AS day,
    AVG(value) AS avg_value
FROM
    sensor_data
GROUP BY
    device_id, day;

Archiving and Purging:

• Regularly archive or purge old data that is no longer needed. This reduces the volume of data in the active database and can improve query performance.

DELETE FROM sensor_data WHERE timestamp < '2022-01-01';

Compression:

• Use compression for large tables, especially if storage space is a concern. PostgreSQL supports various compression techniques that can help reduce the physical size of the database.

CREATE TABLE sensor_data_compressed AS
SELECT * FROM sensor_data
WITH (FILLFACTOR = 90, COMPRESSDATA = 'ON');

Regular Database Maintenance:

• Perform regular database maintenance tasks, such as vacuuming and analyzing, to optimize the performance of the PostgreSQL database.

VACUUM ANALYZE;

Scaling:

• Consider scaling horizontally by using techniques such as sharding or distributed databases to distribute the load across multiple servers.

-- Example of sharding with Citus extension
SELECT create_distributed_table('sensor_data', 'device_id');

Caching:

• Implement caching mechanisms to store and retrieve frequently accessed data in memory, reducing the need to query the database for the same data repeatedly.

Query Optimization:

• Regularly review and optimize your queries. Use the EXPLAIN command to analyze query plans and identify areas for improvement.

EXPLAIN SELECT * FROM sensor_data WHERE device_id = 123;

By implementing these strategies, you can manage large volumes of data in PostgreSQL while ensuring that queries remain performant over time.

Storing sensor data at a rate of 30,000 rows per minute and keeping the last 5 years of data

storing sensor data at a rate of 30,000 rows per minute and keeping the last multiple years of data, efficiently managing the sensor_data table becomes crucial. Here are precise recommendations tailored to this scenario:

Data Partitioning by Time:

• Partition the sensor_data table by time, with a partition key based on the timestamp. This allows for quick retrieval of data within specific time ranges.

CREATE TABLE sensor_data (
    reading_id SERIAL PRIMARY KEY,
    device_id INT REFERENCES devices(device_id),
    timestamp TIMESTAMP,
    sensor_type VARCHAR(50),
    value NUMERIC,
    -- Other sensor data attributes
) PARTITION BY RANGE (timestamp);

-- Create partitions for each year
CREATE TABLE sensor_data_2023 PARTITION OF sensor_data
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

Indexes:

• Create indexes on columns commonly used in queries, such as the timestamp and device_id.

CREATE INDEX idx_timestamp ON sensor_data(timestamp);
CREATE INDEX idx_device_id ON sensor_data(device_id);

Archiving and Purging:

• Regularly archive or purge old data to keep the active dataset manageable. In this case, you can archive or delete data older than 5 years.

DELETE FROM sensor_data WHERE timestamp < NOW() - INTERVAL '5 years';

Compression:

• Consider using table compression to save storage space, especially if the dataset is large.

CREATE TABLE sensor_data_compressed AS
SELECT * FROM sensor_data
WITH (FILLFACTOR = 90, COMPRESSDATA = 'ON');

Table Maintenance:

• Perform routine maintenance tasks, such as vacuuming and analyzing, to optimize the performance of the sensor_data table.

VACUUM ANALYZE sensor_data;

Adjust Autovacuum Settings:

• Tune autovacuum settings to suit the write-intensive nature of your IoT application. Ensure that autovacuum is adequately managing dead rows and reclaiming space.

-- Adjust autovacuum settings in postgresql.conf or dynamically
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_scale_factor = 0.05

Consider Data Aggregation:

• Depending on the nature of queries, consider aggregating data at a coarser granularity (e.g., hourly or daily averages) and storing this aggregated data separately. This can significantly speed up certain types of queries.

CREATE TABLE hourly_sensor_avg AS
SELECT
    device_id,
    date_trunc('hour', timestamp) AS hour,
    AVG(value) AS avg_value
FROM
    sensor_data
GROUP BY
    device_id, hour;

Data partitioning is a database design technique

Data partitioning is a database design technique where a large table is divided into smaller, more manageable pieces called partitions. Each partition contains a subset of the data based on a specific range or condition. In the context of IoT data, which often involves a time series, partitioning by time is a common and effective strategy.

Let's break down the provided SQL code for partitioning the sensor_data table by time:

CREATE TABLE sensor_data (
    reading_id SERIAL PRIMARY KEY,
    device_id INT REFERENCES devices(device_id),
    timestamp TIMESTAMP,
    sensor_type VARCHAR(50),
    value NUMERIC,
    -- Other sensor data attributes
) PARTITION BY RANGE (timestamp);

This SQL code creates the main table sensor_data with the following characteristics:

• reading_id: A serial column serving as the primary key.
• device_id: An integer column referencing the device_id in the devices table.
• timestamp: A timestamp column representing the time of the sensor reading.
• sensor_type: A varchar column for the type of sensor.
• value: A numeric column for the sensor reading value.
• Other sensor data attributes can be added as needed.

The crucial part here is PARTITION BY RANGE (timestamp). This line specifies that the table will be partitioned based on the timestamp column, and the partitions will be defined using a range.

Now, to create partitions for specific time ranges (in this example, each year), the following code is used:

-- Create a partition for the year 2023
CREATE TABLE sensor_data_2023 PARTITION OF sensor_data
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

Here, a new table sensor_data_2023 is created as a partition of the main sensor_data table. The FOR VALUES FROM ('2023-01-01') TO ('2024-01-01') clause specifies that this partition will store data for the time range from January 1, 2023, to January 1, 2024.

By creating separate partitions for each year, you can efficiently manage and query data for specific time ranges. This design optimizes performance when retrieving data within a particular timeframe, as the database engine knows where to look based on the partitioning key (timestamp), reducing the amount of data that needs to be scanned.

Managing Subsequent Year Partitions:

Create a Template for Partition Creation:

• Create a template SQL script that you can reuse for creating new yearly partitions. This template should include the necessary SQL statements to create a new partition for a specific year.

-- Template for creating yearly partition
CREATE TABLE sensor_data_<year> PARTITION OF sensor_data
    FOR VALUES FROM ('<year>-01-01') TO ('<year + 1>-01-01');

Create Partitions for Subsequent Years:

• As each new year begins, execute the template script with the appropriate year values to create partitions for subsequent years.

-- Create a partition for the year 2024
CREATE TABLE sensor_data_2024 PARTITION OF sensor_data
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

• Repeat this step for each new year.

Drop Old Partitions (Optional):

• Depending on your data retention policy, you may want to drop partitions for years that are no longer relevant. Be cautious when doing this to avoid data loss.

-- Drop the partition for the year 2018 (example)
DROP TABLE sensor_data_2018;

Automation with a Script:

• To streamline the process, consider writing a script or using a scheduling tool that automates the creation of new yearly partitions based on the current date.
• Example (pseudo-code):

bashCopy code
current_year = get_current_year()
next_year = current_year + 1

sql_script = generate_sql_script(template_script, current_year, next_year)

execute_sql_script(sql_script)

• Such a script can be scheduled to run at the beginning of each year.

Important Considerations:

• Data Retention Policy:
• Ensure that you have a clear data retention policy in place. Dropping old partitions is a way to manage storage, but make sure it aligns with your business requirements.
• Testing:
• Before implementing any changes, thoroughly test the process in a staging environment to avoid unintended consequences in the production database.
• Monitoring:
• Implement monitoring to keep track of partition sizes, database performance, and any potential issues arising from the manual management process.
• Review and Update:
• Periodically review and update your partitioning strategy based on evolving data patterns and business needs.

Remember, managing partitions manually can become challenging as the number of years grows. If automation is feasible within your environment, it is generally a more sustainable solution.

In PostgreSQL, indexes

In PostgreSQL, indexes can be either clustered or non-clustered. Let's break down the concepts and understand the specific case of the sensor_data table.

Non-Clustered Indexes:

Timestamp Index:

CREATE INDEX idx_timestamp ON sensor_data(timestamp);

This statement creates a non-clustered index named idx_timestamp on the timestamp column of the sensor_data table. Non-clustered indexes store a separate data structure that contains a mapping between the indexed values (timestamps, in this case) and the corresponding rows in the table. They do not alter the physical order of the table itself.

Device_ID Index:

CREATE INDEX idx_device_id ON sensor_data(device_id);

Similarly, this statement creates a non-clustered index named idx_device_id on the device_id column. This index is useful when queries involve filtering or sorting based on the device_id.

Clustered Indexes:

In PostgreSQL, the term "clustered index" is used differently than in some other database systems. In PostgreSQL, there is a feature called the "cluster" command that physically reorders the rows in a table based on the order of an index. This is not the same as a traditional clustered index in some other databases.

For example, you might use the CLUSTER command like this:

CLUSTER sensor_data USING idx_timestamp;

This command reorganizes the sensor_data table on disk so that the rows are physically stored in the order defined by the idx_timestamp index. This operation can improve the performance of range queries on the timestamp column.

No Default Clustered Index:

It's important to note that PostgreSQL does not have a default clustered index for tables. When you create an index, it is non-clustered by default. The decision to cluster a table based on an index is explicit and needs to be done using the CLUSTER command.

Considerations:

• Cluster Command Use:
• Using the CLUSTER command can be resource-intensive and should be done carefully, especially in production environments. It involves temporarily taking the table offline during the clustering process.
• Regular Index Use:
• In many cases, non-clustered indexes (like those created with the CREATE INDEX statements) are sufficient for optimizing query performance. Consider using these indexes unless you have specific reasons to cluster the table.
• Index Maintenance:
• Regularly monitor and maintain your indexes. PostgreSQL's autovacuum process helps manage the health of indexes over time.

Remember that the decision to use clustered indexes depends on the specific requirements and usage patterns of your application. It's recommended to analyze query performance, evaluate the impact of clustering on your specific workload, and make decisions accordingly.

Fragmentation in the context of a database

Fragmentation in the context of a database generally refers to the phenomenon where data is scattered or dispersed in a non-contiguous manner, leading to suboptimal performance. There are two main types of fragmentation: internal fragmentation and external fragmentation.

1. Internal Fragmentation:

• Definition: Internal fragmentation occurs within data structures, such as tables or indexes, when space is allocated but not fully utilized. This can happen due to variable-length data types, padding, or inefficient storage allocation.
• Example: In the context of a database, internal fragmentation might occur when variable-length columns are used, and the actual data does not fully utilize the allocated space.
• How to Handle Internal Fragmentation:
• Regularly perform maintenance tasks like vacuuming or rebuilding indexes. This helps reclaim unused space within the data structures.

2. External Fragmentation:

• Definition: External fragmentation occurs when the physical storage of data becomes non-contiguous, leading to inefficiencies in data access. This can happen when data is deleted or updated, and free space becomes fragmented across different disk locations.
• Example: In a table, if rows are frequently deleted or updated, the free space left by these operations might be scattered across various data pages, causing external fragmentation.
• How to Handle External Fragmentation:
• Reorganize Data: Periodically reorganize or rebuild tables and indexes to consolidate fragmented free space.
• Use Indexes Wisely: Well-designed indexes can reduce the impact of external fragmentation by providing efficient access paths to data.

Handling Fragmentation in PostgreSQL:

Vacuuming:

• PostgreSQL uses a process called vacuuming to manage internal and external fragmentation. The VACUUM command reclaims storage occupied by dead rows and ensures that space is used efficiently.

VACUUM;

Auto-vacuum:

• PostgreSQL has an auto-vacuum process that runs automatically in the background. It helps manage internal and external fragmentation without manual intervention. Ensure that auto-vacuum is appropriately configured.

Rebuilding Indexes:

• If you observe significant index fragmentation, consider rebuilding indexes. This can be done using the REINDEX command.

REINDEX INDEX idx_example;

Cluster Command:

• The CLUSTER command can be used to physically reorder the table based on an index. This can help reduce both internal and external fragmentation, but it requires exclusive access to the table.

CLUSTER my_table USING idx_example;

Analyze:

• Regularly analyze the performance of your database, identify tables or indexes with high fragmentation, and take appropriate corrective actions.

Remember that managing fragmentation is an ongoing process, and the specific strategies you employ may depend on the workload, data patterns, and maintenance requirements of your PostgreSQL database.

Auto-vacuum in PostgreSQL

Auto-vacuum in PostgreSQL is a background process that helps manage the storage and performance of the database by reclaiming space occupied by dead rows and updating statistics. In most PostgreSQL installations, auto-vacuum is enabled by default. However, you may need to adjust its configuration settings to better suit your database workload.

Here's how you can configure auto-vacuum in PostgreSQL:

Check Auto-vacuum Status:

Before making changes, you can check the current status of auto-vacuum in your PostgreSQL database:

OW autovacuum;
SHOW autovacuum_vacuum_scale_factor;
SHOW autovacuum_analyze_scale_factor;
SHOW autovacuum_vacuum_cost_limit;

Adjust Auto-vacuum Configuration:

Enable/Disable Auto-vacuum:

By default, auto-vacuum is enabled. If, for some reason, it's disabled, you can enable it with the following command:

ALTER SYSTEM SET autovacuum = on;

If you need to disable auto-vacuum (not recommended in most cases), you can use:

ALTER SYSTEM SET autovacuum = off;

Adjust Scale Factors:

Auto-vacuum has scale factors that determine when it should run based on the number of updated or inserted rows. You can adjust these factors to fine-tune the auto-vacuum behavior.

-- Adjust the scale factor for vacuum
ALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.1;

-- Adjust the scale factor for analyze
ALTER SYSTEM SET autovacuum_analyze_scale_factor = 0.05;

The scale factors control the fraction of the table that needs to be modified before auto-vacuum is triggered.

Set Vacuum Cost Limit:

The autovacuum_vacuum_cost_limit parameter controls how much work is performed by a single vacuuming process before it sleeps to avoid impacting other database activities.

-- Set the vacuum cost limit
ALTER SYSTEM SET autovacuum_vacuum_cost_limit = 2000;

Adjust this value based on your system's resources and workload.

Reload Configuration:

After making changes to the configuration, you need to reload the configuration to apply the new settings:

SELECT pg_reload_conf();

Monitoring Auto-vacuum:

You can monitor auto-vacuum activity using the following system views:

pg_stat_user_tables:

• Displays information about tables.

SELECT * FROM pg_stat_user_tables;

pg_stat_user_indexes:

• Displays information about indexes.

SELECT * FROM pg_stat_user_indexes;

pg_stat_bgwriter:

• Displays information about the background writer process.

SELECT * FROM pg_stat_bgwriter;

These views provide insights into the auto-vacuum activity, including the number of rows and pages vacuumed or analyzed.

Remember that auto-vacuum is generally well-tuned by default, and you may not need to make significant changes unless you have specific performance requirements or challenges in your PostgreSQL environment.

In the world of software development, event-driven architecture (EDA) has gained significant popularity as a powerful approach to building scalable and decoupled systems. By leveraging events as the primary means of communication between components, EDA enables flexible and loosely coupled architectures that can handle complex workflows and seamlessly integrate with various services. In this blog post, we'll explore the fundamentals of event-driven architecture and demonstrate how you can implement it using C# snippets.

Items to Cover

1. What is Event-Driven Architecture?
2. Key Components of Event-Driven Architecture
3. Benefits of Event-Driven Architecture
4. Implementing the Event-Driven Architecture in C# a. Setting Up the Event Infrastructure b. Defining Events and Event Handlers c. Publishing and Subscribing to Events d. Error Handling and Retry Mechanisms
5. Real-World Use Cases for Event-Driven Architecture
6. Conclusion

1. What is Event-Driven Architecture

Event-Driven Architecture is a design pattern where components communicate and react to events occurring within the system. An event represents a significant occurrence or change in the system's state. Examples of events can include user actions, system events, or external triggers. The primary characteristic of EDA is the decoupling of components, allowing them to function independently and react to events asynchronously.

2. Key Components of Event-Driven Architecture

• Event: A significant occurrence or change in the system that triggers a reaction.
• Event Producer: The component responsible for generating events and notifying the system.
• Event Consumer: The component that reacts to events by executing specific actions or processes.
• Event Broker/Bus: A middleware component that handles event publishing, event routing, and event subscriptions.
• Event Handler: A piece of code that defines the logic to be executed when a specific event occurs.

3. Benefits of Event-Driven Architecture

• Loose Coupling: Components are decoupled, allowing them to evolve independently.
• Scalability: Event-driven systems can scale horizontally by adding more event consumers.
• Flexibility: New functionalities can be added by simply subscribing to relevant events.
• Asynchronicity: Components can process events independently without blocking each other.
• Resilience: Failure of one component doesn't impact the entire system.
• Event Traceability: Events provide a comprehensive audit trail of system actions.

4. Implementing Event-Driven Architecture in C#

a. Setting Up the Event Infrastructure: To implement event-driven architecture in C#, you'll need to set up an event infrastructure. This can be achieved using a message broker like RabbitMQ or a pub-sub system like Azure Service Bus.

b. Defining Events and Event Handlers: Define events as classes or structs in C#. An event handler is a method that gets executed when the corresponding event occurs. For example:

public class OrderPlacedEvent
{
    public Guid OrderId { get; set; }
    public DateTime OrderDate { get; set; }
    // Additional properties
}

public class OrderPlacedEventHandler
{
    public void Handle(OrderPlacedEvent @event)
    {
        // Event handling logic
    }
}

c. Publishing and Subscribing to Events: To publish an event, you need to instantiate the event class and send it to the event broker or bus. Subscribing to an event involves registering the event handler with the broker or bus. Here's an example:

// Publishing an event
var orderPlacedEvent = new OrderPlacedEvent { OrderId = orderId, OrderDate = DateTime.Now };
eventBus.Publish(orderPlacedEvent);

// Subscribing to an event
eventBus.Subscribe<OrderPlacedEvent>(new OrderPlacedEventHandler().Handle);

d. Error Handling and Retry Mechanisms: Implement error handling and retry mechanisms to ensure the resilience and reliability of the event-driven system. Techniques like dead-letter queues, exponential backoff, and circuit breakers can be used to handle failures gracefully.

5. Real-World Use Cases for Event-Driven Architecture

• E-commerce platforms: Handling order placement, inventory management, and shipping updates.
• Microservices architectures: Enabling communication between services while maintaining loose coupling.
• Internet of Things (IoT) systems: Reacting to sensor data and triggering appropriate actions.
• Real-time analytics: Processing data streams and generating insights in real-time.

3. Conclusion

Event-Driven Architecture offers a flexible and scalable approach to building complex systems. By leveraging events and decoupling components, you can achieve resilience, scalability, and maintainability. With C# and the provided code snippets, you can start implementing event-driven architectures and harness the power of event-based systems in your applications.

Remember, event-driven architecture is a paradigm that requires careful design and consideration of your system's requirements. When used appropriately, it can lead to highly scalable, responsive, and modular applications.

Happy event-driven coding in C#!

To describe the Coupling in OOP and its benefits.

Explanation

Coupling in object-oriented programming (OOP) refers to the degree of interdependence between classes or modules. It indicates how closely two or more classes are connected or rely on each other.

Coupling can be categorized into different levels:

1. Loose coupling: Classes are minimally dependent on each other, and changes in one class have minimal impact on other classes. Communication between classes is typically achieved through well-defined interfaces or abstractions.
2. Moderate coupling: Classes have some level of dependency on each other, but changes in one class may require corresponding modifications in other classes. Communication between classes may involve passing data or invoking methods directly.
3. Tight coupling: Classes are highly dependent on each other, and changes in one class can significantly impact or break the functionality of other classes. Communication between classes involves direct references or dependencies on concrete implementations.

While loose coupling is generally considered beneficial, it is important to strike a balance based on the specific requirements and design of the system. Here are some reasons why loose coupling is helpful:

1. Modularity: Loose coupling promotes modularity by allowing classes or modules to be developed and maintained independently. Changes or updates to one module can be made without affecting others, making the system more flexible and easier to manage.
2. Code reusability: Loose coupling enables greater code reusability. Well-defined interfaces and abstractions make it easier to replace or extend the implementation of a class without impacting other parts of the system.
3. Testability: Loosely coupled classes are easier to test in isolation since they can be mocked or stubbed more effectively. This facilitates unit testing, as individual classes can be tested independently without requiring the presence of all the dependencies.
4. Scalability and Parallel Development: Loose coupling supports scalability and parallel development. Different teams or developers can work on different modules simultaneously without stepping on each other's toes, as long as they adhere to the agreed-upon interfaces and contracts.
5. Maintainability: Loose coupling enhances maintainability by reducing the ripple effect of changes. Modifications or bug fixes in one class have minimal impact on other classes, making it easier to understand, modify, and enhance the system over time.

Example with C#

Here's an example in C# to illustrate different levels of coupling:

// High coupling (Tight coupling)
public class Car
{
    private Engine engine;

    public Car()
    {
        engine = new Engine();
    }

    public void Start()
    {
        engine.Start();
    }
}

public class Engine
{
    public void Start()
    {
        Console.WriteLine("Engine started.");
    }
}

In the above example, the Car class has a tight coupling with the Engine class. It directly creates an instance of the Engine class within its constructor and invokes the Start method on it. Any changes to the Engine class, such as renaming the method or modifying its behavior, may require corresponding modifications in the Car class.

// Moderate coupling
public interface IEngine
{
    void Start();
}

public class Car
{
    private IEngine engine;

    public Car(IEngine engine)
    {
        this.engine = engine;
    }

    public void Start()
    {
        engine.Start();
    }
}

public class Engine : IEngine
{
    public void Start()
    {
        Console.WriteLine("Engine started.");
    }
}

In this example, the Car class is moderately coupled with the Engine class through the IEngine interface. The Car class depends on the IEngine interface, allowing different implementations of the engine to be injected at runtime. This flexibility allows for easier testing and decouples the Car class from specific implementations of the engine.

// Low coupling (Loose coupling)
public interface IEngine
{
    void Start();
}

public class Car
{
    private IEngine engine;

    public Car(IEngine engine)
    {
        this.engine = engine;
    }

    public void Start()
    {
        engine.Start();
    }
}

public class ElectricEngine : IEngine
{
    public void Start()
    {
        Console.WriteLine("Electric engine started.");
    }
}

public class CombustionEngine : IEngine
{
    public void Start()
    {
        Console.WriteLine("Combustion engine started.");
    }
}

In this example, the Car class has low coupling by depending on the IEngine interface, which can be implemented by different engine types. The ElectricEngine and CombustionEngine classes implement the IEngine interface, providing specific engine behaviors. The Car class can work with any engine type as long as it adheres to the IEngine interface, resulting in loose coupling.

By using interfaces and dependency injection, we can achieve lower levels of coupling, making the system more modular, extensible, and maintainable.

Conclusion

Overall, loose coupling in OOP promotes flexibility, modularity, reusability, testability, and maintainability, contributing to the development of robust and adaptable software systems.

The purpose of this blog post is to provide an overview of Dependency Injection (DI) and Inversion of Control (IoC) in software development, explain their differences and similarities, and help readers understand how they can use them to improve the design and maintainability of their applications.

Background

In software development, managing dependencies is an important aspect of creating maintainable and testable code. Both DI and IoC are patterns that can be used to manage dependencies and improve the flexibility and maintainability of applications.

Problem statement

One common problem in software development is managing complex dependencies between objects and modules. If dependencies are not managed properly, it can be difficult to test and maintain the code, and changes to one module can have unintended consequences on other modules. This can lead to code that is hard to maintain, understand, and extend.

Possible solutions

Dependency Injection

DI is a technique for implementing IoC by injecting dependencies into objects, rather than creating or managing them within the object itself. This makes the dependencies explicit and testable, and it allows them to be easily replaced or updated without modifying the object itself.

Let's start with an example of Dependency Injection in C#. Suppose we have a simple application that needs to print a message to the console. We can create an interface IPrinter and an implementation of that interface ConsolePrinter as follows:

public interface IPrinter
{
    void Print(string message);
}

public class ConsolePrinter : IPrinter
{
    public void Print(string message)
    {
        Console.WriteLine(message);
    }
}

We can then create a class MessageService that depends on an IPrinter, which is injected as a constructor argument:

public class MessageService
{
    private readonly IPrinter _printer;

    public MessageService(IPrinter printer)
    {
        _printer = printer;
    }

    public void PrintMessage(string message)
    {
        _printer.Print(message);
    }
}

We can now create an instance of MessageService and inject an instance of ConsolePrinter:

var messageService = new MessageService(new ConsolePrinter());
messageService.PrintMessage("Hello, world!");

In this example, we're injecting an instance of ConsolePrinter into MessageService. If we wanted to use a different implementation of IPrinter, we could simply create a new implementation and inject it instead.

Inversion of Control

IoC is a design principle that states that the control flow of a program should be inverted, so that the framework or container manages the dependencies and control flow of the application, rather than the application managing its own dependencies. This means that the application doesn't create or manage its own dependencies, but instead relies on an external framework or container to provide them. This makes the application more flexible and easier to modify, because dependencies can be easily swapped out or updated.

An example of Inversion of Control using a container. We can use a third-party IoC container such as Autofac to manage our dependencies.

First, we need to register our dependencies with the container:

var builder = new ContainerBuilder();
builder.RegisterType<ConsolePrinter>().As<IPrinter>();
builder.RegisterType<MessageService>();
var container = builder.Build();

We're registering ConsolePrinter as an implementation of IPrinter, and MessageService as a type that depends on IPrinter.

We can now resolve an instance of MessageService from the container:

var messageService = container.Resolve<MessageService>();
messageService.PrintMessage("Hello, world!");

When we call container.Resolve<MessageService>(), Autofac automatically resolves the dependencies of MessageService, which in this case is an instance of ConsolePrinter.

In this example, we're using an IoC container to manage our dependencies and control the flow of our application. If we wanted to use a different implementation of IPrinter, we could simply register a different implementation with the container. This makes our application more flexible and easier to modify over time.

Conclusion

Both DI and IoC are powerful patterns that can help improve the flexibility, testability, and maintainability of applications. Which pattern to use depends on the specific requirements and constraints of the application. However, in general, using DI and IoC can lead to better-organized code, improved testability, and easier maintenance and extension of the application over time. It is recommended that developers become familiar with these patterns and incorporate them into their design process.

Recently I have become a Scrum Master. The workaround of Scrum is very interesting to me. This is an approach to value-oriented project management.

The comprehensive consolidation of the Scrum aspects is listed in the fantastic presentation platform named Prezi by me. The details can be viewed from the link below:

SCRUM Overview

FRAMEWORK FOR PROJECT MANAGEMENT SCRUM Prepared by: Nazmul Hossain Waterfall vs Scrum Waterfall VS Scrum Development – with Scrum we have iterative development and with Waterfall it’s sequential Progress – with Scrum we see the progress by the delivery of valued features every How

prezi.com

Designing an IoT solution by considering all the scenarios is very important for the long run of the product. Will describe a Microservice-based IoT Solution Architecture in this article.

Background

Before you finish reading this idea of architecting IoT solution, hundreds of new devices will be connected to the web at a breathtaking pace of 127 additions per minute. For the most part, they belong to the Internet of Things (IoT), or gadgets capable of communicating and sharing data without human interaction.

The technology will shift into an even higher gear with the wide use of fifth-generation or 5G networks supporting a million gadgets per square kilometer — ten times as many as in the 4G era. The number of active IoT connections is expected to double by 2025.

Day by day, the IoT sees wider adoption, opening new opportunities and driving more value to both businesses and their clients. For companies, incorporating the consistent IoT strategy into daily routine means continuous access to valuable data about products and processes that can be translated into reduced expenses, improved efficiency in logistics and maintenance, better products, and enhanced customer experience.

But to build and run a robust infrastructure, a manufacturer or service provider needs a solid foundation — in other words, an IoT platform that connects devices, collects data, and creates insights. This article will explain the IoT ecosystem, outline the core IoT platform functionality, and compare major players to help you make the right choice.

Problem statement (Issue or Challenge)

There are multiple layers to consider before designing the solution. With an IoT platform acting as a bridge between the physical world and business processes, the IoT infrastructure contains several key layers:

• Perception layer (hardware components such as sensors, actuators, and devices)
• Transport layer (networks and gateway)
• Processing layer (middleware or IoT platforms) and
• Application layer (software solutions for end users)

Perception layer: IoT hardware

The hardware or “things” layer includes the following gears that work with signals from the physical world.

Electronic sensors capture signals from the physical world, convert them into digital form, and feed them to the IoT system. You can monitor and manage sensors remotely, using a special application.

Actuators receive signals from the IoT system and translate them into physical actions manipulating equipment. Similar to sensors, actuators can be configured from remote computers.

Devices are connected to sensors or even have them embedded as an integral part. On the other side, devices link to a gateway or directly to an IoT platform. These hardware components cache and preprocess real-time data, reducing the burden on central storages and main processors.

Transport layer: networks and gateways

The transport layer is responsible for smooth and secure data transmission from the perception layer to the processing layer. It encompasses wired or wireless networks and a gateway — a hardware or software module that consolidates data from devices, analyzes it, performs translation between different protocols, and forwards information to the cloud. As a rule, the gateway converts all information into MQTT messages — the lightweight protocol most widely used in the IoT.

Processing layer: cloud middleware or IoT platforms

Here we come to an IoT platform or middleware that actually drives IoT, enabling you to get all components and data streams connected. On the one side, it links to gateways or devices, and on the other side integrates with third-party applications and systems via APIs.

Typically, fully-fledged platforms take care of such important tasks:

• connectivity or ensuring smooth data streaming and interactions between all IoT components;
• device management, which enables you to control and configure each piece of hardware in the IoT network as well as update software running on devices and gateways;
• data management, including data collection, processing, and storage;
• data analysis for extracting valuable patterns with machine learning, predictive analytics, and other methods;
• visualization or displaying data findings in the form of charts, graphs, 2D or 3D models;
• digital twin or creating the virtual representation of a device;
• IoT app development — platforms provide a workspace with a set of tools and templates to speed up app designing; and
• edge/fog computing — the practice of processing and storing data on devices, microcontrollers, gateways, and other IoT nodes to reduce the burden on cloud servers.

Application layer: software solutions for users

IoT software solutions allow end users to gain data insights, monitor and control devices, and, generally, manipulate the physical world through the IoT platform from computers and/or smartphones. Applications can be built on top of the IoT platform or integrated with it through APIs.

Now, with the place of IoT platforms in the connected ecosystem more clearly defined, let’s concentrate on their functions, options available on the market, and how to choose between them.

Possible solutions

The proposed architecture of a generic IoT system can be visualized as below:

There are multiple components in the architecture. A quick view of the components as the list given below:

1. Sensors / Edges
   • Sensor / Edge Driven
     • Broker (EMQX / KAFKA)
     • HTTP
   • Host Driven
     • Trigger (i.e: serverless azure function) / Scheduler (hangfire, Quartz)
2. Data Source Gateway
3. Extract Transform Load (ETL) engine
4. Database
   • Relational: (PostgreSQL with Timescale DB / SQL Server)
   • No-SQL: Redis
5. App Tier
   • API
   • Redis
   • Lazy Cache
6. Web Tier
   • Web Apps / Mobile Apps
   • Grafana
   • Angular / React
7. Separate Internal Messaging Broker
   • EMQX / MQTT / Kafka
8. Notification Hub
   • Redis as its own DB
   • Alert Rules
   • Notify
     • Email
     • SMS
     • Push Notification
9. End User

Future Considerations:

1. IP whitelist from the sources to tighten the data flow.
2. Compress and encrypt the payload during communication.

Conclusion

Above architecture is a proposed generic solution architecture to start with, but based on domain and requirements the actual system, design components may vary.

Going to show the common facing problem to check the connectivity between pods from different service points. If the service can not connect to the desired service via the service endpoint. Then what are the debugging steps we can follow to rectify that?

Background

We have two applications, each application running with two replica sets (i.e. 2 pods). The application names are app1 and app2.

Manifest for both apps are as given below:

apiVersion: apps/v1
kind: Deployment
metadata:
  name: app1-deployment
  labels:
    app: app1
spec:
  replicas: 2
  selector:
    matchLabels:
      app: app1
  template:
    metadata:
      labels:
        app: app1
    spec:
      containers:
      - name: app1
        image: nhossaincse/apiapp1:latest
        args:
          - --text=hello-world
        imagePullPolicy: Always
        ports:
        - containerPort: 80

apiVersion: apps/v1
kind: Deployment
metadata:
  name: app2-deployment
  labels:
    app: app2
spec:
  replicas: 2
  selector:
    matchLabels:
      app: app2
  template:
    metadata:
      labels:
        app: app2
    spec:
      containers:
      - name: app2
        image: nhossaincse/apiapp1:latest
        args:
          - --text=hello-world
        imagePullPolicy: Always
        ports:
        - containerPort: 8081

We see that app1 running 80 post and the app2 running on 8081 port. We used a test image as the base of the apps, which is nhossaincse/apiapp1. apiapp1 is an in-memory web API, for a get request it serves the JSON. This is especially useful for demos or a more extensive "weatherforecast" Docker application. This is best for our case to simulate the scenario. As we are going to check the HTTP API call from app1 to app2.

Now we need connectivity of these two apps.

An example to simplify the objective is, we need to call an API of app2 from app1.  

To do this, first how app1 will reach to app2? This is elaboratively described in my earlier post which can be read from this link.

Kubernetes - Exposing Applications for Internal Access

Purpose Often we need to communicate between pods internally deployed in Kubernetes. As I said, internally means wanting to communicate without getting any public IP (which costs an extra $) rather in a local area network(LAN) manner. Background When pods are created, they are assigned an IP ad…

Nazmul HossainMd. Nazmul Hossain

So, as described in that post we are going to use the best to reach app2 from app1 with service.

Hence, we have now two apps and two services associated with each app. The service names are as app1-service and app2-service respectively.

Now the manifests for the two services are given below:

apiVersion: v1
kind: Service
metadata:
  name: app1-service
spec:
  selector:
    app: app1
  ports:
    - protocol: TCP
      port: 5678
      targetPort: 80

apiVersion: v1
kind: Service
metadata:
  name: app2-service
spec:
  selector:
    app: app2
  ports:
    - protocol: TCP
      port: 5678
      targetPort: 8081

Problem statement (Issue or Challenge)

Now we will try to reach app2 from app1.

To do this first step is to go get into app1. We can do this by getting in an interactive session on app1. As app1 running with two pods, from anyone we can do the test. To get the list of pods below is the command:

$ kubectl get pods

The output of the running pods can be as below:

NAME                                 READY   STATUS    RESTARTS      AGE
app1-deployment-596b67c689-4m28f     1/1     Running   0             10m
app1-deployment-596b67c689-6qkqw     1/1     Running   0             10m
app2-deployment-64ddcf47dd-8qfh4     1/1     Running   0             10m
app2-deployment-64ddcf47dd-bb8dt     1/1     Running   0             10m

We can do this by getting in an interactive session on app1. The sample command to do this is as below:

$ kubectl exec -it <pod_name> /bin/bash 

In our case, we want to do this for app1 pod so for the first pod to get into the interactive session:

$ kubectl exec -it app1-deployment-596b67c689-4m28f /bin/bash

Now we are in interactive mode inside the pod (think of this one as an independent Linux machine). Now we need to install some basic network packages inside the pod.

$ apt update -y && apt install net-tools -y && apt install dnsutils -y && apt install curl -y

Now to get  the app2 Fully Qualified Domain Name (FQDN), inside here we can use the nslookup command the argument with the <service_name>, in our case which is `app2-service`:

[ root@curl:/ ]$ nslookup app2-service
Server:    10.96.0.10
Address 1: 10.96.0.10 kube-dns.kube-system.svc.cluster.local

Name:      app2-service
Address 1: 10.106.29.144 app2-service.default.svc.cluster.local

We see that, the FQDN for the app2-service is app2-service.default.svc.cluster.local.

Now, we can do the curl to check the connectivity to app2-service.

[ root@curl:/ ]$ curl app2-service.default.svc.cluster.local:5679
curl: (7) Failed to connect to app2-service.default.svc.cluster.local port 5679: Connection refused

And, we see we got the error as port 5678: Connection refused.

Possible solutions

First, we need to check which port the port listening to. To do this we can run the below command to check which port actually the app2 pods listening to. In order to achieve this, we need to access app2 the pod as we did for app1 the pod with the below command in interactive mode. But, don't forget we need to install basic network utility as we did for app1 like running the command as $ apt update -y && apt install net-tools -y && apt install dnsutils -y && apt install curl -y.

Then, the below command will take us inside one of the pods of two with the pod name. Definitely, the pod name at your side will be different and you need to get that by running the command kubectl get pods.

$ kubectl exec -it app2-deployment-9598898-lm5sf /bin/bash

Now with the below command, we can check the ports that app2 listening to with the following command?

$ netstat -ntlp

The result would be like the below:

root@app2-deployment-9598898-lm5sf:/app# netstat -nltp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp6       0      0 :::80                   :::*                    LISTEN      1/dotnet

We see that the port is listening to 80. So we need to correct in our manifest for the app2-deployment and app2-service.yaml with the port # 80

apiVersion: apps/v1
kind: Deployment
metadata:
  name: app2-deployment
  labels:
    app: app2
spec:
  replicas: 2
  selector:
    matchLabels:
      app: app2
  template:
    metadata:
      labels:
        app: app2
    spec:
      containers:
      - name: app2
        image: hashicorp/http-echo
        args:
          - --text=hello-world
        imagePullPolicy: Always
        ports:
        - containerPort: 80

apiVersion: v1
kind: Service
metadata:
  name: app2-service
spec:
  selector:
    app: app2
  ports:
    - protocol: TCP
      port: 5678
      targetPort: 80

After that the curl to check the connectivity with the below command from the app1 pod as below:

root@app1-deployment-96b7f85bc-j5649:/app# curl app2-service.default.svc.cluster.local:5678/weatherforecast
[{"date":"2022-08-14T15:17:46.0946196+00:00","temperatureC":-2,"summary":"Freezing","temperatureF":29},{"date":"2022-08-15T15:17:46.095428+00:00","temperatureC":11,"summary":"Bracing","temperatureF":51},{"date":"2022-08-16T15:17:46.0954307+00:00","temperatureC":37,"summary":"Freezing","temperatureF":98},{"date":"2022-08-17T15:17:46.0954308+00:00","temperatureC":31,"summary":"Bracing","temperatureF":87},{"date":"2022-08-18T15:17:46.095431+00:00","temperatureC":53,"summary":"Sweltering","temperatureF":127}]root@app1-deployment-96b7f85bc-j5649:/app#

Finally, success! We can now connect the app2 form  app1. This process to connect rooms app2 to app1 will be the same as well. API call result with curl from app2 to app1 as below.

root@app2-deployment-788d664db7-kst6d:/app# curl app1-service.default.svc.cluster.local:5678/weatherforecast
[{"date":"2022-08-14T15:23:39.454618+00:00","temperatureC":17,"summary":"Balmy","temperatureF":62},{"date":"2022-08-15T15:23:39.4554411+00:00","temperatureC":0,"summary":"Chilly","temperatureF":32},{"date":"2022-08-16T15:23:39.4554442+00:00","temperatureC":32,"summary":"Warm","temperatureF":89},{"date":"2022-08-17T15:23:39.4554444+00:00","temperatureC":20,"summary":"Hot","temperatureF":67},{"date":"2022-08-18T15:23:39.4554445+00:00","temperatureC":-15,"summary":"Sweltering","temperatureF":6}]root@app2-deployment-788d664db7-kst6d:/app#

Conclusion

We can use these steps to debug the connectivity problems between apps deployed in Kubernetes. This will help to detect the pain point in a quick manner.

Often we need to communicate between pods internally deployed in Kubernetes. As I said, internally means wanting to communicate without getting any public IP  (which costs an extra $) rather in a local area network(LAN) manner.

Background

When pods are created, they are assigned an IP address. You use this IP to access the pod from anywhere within the Kubernetes cluster. Containers inside a pod share the same network space, which means that, within the pod, containers can communicate with each other by using the localhost address.

A Kubernetes cluster might be split across different nodes. A node is a physical machine where resources run. A cluster is a logical view of a set of nodes. These nodes are different machines, but they work together as a logical unit. This makes it easier to work with different machines at the same time because you can simply deploy resources to the cluster and not to individual nodes.

Problem Statement (Issue or Challenge)

In a real-world environment, deployments are performed on a daily basis. When bugs are fixed or new features are added to an application, a new image version is created and deployed. This means that pods are constantly created and destroyed (the pods of the older version are removed and new pods are allocated for the newer version).

At the same time, applications usually have several replicas, and traffic is split across the replicas. This ensures that no single replica is overworked. This is called load-balancing.

In both use cases, the problem is the same: you need a way to reach the pods regardless of the machine where they are located.

Possible solutions

To solve this, Kubernetes introduces the concept of Service.

A service is an abstraction that defines access to a set of pods. By using a service, you don’t access pods directly through their private IP addresses. Instead, a service targets several pods based on certain criteria (for example, a label) and forwards any requests to one of the pods matching that criteria.

In other words, a service allows you to group pods with a logical relationship and it allows you to reach them in a reliable way. At the same time, it implements a load-balancing mechanism among the pods that it targets.

For example, if you want to have three replicas of your application then three pods will be created. If you create a service that targets these pods, then the service receives any incoming requests and routes them to one of them.

By default, a service is given a cluster-internal IP address, which is only valid within the cluster. This type of service is called ClusterIP. This means that pods deployed in the cluster can make requests to the service by using the ClusterIP.

The following diagram illustrates the communication between pods and services. For example, Pod 1 uses the ClusterIP of Service 2 to make requests to the service.

Creating Kubernetes Services

When creating a service, it is necessary to define the port that the service will serve on. This port is mapped to a target port inside the pod that the service targets. Incoming requests to the service in port are forwarded to the target port in the pod. If no target port is provided, then the port value is used.

There are two ways to create a service in Kubernetes:

• Using kubectl expose

The easiest way to create a service is by using the kubectl expose command.

$ kubectl expose deployment deployment-name --port=8081 --name=service-name --target-port=3000

The previous command creates a service named service-name, which targets deployment deployment-name. It listens on port 80 and it points to port 3000 inside the pod.

Use the command kubectl get service to list the services available. The output will provide you with information such as the ClusterIP (IP only valid within the Kubernetes cluster) and the port used to access the service. A sample output might look like this:

NAME           TYPE        CLUSTER-IP      EXTERNAL-IP   PORT(S)    AGE
service-name   ClusterIP   10.105.52.105   <none>        80/TCP     3s

• Applying a manifest

An approach in line with the DevOps principles is creating services through a manifest. The following sample creates a service named app1-service and targets any pod with the label app: app1. The service listens for requests in port 8080 and forwards them to port 3000 inside the pod. Because the manifest does not include the type field, it creates a service with a type ClusterIP.

apiVersion: v1
kind: Service
metadata:
  name: app1-service (1)
spec:
  selector: (2)
    app: app1
  ports: (3)
    - protocol: TCP
      port: 8081 (4)
      targetPort: 3000 (5)

1. Name of the service
2. Labels used to select the target pods
3. Port mapping
4. The port that the service will serve on
5. The port inside the pod where requests are forwarded

Discovering Kubernetes Services

A service abstracts your application from knowing the exact location of the pods you are using, but you still need to know the IP of the service to use it from your application. Directly using the IP is a bad idea because if the IP changes in the future, then you would need to manually update it in your application. To avoid this, Kubernetes provides two ways to discover services:

• Environment variables

By default, when a service is created, Kubernetes injects some environment variables in pods within the same namespace. These variables follow the pattern:

SERVICE-NAME_VARIABLE-NAME

If you have a service named app1-service, that generates the following variables (non-exhaustive) then you can simply inject these environment variables into your application:

• APP1-SERVICE_SERVICE_HOST, which contains the IP address of the Service. For example, 10.0.0.11
• APP1-SERVICE_SERVICE_PORT, which contains the port where Service listens on. For example, 6379

However, your application tries to fetch the environment variables only on start-up. This means that if the value of the variable changes (for example, a service gets a different IP) after your application has started, then your application is not notified and it references an invalid value (the previous IP address of the service). The same happens if the service is created after your application boots up.

• DNS

Given the limitations of the Kubernetes built-in environment variables, the preferred way of accessing services from your application is using DNS.

Every service in the cluster is assigned a DNS name, which matches with the service’s lower cased name. This allows applications to access services using always the same reference. The default FQDN follows the pattern:

service.namespace.svc.cluster.local

However, it is possible to avoid this long form. The DNS server also resolves the following hosts:

• service.namespace.cluster.local
• service.namespace
• service (in this case, Kubernetes expects the service to be in the same namespace)

For example, if you have a service named app1-service that exposes an HTTP endpoint in the default HTTP port (80), then you can use http://app1-service if your application is in the same namespace as the service. If the service was in a namespace named namespace-a, then you use http://app1-service.namespace-a.

Conclusion

Now we should be able to enable intra-pod network communications for applications deployed in Kubernetes and learn how to keep communication up even with automatic deployments. This is an effective way for internal communication of pods as there are no extra costs like LoadBalancer/ Ingress Controller.

Consuming APIs over HTTP is a very common scenario when building applications. In this article, we are going to explore Refit to consume APIs in C#.

Let’s dive in.

What is Refit?

The Refit library for C# provides us with a type-safe wrapper for interacting with HTTP-based APIs. Instead of using HttpClient, which is provided for us by ASP.NET Core, we can define an interface that represents the API we want to interact with.

With this interface, we define the endpoints (GET, POST, PUT) our API contains, along with any route or body parameters. Also, we can include headers in the interface, such as ones for Authorization.

Components of a Refit Client

Before creating an application to demonstrate Refit, let’s explore some of the main components that make up a Refit client.

HTTP Methods

Any time we interact with an API over HTTP, we must be familiar with the different HTTP methods available to us, and how they work. Refit provides a set of attributes that allow us to decorate our interface methods:

[Get("/users")]
Task<IEnumerable<User>> GetUsers();

By decorating the GetUsers() method with [Get("/users")], we tell Refit this is an HTTP GET method, to the /users endpoint.

Refit provides attributes for all the common HTTP methods.

Route Parameters

When working with RESTful APIs that follow good routing conventions, we’ll often see an endpoint like /users/1, which we would expect to return us a user with id 1. Refit uses attribute routing, the same as ASP.NET Core, that allows us to easily define routes that contain parameters:

[Get("/users/{id}")]
Task<User> GetUser(int id);

By adding { and } around id in the route, we tell Refit that this is a dynamic parameter that comes from the id parameter in the GetUser() method.

Request and Response Serialization

The most common way to send data over HTTP is by serializing it as JSON and adding it to the request body. Refit provides this automatically for us.

This allows us to provide classes as parameters to a Refit method, and also specify them as the return type that we expect to be returned from the API:

[Put("/users/{id}")]
Task<User> UpdateUser(int id, User user);

Refit will automatically serialize the user parameter to JSON when sending the request and will attempt to deserialize the response into a User object.

Instantiating a Refit Client

Refit provides us with two ways to instantiate a client, either by using the RestService class provided by Refit, or by registering the Refit client with HttpClientFactory, and injecting the interface into a class constructor.

Let’s assume we have an API for interacting with users, along with a Refit interface:

public interface IUsersClient
{
    [Get("/users")]
    Task<IEnumerable<User>> GetUsers();
}

First, we can instantiate the client using the RestService class:

var usersClient = RestService.For<IUsersClient>("https://mywebapi.com");
var users = await usersClient.GetUsers();

We can also register the client with  HttpClientFactory provided by ASP.NET Core:

services
    .AddRefitClient<IUsersClient>()
    .ConfigureHttpClient(c => c.BaseAddress = new Uri("https://mywebapi.com"));

Both of these are valid ways to register and use Refit clients.

However, if we want to make our code more maintainable and testable, registering the client with HttpClientFactory and injecting it into the required class constructors is the way to go. This allows us to easily inject a mock of the interface for testing purposes, without having to rely on any of the implementation details of either HttpClient or the Refit library.

Because of this, we will opt for the latter method for the rest of this article.

Setting up an API

Instead of setting up a new API from scratch, we can use JSONPlaceholder. It is a free, fake API that can be used for testing, and fits our needs perfectly. It provides various resources to interact with, but for this demo, we’ll use the users resource.

Creating Console Application

With our API solution chosen, let’s create a console application, either through the Visual Studio template or by using dotnet new console.

We must also add the Refit library from NuGet. As we will be using the HttpClientFactory registration method, we need to add two packages:

• Refit
• Refit.HttpClientFactory

As we have chosen the users resource, we’ll create a User model:

public class User
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }

    public override string ToString() =>
        string.Join(Environment.NewLine, $"Id: {Id}, Name: {Name}, Email: {Email}");
}

We override the ToString() method so we can easily display the users retrieved from the API in the console.

Now we can create our Refit interface.

Implementing Refit Client

We start by creating an interface and defining a GetAll method:

public interface IUsersClient
{
    [Get("/users")]
    Task<IEnumerable<User>> GetAll();
}

To turn this interface into a Refit client, we add the Get attribute to the GetAll() method, and define the route as /users. As the API will return us a list of users, the method return type is an IEnumerable<User>.

This is enough to get us started.

Consuming API Data

As we’ve opted to register our Refit client with the ASP.NET Core dependency injection framework, we need to add the Microsoft.Extensions.Hosting NuGet package to our console application.

With this done, let’s register the Refit client in the Program class:

using IHost host = Host.CreateDefaultBuilder(args)
    .ConfigureServices((_, services) =>
    {
        services
            .AddRefitClient<IUsersClient>()
            .ConfigureHttpClient(c => c.BaseAddress = new Uri("https://jsonplaceholder.typicode.com/"));
    }).Build();

We use the AddRefitClient() extension method to register the IUsersClient interface, and then configure the HttpClient, setting the BaseAddress to the JSONPlaceholder address.

With our service registration complete, we can retrieve an instance of IUsersClient, and retrieve some users:

var usersClient = host.Services.GetRequiredService<IUsersClient>();
var users = await usersClient.GetAll();

foreach (var user in users)
{
    Console.WriteLine(user);
}

First, we retrieve an IUsersClient from the service collection, and call the GetAll() method to retrieve a list of users, which we then print to the console.

This demonstrates how simple it is to use a Refit client to abstract HTTP calls. We make a method call that returns our populated User model.

Next, let’s explore some of the further capabilities of Refit, by adding more methods to IUsersClient.

Extending IUsersClient

Let’s add some basic CRUD (Create, Read, Update, Delete) operations for our API:

public interface IUsersClient
{
    [Get("/users")]
    Task<IEnumerable<User>> GetAll();

    [Get("/users/{id}")]
    Task<User> GetUser(int id);

    [Post("/users")]
    Task<User> CreateUser([Body] User user);

    [Put("/users/{id}")]
    Task<User> UpdateUser(int id, [Body] User user);

    [Delete("/users/{id}")]
    Task DeleteUser(int id);
}

Firstly, we add the GetUser() method, which takes an id parameter to identify the user we want to retrieve. We decorate this method with the Get attribute, and in the route we define a dynamic parameter using { and }.

Next up is the CreateUser() method, which takes a User as a parameter, and because we want this to be passed in the HTTP request body, we decorate the parameter with the Body attribute. This time, it’s a Post request that the API expects.

To update a user, we need a Put method, combining both a route parameter, id, and body content, which is the User we want to update.

Finally, to delete a user, we make a Delete request providing the id of the user to delete.

This gives us CRUD functionality on the Users API. Now we can test this out.

Testing CRUD Functionality

Back in the Program class, let’s start by creating a new user:

var user = new User
{
    Name = "John Doe",
    Email = "john.doe@test.com"
};

var usersClient = host.Services.GetRequiredService<IUsersClient>();
var userId = (await usersClient.CreateUser(user)).Id;

Console.WriteLine($"User with Id: {userId} created");

Initially, we create a new User object. With this user, we call CreateUser(), which will return a User object, giving us the Id of the newly created user, which we log to the console.

Next, we can retrieve an existing user using the GetUser() method:

var existingUser = await usersClient.GetUser(1);

With this user, let’s update the Email:

existingUser.Email = "john.doe@gmail.com";
var updatedUser = await usersClient.UpdateUser(existingUser.Id, existingUser);

Console.WriteLine($"User email updated to {updatedUser.Email}");

Here, we use the UpdateUser() method, passing in the Id of the user, along with the updated user object.

The final step is to delete the user:

await usersClient.DeleteUser(userId);

We simply call DeleteUser(), providing the userId to delete.

This covers the basic CRUD functionality and shows how simply we can create an interface to interact with an API, without the need of handling complex HTTP logic with an HttpClient.

Conclusion

In this article, we’ve learned how we can abstract interaction with HTTP-based APIs by using Refit and creating a simple interface for our API. This allowed us to avoid dealing with complex HTTP logic, such as creating request messages and deserializing responses and instead focus on the core logic relating to our applications.

You are assigned to a running project, with limited documentation. Live operations/transactions are going on in the project database. Now you need to understand the dataflow. Database table and column names are not much meaningful to you! Also, if the database has more than 50 stored procedures, it is difficult to find some hard code logic of those scripts which one that logic resides?

Possible Approach

If the project has any UI/Front End application, then from the project Front End/UI do some test entries and check which table/columns are storing those data.

Here, is one generic stored procedure to search with a keyword inside the entire database tables and stored procedures. It will give you the location of that keyword and which table/column or stored procedures it resides in.

CREATE PROCEDURE [dbo].[SearchAllTables] (
  @SearchStr nvarchar(100)
) AS BEGIN 

-- Purpose: To search all columns of all tables and stored procedures for a given search string
-- Written by: Md. Nazmul Hossain
-- Site: https://nhossaincse.com
-- Tested on: SQL Server 12.0

DECLARE @Results TABLE(
  ColumnName nvarchar(370), 
  ColumnValue nvarchar(3630)
) 
SET 
  NOCOUNT ON DECLARE @TableName nvarchar(256), 
  @ColumnName nvarchar(128), 
  @SearchStr2 nvarchar(110) 
SET 
  @TableName = '' 
SET 
  @SearchStr2 = QUOTENAME('%' + @SearchStr + '%', '''') 
WHILE @TableName IS NOT NULL 
BEGIN 
SET 
  @ColumnName = '' 
SET 
  @TableName = (
    SELECT 
      MIN(
        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
      ) 
    FROM 
      INFORMATION_SCHEMA.TABLES 
    WHERE 
      TABLE_TYPE = 'BASE TABLE' 
      AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName 
      AND OBJECTPROPERTY(
        OBJECT_ID(
          QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
        ), 
        'IsMSShipped'
      ) = 0
  ) 
WHILE (@TableName IS NOT NULL) 
  AND (@ColumnName IS NOT NULL) 
BEGIN 
SET 
  @ColumnName = (
    SELECT 
      MIN(
        QUOTENAME(COLUMN_NAME)
      ) 
    FROM 
      INFORMATION_SCHEMA.COLUMNS 
    WHERE 
      TABLE_SCHEMA = PARSENAME(@TableName, 2) 
      AND TABLE_NAME = PARSENAME(@TableName, 1) 
      AND DATA_TYPE IN (
        'char', 'varchar', 'nchar', 'nvarchar'
      ) 
      AND QUOTENAME(COLUMN_NAME) > @ColumnName
  ) IF @ColumnName IS NOT NULL BEGIN INSERT INTO @Results EXEC (
    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                FROM ' + @TableName + ' (NOLOCK) ' + ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
  ) END END END 
  --SELECT ColumnName, ColumnValue FROM @Results
  --UNION 
  --DECLARE @Search varchar(255)
  --SET @Search='quizplay'
INSERT INTO @Results(ColumnName, ColumnValue) 
SELECT 
  DISTINCT o.name AS ColumnName, 
  o.type_desc as ColumnValue 
FROM 
  sys.sql_modules m 
  INNER JOIN sys.objects o ON m.object_id = o.object_id 
WHERE 
  m.definition Like '%' + @SearchStr + '%' --ORDER BY 2,1
SELECT 
  ColumnName, 
  ColumnValue 
FROM 
  @Results 
 END

Note: for a big database this take time and not supposed to run in production environment as may lead to lock of operations.  

Often we may need to check the number of rows in a `table`. If the table size is not big enough, then need not to worry much about that. But what if your table has more than `10M rows`? You will find the first two approach described in below is very slow and may lead to lock the table operation.

Approach 1:

The first way to check will come in head is with count(*) like below snippet, right?

select count(*) from [tableName];

Approach 2:

This is also costly and unnecessarily selects all the columns:

select count(1) from [tableName];

Approach 3:

But the fastest one is the below one, as it takes the advantage from the direct index heap.

select sum([rows])
from sys.partitions
where object_id=object_id('tablename')
and index_id in (0,1);

Note: please keep in mind that, the fastest way to get the number of rows in a table will give you an approximate row count.