Forem: mortylen

When a Precise Specification Is Not Enough

mortylen — Wed, 15 Apr 2026 19:30:19 +0000

When people talk about requirements for industrial software, most imagine what an operator sees: process screens, alarms, trends, production overviews, or dispatch panels. And that is largely justified. These parts of the system tend to have a relatively clear structure, as they are derived from the technology itself, process diagrams, operational habits, and well-established principles of the SCADA and HMI world.

It would therefore not be accurate to say that customers in industrial environments do not know what they want to see. Operator screens are often quite predictable. They follow established logic, conventions, and expectations.

But that is only the visible half of the system.

The other, less visible half is much harder to define. And it is precisely there that the limitations of traditional thinking about software requirements most often become apparent.

What the Operator Sees Is Only Half of the System

From experience, the greatest uncertainty in industrial projects does not appear on the screens seen by operators, but in the tools used behind the scenes.

In more robust SCADA systems, there is usually not just a single “main” application. Alongside it, an entire ecosystem of service and administrative tools emerges. These are not displayed on the control room video wall, but without them the system would not function properly in the long run.

They include, for example, historical data viewers, tools for working with measurements and data exports, database and archive management utilities, configuration of devices and communication points, alarm setup, communication diagnostics, as well as user management and audit logs.

These tools are typically not seen by operators. They are used by system administrators, service technicians, process engineers, or integrators. They run on service stations, laptops, or administrative workstations. They are not visually impressive, but they are critical.

Because of this, their importance is often underestimated. While the operator interface is perceived as the “real product,” service tools are often seen as just an add-on. In real operations, however, it is often the opposite.

Without them, a well-designed SCADA solution quickly becomes a system that can still be operated, but is difficult to maintain, hard to extend, and unpleasant to service.

Why Precise Requirements Are Often Missing

This brings me to the main idea.

For service and supporting applications, detailed requirements often do not exist. Not because the project is poorly managed or because the customer is not interested. The reason is simpler: customers often do not know what they will truly need when operating a complex system over time.

They can define the goal. They want a system that is stable, maintainable, and extensible. They know they need historical data, configuration management, alarms, or diagnostics. But they cannot precisely describe what tools and details will actually be used in day-to-day operations.

They do not know which filters will be essential when analyzing data, what will need to be changed in bulk, where exports or configuration comparisons will be required, or which information will be missing when troubleshooting issues in production. Many of these needs only become clear once the system is in use, evolving, and expanding.

The customer knows the goal, but not all the tools and processes required to maintain it over time.

In such situations, much of the solution is not based on a precise specification, but on trust in the team and their experience. In essence, the customer is saying: we know what the outcome should be, and we expect the vendor to design the tools that make it possible.

This is not chaos. It is a natural distribution of knowledge.

The customer understands the process. The vendor understands what such a system requires in practice in order to remain maintainable in the long run. And it is at the intersection of these two perspectives that tools emerge which are very difficult to specify in advance.

The Illusion of a Precise Specification

If we try to write a fully detailed specification for these tools at the beginning of a project, the result will often look better on paper than in reality.

A document is created that appears precise and professional, but in fact it only formalizes assumptions. We describe how a configuration editor should look, what filters a trend viewer should have, or how data management should work. However, the correctness of these decisions only becomes clear once someone actually starts using the tools in practice.

This is where the requirements paradox emerges.

The more detailed we try to describe something that has not yet been validated in practice, the more we feel that we understand it. In reality, we are only recording hypotheses with precision.

This is especially visible in service tools:

a configuration management tool fulfills the specification but is slow in everyday use
a trend viewer displays correct data but lacks the filters needed during troubleshooting
a database tool is technically correct but too complex for daily operations
an application allows editing individual records but lacks bulk operations
a diagnostic view shows a large amount of data, but not the information that matters in critical moments

Formally, everything may be correct. In practice, the solution can still fall short.

Only Usage Reveals What Is Missing

Requirements for service tools often do not emerge at the beginning of a project, but only when the system starts being used in practice.

Some needs only become visible in real operation. When a new technology is added, hundreds of tags may need to be adjusted at once, historical data must be compared, alarm configurations changed, or communication issues diagnosed. Other needs arise when the system owner changes, when the solution is expanded, or during larger interventions such as migrations or shutdowns.

It is at this point that it becomes clear whether the system was designed only for presentation, or also for long-term operation.

This is why industrial software should not be evaluated only by what the operator sees. Its quality is largely reflected in the tools available to the people who will maintain, modify, diagnose, and operate it over the years.

A Better Approach in Practice

For these parts of a system, a different approach tends to work better in practice than trying to create a perfect specification from day one.

It is more effective to rely on experience from previous projects, prepare a reasonable initial design of the service tools, and present it as early as possible to the people who will actually use them.

Only during real use does it become clear where users get stuck, what is unnecessarily complex, and what is missing. Based on this feedback, the design can be gradually refined, simplified, and extended.

This approach is more practical than trying to design everything in advance on paper. When people see a concrete tool for managing measurements, configurations, or data, they can provide much more precise feedback.

Instead of vague statements like “just make it work somehow,” they will say they need bulk operations, saved filters, configuration comparisons, export capabilities for service purposes, or audit trails of changes.

At that moment, trust turns into concrete requirements. Not in a discussion over a blank sheet of paper, but through interaction with a real tool.

Trust Still Requires Rules

The fact that detailed specifications often do not exist for these applications does not mean they should be built without structure or responsibility.

Trust in the vendor is not a substitute for craftsmanship. On the contrary, it places even higher demands on the team. The developer is no longer just an executor of requirements, but a co-creator of the solution.

They rely not only on current requirements, but also on experience from similar systems. They must be able to estimate which service tools will actually be needed, avoid unnecessary complexity, distinguish between a prototype and a production-ready solution, and continuously validate whether the design makes sense in real use.

A good team in this context does not wait for a fully detailed specification, but continuously checks whether its assumptions hold up in real operation.

Conclusion

If I were to summarize it in one sentence: in industrial systems, customers often do not explicitly order the tools that ultimately determine whether the system can be used in the long term.

Operator-facing SCADA screens are usually relatively easy to imagine and specify. The real uncertainty, however, is hidden in the background: in service applications, configuration tools, diagnostic utilities, database tools, and all the supporting parts of the system without which a large solution cannot be sustainably operated.

This is precisely where detailed requirements are often missing at the start. There is a goal, there is the vendor’s experience, and there is trust that the final solution will also make sense in practice.

For this reason, it may be more useful to ask not “what is the exact specification?”, but rather “how will the system behave after one, two, or five years in operation?”

Because the quality of industrial software is not revealed only on the operator screen. It is revealed above all in how it feels to the people who have to maintain and operate it every day.

SqlDependency in .NET – Query Notifications and Real-Time Data Change Reactions

mortylen — Thu, 09 Apr 2026 18:19:49 +0000

Imagine your application constantly bombarding the database with questions: "Has anything changed? How about now? And now?" Every second, every minute. You're needlessly burning CPU, network, and database resources, even though the data might only change once an hour.

There's a much better way. Let the database tell you when something changes. That's exactly what SqlDependency is for.

SqlDependency is a relatively little-known technology, yet it's a great fit for many projects that work with SQL databases. In this article, we'll look at how it works in .NET, how to set it up step by step, and how to build a simple console application that reacts to data changes in real time.

What Is SqlDependency?

SqlDependency is a class in .NET that allows your application to receive notifications from SQL Server whenever the data you're querying changes. Under the hood, it relies on two key SQL Server mechanisms:

Service Broker – an internal messaging system built directly into SQL Server
Query Notification – a mechanism that monitors whether the result of a specific query has changed

The principle is simple: instead of repeatedly asking the database (polling), you register a "subscription" for a specific query. When the result changes (INSERT, UPDATE, DELETE), SQL Server sends a notification and your application reacts to it.

Polling vs. SqlDependency

With traditional polling, your application queries the database every X seconds — even when nothing has changed. This wastes CPU, network, and database resources, and the change is only detected on the next cycle.

SqlDependency works the other way around. The database itself notifies the application when a change occurs. Queries are only executed when truly needed, and the reaction is nearly instant. Instead of a pull model, you switch to push.

Put simply: polling is like constantly opening the fridge to check if new food appeared. SqlDependency is like a doorbell that rings when the delivery arrives.

When Not to Use SqlDependency?

When you need historical data about what changed (SqlDependency only tells you that a change occurred)
With very complex queries — there are syntax restrictions
When periodic reads are sufficient and you don't need real-time reactions
In Azure SQL, where Service Broker support is limited

How It Works Under the Hood

The entire process happens in several steps:

The application calls SqlDependency.Start() — an internal listener is created
You create a SqlCommand and attach a SqlDependency to it
You call ExecuteNonQuery() or ExecuteReader()
Data changes (INSERT, UPDATE, DELETE)
SQL Server sends a notification
The registration is removed — it's a one-time subscription

What We're Going to Build

Let's move from theory to practice. We'll create a simple console application in .NET 10 that monitors a Messages table in a SQL Server database.

The table will serve as a simple message queue. Each record contains a message text and a status (New, Processed). At startup, the application registers to watch for messages with the status 'New'. When a change occurs, it immediately receives a notification, prints the current state of the table, and re-registers for further changes.

The entire solution consists of a single SQL script and a single Program.cs file. No complex architecture — just a clean demonstration of SqlDependency in action.

Setting Up the Database (Step by Step)

Before writing any C# code, we need to prepare the database and the environment we'll be testing with.

Creating the Database

USE master;
GO
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'SimpleSqlDepDB')
    CREATE DATABASE SimpleSqlDepDB;
GO

Enabling Service Broker

USE SimpleSqlDepDB;
GO
ALTER DATABASE SimpleSqlDepDB SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;
GO

Important: SqlDependency will not work without Service Broker enabled. This is the most common mistake. Service Broker can also be disabled after a SQL Server restart, so always verify its status.

Creating the Table

IF OBJECT_ID('dbo.Messages', 'U') IS NULL
BEGIN
    CREATE TABLE dbo.Messages (
        Id INT IDENTITY(1,1) PRIMARY KEY,
        Message NVARCHAR(100) NOT NULL,
        Status NVARCHAR(20) NOT NULL DEFAULT 'New'
    );
END
GO

Inserting Test Data

INSERT INTO Messages (Message, Status) VALUES ('Initial message', 'New');
GO

Table and Query Requirements

SqlDependency has strict rules about what kind of queries you can use. It's worth considering these constraints early on to make sure they fit your architecture.

You must follow these rules:

The table must have a primary key or a unique index
All columns must be explicitly listed (no SELECT *)
Use two-part table names (dbo.Messages)

The query must not contain:

SELECT *
SELECT DISTINCT
UNION
Certain subqueries
TOP without ORDER BY
Aggregate functions (COUNT, SUM, AVG)

C# Implementation (.NET 10)

Now let's look at the actual implementation. The project uses .NET 10 and the Microsoft.Data.SqlClient package.

Project Structure

<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net10.0</TargetFramework>
    <ImplicitUsings>enable</ImplicitUsings>
    <Nullable>enable</Nullable>
  </PropertyGroup>
  <ItemGroup>
    <PackageReference Include="Microsoft.Data.SqlClient" Version="7.0.0" />
  </ItemGroup>
</Project>

Complete Code (Program.cs)

using System;
using System.Data;
using Microsoft.Data.SqlClient;

class Program
{
    // Connection string – connecting to a local SQL Server Express instance
    static readonly string connStr = $"Server={Environment.MachineName}\\SQLEXPRESS;Database=SimpleSqlDepDB;Trusted_Connection=True;TrustServerCertificate=True;";

    // The query we'll be monitoring
    // NOTE: Must use explicit column names and a two-part table name (dbo.Messages)
    static readonly string query = "SELECT Id, Message, Status FROM dbo.Messages WHERE Status = 'New'";

    static void Main(string[] args)
    {
        Console.WriteLine("Listener started. Waiting for a value change...");

        // 1. INITIALIZATION – Start the internal infrastructure for receiving notifications
        SqlDependency.Start(connStr);

        // Print the current state of the table
        Console.WriteLine("\n--- Initial table data ---");
        ReadTableData();

        // 2. REGISTRATION – Subscribe to data change notifications
        StartListening();

        Console.WriteLine("\nPress Enter to exit...");
        Console.ReadLine();

        // 3. CLEANUP – Shut down gracefully and release resources
        SqlDependency.Stop(connStr);
    }

    static void ReadTableData()
    {
        using SqlConnection conn = new SqlConnection(connStr);
        conn.Open();

        using SqlCommand cmd = new SqlCommand(query, conn);
        using SqlDataReader reader = cmd.ExecuteReader();

        if (!reader.HasRows)
        {
            Console.WriteLine("(no rows)");
            return;
        }

        // Formatted table output to the console
        Console.WriteLine($"{"Id",-5} {"Message",-30} {"Status",-15}");
        Console.WriteLine(new string('-', 52));

        while (reader.Read())
        {
            Console.WriteLine($"{reader["Id"],-5} {reader["Message"],-30} {reader["Status"],-15}");
        }
    }

    static void StartListening()
    {
        using SqlConnection conn = new SqlConnection(connStr);
        conn.Open();

        using SqlCommand cmd = new SqlCommand(query, conn);

        // Create the dependency and attach the event handler
        SqlDependency dependency = new SqlDependency(cmd);
        dependency.OnChange += Dependency_OnChange;

        // ExecuteNonQuery registers the query on the SQL Server side
        cmd.ExecuteNonQuery();
    }

    static void Dependency_OnChange(object sender, SqlNotificationEventArgs e)
    {
        Console.WriteLine($"\n⚡ Notification — Type: {e.Type}, Info: {e.Info}, Source: {e.Source}");

        if (e.Type == SqlNotificationType.Change)
        {
            try
            {
                Console.WriteLine("\n--- Table data after change ---");
                ReadTableData();
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error reading table data: {ex.Message}");
            }
        }
        else
        {
            // If the type is not Change, it indicates an error or an invalid query
            Console.WriteLine("Subscription error or invalid query — not re-subscribing.");
            return;
        }

        // IMPORTANT: After receiving a notification, we MUST re-register!
        try
        {
            StartListening();
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Error re-registering listener: {ex.Message}");
        }
    }
}

Code Walkthrough

Let's go through the key parts.

Connection String

We're connecting to a local SQL Server Express instance using Windows authentication. Adjust the connection string to match your own environment.

SqlDependency.Start() and Stop()

Start() initializes the infrastructure for receiving notifications, and Stop() shuts it down cleanly. Both calls are important to prevent resource leaks.

Registering the Dependency

By creating a SqlDependency and attaching it to a SqlCommand, you register the query for monitoring on the SQL Server side. The actual registration happens when the command is executed.

Re-registration

This is a key detail. The registration is always one-time only. After a notification is received, it's automatically removed and must be recreated.

SqlNotificationEventArgs

This object contains information about the notification: type, detail, and source. A value of Change means the data actually changed; other values usually indicate a problem.

Testing

After running the application (dotnet run), you'll see the current state of the table and the application will begin waiting for changes:

Listener started. Waiting for a value change...

--- Initial table data ---
Id    Message                        Status
----------------------------------------------------
1     Initial message                New

Press Enter to exit...

If you insert a new record or modify an existing one, the notification arrives immediately and the application prints the updated data. The change is only detected if it affects the result of the monitored query.

Open SQL Server Management Studio or use any other tool to send an SQL query and make a change:

-- Insert a new record
INSERT INTO dbo.Messages (Message, Status) VALUES ('Test notification', 'New');

You'll immediately see in the console:

⚡ Notification — Type: Change, Info: Insert, Source: Data

--- Table data after change ---
Id    Message                        Status
----------------------------------------------------
1     Initial message                New
2     Test notification              New

Try an UPDATE as well:

UPDATE dbo.Messages SET Status = 'Processed' WHERE Id = 1;

⚡ Notification — Type: Change, Info: Update, Source: Data

--- Table data after change ---
Id    Message                        Status
----------------------------------------------------
2     Test notification              New

Notice that after the UPDATE, the record with Id = 1 no longer appears, because our query only filters for records with Status = 'New'.

Common Pitfalls

Service Broker is not enabled — The most common issue. Verify its status and enable it if needed.
Incorrect query format — SqlDependency is strict about syntax. If the query doesn't meet the requirements, registration will fail.
Forgetting to re-register — Without re-subscribing, you'll only receive a single notification.
Connection leaks — Use using blocks. Even when the connection is closed, the registration on the SQL Server side remains active.

Real-World Use Cases

SqlDependency is a good fit wherever you need to react to data changes immediately.

A typical example is live dashboards that only refresh when data actually changes, or simple job queues where new records trigger processing. It's also commonly used for cache invalidation — the application receives a notification and knows it's time to refresh cached data.

It also has a place in IoT scenarios and simple real-time notifications where fast reactions without unnecessary polling are essential.

Comparison with Alternatives

SqlDependency — a simple solution built into SQL Server, but with limitations
Polling — universal but inefficient
SignalR — great for real-time web, requires an additional layer
Message Queue — a robust solution for distributed systems
Change Tracking / CDC — suited for auditing and change history

Summary

SqlDependency is an elegant way to react to database changes without unnecessary polling. Instead of constantly querying, let SQL Server alert you only when something actually changes.

Fewer queries, less load, and faster reactions. Exactly what we expect from modern applications.

The complete source code for this project is available on GitHub: github.com/mortylen/sql-dependency-notifier

Why Simple Architecture Wins

mortylen — Tue, 07 Apr 2026 15:23:32 +0000

There is a common misconception shared by many developers: a good architecture has to be complex. It must have layers, abstractions, patterns, and frameworks. If a system doesn’t look impressive on a whiteboard, it’s probably not “professional enough.”

The truth is the opposite, and that is exactly what this article is about.

Complex systems don't appear by accident. But neither do simple ones.

Simplicity Is Not the Default

Most systems don’t start out complex. They begin as small projects with a clear goal: to solve a specific problem. Then another feature is added, and then another.

Someone adds a layer “just to be tidy.” Someone else adds an abstraction “for flexibility.” A year later, no one can quickly navigate the code that two people wrote over a weekend.

Complexity grows gradually. If you don’t actively manage it, it will eventually become overwhelming. Simplicity is therefore a conscious choice. Not just at the start, but throughout the system’s entire lifecycle.

You have to actively protect it during the entire life of the system.

What “Simple” Really Means

When people hear “simple architecture,” they often imagine something that isn’t what we actually mean:

Primitive solution – something thrown together quickly without much thought
Quick hack – code that works today but no one wants to touch tomorrow
Less code – short code can be just as confusing as long code

Simplicity actually means something different:

You can understand it quickly. A new developer can get up to speed in hours, not weeks.
Changes are local. When you modify something, you don’t accidentally break five other things.
Behavior is predictable. You know what will happen when you call a function.

A good way to understand this is by comparing it to so-called “flexible and generic” solutions. These systems are designed to handle almost anything that could come up. The problem is that they usually handle everything only moderately well and nothing particularly well.

On top of that, they are hard to understand. They are full of abstractions that exist “just in case something might happen someday,” which often ends up confusing developers unnecessarily.

Why Simplicity Is Hard

If simplicity is so good, why don’t we choose it automatically?

The reason is simple. It is psychologically challenging and requires discipline and constant conscious decision-making.

Planning for the Future

Developers naturally think ahead. “What if customers want more payment options?” “What if the system grows to ten times its current size?”

These questions are valid. But most of them will never happen. In the meantime, we pay the price for complexity that was never needed.

Complexity Looks Professional

A diagram with twenty boxes and arrows looks more impressive than one with five components. When presenting your architecture to colleagues or management, a simple solution can seem like you didn’t try hard enough.

This social dynamic is real and can be dangerous because developers or managers may start favoring complex solutions just because they look more professional, even when a simpler approach would work better and faster.

Fear of Rewriting

“What if we have to redo this later?” This is often the excuse behind unnecessary abstractions or complicated solutions.

The problem is that a system filled with abstractions meant to make future changes easier usually ends up being harder to change than simple, straightforward code. Instead of making your work easier, it can make every change unnecessarily complicated.

Following “Ideal” Examples

We often see articles about perfect architectures, training videos, or conference presentations where everything works elegantly and sophisticatedly.

It’s tempting to copy these ideal examples in your own project, creating layers, abstractions, and frameworks that look professional.

The problem is that these solutions were built for very specific conditions, like large teams, millions of users, and complex processes. For a small project, they are often unnecessary and only add extra complexity.

Simplicity often feels risky. Complexity feels safe, but only in the short term.

What Makes a System Unnecessarily Complex

Some problems in a system tend to repeat themselves and immediately signal unnecessary complexity. Here are the most common ones:

Too many layers. If adding a simple feature requires going through six classes and five interfaces, the system is too complicated.
Generic abstractions with no real use. For example, a class like AbstractBaseEntityManagerFactory exists, but only one part of the system actually uses it, and that part would work just fine without it.
Solving future scenarios. The system is designed for needs that don’t exist yet and may never appear.
Premature splitting. If a system is divided into ten separate services before anyone really understands what they do, unnecessary complexity is introduced.

If you recognize any of these in your own work, you are not alone. Most of us have been there. The important part is to consciously notice where complexity accumulates and choose a better direction.

Principles of Simple Architecture

These guidelines help you keep systems simple and easy to manage. They are not rigid rules that must be followed every time, but rather a compass to guide your decisions.

Prefer explicit over generic

Write a concrete solution for the current problem instead of creating a generic structure that claims to "handle everything." Explicit code is easy to read, while generic code often requires extra effort to understand.

# Generic – hard to follow
def process(entity, strategy, context):
    return strategy.execute(entity, context)

# Explicit – clear at first glance
def send_welcome_email(user):
    email_service.send(user.email, template="welcome")

Solve today's problem first

Focus on solving today's problem well. If a new problem arises later, you will handle it with better understanding. Architecture designed for an uncertain future is often just unnecessarily complicated.

Add abstractions only when needed

Introduce abstractions only when they are truly necessary. They should emerge as a response to a real problem, not as a way to anticipate every possible future complication. If repeating code becomes difficult to manage, add an abstraction. Adding it too early creates unnecessary complexity and confusing code for both yourself and other developers.

Keep the system simple

The fewer concepts and rules a system has, the easier it is for everyone to navigate, including yourself six months from now. Every new layer, abstraction, or pattern adds cognitive load and makes the system harder to understand. Keep the system clear and minimize unnecessary parts to make working with the code simpler and faster.

Make the common case easy

The most frequent scenarios in the system should be simple to implement and understand. If a process represents the majority of system activity, it shouldn’t require five classes or complex configuration. More complex cases and exceptions can be more complicated, because they happen less often, and that is perfectly fine.

How to Tell if a Solution is Simple Enough

These principles provide a framework for thinking about system design. To judge whether a solution is truly simple, you can ask yourself a few practical questions. This simple checklist helps identify where unnecessary complexity is building up and what can still be simplified.

[ ] Can a new developer understand the core of the system within a few hours?
[ ] Can a new feature be added without major changes to unrelated parts of the system?
[ ] Does the code contain more business logic than “glue” (boilerplate, wiring, configuration)?
[ ] Can you explain the architecture clearly on a single A4 page?
[ ] Do you avoid giving every new teammate a 30-minute onboarding just to understand the basics of the system?

If you can answer “yes” to most of these, your solution is likely simple enough. If not, take a closer look at where complexity has accumulated and consider whether it is really necessary.

Practical Example: SQL vs. NewSQL

Imagine a small project building a web application. It needs an API and a database, and the team is deciding between a traditional SQL database and a NewSQL solution.

Why REST / Traditional SQL is Often the Better Choice

Traditional SQL databases, such as PostgreSQL, work well for most small to medium-sized applications. They are well-documented, have a large community, and offer tools that support developers.

Setting up a local development environment is easier and faster
Performance tuning and transaction management are predictable
New developers can quickly understand the system
Migrations and schema management are straightforward

Choosing PostgreSQL from the start allows the team to focus on the business logic and solve problems that actually exist without adding unnecessary complexity.

When NewSQL Makes Sense

NewSQL databases, like CockroachDB, TiDB, or PlanetScale, are powerful and scalable tools. Their power comes at a cost:

More complex local development environments
Different transaction and consistency models
A need to learn new concepts before solving the business problem
More complicated migrations and schema management

These databases make sense when a project truly needs horizontal scaling or must handle a very high number of concurrent users. For smaller projects, it is usually unnecessary to introduce them right away.

Key Takeaway

NewSQL is not a bad choice. It is the right solution for specific problems that most projects will never encounter. PostgreSQL can handle a massive amount of data and load, and when the time comes to scale, the team will have enough experience and data to decide whether NewSQL is truly needed.

Conclusion

Simplicity is not about laziness or avoiding thinking. On the contrary, it requires more discipline than adding extra layers, abstractions, or unnecessary generic solutions.

Good architecture is neither impressive nor complicated. It should fit the problem, the team, and the stage the project is in.

Every system that later appears elegant and simple was built through a series of deliberate decisions. These are decisions about what not to add, what not to solve in advance, and what can be safely addressed later.

Before making any major architectural decision, it’s worth asking yourself one simple question:

Are we making this simpler, or just more sophisticated?

👉 Explore practical tips for architectural decisions at Stack Compass Guide.

When Good Intentions Become a Problem: Overengineering

mortylen — Mon, 30 Mar 2026 15:36:19 +0000

Many software systems are not problematic because they are too simple. The problem often arises when they are unnecessarily complex.

When a system is too complicated, it becomes harder to navigate, changes take longer, and bugs are more difficult to find. The result is slower development and more stress in day-to-day work.

This complexity usually doesn’t appear all at once. It builds up gradually through decisions that initially make sense. The goal is to be prepared for growth, maintain flexibility, and avoid costly changes in the future.

The problem begins when future problems are addressed too early.

That is what we often call overengineering.

What is overengineering

Overengineering means that a system is more complex than it currently needs to be.

It’s not about a specific technology being bad on its own. The problem arises when it is used too early or without a clear reason.

Simply put:

more layers are added than necessary,
more abstractions are created than are actually used,
we prepare for problems that don’t exist yet.

Such an architecture may look professional at first glance. At the same time, it introduces immediate costs. The system becomes harder to understand, harder to change, and more difficult to operate.

Why it happens

Overengineering is rarely intentional. On the contrary, the goal is usually to do things properly. We want to avoid future problems, prepare the system for growth, and feel like we are building a solid solution.

These are perfectly reasonable goals. The problem is that the future in software is hard to predict. What seems like good preparation today may turn out to be unnecessary in a few months.

Another reason is inspiration from large companies. When we read how tech giants build systems, it’s easy to feel like we need a similar architecture. But large companies are solving very different problems than a smaller project or a new product.

There is also an aesthetic aspect. A simple solution can feel ordinary, while a complex one looks more advanced. That doesn’t mean it is better.

When a good pattern becomes an anti-pattern

Many software patterns are useful. Microservices, CQRS, layered architecture, or GraphQL are not bad on their own.

The problem arises when they are used at the wrong time.

A pattern can become an anti-pattern when its costs are high today, its benefits remain theoretical, and the project doesn’t actually have a problem that the solution is meant to solve.

In other words, a pattern becomes an anti-pattern when it is unnecessarily expensive and unnecessarily complex for a given project.

This often happens subtly. The decision is justified with arguments like “we might need it later” or “this is the proper way to do it.” But without a concrete problem, it’s more of a hypothesis than a real need.

It’s important to realize that most patterns were created as a response to specific problems. If we don’t have those problems, we probably don’t need the solution either.

A better approach is to introduce patterns gradually—at the moment when they start solving a real, recurring problem. At that point, it’s no longer a guess, but a response to actual experience.

Common forms of overengineering

One of the most common situations is preparing for scale that doesn’t exist yet. The system is designed as if it already had a massive number of users, even though the product is still in its early stages.

Another issue is premature abstractions. A large number of interfaces, base classes, or generic solutions are created before there are multiple real use cases.

A frequent problem is also excessive flexibility. The system is designed to handle almost anything, but everyday work with it becomes unnecessarily complicated.

And finally, premature decomposition. The application is split into multiple parts before there are clear boundaries and a concrete reason to do so.

In all of these cases, complexity is added before there is any real benefit from it.

Example: REST API vs. GraphQL

Let’s imagine a smaller project building a web application. It needs an API for the frontend and is deciding between a REST API and GraphQL.

A REST API is usually the simpler starting point. It has clear endpoints, is easy to explain, and straightforward to work with. For a smaller application, it is often more than enough.

GraphQL can be very useful, especially when there are multiple clients with different data needs or complex screens that compose data from multiple sources. The client can request exactly what it needs.

That doesn’t mean GraphQL is automatically better.

If the application is simple, has a single frontend, and standard data requirements, GraphQL can introduce more complexity than value. You need to deal with schema design, resolvers, security, caching, and other concerns that are much more straightforward with a simple REST API.

In such a case, adopting GraphQL just because it feels more modern would be unnecessary.

On the other hand, as the application grows, data requirements become more complex, and the REST API starts to feel limiting, GraphQL can start to make sense.

Don’t choose technology based on what sounds more advanced. Choose it based on what solves your current problem in the simplest way.

A better approach

Instead of adding complexity upfront, it’s better to start simple and introduce new layers only when there is a clear reason.

It helps to ask a few simple questions:

What problem are we solving right now?
Is it a real problem we have, or something we assume might happen?
Would a simpler solution be enough for now?
How much complexity are we adding?

This approach doesn’t mean ignoring the future. It just means not paying the cost of complexity before it is actually needed.

Conclusion

Overengineering is dangerous because it often looks reasonable at the beginning.

It feels like thoroughness, preparedness, and solid design. In reality, it can slow down development and make the system unnecessarily hard to understand and maintain.

Good architecture doesn’t have to be complex. It should be appropriate for what the product needs today.

Before making a bigger technical decision, it’s worth asking a simple question:

Are we solving a real problem, or just creating future complexity?

👉 Explore practical tips for architectural decisions at Stack Compass Guide.

When Code Hurts: Anti-Patterns in Software Development

mortylen — Mon, 23 Mar 2026 18:20:33 +0000

Imagine building a house without a proper plan. At first, everything moves fast. The foundations are done, the walls go up, the roof is finished sooner than expected. Every day you see progress, and it pushes you forward.

But gradually, problems start to appear. Pipes run through places where load-bearing walls should be. Electrical wiring is laid out in a way that makes it inaccessible without tearing things apart. If you want to add a window, you end up interfering with the structure of the entire building.

Suddenly, you are no longer building. You are just fixing previous decisions. Every small change costs more time, energy, and money than the construction itself.

This is exactly how anti-patterns emerge in software. The application runs, the user interface works, and the deadline is met. Not because of one big mistake, but because of a series of small compromises that gradually turn into a system that is hard to change. Every small modification becomes complicated, new bugs appear, and development slows down. The common denominator in these situations is anti-patterns.

An anti-pattern is a “bad solution that looks like a good one.” It is not intentional sabotage, but a decision that makes sense in the moment while causing more harm than good in the long run.

It is also important to understand that an anti-pattern is not the same as a bug. A bug means the code does not work correctly, while an anti-pattern describes code that works but is poorly designed, with problems that only become visible over time when the code needs to be changed or extended.

It is also worth noting that anti-patterns are not just made by beginners. Everyone makes them, especially under pressure, without enough context, or simply because the fastest solution is not always the best one.

Why It Happens

The most common trigger for anti-patterns is time. Every developer knows the phrase “We need this by tomorrow.” When a deadline is looming, few people think about ideal architecture or elegant solutions. The goal is simple: make the code work and meet the deadline.

The problem is that these “temporary solutions” very easily become permanent. What started as a small shortcut gradually accumulates, and the code becomes harder to maintain.

Changing requirements make things even worse. The project keeps expanding, and new tasks appear, like “just add this”, “tweak it a bit”, or “let’s change the logic.” The code starts piling up without a clear plan, and what was once a clean application slowly turns into chaos.

Later, when a new developer joins the project, they inherit this unstructured code, and the anti-patterns continue to compound. That is exactly the moment when every change starts to “hurt,” and even small modifications can cause unexpected issues.

The Most Important Anti-Patterns

Software development is full of pitfalls, and some mistakes happen so often that they have their own names. The following examples are among the most common and show how a seemingly small decision can gradually complicate an entire project.

God Object – When One Class Knows Everything

This is one of the most widespread problems. A single class or file gradually starts taking on more and more responsibilities. For example, it validates data, communicates with the database, sends emails, and logs events.

At first, it feels convenient because everything is in one place. Later, it turns into a nightmare, because any change can break something else and testing becomes almost impossible.

A simple rule: one class, one responsibility.
If you need to use the word “and” when describing a class, it is probably doing too many things.

Spaghetti Code – Code Without Structure

The result is deeply nested conditions, function calls from unpredictable places, and no clear flow of logic. It emerges gradually when new functionality is simply “glued” onto existing code instead of thinking through the structure.

The best indicator that code is problematic? When you hear someone on the team say: “Better not touch that.”

The following example shows how code can work while still being almost unreadable:

def process(data, mode, flag):
    if mode == 1:
        if data:
            if flag:
                for item in data:
                    if item > 0:
                        # more logic...
                        pass
            else:
                pass
    elif mode == 2:
        # another branch...
        pass

Without carefully reading every line, it is hard to understand what is going on. The solution is to break the logic into well-named functions, each doing one thing.

Copy-Paste Programming – Duplication That Backfires

Copying a block of code into multiple places and slightly modifying it may look like a quick solution. In practice, it means that when you find a bug, you have to fix it in several places. It is easy to miss one.

Shared logic belongs in a function or a common module, not scattered across the project.

Magic Numbers – Numbers Without Context

if user.age > 18:
    ...

Why 18? A legal requirement? An internal rule? A test value? The code does not make it clear.

A small change makes a big difference:

LEGAL_AGE = 18
if user.age > LEGAL_AGE:
    ...

A single named constant significantly improves readability, and changing the value becomes a one-line update.

Tight Coupling – When Everything Depends on Everything

Coupling describes how interconnected parts of a system are. Some level of dependency is inevitable, but the problem arises when there is too much.

If a change in one part of the system breaks other parts, the components are too tightly coupled.

This makes every modification complicated and turns expanding the application into a risky adventure. The goal is for each part of the system to be as independent as possible. Such components are easier to test, modify, and replace.

Lava Flow – Old Code Everyone Fears

The name comes from an analogy. Old lava hardens and becomes a permanent part of the landscape – removing it is almost impossible.

Similarly, old parts of the code accumulate, and no one really knows what they do. There are no tests for them, and no one wants to touch them for fear of breaking something.

The result? This code stays in the project for years, and developers prefer to write workarounds around it rather than understand what’s inside.

A Practical Example

Imagine you are tasked with writing user registration. A quick solution might look like this:

class UserManager:
    def create_user(self, data):
        if not data.get("email"):
            raise Exception("Email is required")
        user = save_to_db(data)
        send_email(user["email"], "Welcome!")
        print("User created:", user)
        return user

It works. Deadline met.

The problem arises when you need to add SMS notifications, change validation, save to a different database, or add an audit log. The class starts growing, readability drops, and the risk of errors increases.

A better approach separates responsibilities:

class UserValidator:
    def validate(self, data):
        pass  # validation logic goes here

class UserRepository:
    def save(self, data):
        pass  # database operations go here

class NotificationService:
    def send_welcome_email(self, email):
        pass  # email sending logic goes here

class UserService:
    def __init__(self, validator, repository, notifier):
        self.validator = validator
        self.repository = repository
        self.notifier = notifier

    def create_user(self, data):
        self.validator.validate(data)
        user = self.repository.save(data)
        self.notifier.send_welcome_email(user["email"])
        return user

Each class has a single responsibility. If you change the way emails are sent, you only modify NotificationService and the rest of the system remains untouched.

Notice also that anti-patterns often appear in combination. UserManager from the first example is not only a God Object, but also the beginning of Spaghetti Code and Tight Coupling.

Most real-world code problems are a mix of several anti-patterns at once.

How to Avoid Anti-Patterns

You don’t need to be a senior architect to avoid the most common anti-patterns. A few simple habits can improve code quality and make maintenance easier.

1. Separate Responsibilities

If you feel that a class or function is doing too many things, it probably is. Strive to give each part of the code a clear role. Validation, database operations, and sending notifications should be separated. This makes the code easier to test and modify without breaking anything else.

2. Refactor Continuously

Refactoring is not a big system rewrite, but a series of small steps done regularly. Rename variables, split large functions, remove duplication, and gradually improve code clarity. Small daily improvements accumulate over time, turning chaos into a clear structure.

3. Write Tests

Even simple tests greatly reduce the risk of breaking something when making changes. Testing also helps you understand the code’s logic better and catch inconsistencies before they reach production. Unit tests, even basic ones, keep the project under control and give you confidence during refactoring.

4. Keep It Simple

The simplest solution is often the best. Don’t try to optimize “for the future” unless there is a specific reason. Code should first and foremost be readable and understandable. Make it work first, and optimize later if needed.

5. Review Your Own Code

A short self code review a few hours after writing code can reveal things you missed. Imagine someone new reading it—if it doesn’t make sense, fix it immediately. This simple habit improves clarity and uncovers hidden issues before they spread.

6. Gradually Improve Existing Code

If you join a project with existing anti-patterns, don’t rewrite everything at once. Identify the most critical areas and improve them gradually, always with tests. Small steps are more effective than radical changes, and even partial order is better than chaos.

By following these habits, your projects will become more organized, less prone to anti-patterns, and easier to work with for you and your team. The goal is not perfection, but continuous improvement and quality control, making code more readable and easier to modify.

In Conclusion

Anti-patterns are a natural part of software development. You can’t avoid them completely, nor should you try. Every developer encounters them. The difference between a beginner and an experienced developer is not that they never create them, but that they can recognize and gradually eliminate them.

Code is never truly finished. Good projects aren’t created perfectly on the first try. They grow over time through writing, testing, and improving.

It’s enough to regularly ask yourself a few simple questions:

Is this solution understandable? Could someone else modify it? Will it still work smoothly a month from now?

If the answer is yes to all of them, you’re on the right track.

👉 Explore practical tips for architectural decisions at Stack Compass Guide.

Most Software Architecture Decisions Are Actually About Trade-offs

mortylen — Wed, 18 Mar 2026 09:38:57 +0000

Development teams rarely struggle with having too few options. Much more often, they face the opposite problem: there are too many.

Today, almost every part of a system can be built in multiple ways. You can build a monolith or split your application into microservices. You can use REST APIs or GraphQL. You can store data in SQL or NoSQL databases. You can keep operations simple with Docker Compose or move to Kubernetes.

At first glance, this sounds like an advantage. In practice, however, it often leads to confusion and lengthy debates. The hardest part is usually not discovering new technologies. The hardest part is choosing between them without the team falling into hype, personal preferences, or the fear of making the “wrong” decision.

That’s why most software architecture decisions are not really about technology.

They are decisions about trade-offs between simplicity, speed, cost, and risk.

Problem: Architecture Decisions Are Often Intuitive Rather Than Systematic

In many teams, architecture decisions don’t happen in complete chaos—but they aren’t fully systematic either. They usually emerge as a mix of time, team experience, existing stack, current project pain points, and business pressure.

In practice, it often looks like this:

Someone proposes a technology because they already know it, have read about it, or successfully used it in another project.

The team starts weighing pros and cons, but the discussion quickly shifts from real needs to personal preferences.

Several other factors also influence the decision: deadlines, budget, existing infrastructure, the team’s ability to maintain a new technology, client requirements, security rules, hiring, and sometimes even internal politics.

In the end, a choice is made not because the team found the objectively “best” option, but because one option seems the most acceptable at the moment.

That alone isn’t the problem. The problem arises when the real reasons behind a decision remain implicit and unspoken.

The team may think it’s deciding on technology, but it’s actually deciding on something else:

speed of delivery,
operational complexity,
what the team already knows vs. what it still needs to learn,
the level of risk the team is willing to accept,
whether today’s simplicity or future migration will hurt more.

When these factors aren’t explicitly named, predictable patterns emerge. Opinions start to outweigh facts. Discussions turn into defending favorite tools. Trade-offs remain hidden. The team ends up optimizing for novelty, status, or hypothetical future scenarios instead of current needs.

The result is rarely a “bad” technical solution in an absolute sense. More often, it’s a solution that is too costly, too complex, or simply mismatched to the team’s and product’s current situation.

This is exactly how architecture can look convincing on a diagram, yet in daily practice it brings unnecessary costs, slows development, and increases operational overhead.

Most Technologies Aren’t “Better” – They Just Optimize Different Trade-offs

One of the most useful mindset shifts in thinking about architecture is this: most technologies aren’t universally better than their alternatives. They are simply better suited for specific constraints, priorities, and situations.

In other words: architecture decisions aren’t about finding the best technology.

They’re about choosing the right trade-offs for a particular context.

A few examples make this clear.

Monolith vs. microservices isn’t a debate between simplicity and sophistication. It’s usually a trade-off between simplicity and speed of delivery on one side, and independent scalability, greater team autonomy, and higher operational complexity on the other.

Kubernetes vs. Docker Compose isn’t enterprise vs. amateur tooling. It’s a trade-off between operational power and large-scale automation on one hand, and lower setup and maintenance complexity on the other.

A technology can be excellent and still be the wrong choice for a given project.

The wrong technology is often just the right technology used in the wrong context.

A Simple Framework for Architecture Decisions

If architecture is mostly about trade-offs, teams need a simple way to make those decisions. It doesn’t have to be complicated. In fact, it works better when it’s kept as simple as possible.

Here’s a straightforward, practical framework:

Step 1: Define Your Constraints

Before diving into specific technologies, clarify the environment in which the decision will operate.

Helpful questions include:

How big is the team?
What are the team’s current strengths?
How much time and budget do you have?
What are the real requirements for reliability and performance?
How mature is your infrastructure (deployment, monitoring, processes)?

A small startup and a large platform company have completely different constraints. Using the same architecture for both is almost always a mistake.

A five-person startup building an MVP faces very different constraints than a platform team running multiple products in production. If both teams copy the same architecture, one of them is likely making the wrong choice.

Step 2: Make Trade-offs Explicit

Once you understand your constraints, make the trade-offs visible.

For each option, ask:

What do we gain?
What do we pay for it?
What complexity does this introduce?
What risks are we accepting?

Trade-offs aren’t a side effect of a decision.

Trade-offs are the decision itself.

For example, moving to distributed services may improve isolation and scalability, but it also brings network failures, more complex deployments, higher observability demands, and greater coordination costs. These aren’t side notes—they are an inherent part of the decision.

Step 3: Ask the Right Questions

Good decisions don’t come from strong opinions—they come from the right questions.

For example, instead of asking, “Should we use microservices?”

Ask:

Do we need independent scaling right now?
Do we have multiple teams that need to deploy independently?
Can our team handle the increased complexity?
Are the system boundaries stable enough?

The right questions force the team to think about reality, not trends.

Step 4: Favor the Simplest Solution That Works

This is the step teams most often skip.

In software development, teams frequently solve problems too early. They think about large-scale scaling before they have many users, complex deployments before the system is properly modularized, and heavy infrastructure before they have people who can manage it.

A better approach:

Choose the simplest solution that meets your current needs.

This doesn’t mean ignoring the future. It means avoiding paying for complexity before it’s truly necessary.

The goal is to add complexity only when its need is clearly demonstrated.

This framework isn’t perfect or universal. But it helps teams make decisions consciously, rather than based on impressions, trends, or chance.

Example: Monolith vs. Microservices

Deciding between a monolith and microservices is one of the best examples of thinking in terms of trade-offs.

Imagine a small team building a product in a changing domain. Requirements are still evolving. Functionality is added gradually. System boundaries aren’t fully stable. The business primarily pressures for speed of delivery.

How would decision-making look in this situation?

First, clarify your constraints.

How many developers are on the team? How often do you deploy? How painful are your current releases? Do you already have solid monitoring, tracing, CI/CD, and incident response processes?

Next, make the trade-offs explicit.

A monolith usually brings faster local development, simpler deployment, easier debugging, and lower operational costs. Microservices can provide better service isolation, independent scaling, and clearer ownership boundaries—but only if the organization can handle the additional complexity.

Finally, ask the decisive questions.

Do you truly need independent scaling right now? Do multiple teams need to deploy independently? Does your DevOps maturity support managing many moving parts? Are the service boundaries stable enough for splitting the system to reduce coupling rather than just moving it across the network?

If the answer to most of these questions is no, a modular monolith is usually the better starting point.

If the answer to most is yes, microservices may make sense.

The decision should be driven by real system pressures, not architectural fashion.

Start with a monolith, modularize early, and split into services only when the need is proven.

Why Developers Still Overcomplicate Architecture

If all of this sounds reasonable, why do teams still end up with unnecessarily complex architectures?

Because architecture decisions aren’t just technical—they’re also human.

Trend Pressure

Companies like Netflix, Amazon, or Uber use a particular approach, which can create the feeling that it’s the “right” way.

But large companies solve large-company problems.

Copying their architecture without their scale, teams, and infrastructure is a common mistake.

Resume-Driven Development

Some decisions aren’t driven by product needs, but by a desire to work with modern technologies.

The system then ends up optimized more for a résumé than for reality.

The result: more complexity, less value.

Premature Scaling

Teams often design architecture for problems they don’t yet have:

high traffic,
many teams,
extreme complexity.

The costs are immediate. The benefits may never materialize.

Technical Possibility ≠ Business Need

Just because a system can be more distributed, flexible, or abstract doesn’t mean it should be.

In most cases, the problem isn’t the technology.

The problem is optimizing for the wrong things.

A Better Approach: Turn Decisions into Questions

One of the simplest improvements a team can make:

Stop debating technologies directly and start turning decisions into questions.

Instead of asking:

“Should we use GraphQL?”

Ask:

Do we have many clients with different data needs?
Are fixed REST responses inefficient for us?

Instead of asking:

“Should we move to Kubernetes?”

Ask:

Do we really need cluster orchestration?
Do we need self-healing and advanced automation?
Does the added operational complexity justify itself?

Instead of asking:

“Should we use NoSQL?”

Ask:

Are our data models unsuitable for a relational approach?
Do we have scaling requirements that SQL cannot handle?
Do we need a flexible schema?

This approach works because questions reveal hidden assumptions. They don’t eliminate uncertainty, but they make decisions more conscious and defensible.

Good questions don’t lead to perfect answers—they lead to better decisions.

Conclusion

Most software architecture decisions aren’t a battle between good and bad technologies. They are a choice between trade-offs.

The goal isn’t to find a universally “best” tool. The goal is to choose a solution that fits your team, your constraints, and your product’s current stage.

To make better architectural decisions:

define your constraints,
make trade-offs explicit,
ask the right questions,
choose the simplest solution that works.

Good architecture isn’t the most modern. It’s the one that makes sense in the given context.

👉 Explore practical tips for architectural decisions at Stack Compass Guide.

Photo by JIBIN SAMUEL

k-NN Classification and Model Evaluation

mortylen — Tue, 26 Aug 2025 07:19:29 +0000

In this article, I focus on selecting evaluation metrics such as Accuracy, Precision, Recall, and F1-Score, and I will try to explain in which situations each of them is appropriate to use. We will also see how to implement k-NN in the Rust programming language. This article is intended for readers who want to understand how to evaluate models for simple classification in machine learning, and I will also outline how the k-NN algorithm works. I chose the k-NN algorithm for its simplicity and ease of understanding.

Introduction to k-NN Classification

The goal of classification algorithms is to predict the category or class to which a given object belongs, based on historical data.

A typical example is predicting whether an email is spam or not, whether a credit card is fraudulent, or whether a programming project will be successful based on various factors such as the programmer’s experience, technological difficulty, or the number of cups of coffee they drink throughout the day.

k-NN (k-Nearest Neighbors) Algorithm

One of the simplest and most intuitive classification algorithms is k-NN (k-Nearest Neighbors), which belongs to the family of supervised learning algorithms. This algorithm is based on the idea that objects (or data points) within the same or similar categories will be closer to each other in space.

k-NN works by, for each new unlabeled object (e.g., a project), finding the "k" nearest neighbors in the training set, and based on their category (e.g., successful/unsuccessful project), it assigns the new object to the same category. The advantage is the simplicity of implementation and intuitive understanding. A disadvantage can be the higher computational cost with large datasets.

Data Preparation for k-NN Classification

Before we start training the model, it’s crucial to prepare the data. The k-NN algorithm is very sensitive to the quality and format of the input data, so it’s essential to ensure that the data is correctly prepared for analysis. I will briefly mention the most important data processing steps, which you can use as a simple checklist.

Handling Missing Data

If the dataset has missing values in some attributes, we must decide whether to fill in these values, remove them, or replace them. In most cases, we can replace missing values with the mean (imputation) or remove them if the number of missing values is negligible.

Categorizing Data

If we have categorical data (e.g., the category "experience level": beginner, intermediate, expert), we need to encode this data into numerical values. For k-NN, numerical values are required, so we could convert these categories into numbers (e.g., 1 for beginner, 2 for intermediate, and 3 for expert).

Outlier Detection and Removal

It’s important to check if any data contains extreme values that could skew the results. These outliers should either be corrected or removed.

Data Normalization/Scaling

k-NN works on the principle of calculating distances between points in the data space. If we have attributes with different ranges of values (e.g., experience from 1 to 10 and coffee from 1 to 100), some attributes may dominate the distance calculation. Therefore, it’s important to normalize or scale these values so that each attribute has an equal impact on the computation.

In the practical example, I have already prepared a clean dataset.

k-NN Algorithm Implementation

The k-Nearest Neighbors (k-NN) algorithm is simple yet very powerful for classification. Its basic idea is that an unlabeled point is classified based on the classes of its nearest neighbors in the data space.

The algorithm works as follows:

For each point in the test set, calculate the distance to all points in the training set.
Select the "k" nearest neighbors (using Euclidean distance or other metrics).
Predict the class (e.g., successful/unsuccessful project) based on the majority class of the k nearest neighbors.

We can use various distance metrics for calculating the distance, such as Euclidean distance, Manhattan distance, or Minkowski distance.

In this article, I’ve chosen the 3 most commonly used distance measurement methods to compare and select the most appropriate one.

Code Example

Our task is to predict the success of a programming project based on three attributes:

Experience (the programmer’s experience).
Tech Difficulty (the technological difficulty).
Coffee (the number of cups of coffee the programmer drinks while working).

For each project in the test set, we will decide whether the project will be successful or unsuccessful based on how its attributes match with the attributes of projects in the training set. To do this, we’ll use the k-NN algorithm, which employs Euclidean distance to compute the similarity between projects.

I have divided the project into several files:

data.csv: Our dataset, which contains a list of projects with attributes (experience, tech difficulty, coffee, and success).
data.rs: Responsible for loading data from the CSV file.
distance.rs: Contains methods for distance calculation (Euclidean, Manhattan, and Minkowski distance).
metric.rs: Contains metric methods (accuracy, precision, recall, and F1-score).
knn: Handles the k-NN computation and prediction.
main.rs: Ties everything together and prints the results.

In this article, I focus only on the most important functions (from main.rs). The full project code can be found on GitHub: https://github.com/mortylen/ml-knn-metrics-rs

main.rs:

mod data;
mod distance;
mod knn;
mod metrics;

use crate::data::{load_projects_from_csv, Project};
use crate::distance::{euclidean_distance_weighted, manhattan_distance_weighted, minkowski_distance_weighted, Weights};
use crate::knn::{knn, DistanceFn};
use crate::metrics::{accuracy, precision, recall, f1_score};

fn main() -> Result<(), Box<dyn std::error::Error>> {
    // Load training data from CSV
    let data = load_projects_from_csv("data/data.csv")?;

    // Split data into training and test sets - for simplicity, take the last 10 as test
    let (train_data, test_data) = data.split_at(data.len() - 10);
    let train_vec = train_data.to_vec();

    // Set "k" parameter
    let k = 3;

    // Set weights for features
    let weights = Weights {
        experience: 1.0,
        tech_difficulty: 1.0,
        coffee: 1.0,
    };

    let euclid_fn: DistanceFn = euclidean_distance_weighted;
    let manhattan_fn: DistanceFn = manhattan_distance_weighted;
    let minkowski_fn: DistanceFn = |a, b, w| minkowski_distance_weighted(a, b, w, 3.0);

    // Helper to predict labels for a test set
    fn predict_all(train: &Vec<Project>, test: &[Project], k: usize, dist_fn: DistanceFn, weights: &Weights) -> Vec<u32> {
        test.iter().map(|proj| knn(train, proj, k, dist_fn, weights)).collect()
    }

    let true_labels: Vec<u32> = test_data.iter().map(|p| p.success).collect();
    let predicted_labels_euclid = predict_all(&train_vec, test_data, k, euclid_fn, &weights);
    let predicted_labels_manhattan = predict_all(&train_vec, test_data, k, manhattan_fn, &weights);
    let predicted_labels_minkowski = predict_all(&train_vec, test_data, k, minkowski_fn, &weights);

    print_all_metrics(&true_labels, &predicted_labels_euclid, &predicted_labels_manhattan, &predicted_labels_minkowski);

    // Prediction for new input
    let new_project = Project { experience: 4, tech_difficulty: 2, coffee: 5, success: 0 }; // success is a placeholder
    println!(
        "\nPrediction for new project: experience={}, tech_difficulty={}, coffee={}",
        new_project.experience, new_project.tech_difficulty, new_project.coffee
    );
    print_single_prediction("Euclidean", knn(&train_vec, &new_project, k, euclid_fn, &weights));
    print_single_prediction("Manhattan", knn(&train_vec, &new_project, k, manhattan_fn, &weights));
    print_single_prediction("Minkowski (p=3)", knn(&train_vec, &new_project, k, minkowski_fn, &weights));

    Ok(())
}

fn print_metrics(true_labels: &[u32], predicted_labels: &[u32]) {
    let acc = accuracy(true_labels, predicted_labels);
    let prec = precision(true_labels, predicted_labels);
    let rec = recall(true_labels, predicted_labels);
    let f1 = f1_score(true_labels, predicted_labels);
    println!("Accuracy: {:.3}", acc);
    println!("Precision: {:.3}", prec);
    println!("Recall: {:.3}", rec);
    println!("F1-Score: {:.3}", f1);
}

fn print_all_metrics(true_labels: &[u32], euclid: &[u32], manhattan: &[u32], minkowski: &[u32]) {
    println!("-- Euclidean distance metrics --");
    print_metrics(true_labels, euclid);
    println!("\n-- Manhattan distance metrics --");
    print_metrics(true_labels, manhattan);
    println!("\n-- Minkowski distance (p=3) metrics --");
    print_metrics(true_labels, minkowski);
}

fn print_single_prediction(name: &str, pred: u32) {
    println!(
        "→ {} prediction: {} ({})",
        name,
        pred,
        if pred == 1 { "successful" } else { "unsuccessful" }
    );
}

Brief Explanation

First, the dataset (data.csv) is loaded and split into training and test sets:

// Load training data from CSV
let data = load_projects_from_csv("data/data.csv")?;

// Split data into training and test sets - for simplicity, take the last 10 as test
let (train_data, test_data) = data.split_at(data.len() - 10);
let train_vec = train_data.to_vec();

Setting the "k" parameter and the weights for the features:

// Set "k" parameter
let k = 3;

// Set weights for features
let weights = Weights {
    experience: 1.0,
    tech_difficulty: 1.0,
    coffee: 1.0,
};

Defining the distance functions (Euclidean, Manhattan, and Minkowski distances):

let euclid_fn: DistanceFn = euclidean_distance_weighted;
let manhattan_fn: DistanceFn = manhattan_distance_weighted;
let minkowski_fn: DistanceFn = |a, b, w| minkowski_distance_weighted(a, b, w, 3.0);

Predicting the success of the projects in the test set using "k-NN":

fn predict_all(train: &Vec<Project>, test: &[Project], k: usize, dist_fn: DistanceFn, weights: &Weights) -> Vec<u32> {
    test.iter().map(|proj| knn(train, proj, k, dist_fn, weights)).collect()
}

Evaluating performance (accuracy, precision, recall, and F1-score) and printing the results:

print_all_metrics(&true_labels, &predicted_labels_euclid, &predicted_labels_manhattan, &predicted_labels_minkowski);

Finally, predicting the outcome of a new project (experience: 4, tech_difficulty: 2, coffee: 5):

let new_project = Project { experience: 4, tech_difficulty: 2, coffee: 5, success: 0 }; // success is a placeholder
println!(
    "\nPrediction for new project: experience={}, tech_difficulty={}, coffee={}",
    new_project.experience, new_project.tech_difficulty, new_project.coffee
);
print_single_prediction("Euclidean", knn(&train_vec, &new_project, k, euclid_fn, &weights));
print_single_prediction("Manhattan", knn(&train_vec, &new_project, k, manhattan_fn, &weights));
print_single_prediction("Minkowski (p=3)", knn(&train_vec, &new_project, k, minkowski_fn, &weights));

Further experimentation is needed with different settings of the "k" parameter, adjusting the weights (for example, we could give more weight to the number of cups of coffee ☕, which certainly has a significant impact on project success 😂), adding more data to the dataset, and trying different splits of training and test data.

Output

After running the program, we will get a prediction for the success of the project based on its attributes (experience, technological difficulty, coffee). The program calculates the distances between the test project and all projects in the training set, selects the 3 nearest neighbors, and decides whether the project will be successful or unsuccessful.

-- Euclidean distance metrics --
Accuracy: 0.800
Precision: 0.800
Recall: 1.000
F1-Score: 0.889

-- Manhattan distance metrics --
Accuracy: 0.800
Precision: 0.800
Recall: 1.000
F1-Score: 0.889

-- Minkowski distance (p=3) metrics --
Accuracy: 0.800
Precision: 0.800
Recall: 1.000
F1-Score: 0.889

Prediction for new project: experience=4, tech_difficulty=2, coffee=5
→ Euclidean prediction: 1 (successful)
→ Manhattan prediction: 1 (successful)
→ Minkowski (p=3) prediction: 1 (successful)

Model Performance Evaluation

Every machine learning model, including our k-NN algorithm, can be evaluated based on how well it predicts outcomes on new data. Without evaluation, we wouldn’t be able to determine whether our model is actually good or if it needs improvement. k-NN is very sensitive to data quality, the choice of the "k" parameter, and the correct evaluation method. To this end, we use various evaluation metrics to help us understand how the model behaves in different situations.

Accuracy

Accuracy is the simplest metric that tells us the percentage of correct predictions made by the model. It is the most commonly used metric, especially when we have balanced data, meaning an equal number of cases for both classes.

$$\text{Accuracy} = \frac{TP + TN}{TP + TN + FP + FN}$$

TP: True Positives — correctly predicted positive instances.
TN: True Negatives — correctly predicted negative instances.
FP: False Positives — negative instances incorrectly predicted as positive.
FN: False Negatives — positive instances incorrectly predicted as negative.

Advantages:

Simple to understand and interpret.
Suitable for balanced datasets where the classes are equally represented.

Disadvantages:

Can be misleading when we have imbalanced data (e.g., when there are more negative cases than positive ones). If the number of negative cases is much higher than the positive ones, accuracy may show a high value even when the model never predicts the positive class. For example, if we have 95% negative cases, a model that always predicts the negative class might achieve 95% accuracy, even though it wouldn’t be useful.

pub fn accuracy(true_labels: &[u32], predicted_labels: &[u32]) -> f64 {
    let total = true_labels.len();
    let correct = true_labels.iter()
        .zip(predicted_labels.iter())
        .filter(|(t, p)| t == p)
        .count();

    correct as f64 / total as f64
}

Precision

Precision indicates how many of the positive predictions were actually correct. It is important when we want to minimize False Positives (incorrect positive predictions), such as in disease diagnosis, where we don’t want the model to incorrectly label a healthy person as sick.

$$\text{Precision} = \frac{TP}{TP + FP}$$

Advantages:

Useful for minimizing false positives. It can be very important to prevent the model from labeling a positive case (e.g., spam email or disease) as negative.
Suitable in cases where there are high costs associated with false positives, such as disease testing, where an incorrect diagnosis could lead to unnecessary treatment.

Disadvantages:

Does not account for false negatives. In situations where we also care about capturing all positive cases (e.g., disease detection), precision may not be enough.

pub fn precision(true_labels: &[u32], predicted_labels: &[u32]) -> f64 {
    let tp = true_labels.iter()
        .zip(predicted_labels.iter())
        .filter(|(t, p)| **t == 1 && **p == 1)
        .count() as f64;

    let fp = predicted_labels.iter()
        .zip(true_labels.iter())
        .filter(|(p, t)| **p == 1 && **t == 0)
        .count() as f64;

    if tp + fp == 0.0 {
        0.0
    } else {
        tp / (tp + fp)
    }
}

Recall

Recall, also known as Sensitivity or True Positive Rate, indicates how many of the actual positive cases were correctly identified. It is important when we want to minimize False Negatives (incorrectly missed positive predictions), such as in rare disease detection, where it is crucial not to miss any positive cases.

$$\text{Recall} = \frac{TP}{TP + FN}$$

Advantages:

Minimizes false negatives. For certain applications, it is important not to miss any positive case.
Suitable for cases where detecting as many positive cases as possible is important, even if it leads to a higher number of false positives.

Disadvantages:

Does not account for false positives, which can lead to the model predicting many incorrect positive cases.

pub fn recall(true_labels: &[u32], predicted_labels: &[u32]) -> f64 {
    let tp = true_labels.iter()
        .zip(predicted_labels.iter())
        .filter(|(t, p)| **t == 1 && **p == 1)
        .count() as f64;

    let fn_ = true_labels.iter()
        .zip(predicted_labels.iter())
        .filter(|(t, p)| **t == 1 && **p == 0)
        .count() as f64;

    if tp + fn_ == 0.0 {
        0.0
    } else {
        tp / (tp + fn_)
    }
}

F1-Score

The F1-Score is the harmonic mean between Precision and Recall. This metric is very useful when we do not want to favor one method over the other and need a balance between minimizing False Positives and False Negatives.

$$\text{F1} = 2 \cdot \frac{\text{Precision} \cdot \text{Recall}}{\text{Precision} + \text{Recall}}$$

Advantages:

Balances Precision and Recall. The F1-Score is useful when we do not know whether it is more important to minimize False Positives or False Negatives.
Suitable for imbalanced data, where one type of error (False Positives or False Negatives) may have a greater impact.

Disadvantages:

Not always intuitive, as it’s not as straightforward to interpret as Accuracy or Precision.

pub fn f1_score(true_labels: &[u32], predicted_labels: &[u32]) -> f64 {
    let p = precision(true_labels, predicted_labels);
    let r = recall(true_labels, predicted_labels);

    if p + r == 0.0 {
        0.0
    } else {
        2.0 * (p * r) / (p + r)
    }
}

Which Metric is the Right One?

Choosing the right evaluation metric is crucial for effectively assessing model performance, especially in classification tasks. Accuracy, Precision, Recall, and F1-Score are just some of the many metrics used to evaluate a model. Each of these metrics has its advantages and disadvantages, and the right choice depends on the nature of the problem and the data available.

When selecting metrics, it’s important to consider what matters most to you: whether to minimize False Positives or False Negatives, or simply maximize accuracy.

Metric Comparison:

fn main() {
    let true_labels = vec![1, 0, 1, 1, 0, 1, 0, 0, 1, 0];
    let predicted_labels = vec![1, 0, 1, 0, 0, 1, 0, 1, 1, 0];

    let acc = accuracy(&true_labels, &predicted_labels);
    let prec = precision(&true_labels, &predicted_labels);
    let rec = recall(&true_labels, &predicted_labels);
    let f1 = f1_score(&true_labels, &predicted_labels);

    println!("Accuracy: {}", acc);
    println!("Precision: {}", prec);
    println!("Recall: {}", rec);
    println!("F1-Score: {}", f1);
}

Evaluating the model provides key insights into how it behaves in practice, helping us decide if it’s accurate enough for deployment in the real world.

Accuracy: Let’s imagine our model predicts whether a product is "cheap" or "expensive," and we have 100 products, with 50 labeled as "cheap" and 50 as "expensive." If our model correctly predicted 80 of these 100 products, its Accuracy would be:

$$Accuracy = \frac{80}{100} = 0.8 = 80\%$$

Precision: If we have a model predicting spam emails and we don’t want the model to wrongly label legitimate emails as spam, it’s crucial that the model has high Precision. If the model labeled 20 emails as spam, of which 18 were indeed spam, the Precision would be:

$$Precision = \frac{18}{20} = 0.9 = 90\%$$

Recall: Let’s imagine a model predicting whether a patient has a disease (e.g., cancer). If it’s crucial to capture all truly sick patients, even if some healthy patients are mistakenly labeled as sick (False Positives), we would prioritize Recall. If the model caught 15 out of 20 truly sick patients, the Recall would be:

$$Recall = \frac{15}{20} = 0.75 = 75\%$$

F1-Score: In the case where a model predicts whether a product is "cheap" or "expensive" and we care about balancing the accuracy and completeness of predictions (i.e., balancing Precision and Recall), we use the F1-Score. If the model achieved Precision = 0.8 and Recall = 0.6, the F1-Score would be:

$$F1 = 2 \times \frac{0.8 \times 0.6}{0.8 + 0.6} = 0.6857 = 68.57\%$$

In the case of our example with programmers, technical difficulty of projects, and coffee consumption, F1-Score would be the most suitable metric because:

The technical difficulty of projects and coffee consumption habits of programmers might be imbalanced (e.g., most projects could be "low difficulty," but we still need to capture the "high difficulty" ones).
Coffee is just one of the factors, and it’s not entirely certain that a high-difficulty project automatically means the programmer will drink coffee.
We want to achieve a balance between precision (i.e., when the model says the programmer drinks coffee on a difficult project, it’s correct) and recall (i.e., we capture all those who actually drink coffee, even if the model might make some wrong predictions).

Model Tuning and Optimization

After evaluating our model and selecting the appropriate evaluation metric, the time comes to optimize it. No matter how good the model is initially, it can always be improved. We will focus on various techniques that can be used to tune a k-NN (k-Nearest Neighbors) model, particularly the selection of the optimal number of "k" (k-nearest neighbors), choosing a distance metric, and scaling the data.

Selecting the Optimal Number of k (k-Nearest Neighbors)

One of the most important hyperparameters in k-NN algorithms is the number "k", which determines how many nearest neighbors are used to classify a new point.

Small k (e.g., k = 1): The model becomes overly sensitive to individual outliers in the data and may suffer from overfitting (it fits too much to the training data).
Large k (e.g., k = 100): The model becomes smoother, as it makes decisions based on a larger number of neighbors. However, if "k" is too large, the model may overlook details and suffer from underfitting.

The optimal "k" can be found in several ways, such as trying different values of "k" and observing how Accuracy, Precision, Recall, and F1-Score change. Alternatively, cross-validation can be used, where the data is split into multiple subsets (folds), and the model is trained with different "k" values on each fold to find the optimal value that minimizes error.

If we use k = 3, we may notice that the model is more sensitive to details, resulting in higher Precision but lower Recall. If we set k = 7, the model will be less sensitive to fluctuations, which may improve Recall but reduce Precision.

Selecting the Distance Metric

The k-NN algorithm also depends on the selection of the metric used to measure the distance between points. While Euclidean distance is the most common, there are other metrics that may be more appropriate for different problems.

Euclidean Distance (L2 norm): The most common metric, suitable for continuous data (such as our example with technical difficulty and coffee habits).

$$d(x, y) = \sqrt{ \sum_{i=1}^{n} (x_i - y_i)^2 }$$

fn euclidean_distance(x: &Vec<f32>, y: &Vec<f32>) -> f32 {
    assert_eq!(x.len(), y.len(), "Vectors must have the same length");

    x.iter()
     .zip(y.iter())
     .map(|(xi, yi)| (xi - yi).powi(2))
     .sum::<f32>()
     .sqrt()
}

fn main() {
    let x = vec![1.0, 2.0, 3.0];
    let y = vec![4.0, 5.0, 6.0];
    let distance = euclidean_distance(&x, &y);
    println!("Euclidean distance: {}", distance);
}

Manhattan Distance (L1 norm): This metric is used when the data is discrete or when the differences between values are "more even" and we don't want large fluctuations between neighbors to have a significant impact.

$$d(x, y) = \sum_{i=1}^{n} |x_i - y_i|$$

fn manhattan_distance(x: &Vec<f32>, y: &Vec<f32>) -> f32 {
    assert_eq!(x.len(), y.len(), "Vectors must have the same length");

    x.iter()
     .zip(y.iter())
     .map(|(xi, yi)| (xi - yi).abs())
     .sum()
}

fn main() {
    let x = vec![1.0, 2.0, 3.0];
    let y = vec![4.0, 5.0, 6.0];
    let distance = manhattan_distance(&x, &y);
    println!("Manhattan distance: {}", distance);
}

Minkowski Distance: This generalizes both of the previous distances. It includes a "p" parameter, which determines the weight assigned to each dimension. For p=2, we get Euclidean distance, and for p=1, we get Manhattan distance.

$$d(x, y) = \left( \sum_{i=1}^{n} |x_i - y_i|^p \right)^{\frac{1}{p}}$$

fn minkowski_distance(x: &Vec<f32>, y: &Vec<f32>, p: f32) -> f32 {
    assert_eq!(x.len(), y.len(), "Vectors must have the same length");

    x.iter()
     .zip(y.iter())
     .map(|(xi, yi)| (xi - yi).abs().powf(p))
     .sum::<f32>()
     .powf(1.0 / p)
}

fn main() {
    let x = vec![1.0, 2.0, 3.0];
    let y = vec![4.0, 5.0, 6.0];

    let distance_p1 = minkowski_distance(&x, &y, 1.0); // Manhattan distance
    let distance_p2 = minkowski_distance(&x, &y, 2.0); // Euclidean distance

    println!("Minkowski distance (p=1, Manhattan): {}", distance_p1);
    println!("Minkowski distance (p=2, Euclidean): {}", distance_p2);
}

Conclusion

Classification using k-NN is an extremely powerful tool in machine learning, especially for simple problems where it is necessary to quickly and efficiently predict the class based on historical data. However, selecting the right metric for evaluating the model is crucial for success in practice. Accuracy, precision, recall, and F1-score are all useful in different applications, and the correct metric can significantly affect the model's performance in real-world conditions.

k-NN is not perfect for all types of tasks. When focusing on improving the model's performance, we need to concentrate on optimizing parameters such as "k", selecting the appropriate distance metric, weighting neighbors, and normalizing the data. Thanks to these improvements, k-NN can be a powerful tool in a wide range of applications, which can be tailored to the needs of our users or specific tasks.

More about machine learning algorithms and metrics can be found in a separate project:

👉 https://mlcompassguide.dev/

The repository for this article can be found on GitHub:

👉 https://github.com/mortylen/ml-knn-metrics-rs

If you’ve made it this far, congratulations! I hope the article was useful and inspired you with new ideas or experiences. If you have any questions or comments, feel free to share them in the comments.

📷 Cover photo by Amr Taha™

I’ve Started a New Machine Learning Project

mortylen — Sun, 24 Aug 2025 09:09:16 +0000

Over the last few months, I’ve been working on a small project to organize my knowledge of machine learning algorithms, data processing techniques, and evaluation metrics.

At first, it was just a personal learning exercise, meant to better understand the concepts and keep everything structured. But then I realized it could also be useful for others who are trying to navigate the ML landscape, so I decided to make it public.

What’s the goal?

The purpose of ML Compass Guide is to make the world of machine learning easier to navigate.

A clear decision map that shows where each algorithm belongs.
Explanations of algorithms.
Practical code examples.
A collection of metrics methods that help you evaluate models.

Current state

The project is still in its early stages (so expect a few rough edges 😅). Some sections are complete, others are just placeholders for now. But I’ll keep expanding it step by step.

How you can help

I’d love to get feedback from the community!

Is the structure clear?
What would make it more useful for you?
Are there algorithms/metrics you’d like to see next?

👉 You can check it out here: mlcompassguide.dev

Thanks for reading.

Change Data Capture in SQL Server

mortylen — Sat, 28 Jun 2025 19:19:56 +0000

Change Data Capture functionality is not available in the SQL Server Express edition. It is supported only in higher editions such as Standard, Enterprise, or Developer.

Main Components

The main components of CDC in SQL Server include:

Transaction Log: Serves as the source of information about changes made in database tables.
Capture Job: A SQL Agent job that regularly reads the transaction log and extracts changes.
Cleanup Job: A SQL Agent job that removes older records from CDC tables based on retention settings.
CDC Capture Tables: System tables where captured changes are stored, including operation type, original and new values.

Workflow

Change in monitored table

The user performs an INSERT, UPDATE, or DELETE operation on a monitored table.
Recording in transaction log

SQL Server records this change in the database’s transaction log.
CDC Capture Job reads transaction log

SQL Server Agent regularly (typically every few seconds) runs the CDC capture job, which reads new change records for all monitored tables.
Writing changes to CDC capture tables

The job extracts information about the changes (original and new values, operation type, timestamp) and stores them in system CDC tables.
Cleanup Job deletes old records

Based on the configured retention period (e.g., 3 days), the CDC cleanup job runs to remove old records from CDC tables.
User reads changes

The user can query special views to retrieve changes for a specified time period and process them further.

User modifies table (INSERT/UPDATE/DELETE)
       ↓
Transaction Log (records changes)
       ↓
CDC Capture Job (reads changes from log)
       ↓
CDC Capture Tables (e.g. cdc.dbo_Employees_CT)
       ↓
CDC Cleanup Job (deletes old records)

Setting Up and Testing CDC

Now that we understand what the technology is about, let's proceed with a simple test.

We will create a test database and a table within it, where we will perform changes and verify their capture using CDC.

Create Database and Table

Let's create a test database, for example TestCDC, and within it a table called Employees.

-- Create Database
CREATE DATABASE TestCDC;
GO

-- Create Table
USE TestCDC;
GO

CREATE TABLE Employees (
    ID INT PRIMARY KEY,
    FullName NVARCHAR(100),
    Position NVARCHAR(100),
    Rating DECIMAL(10,2)
);
GO

Enable Change Data Capture

Now that we have something to test on, let's enable Change Data Capture on our new database and table.

-- Enable CDC for Database
USE TestCDC;
GO

EXEC sys.sp_cdc_enable_db;
GO

-- Enable CDC for Table
EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name = N'Employees',
    @role_name = NULL,
    @supports_net_changes = 0;
GO

@source_schema: Name of the schema where the tracked table is located.
@source_name: Name of the table we want to track.
@role_name: Name of the database role that will have access to CDC data. If set to NULL, anyone with access to the database will be able to access the CDC data.
@supports_net_changes: If set to 1, enables the "net change" mode – an aggregated view of the changes (only the latest version of each row). If set to 0, all changes are recorded in detail.

Test Change Data Capture

We have everything set up for our testing. CDC is enabled for both the database and the tracked table.

Let's test it by inserting, updating, and deleting records in the table.

USE TestCDC;
GO

-- Insert data
INSERT INTO Employees (ID, FullName, Position, Rating)
VALUES (1, 'John Novak', 'Analytic', 18.00),
       (2, 'Peter Burn', 'Programmer', 20.00);
GO

-- Update data
UPDATE Employees
SET Rating = 23.00, Position = 'Gardener'
WHERE ID = 2;
GO

-- Delete data
DELETE FROM Employees
WHERE ID = 1;
GO

In our test database TestCDC, we should now see several new system tables that contain the captured changes:

cdc.captured_columns: A list of columns that are being tracked by CDC.
cdc.change_tables: Metadata about all CDC instances in the database, i.e., which tables are being tracked.
cdc.dbo_Employees_CT: Change data (INSERT/UPDATE/DELETE) for the specific Employees table.
cdc.ddl_history: History of DDL changes on tracked tables (e.g., ALTER TABLE), records structural modifications to the tables.
cdc.index_columns: Information about indexes on tracked tables, mainly primary keys used for identifying changes.
cdc.lsn_time_mapping: Mapping of LSN (Log Sequence Number) to the actual change time (datetime).
dbo.systranschemas: Internal helper table used to identify schemas for CDC.

Let's try a few simple queries and look at their results.

Viewing Captured Changes

-- Change data
SELECT * FROM cdc.dbo_Employees_CT

-- Added timestamp
SELECT 
  *, 
  sys.fn_cdc_map_lsn_to_time(__$start_lsn) AS CaptureTimeStamp
FROM cdc.dbo_Employees_CT;

-- A slightly cleaner view
SELECT 
    __$start_lsn,
    sys.fn_cdc_map_lsn_to_time(__$start_lsn) AS CaptureTimeStamp,
    __$operation,
    CASE __$operation
        WHEN 1 THEN 'DELETE'
        WHEN 2 THEN 'INSERT'
        WHEN 3 THEN 'UPDATE (old)'
        WHEN 4 THEN 'UPDATE (new)'
    END AS OperationType,
    ID,
    FullName,
    Position,
    Rating
FROM cdc.dbo_Employees_CT
ORDER BY __$start_lsn;

The value of the __$operation field indicates the type of change:

1 = delete (DELETE)
2 = insert (INSERT)
3 = old row (previous state during an UPDATE)
4 = new row (new state during an UPDATE)

Disabling Change Data Capture

If change tracking is no longer needed, it's a good idea to disable CDC.

This saves database performance and disk space.

USE TestCDC;
GO

-- Stops tracking changes for a single table
EXEC sys.sp_cdc_disable_table 
    @source_schema = N'dbo', 
    @source_name = N'Employees', 
    @capture_instance = N'dbo_Employees';


-- Disables CDC for the entire database
EXEC sys.sp_cdc_disable_db;

A Bit About Configuration

We have CDC set up and change tracking is working quite well. But what about maintenance and performance?

Change Data Capture automatically deletes old records every few days.

It relies on two main SQL Server Agent Jobs:

Capture Job: Reads the transaction log and copies changes into the CDC tables.
Cleanup Job: Regularly deletes old records from the CDC tables based on the retention settings.

These jobs can be configured according to our preferences.

Capture Job – Settings:

maxtrans: Maximum number of transactions processed in a single batch.
maxscans: Maximum number of log reads before a short pause.
continuous: 1 = job runs continuously, 0 = runs once (useful for testing).
pollinginterval: Number of seconds between log scans (applies only if continuous = 1).

-- Example: sets the log polling interval to every 5 seconds.
EXEC sys.sp_cdc_change_job 
    @job_type = N'capture',
    @pollinginterval = 5;

Cleanup Job – Settings:

retention: Number of minutes after which records in the CDC .CT tables are removed.
threshold: Number of records deleted in a single batch (performance control).

-- Example: sets cleanup of old data to run on a daily cycle.
EXEC sys.sp_cdc_change_job
    @job_type = N'cleanup',
    @retention = 1440; --one day in minute

Restarting CDC Jobs After Configuration Changes

When changing job settings, it is necessary to restart these services.

-- Restart Capture Job.
EXEC sys.sp_cdc_stop_job @job_type = N'capture';
EXEC sys.sp_cdc_start_job @job_type = N'capture';

-- Restart Cleanup Job.
EXEC sys.sp_cdc_stop_job @job_type = N'cleanup';
EXEC sys.sp_cdc_start_job @job_type = N'cleanup';

Monitoring the Size of CDC Tables

In a production environment, it is advisable to monitor the behavior of different configurations and their impact on database size and resource usage. For example, monitoring the size:

SELECT 
    t.name AS TableName,
    SUM(ps.used_page_count) * 8 / 1024 AS SizeMB
FROM sys.dm_db_partition_stats ps
JOIN sys.tables t ON t.object_id = ps.object_id
WHERE t.schema_id = SCHEMA_ID('cdc')
GROUP BY t.name
ORDER BY SizeMB DESC;

For Larger Tables and Frequent Changes, Try:

For larger tables and frequent changes, try reducing the @retention from the default 3 days to less, for example to 1 day (1440 minutes).
Depending on CPU performance, try increasing the @threshold for more efficient cleanup.
For frequent changes, you can reduce the @pollinginterval so the capture job reacts faster and processes changes more quickly.

The status of individual CDC jobs can also be monitored using sys.sp_cdc_help_jobs. This is a system stored procedure in SQL Server that provides information about the Change Data Capture agent jobs in the database.

It helps to find out which CDC agent jobs are configured, whether they are running correctly, their status, and more.

-- List and status of CDC Jobs.
EXEC sys.sp_cdc_help_jobs;

CDC Job Status Output by SQL Server Version

The output may vary depending on the SQL Server version. Versions 2022 and newer report the job status directly. Older versions (2012 - 2019) only show the job settings.

SQL Server Version 2022 and Newer:

job_type: capture or cleanup.
job_id: GUID identifier of the SQL Agent Job.
enabled: 1 = job is enabled, 0 = job is disabled.
status: Job status (numeric value, e.g., 1 = running).
last_run_date: Last run date of the job (YYYYMMDD).
last_run_time: Last run time of the job (HHMMSS).
last_run_outcome: Result of the last run (0 = failure, 1 = success).
message: Text message (if any).

SQL Server Version 2012 – 2019:

job_id: Unique job identifier (GUID).
job_type: Job type: capture or cleanup.
job_name: Name of the job in SQL Server Agent.
maxtrans: (capture only) Maximum number of transactions processed in one batch.
maxscans: (capture only) Maximum number of log scans per cycle.
continuous: 1 = job runs continuously (CDC capture job), 0 = no.
pollinginterval: Time (in seconds) between log scans if continuous = 1.
retention: (cleanup only) Number of minutes records are retained in CT tables.
threshold: (cleanup only) Number of records cleaned in one batch.

If you want to check the status on an older server, you can use the following query:

SELECT 
    j.name AS JobName,
    ja.start_execution_date AS LastStart,
    ja.stop_execution_date AS LastStop,
    ja.run_requested_date,
    ja.run_requested_source,
    h.run_status, -- 1 = Success, 0 = Failed
    h.message
FROM msdb.dbo.sysjobs j
LEFT JOIN msdb.dbo.sysjobactivity ja ON j.job_id = ja.job_id
LEFT JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id
WHERE j.name LIKE 'cdc.%';

Note

Before using CDC, it is recommended to set the recovery model to FULL.

The recovery model has a crucial impact on how CDC works because CDC does not read directly from the tracked table, but from the transaction log.

It is important how long the records are retained in the log — if the recovery model is set to SIMPLE, the log records might be deleted before CDC can process them.

Checking the Recovery Model:

SELECT name, recovery_model_desc
FROM sys.databases
WHERE name = 'YUOR-DATABASE-NAME';

Changing the Recovery Model:

ALTER DATABASE YOUR-DATABASE-NAME SET RECOVERY FULL;

Things to Watch Out For

Unbacked log: The log grows indefinitely.
CDC job not running: No new records, risk of data loss.
Large INSERT/UPDATE: Can cause temporary performance degradation.
Too long retention: Unnecessarily large .CT tables.
CDC enabled on a table without a PK: Difficult to track changed rows.

Conclusion

Change Data Capture is a very powerful tool for recording changes in SQL Server, minimizing performance impact, eliminating the need for custom triggers, and ideal for data warehousing, replication, and monitoring. A properly configured CDC system allows you to track and analyze all changes in data without complex logic or manual intervention.

If you found this useful, consider supporting me:

☕ Buy Me a Coffee

👉 My github profile GitHub

👉 My blog page Hashnode

🦀 Back to Rust!

mortylen — Sun, 15 Jun 2025 17:26:59 +0000

A while ago, I built these two small projects in Rust:
🔹 Joule heat calculator
🔹 OpenAI Language Lector

Now I’m diving into a Rust training course, and I’m curious (and slightly terrified) to discover how many things I got wrong back then 😅

Looking forward to learning what to fix… or maybe rewrite completely! Stay tuned for updates.

Gitea Self-Hosted Workflow Action For CI

mortylen — Thu, 27 Jun 2024 17:04:40 +0000

In this short tutorial I would like to describe the installation and setup of CI/CD Actions for a self-hosted Gitea server running on an Ubuntu. I will describe a script for testing and compiling code written in C# in a Visual Studio environment. I decided to separate the Actions server to a separate Ubuntu server instance for easier administration and to keep running processes from overwhelming the standalone git server. Anyway, it is possible to run Actions on the same server as Gitea, or to run Runners in Dockers. I described the installation and setup of a self-hosted git server in the previous article Gitea Self-Hosted Action Ubuntu Server. All the steps described below assume that you already have Gitea (Git-Server) installed.

Gitea Actions consists of several components. For our purpose, it is enough to know that we need ActRunner to run Actions. Like other CI Runners, ActRunner is designed to run independently on another server. It can be run using Docker or directly on the host. In this guide I will focus on running with the Docker engine. More information can be found on the official Gitea website.

Docker

The first component we will need is Docker. Using Docker we will later start ActRunner. For more information about installing Docker, see the official guide.

Let's do this.

Update local packaged:

$ sudo apt update
Allow APT to access repositories via the HTTPS orotocol:

$ sudo apt install apt-transport-https ca-certificates curl software-properties-common
Add the Docket GNU Privacy Guard key to the APT keyring:

$ curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo apt-key add -
Add the Docker repository to the APT package manager:

$ sudo add-apt-repository "deb [arch=amd64] https://download.docker.com/linux/ubuntu focal stable"
Prepare an installation from a Docker repository:

$ apt-cache policy docker-ce
Install Docker:

$ sudo apt install docker-ce
Check the status of the Docket service:

$ sudo systemctl status docker

Enable Docker to start automatically on system boot:

$ sudo systemctl enable docker
Adding an account to a Docker group:

$ sudo usermod -aG docker ${USER}
Set permissions to run the service.

$ sudo chmod +x /var/run/docker.sock

Act Runner

Once we have Docker up and running, we can start installing ActRunner. In order for ActRunner to run on a separate server, or in a separate container, and connect to a correct Gitea instance, we need to register it with a token.

Download the current version of ActRunner using wget. Replace the URL with the desired version. We recommend opting for the latest version. Here's an example for 64-bit Linux, version 0.2.10. For the full list, visit https://dl.gitea.com/act_runner/:

$ sudo wget -O act_runner https://dl.gitea.com/act_runner/0.2.10/act_runner-0.2.10-linux-amd64
$ sudo chmod +x act_runner

Check the version of ActRunner:

$ ./act_runner --version

ActRunner registration is important for the Runner to know for which Gitea instance to run the jobs. For this you need to generate a token. Gitea provides three levels of tokens:

Instance level: The admin settings page, like <your_gitea.com>/admin/actions/runners.
Organization level: The organization settings page, like <your_gitea.com>/org/settings/actions/runners.
Repository level: The repository settings page, like <your_gitea.com>/settings/actions/runners.

You can find your token on Gitea <your_gitea.com>/admin/actions/runners under Create new runner. Or separately for each repository <your_gitea.com>/settings/actions/runners under Create new runner.

Instead of <INSTANCE> enter your own URL and instead of <TOKEN> enter your own token:

$ ./act_runner register --no-interactive --instance <INSTANCE> --token <TOKEN>

For Example:

$ ./act_runner register --no-interactive --instance http://192.168.52.130:3000 --token MyAyfw5v4i8hwVGZR9NXjW0ikIHOXXXXXXXXXXXX

After registration, all you have to do is launch ActRunner using Daemon:

$ sudo ./act_runner daemon

You should now see the service tunning in the Gitea web environment.

If you want the service to start automatically after rebootong the server, write a simple bash script and add it to Crontab:

Create a file and insert the following script into it. Change the <USER> to your administrator account or choose any other location to store the file:

$ nano /home/<USER>/start_act_runner.sh
Insert the script into the created file and edit the path to act_runner if it is different:

#!/bin/sh
sleep 60        # waiting for all services to be started
cd /home/<USER>
./act_runner daemon

Enable the execution rule of our new script:

$ sudo chmod +x /home/<USER>/start_act_runner.sh
Open and edit Crontab:

$ crontab -e
Adding an instruction to the Crontab. Ensures that the script is run after the server restarts. Change <USER> to the administrator account or location where you saved the script:

@reboot /home/<USER>/start_act_runner.sh

Write Workflow Action

Now that everything is set up and the Runner is running, we can create a script to automate the building and testing of source code written in the Visual Studio environment.

We'll start by creating a simple console application for testing. In this application, we'll write a basic class, such as MyMath, which will contain a function to add two numbers.

public class MyMath
{
    public double Add(double num1, double num2)
    {
        return num1 + num2;
    }
}

Next, we will add a new NUnit Test Project to our solution. Left-click on your Solution and select Add -> New Project. Visual Studio will automatically install all the necessary NUnit packages. In the newly created NUnit test project, we will add a dependency on our console application to access the MyMath class. Now, we can write a simple test for our mathematical function.

namespace TestProject1
{
    public class Tests
    {
        private MyMath _myMath;

        [SetUp]
        public void Setup()
        {
            _myMath = new MyMath();
        }

        [TestCase(100000.0, 10.1, 100010.1)]
        [TestCase(-100000.0, -10.1, -100010.1)]
        [TestCase(0.0, 0.0, 0.0)]
        [Description("Verifies that the MyMath.Add() function works correctly with real numbers.")]
        public void MyMath_Add_RealNumber(double number1, double number2, double expected)
        {
            // Act
            double result = _myMath.Add(number1, number2);
            // Assert
            Assert.That(expected, Is.EqualTo(result), $"Not Correct: ({number1}) + ({number2})");
        }
    }
}

Don't forget to add dependency to your test project. As shown, three test cases are performed. The first test case checks positive numbers, the second tests negative numbers, and the last one tests zero. If the function calculates correctly, the test should pass.

You can try to run your test in Visual Studio.

Now for the interesting part. We will create a new repository on Gitea and link it to the project in Visual Studio. After that, we simply push the project to Gitea. With the foundation in place, we can start writing the action to run the automated testing.

All actions must be stored in the .gitea/workflows folder in our repository. Actions are written in YAML format, and any file with the yaml suffix placed in .gitea/workflows/ will be automatically executed.

Let's create the following action, name it for example nunit_test.yaml, and save it in the .gitea/workflows/ directory of the repository:

name: Testing Example
on:
  push:
    branches:
      - master

jobs:
  build-and-test:
    runs-on: ubuntu-latest
    steps:
      - name: Check out repository code
        uses: actions/checkout@v4

      - name: Setup dotnet
        uses: actions/setup-dotnet@v3
        with:
          dotnet-version: '8.0.x'

      - name: Restore dependencies
        run: dotnet restore

      - name: Build app
        run: dotnet build -c Release --no-restore

      - name: Run automated tests
        run: dotnet test -c Release --no-build

The first line name: Testing Example is the name of the workflow. We can name the workflow action as it suits us.

on:             # The trigger for this workflow.
  push:         # Push event, it is run whenever someone makes a push.
    branches:   # Filter for a specific branche. You can skip it if you want to run action for each branche.
      - master

The jobs: section represents a group of tasks that will run sequentially. In this case, the job is named build-and-test. The runs-on: attribute specifies the operating system for the job.

Steps Breakdown

Check out the repository code: It's always a good idea to check out the source code of your repository within your workflow at the beginning.

- name: Check out repository code
  uses: actions/checkout@v4

Set up the .NET SDK environment: Ensure the correct version of the .NET SDK is installed for the next steps. Specify your required version(s).

- name: Setup dotnet
  uses: actions/setup-dotnet@v3
  with:
    dotnet-version: '8.0.x'

Restore dependencies: Execute shell commands directly from the script using the run command. The dotnet restore command will ensure that all required dependencies and NuGet packages are downloaded and restored.

- name: Restore dependencies
  run: dotnet restore

Build the application: Build the source code to check if the compiler finds any errors in the code.

- name: Run automated tests
  run: dotnet build -c Release --no-restore

Run the automated tests: Execute the tests you wrote.

- name: Run automated tests
  run: dotnet test -c Release --no-build

Enhancing the Workflow

To improve the workflow, we can log the test results and upload them as an artifact. Rewrite the test execution and add another step to the job, specifying your own path to the generated file.

- name: Generate test report
  run: dotnet test -c Release --no-build --logger "html;logfilename=test_results.html"

- name: Upload report as artifact
  uses: actions/upload-artifact@v3
  with:
    name: test-reports
    path: ${{ gitea.workspace }}/TestProject1/TestResults/test_results.html

Test Workflow Action

If we have both the console project and the test project stored in the Gitea repository and our workflow action is ready, the next step is to test it. Let's make a change to our code, commit the changes, and push them to the master branch of the repository. Then, in the Gitea web interface, we will see the action running.

Conclusion

With Gitea up and running, we added Docker to our setup to facilitate containerized environments, which streamline development and deployment processes. We then configured a Gitea Actions runner using Docker, allowing us to automate our build and test workflows. To demonstrate of this setup, we created a simple console application in Visual Studio, wrote a basic MyMath class, and then added an NUnit Test Project to test our code. We crafted a Gitea workflow action to automatically build and test our application whenever changes are pushed to the repository. By following these steps, we have created self-hosted Git server environment that supports automated testing and continuous integration.

With your Gitea server fully operational, you can now enjoy the benefits of a self-hosted Git solution, customize it to fit your team's needs, and continue to expand its capabilities with additional workflows and integrations. May your code bring you joy!

For more details on configuring Gitea and other settings, be sure to check out my article Gitea Self-Hosted Action Ubuntu Server.

If you found this useful, consider supporting me:

☕ Buy Me a Coffee

👉 My github profile GitHub
👉 My blog page Hashnode
📷 Cover photo by Yancy Min

Easy Self-Hosted Git Installation on Ubuntu Server

mortylen — Wed, 01 May 2024 15:27:07 +0000

Welcome to a quick guide detailing the installation and setup of a self-hosted git server designed for beginners. We'll start by installing Ubuntu server, a popular choice known for its ease of use. Next, we'll set up PostgreSQL, a powerful and reliable open-source database management system, to ensure seamless data management for our git server. Finally, we'll integrate Gitea, a lightweight self-hosted git service, enabling you to effortlessly host your repositories and collaborate with your team members. For advanced configurations, detailed manuals for each service will be referenced later in the text. Let's proceed, step by step.

Ubuntu Server

To begin, we'll need an operating system. For this guide, Ubuntu Server is our choice. If you already have a Linux system installed, feel free to skip this step. Installing Ubuntu Server is straightforward—simply follow these steps:

Download the Ubuntu Server ISO image from the official website: https://ubuntu.com/download/server
Create a bootable drive and proceed with the installation, or create a new virtual machine from the ISO file.
Install the server according to your preferences. Detailed installation instructions can be found on the official website: https://ubuntu.com/tutorials/install-ubuntu-server

For this tutorial I have chosen the following settings:

Language: English

Keyboard: English (US)

The base for the installation: Ubuntu Server

Network: Choose according to your own preferences

Storage and file system: Choose according to your own preferences

Profile setup: Choose your name and password

name: administrator

server name: git_server

password: choose your strong password.

OpenSSH: Install OpenSSH server

After installing the server, log in with your login to the system.

PostgreSQL

Before we proceed with installing Gitea, it's essential to set up the database. Gitea supports various databases, including PostgreSQL, MySQL, MariaDB, and MSSQL. In this guide, we'll focus on installing and configuring PostgreSQL on the local server.

First, ensure your system is up to date:

$ sudo apt update
Install PostgreSQL and its additional components:

$ sudo apt install postgresql postgresql-contrib

or

$ sudo apt install postgresql
Enable PostgreSQL to start automatically on system boot:

$ sudo systemctl enable postgresql.service
Check the status of the PostgreSQL service:

$ sudo systemctl status postgresql.service

Switch to the postgres user:

$ sudo su - postgres
View the postgresql.conf file to check the 'password_encryption' attribute. You can use a text editor like nano. Find the path to the configuration file using the 'SHOW config_file' command.

$ psql -c 'SHOW config_file'

$ nano [FILE-NAME]

Ensure 'password_encryption' is set to 'scram-sha-256':

password_encryption = scram-sha-256
Restart PostgreSQL to apply the changed settings:

$ systemctl restart postgresql
Access the PostgreSQL terminal:

$ psql
Create a database user. Replace 'mypassword' with a strong password. Don't forget the semicolon at the end:

CREATE ROLE gitea WITH LOGIN PASSWORD 'mypassword';

Create the 'giteadb' database. Later, you connect to the database using the name of that database and the name of the user created above:

CREATE DATABASE giteadb WITH OWNER gitea TEMPLATE template0 ENCODING UTF8 LC_COLLATE 'en_US.UTF-8' LC_CTYPE 'en_US.UTF-8';
Exit the PostgreSQL terminal:

\q
Stay logged in with 'postgres' user and add an authentication rule to the 'pg_hba.conf' file. You can use a text editor like nano. Find the path to the configuration file using the 'SHOW hba_file' command:

$ psql -c 'SHOW hba_file'

$ nano [FILE-NAME]

Add the following line, where 'giteadb' is the database name and 'gitea' is the user name:

local giteadb gitea scram-sha-256
Restart PostgreSQL to apply the changed settings:

$ systemctl restart postgresql
Test the connection to the database, where 'gitea' is the user name and 'giteadb' is the database name:

$ psql -U gitea -d giteadb

Exit the PostgreSQL terminal and switch user with a user with sudo privileges:

\q

$ su - administrator

Gitea

For self-hosted git I chose Gitea. It's a powerful tool for smaller teams that provides many advanced features such as Git hosting, code review, team collaboration, CI/CD, and more. For a detailed feature comparison, visit https://docs.gitea.com/installation/comparsion. Additionally, you'll find a comprehensive installation guide there.

Install Gitea using wget. Replace the URL with the desired version. We recommend opting for the latest version. Here's an example for 64-bit Linux, version 1.22. For the full list, visit https://dl.gitea.com/gitea/:

$ wget -O gitea https://dl.gitea.com/gitea/1.22/gitea-1.22-linux-amd64

$ chmod +x gitea
Verify if Git is installed:

$ git --version

Create a user for Gitea:

$ sudo adduser --system --shell /bin/bash --gecos 'Git Version Control' --group --disabled-password --home /home/git git

Set up the directory structure:

$ mkdir -p /var/lib/gitea/{custom,data,log}

$ chown -R git:git /var/lib/gitea/

$ chmod -R 750 /var/lib/gitea/

$ mkdir /etc/gitea

$ chown root:git /etc/gitea

$ chmod 770 /etc/gitea
Copy Gitea to a global location:

$ sudo cp gitea /usr/local/bin/gitea
Create a system service. Download the file and save it to /etc/systemd/system/ or view the raw file in a browser and replace the URL with the version of Gitea you installed. You can find the list on https://github.com/go-gitea/gitea/blob/release/v1.22/contrib/systemd/gitea.service:

$ sudo wget https://raw.githubusercontent.com/go-gitea/gitea/release/v1.22/contrib/systemd/gitea.service -P /etc/systemd/system/
Enable automatic start of the service:

$ sudo systemctl enable gitea --now

If you have a problem running the service, check the contents of the file: `/etc/systemd/system/gitea.service`

Verify the status of the Gitea service:

$ systemctl status gitea

Logging in to the Gitea Web Interface

If the installation was successful and the gitea service is running, it's time to log in to Gitea via the web interface. Launch the web browser and enter the IP of your new git server and port 3000 in the address field. You should see the Initial Configuration page.

Example: 192.168.52.129:3000

Initial Configuration - Database Settings

In this section we will configure the connection between git and your PostgreSQL database.

Database Type: Select PostgreSQL
Host: Since we have installed the database as local, we choose local IP and default port for PostgreSQL. 127.0.0.1:5432
Username: The PostgreSQL database user (role) we created. In this case gitea.
Password: Password for the PostgreSQL database user we created. In this case mypassword.
Database Name: The name of the database we created. In this case giteadb.
SSL: Securing the communication between the gitea and the PostgreSQL database. In this case Disable since the database server is installed locally.
Schema: Leave blank, the database is in the default Public scheme.

Initial Configuration - General Settings

Gitea configuration. We can use the default settings, or change them according to our needs. It is worth mentioning the path to the repositories, LFS and logs. Change to a more reasonable repository if necessary.

Initial Configuration - Optional Settings

In this section it is possible to set up a mailer for gitea, set up a server and third party service and set up an administrator account.

For this quick tutorial we can leave it in the default settings and click the Install Gitea button. All settings can be changed later in the Gitea configuration file /etc/gitea/app.ini

Sign In to Gitea

If you have created an administrator account in Initial Configuration - Optional Settings, you can now log in with it. If not, register a new account. The first user registered to Gitea becomes the admin.

And that's it, your self-hosted git server is ready to use.

Let's Test Your Self-Hosted Git Using MS Visual Studio.

We have no choice but to try git server. First we create a new test repository in Gitea and then link it to the project in Visual Studio.

Create New Repository

After logging into Gitea, we create a new repository.

Fill in all the settings according to your preferences and click the Create Repository button.

Visual Studio and Git

First, we'll copy the URL link from our new test repository in Gitea.

Now in the Visual Studio project, go to the Git tab and select Create Git repository.... On the left hand side under the Other category, select Existing remote. In the Push your code to an existing remote section, paste your copied URL link and click on Create and Push. Visual Studio prompts you for a username and password.

In the View tab, select Git Changes, using this window you can work with the linked git. If Commit doesn't work, you probably don't have a user name set. Check this in Git -> Settings. In Visual Studio 2022 I noticed a small bug when VS had to be restarted, otherwise it didn't allow me to commit changes even if I had the user set up.

Now you should be able to see all your commits in your git repository in Gitea using the web browser.

Conclusion

In this simple tutorial, I tried to show you how to install git server using Gitea in a simple step-by-step way. It was installed on an Ubuntu server using PostgreSQL as the datastore. I didn't go into any detailed setup of Linux, the database server or Gitea itself, the goal was to show how easy it is to run a self-hosted git server. I have tested all the steps in the tutorial in a virtual environment, there may be slight changes depending on the versions of each component installed. I hope you have fun discovering the new features.

👀 In another article I wrote a little bit about workflow actions. If you would like to run Continuous Integration on Gitea, see here: Gitea Self-Hosted Workflow Action For CI

If you found this useful, consider supporting me:

☕ Buy Me a Coffee

My github profile GitHub
My blog page Hashnode
Cover Photo by Scott Rodgerson on Unsplash