DEV Community: Andrej Kirejeŭ

A Correspondence Match

Andrej Kirejeŭ — Thu, 22 Jan 2026 21:52:42 +0000

In the old Polish comedy "Mistrz zawsze traci" from 1976, a crafty French pharmacist arranges a correspondence match between two grandmasters who have never met each other.

For the past couple of days, I’ve been feeling like that very swindler. I explained to Copilot what needed to be done, waited for it to write the code, then wrapped it all up into a pull request. Then I waited for Cursor BugBot to do the review, copied its recommendations, and forwarded them back to Copilot for fixes. And so it goes, round and round.

Alas, exposure awaits us all sooner or later.

How to connect the Next.js MCP server to VS Code Copilot Chat

Andrej Kirejeŭ — Sat, 22 Nov 2025 21:35:49 +0000

The MCP server functionality is built in starting from Next.js 16 and doesn't require any additional configuration in the settings files. The path to the MCP server API is /_next/mcp.

Assuming your dev server is running on the port number 5002, do the following:

In the .vscode folder in the root of your project, create a mcp.json file.

Put the following content inside it:

{
  "servers": {
  "nextjs-mcp": {
    "url": "http://localhost:5002/_next/mcp",
    "type": "http"
  }
},
"inputs": []
}

Save the file.
Run your project's dev server.
Open Copilot Chat.
Ask something specific to Next.js, for example:

nextjs devtools, are there any build errors?
If prompted, allow running the MCP server and executing actions.

In our case, the answer was:

No Next.js devtools build errors are currently reported. Let me know if you want me to keep monitoring or run a build/test explicitly.

Now let's ask what the MCP server could do for us:

nextjs devtools, tell me what you are able to do?

I’m connected to the Next.js devtools MCP server, so I can pull diagnostics like build/runtime errors, fetch the dev-server logs, list app/pages routes, and inspect server actions. If you need anything along those lines—checking status, seeing routes, digging into errors—just let me know which detail you want.

Enjoy, and happy vibe coding!

Unit Testing React Hooks with NextJS Back-End: Challenges and Solutions

Andrej Kirejeŭ — Sat, 31 May 2025 10:30:11 +0000

Implementing unit tests for React hooks that interact with a back-end can be challenging, especially when the back-end server is built with NextJS. As of Spring 2025, Copilot does not provide assistance for this task, so developers must rely on their own expertise. We hope that the insights shared in this document will save others in similar situations from spending excessive time searching through resources and attempting to integrate various libraries.

Initial state:

A NextJS application where the NextJS server also is the application’s back-end server.
A React hook to be tested that heavily interacts with the back-end server.
Node test runner used as testing framework.

Custom server.ts

In our case the server.ts file is customized to accommodate specific back-end logic and provide global data structures shared among Next.js server pages and route handlers.

In development mode, the server is run using the command:

"scripts": {
  ...
  "dev": "cross-env NODE_ENV=development tsx watch --conditions=typescript --tsconfig tsconfig.server.json ./server.ts",
  ...
}

In production mode, the server is built and then executed as a JavaScript script using bare Node.js:

"build": "cross-env NODE_ENV=production next build && tsc --project tsconfig.server.json",
"start": "cross-env NODE_ENV=production node dist/server.js",

To test React hooks outside the browser environment, install the following libraries as development dependencies:

pnpm add @testing-library/dom @testing-library/react global-jsdom jsdom -D

Writing a unit test

For unit testing, a separate instance of the server should be launched before the test begins. The React hook is then tested, and upon completion of the test, the server instance is shut down.

Running node directly appears to be the main issue. Problems were encountered when running the server through pnpm run or tsx; the node test runner would hang at the end until Ctrl-C was manually pressed in the terminal window.

Here are functions to start and safely shut down the server as a child process:

// Server setup
let serverProcess: ChildProcess | null = null;

/**
 * Start the backend server before tests
 */
async function startServer(): Promise<void> {
  return new Promise((resolve, reject) => {
    console.log('Starting server...');

    serverProcess = spawn('node', ['dist/server.js'], {
      cwd: process.cwd(),
      stdio: 'pipe',
      shell: false,
      env: {
        ...process.env,
        NODE_ENV: 'production'
      }
    });

    let serverReady = false;
    const timeout = setTimeout(() => {
      if (!serverReady) {
        reject(new Error('Server failed to start within timeout'));
      }
    }, 30_000);

    if (serverProcess.stdout) {
      serverProcess.stdout.on('data', (data) => {
        const output = data.toString();
        console.log('Server stdout:', output);

        // Look for server ready indicator
        if (output.includes('Server is running on')) {
          if (!serverReady) {
            serverReady = true;
            clearTimeout(timeout);
            // Small delay to ensure server is fully ready
            setTimeout(resolve, 2_000);
          }
        }
      });
    }

    if (serverProcess.stderr) {
      serverProcess.stderr.on('data', (data) => {
        console.log('Server stderr:', data.toString());
      });
    }

    serverProcess.on('error', (error) => {
      console.error('Server process error:', error);
      clearTimeout(timeout);
      reject(error);
    });

    serverProcess.on('exit', (code) => {
      console.log('Server process exited with code:', code);
      clearTimeout(timeout);
      if (!serverReady) {
        reject(new Error(`Server exited with code ${code}`));
      }
    });
  });
}

/**
 * Stop the backend server after tests
 */
async function stopServer(): Promise<void> {
  return new Promise((resolve) => {
    console.log('Cleaning up test environment...');

    if (serverProcess) {
      console.log('Stopping server...');

      // Set up exit handler before killing
      const exitHandler = () => {
        console.log('Server stopped');
        serverProcess = null;
        resolve();
      };

      serverProcess.once('exit', exitHandler);

      // Try graceful shutdown first
      console.log('Sending SIGTERM to server...');
      serverProcess.kill('SIGTERM');

      // Force kill after timeout
      setTimeout(() => {
        if (serverProcess && !serverProcess.killed) {
          console.log('Force killing server...');
          serverProcess.kill('SIGKILL');
          // Give it a moment to clean up
          setTimeout(() => {
            if (serverProcess) {
              serverProcess = null;
            }
            resolve();
          }, 1_000);
        }
      }, 3_000);
    } else {
      resolve();
    }
  });
}

The unit test is as follows:

/**
 * Wait for server to be ready by making health check requests
 */
async function waitForServer(): Promise<void> {
  try {
    const response = await fetch(`http://localhost:${process.env.PORT}`);
    if (response.ok) {
      console.log('Server is ready!');
    } else {
      throw new Error('Server failed to become ready');
    }
  } catch (error) {
    // Server not ready yet
    throw new Error('Server failed to become ready');
  }
};

describe('useObject Hook Tests', async () => {

  before(async () => {
    await startServer();
  });

  after(async () => {
    await stopServer();
  });


  describe('Test Server is Running', async () => {
    it('should ensure the test environment is set up', async () => {
      assert.ok(serverProcess, 'Server process should be running');
      await waitForServer();
      assert.strictEqual(serverProcess?.killed, false, 'Server process should not be killed');
    });
  });

  describe('Test Entity', () => {
    describe('Data Loading', () => {
      it('should load data successfully when entityParam is provided', async () => {
        const { result } = renderHook(() =>
          useObject({
            entityParam: EntityName.Test
          })
        );

        // Initially should be in loading state
        assert.strictEqual(result.current.state, 'loading');
        assert.strictEqual(result.current.data.length, 0);
        assert.strictEqual(result.current.object, null);

        // Wait for data to load
        await waitFor(() => {
          assert.strictEqual(result.current.state, 'success');
        }, { timeout: 10_000 });

        // Verify final state
        assert.strictEqual(result.current.state, 'success');
        assert(Array.isArray(result.current.data));
        assert(result.current.entity);
        assert.strictEqual(result.current.entity.name, 'Test');
      });
    });
  });
});

To conduct the test, it is necessary to initialize a simulated DOM beforehand. This is achieved by passing the –import switch to the tsx utility. The command required is as follows (adjust for the actual .env location in your case):

"test": "pnpm run build && tsx --enable-source-maps --import=global-jsdom/register --test --env-file ../../.env.local"

All sources are compiled in this gist for your convenience.

Высококачественная транскрипция зашумлённых двухканальных телефонных звонков

Andrej Kirejeŭ — Fri, 02 May 2025 07:24:18 +0000

Высококачественная транскрипция зашумлённых двухканальных телефонных звонков

В одном из наших недавних проектов возникла необходимость транскрибировать телефонные звонки с крайне низким качеством звука. Аудиозаписи было трудно разобрать: речь часто прерывалась перекрывающимися голосами оператора и клиента. Кроме того, на стороне клиента часто присутствовали фоновый шум и посторонние звуки, улавливаемые микрофоном. Вдобавок к этому, речь часто содержала смесь языков и суржик.

В предыдущих проектах мы успешно использовали модель WhisperX, которая показала достойные результаты при удовлетворительном качестве аудио. Однако в данном случае полученные транскрипции оказались слишком низкого качества и непригодными для использования.

С выходом модели GPT-4o-transcribe мы решили оценить её возможности. В отличие от Whisper, эта модель принимает детализированные подсказки (prompts), что позволяет значительно улучшить качество транскрипции. Кроме того, GPT-4o-transcribe можно инструктировать распознавать реплики Оператора и Клиента. К сожалению, из-за отсутствия полноценной диаризации модель часто путает, кто из участников говорит.

Клиент возлагал большие надежды на проект, но остался недоволен первым результатом. Он требовал точной транскрипции, но не был готов выделить время или бюджет на сбор обучающих данных, аннотирование аудио или дообучение Whisper. Единственным улучшением по сравнению с исходными записями было то, что аудиоканалы оператора и клиента записывались отдельно.

Первая попытка (неудачная)

Первым логичным шагом было транскрибировать каждый канал отдельно с помощью Whisper, а затем объединить транскрипции по временным меткам. Этот подход не сработал. Whisper сильно «галлюцинирует» во время длинных пауз и на коротких репликах из одного слова. Кроме того, возвращаемые временные метки часто включали не только речь, но и окружающие участки тишины. Попытка скорректировать эти метки с помощью фильтра тишины из ffmpeg тоже не дала удовлетворительных результатов.

Гибридный подход

Мы пересмотрели наш подход. GPT-4o-transcribe выдаёт качественный текст, но без временных меток. Whisper, напротив, даёт временные метки, но при этом сильно теряет в точности. Внимательный человек, сравнивая оба результата, вполне может восстановить точную структуру речи.

Так мы пришли к следующему пайплайну:

Финальный алгоритм

Транскрибировать канал клиента с помощью gpt-4o-transcribe-mini. Затем определить язык речи с помощью GPT-4o. Язык оператора известен заранее.
Транскрибировать оба канала с помощью whisper-1.
- Установить температуру в 0.0.
- Использовать языковой prompt с несколькими типичными фразами.
- Результат — в формате VTT с временными метками.
Транскрибировать оба канала с помощью gpt-4o-transcribe.
- Установить температуру в 0.1.
- Использовать подробные prompts, адаптированные к содержанию каждого канала.

   Prompt для Оператора:

   Ты эксперт в области аудиотранскрипции.

   1. Предоставленный аудиофайл содержит только реплики оператора из телефонного разговора.
   2. Оператор представляет компанию "XXXXXX", работающую под торговой маркой "YYYYYYY", веб-сайт "ZZZZZZZZ".
   3. Оператор рекламирует услуги кредитования. Он описывает условия, процесс выдачи кредита, процентные ставки, комиссии и сборы.
   4. Клиент может задавать вопросы, соглашаться с условиями, отказываться от них или просить больше не звонить.
   5. Разговор может вестись на языке Language1, языке Language2 или быть их сочетанием. Сохраняйте оригинальный язык реплик.
   6. Качество аудио низкое. Исправляйте галлюцинации и нерелевантный контент.

   Предоставь полную транскрипцию.

   Промпт для Клиента:

   Ты эксперт по расшифровке аудио.

   1. Аудио содержит только реплики клиента.
   2. Оператор продвигает кредитные услуги.
   3. Клиент может спрашивать об условиях, ставках, комиссиях или отклонить предложение.
   4. Клиент может запросить дополнительную информацию или отказаться от дальнейшего общения.
   5. Язык может быть украинским, русским или смешанным. Сохраняйте оригинальные языки.
   6. Качество аудио низкое. Исключайте галлюцинации и несвязанный контент.

   Предоставьте полную расшифровку.

4. Сопоставить транскрипт GPT-4o с временными метками Whisper с помощью GPT-4.1, используя следующий prompt:

   Ты эксперт по анализу и восстановлению транскрипций телефонных звонков, связанных с продвижением кредитных услуг.

   Я предоставлю два текста:

   1. Транскрипция, созданная моделью gpt-4o-transcribe.
   2. Транскрипция в формате VTT с помеченными по времени репликами от whisper-1.

   Твоя задача — выровнять первую транскрипцию, используя временные метки из второй.

   * Если тексты отличаются из-за галлюцинаций, используйте версию GPT-4o.
   * Включите весь контент из GPT-4o.
   * Если временная метка отсутствует, добавьте текст к следующей строке   и скорректируйте время начала соответствующим образом.

   Верни только итоговую транскрипцию в формате VTT.

5. Скорректировать временные интервалы реплик Оператора с использованием периодов тишины, полученных через фильтры ffmpeg.

6. Повторить шаги 4–5 для транскрипта Клиента.

7. Объединить финальные VTT-транскрипты обоих каналов, обеспечив правильное распределение реплик по времени и по участникам разговора.

Заключение

Это решение не является ни быстрым, ни дешёвым — оно требует многократных вызовов моделей OpenAI. Однако результат — почти идеальная транскрипция, даже в тех случаях, когда Whisper полностью проваливается.

Исходный код, адаптированный для работы с украинским и русским языками, можно найти здесь.

High-Quality Transcription of Noisy Dual-Channel Phone Calls

Andrej Kirejeŭ — Thu, 01 May 2025 10:16:52 +0000

In one of our recent projects, we needed to transcribe phone calls with extremely poor audio quality. The recordings were hard to decipher, often interrupted by overlapping voices from both the operator and the client. Additionally, the client’s side frequently featured noisy backgrounds and extraneous sounds captured by the microphone. To make matters worse, the speech often contained a mix of languages and Surzhyk.

In our previous work, we had successfully used WhisperX, which performed reasonably well under decent conditions. However, in this case, the quality of the transcriptions was too poor to be useful.

With the release of GPT-4o-transcribe, we decided to evaluate its capabilities. This model accepts detailed prompts, unlike Whisper, which relies on keyword-based prompts. This flexibility significantly improves transcription quality. Moreover, GPT-4o-transcribe can be instructed to distinguish between the Operator and the Client. Unfortunately, due to its lack of true diarization, this often results in confusion between speakers.

The client had high expectations but was dissatisfied with the initial output. They requested accurate transcription but were unwilling to allocate time or budget for collecting training samples, annotating audio, or fine-tuning Whisper. The only improvement we had over raw recordings was that the operator’s and client’s audio channels were recorded separately.

Initial Attempt (Failed)

The first logical approach was to transcribe each channel separately using Whisper, and then merge the transcripts based on the timestamps. This did not work well. Whisper tends to hallucinate heavily during long silences and with short, one-word utterances. Additionally, the returned timestamps often included stretches of surrounding silence rather than isolating the actual speech. We attempted to refine these timestamps using silence detection via ffmpeg, but the results were still inaccurate.

A Hybrid Strategy

We took a step back and reassessed. GPT-4o-transcribe produces high-quality text but lacks timestamps. Whisper, on the other hand, provides timestamps but delivers lower-quality text. An attentive human could easily deduce the correct structure by comparing both.

Thus, we built the following pipeline:

Final Algorithm

Transcribe the Client’s channel using gpt-4o-transcribe-mini. Then, use GPT-4o to detect the spoken language. The Operator's language is known in advance.
Transcribe both channels using whisper-1.
- Set temperature to 0.0.
- Use a language-specific prompt with a few representative phrases.
- The output is in VTT format with timestamps.
Transcribe both channels using gpt-4o-transcribe.
- Set temperature to 0.1.
- Use detailed prompts tailored to each channel’s content.

   Operator’s Prompt:

   You are an expert in the field of audio transcription.
   1. The provided audio file contains only the operator’s lines from a phone conversation.
   2. The operator represents the company "XXXXXX", operating under the trade name "YYYYYYY", website "ZZZZZZZZ".
   3. The operator is advertising credit services.
   4. They describe terms, loan issuance process, interest rates, fees, and commissions.
   5. The client may ask questions, accept or reject terms, or request no further contact.
   6. The language may be Language1, Language2, or a mixture. Preserve original languages.
   7. Audio quality is poor. Fix hallucinations and unrelated content.

   Provide the full transcription.

   Client’s Prompt:

   You are an expert in audio transcription.
   1. The audio contains only the client’s lines.
   2. The operator promotes credit services.
   3. The client may ask about terms, rates, fees, or reject the offer.
   4. The client may request more info or refuse further contact.
   5. Language may be Ukrainian, Russian, or mixed. Preserve original languages.
   6. Audio quality is poor. Fix hallucinations and unrelated content.

   Provide the full transcription.

4. Align the GPT-4o transcript to Whisper’s timestamps using GPT-4.1. Use the following prompt:

   You are an expert in analyzing and reconstructing transcripts of phone calls promoting credit services.
   I will provide two texts:
   1. A transcript generated using the gpt-4o-transcribe model.
   2. A transcript in VTT format with timestamped utterances from whisper-1.
   Your task: align the first transcript using timestamps from the second.
   - If text differs due to hallucinations, use GPT-4o’s version.
   - Include all content from GPT-4o.
   - If a timestamp is missing, prepend the text to the next line and adjust the start time accordingly.

   Return only the final VTT transcript.

5. Correct the Operator’s timestamps using silence periods detected via ffmpeg filters.

6. Repeat steps 4–5 for the Client’s transcript.

7. Merge the final VTT transcripts from both channels, ensuring accurate timestamp ordering and correct speaker attribution.

Conclusion

This solution is neither fast nor cheap — it involves multiple calls to OpenAI models — but it yields nearly perfect transcripts even in cases where Whisper alone fails dramatically.

You can find the code adapted for handling Ukrainian and Russian languages here.

Trees in SQL (part 2)

Andrej Kirejeŭ — Wed, 25 Dec 2024 16:28:43 +0000

This is the second and final part of the article “Trees in SQL”. Following the principles of a good narrative, the most interesting part is saved for the end.

Interval Tree

Up until now, we have tried to work with a tree structure as if it were a graph. The challenges we faced—such as using recursion, an additional table, or a bulky string field—arose because SQL is inherently designed for working with sets rather than graphs.

To represent a tree structure as nested sets, each node must be assigned two integer parameters: a left boundary and a right boundary. These parameters must satisfy the following conditions:

The right boundary of a node must be greater than or equal to the left boundary.
The left boundaries of descendants must be greater than the left boundary of their parent node.
The right boundaries of descendants must be less than or equal to the right boundary of their parent node.
The intervals defined by the left and right boundaries of nodes with the same parent must not overlap.

The following diagram illustrates the representation of a tree as nested sets:

To store an interval-based tree structure, we create the following table:

CREATE TABLE test4 (
  id INTEGER NOT NULL,
  parent INTEGER,
  lb INTEGER NOT NULL,
  rb INTEGER NOT NULL,

  PRIMARY KEY (id),
  FOREIGN KEY (parent) REFERENCES test4 (id)
    ON UPDATE CASCADE
    ON DELETE CASCADE,
  CHECK (lb <= rb)
);

To improve data access speed, two indexes are required:

CREATE INDEX test4_x_lb ON test4 (lb);
CREATE DESCENDING INDEX test4_x_rb ON test4 (rb);

Extracting Subtree Nodes

A query to extract all nodes of a subtree with root node P would look like this:

SELECT t.*
FROM test4 t JOIN test4 t2
  ON t.lb > t2.lb AND t.rb <= t2.rb
WHERE
  t2.id = :P

If we replace the strict inequality on the left boundary with a non-strict one, the root of the subtree will also be included in the result set.

Manipulating Tree Data

Compared to previously discussed structures, interval trees require the most effort when adding a new node or moving a node from one branch to another. In addition to two triggers, a stored procedure is also needed.

We start with the procedure EL_TEST4, which identifies and returns the left boundary for a descendant of a given node (PARENT). If the parent’s interval is exhausted, it will be expanded by at least the value specified by the DELTA parameter. During the interval expansion, all higher-level intervals are also expanded, and intervals to the right are shifted. Intervals falling within the boundaries defined by LB2 and RB2 are excluded from processing to prevent infinite loops when moving a subtree from one parent to another.

CREATE PROCEDURE EL_TEST4 (
  PARENT INTEGER,
  DELTA INTEGER,
  LB2 INTEGER,
  RB2 INTEGER)
RETURNS (
  LEFTBORDER INTEGER)
AS
  DECLARE VARIABLE R INTEGER;
  DECLARE VARIABLE L INTEGER;
  DECLARE VARIABLE R2 INTEGER;
  DECLARE VARIABLE MKey INTEGER;
  DECLARE VARIABLE MultiDelta INTEGER;
BEGIN
  /* Retrieve parent boundaries */
  SELECT rb, lb
  FROM test4
  WHERE id = :Parent
  INTO :R, :L;

  /* Get the rightmost boundary of the parent's descendants, if any */
  R2 = NULL;
  SELECT MAX(rb) FROM test4 WHERE parent = :Parent
    INTO :R2;

  /* If there are no descendants, use the parent's left boundary */
  IF (:R2 IS NULL) THEN
    R2 = :L;

  /* Check if there is enough space for another descendant */
  IF (:R - :R2 < :Delta) THEN
  BEGIN
    /* If not, expand the interval */
    MultiDelta = :R - :L + 100;

    /* Ensure the new range is sufficient */
    IF (:Delta > :MultiDelta) THEN
      MultiDelta = :Delta;

    /* Shift the right boundary of parent intervals */
    IF (:LB2 > -1) THEN
      UPDATE test4 SET rb = rb + :MultiDelta
      WHERE lb <= :L AND rb >= :R
        AND NOT (lb >= :LB2 AND rb <= :RB2);
    ELSE
      UPDATE test4 SET rb = rb + :MultiDelta
      WHERE lb <= :L AND rb >= :R;

    /* Shift both boundaries of intervals to the right of the parent interval */
    IF (:LB2 > -1) THEN
      UPDATE test4
        SET lb = lb + :MultiDelta,
        rb = rb + :MultiDelta
        WHERE lb > :R
          AND NOT (lb >= :LB2 AND rb <= :RB2);
    ELSE
      UPDATE test4
        SET lb = lb + :MultiDelta,
        rb = rb + :MultiDelta
        WHERE lb > :R;
  END

  /* Return the found boundary */
  LeftBorder = :R2 + 1;
END;

The trigger TEST4_BI is called after inserting a record and assigns its left and right boundaries:

CREATE TRIGGER test4_bi FOR test4
  BEFORE INSERT
  POSITION 32000
AS
BEGIN
  /* Check if adding the root of a new tree or a descendant to an existing node */
  IF (NEW.parent IS NULL) THEN
  BEGIN
    /* If adding a new tree, set its left boundary after the highest known right boundary */
    NEW.lb = NULL;
    SELECT MAX(rb) FROM test4 INTO NEW.lb;

    IF (NEW.lb IS NULL) THEN
      /* Adding the first element to the table; use integers >0 for interval boundaries */
      NEW.lb = 1;
    ELSE
      NEW.lb = NEW.lb + 1;
  END ELSE
  BEGIN
    /* Call the procedure to find a possible left boundary for the descendant. 
       If the parent's interval is exhausted, it will be expanded. */
    EXECUTE PROCEDURE el_test4 (NEW.parent, 1, -1, -1)
      RETURNING_VALUES NEW.lb;
  END

  NEW.rb = NEW.lb;
END;

The trigger TEST4_BU adjusts node intervals when moving a node to a different parent:

CREATE EXCEPTION tree_e_invalid_parent
  'Invalid parent';

CREATE TRIGGER test4_bu FOR test4
  BEFORE UPDATE
  POSITION 32000
AS
  DECLARE VARIABLE OldDelta INTEGER;
  DECLARE VARIABLE L INTEGER;
  DECLARE VARIABLE R INTEGER;
  DECLARE VARIABLE NewL INTEGER;
BEGIN
  /* Check for changes in the parent */
  IF ((NEW.parent <> OLD.parent)
    OR (OLD.parent IS NULL AND NOT NEW.parent IS NULL)
    OR (NEW.parent IS NULL AND NOT OLD.parent IS NULL))
  THEN
  BEGIN
    /* Check for circular references */
    IF (EXISTS(SELECT * FROM test4
      WHERE id = NEW.parent
      AND lb >= OLD.lb AND rb <= OLD.rb)) THEN
    BEGIN
      EXCEPTION tree_e_invalid_parent;
    END ELSE
    BEGIN
      IF (NEW.parent IS NULL) THEN
      BEGIN
        /* Retrieve the rightmost boundary */
        SELECT MAX(rb)
        FROM test4
        WHERE parent IS NULL
        INTO :NewL;
        /* Assume at least one element with a null parent exists */
        NewL = :NewL + 1;
      END ELSE
      BEGIN
        /* Retrieve the new left boundary */
        EXECUTE PROCEDURE el_test4 (NEW.parent,
          OLD.rb - OLD.lb + 1, OLD.lb, OLD.rb)
        RETURNING_VALUES :NewL;
      END

      /* Calculate the shift */
      OldDelta = :NewL - OLD.lb;
      /* Shift main branch boundaries */
      NEW.lb = OLD.lb + :OldDelta;
      NEW.rb = OLD.rb + :OldDelta;
      /* Shift child boundaries */
      UPDATE test4
        SET lb = lb + :OldDelta, rb = rb + :OldDelta
        WHERE lb > OLD.lb AND rb <= OLD.rb;
    END
  END
END;

Deleting all nested elements when a node is deleted is ensured by the ON DELETE CASCADE rule on the PARENT field, so no additional actions are required to compress the intervals.

Conclusion

A reasonable question might arise: is there a universally optimal way to organize tree structures in SQL that suits all applications? Unfortunately, the answer is no. Each structure has its strengths and weaknesses that must be evaluated based on the specific requirements of the task. For instance:

What is the total number of records in the table? For small trees, performance differences between methods are minimal and insignificant.
How often will the tree data be modified? Interval trees achieve the best results for data retrieval, but demand significant computational resources for inserting new records or moving nodes.
What is the maximum depth of the tree? For depths of 10 levels or more, the second and third methods (mentioned earlier) result in a significant increase in database size due to the need to store all node connections along the path to the root.
What is more critical for the task: retrieval speed or data storage size?

Ultimately, the choice is left to the database designer, and we hope this article serves as a helpful guide.

Trees in SQL

Andrej Kirejeŭ — Wed, 25 Dec 2024 16:27:47 +0000

Business applications often need to represent and store information such as a company's organizational hierarchy, a country's administrative divisions, an inventory structure, or simply a set of folders and files. Despite their apparent differences, these examples share a common feature— they are all trees. Not the green, wood-made kind that provide pleasant shade on a sunny day, but, mathematically speaking, connected acyclic graphs. In computer science, the links between nodes are described in terms of parent-child relationships. Each node in the tree, except the root, has exactly one parent and zero, one, or many children.

The figure above shows an example of a tree. Vertex A is called the root of the tree. Nodes without subtrees — C, D, F, G, and H — are called leaves. Edges of the tree show parent-child relationships between nodes. A rooted tree is k-ary if each node has no more than k children. A binary tree is a well-known particular case of a k-ary tree where k = 2.

One of the common tasks performed on tree data is the retrieval of all child nodes at any nesting level, given a parent node. For example, calculating the total turnover of an account involves determining the turnovers of all its subaccounts, or compiling a list of goods belonging to a given goods group includes lists from all the nested subgroups.

In imperative programming languages, we usually use a graph traversal algorithm to find all subnodes. Although most modern database servers support SQL queries with recursive common table expressions, using the recursive part inside complex queries on large amounts of data can significantly degrade performance.

The reason is that SQL deals primarily with sets, not graphs, so one should implement dedicated data structures to represent nodes and edges as nested sets for efficient data selection.

In this article, we will consider four ways of storing tree-like structures inside a relational database and evaluate them in terms of query performance and the space needed for storing the underlying data.

Link to the parent

Perhaps the most intuitive and easiest way to represent tree-like data is to use the adjacency matrix in a relational table. Every record in the table corresponds to a node in the tree and holds the unique ID of the node (primary key) and the ID of its parent (for root nodes, the parent is null).

Below is a DDL query that creates the test1 table:

CREATE TABLE test1 (
  id INTEGER NOT NULL,
  parent INTEGER,
  name VARCHAR(60) NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (parent) REFERENCES test1 (id)
    ON UPDATE CASCADE
    ON DELETE CASCADE
)

Pay attention to the ON DELETE CASCADE clause of the FOREIGN KEY constraint declared on the PARENT field. It guarantees that the entire subtree will be deleted at once if the parent node is deleted. To protect the subtree from accidental deletion, the rule should be changed from CASCADE to RESTRICT.

Corresponding data for the tree shown at the beginning of the article looks like:

id	parent	name
1	NULL	A
2	1	B
3	1	E
4	1	H
5	2	C
6	2	D
7	3	F
8	3	G

Operations on tree data

Addition of a new tree node is done by the INSERT INTO operator:

INSERT INTO test1 (id, parent)
VALUES (<unique id of the node>, 
  <id of the parent node or NULL for a root node>)

To move the node from one parent to another, a value of the PARENT field has to be changed:

UPDATE test1
SET parent = <id of the new parent>
WHERE id = <id of the node being moved>

Obviously, to delete the node, one should invoke DELETE FROM operator providing ID of the node.

DELETE FROM test1
WHERE id = <id of the node>

Selecting whole subtree of the given node

With such an organization of the data, a simple SELECT operator allows us to fetch only one level of nesting of node's descendants.

SELECT id
FROM test1
WHERE parent = :P

So, what should we do if the task is to extract all subnodes, regardless of how deep they lie in the subtree?

An unconventional solution would be to write a query for every possible level of nesting and then merge their outputs using the UNION operator. Fortunately, we can use either recursive stored procedures or recursive common table expressions to assist us.

The procedure below returns the IDs of all subnodes of a given node. The second parameter specifies whether the parent node will be included in the resulting set.

CREATE PROCEDURE RecTest1(AnID INTEGER, Self INTEGER)
  RETURNS (ID INTEGER)
AS
BEGIN
  IF (:Self <> 0) THEN
  BEGIN
    ID = :AnID;
    SUSPEND;
  END

  FOR SELECT id FROM test1
  WHERE parent = :AnID INTO :ID
    DO FOR SELECT id FROM RecTest1(:ID, 1) INTO :ID
      DO SUSPEND;
END;

We can use the procedure on its own or JOIN its output with the tree data to select the required columns:

SELECT t.*
FROM test1 t
  JOIN RecTest1(:P, 0) r ON t.id = r.id

If a specific SQL server supports recursive common table expressions, we can write a query that selects all nested subnodes of the given node:

WITH RECURSIVE
  tree AS (
    SELECT id, parent
    FROM test1
    WHERE parent = <id of the given node>

    UNION ALL

    SELECT g.id, g.parent
    FROM test1 g JOIN tree h
      ON g.parent = h.id
  )
SELECT
  gt.id,
  gt.parent
FROM
  tree gt

Storing auxiliary links

In the previous example, we placed an id-parent link in the table with tree data. Since we only know about the relationship between a given node and its children, we need recursive queries to scan the tree in depth, which is not efficient when working with large amounts of data.

To overcome this limitation, an auxiliary table should be introduced. Such a table holds pairs of connected nodes along with the distance between them, where a distance of 1 indicates a parent-child relationship, and distances greater than 1 correspond to deeper levels of ancestry.

CREATE TABLE test2(
  id INTEGER NOT NULL,
  name VARCHAR(60) NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE test2_link(
  id_from INTEGER NOT NULL,
  id_to INTEGER NOT NULL,
  distance INTEGER NOT NULL,

  PRIMARY KEY (id_from, id_to),
  FOREIGN KEY (id_from) REFERENCES test2 (id)
    ON UPDATE CASCADE
    ON DELETE CASCADE,
  FOREIGN KEY (id_to) REFERENCES test2 (id)
    ON UPDATE CASCADE
    ON DELETE CASCADE
);

Here is data for the tree:

id_from	id_to	distance
B	A	1
C	B	1
D	B	1
C	A	2
D	A	2
E	A	1
F	E	1
G	E	1
F	A	2
G	A	2
H	A	1

Let's get back to our business. Selection of the subtree now done much easier. No recursion required.

SELECT
  t.*
FROM
  test2 t JOIN test2_link l
    ON l.id_from = t.id
WHERE
  l.id_to = :P

Using distance field, we can arrange the resulting set by node's closeness to the given root node.

Manipulating Tree Data

While we have managed to avoid recursion when retrieving data, this has significantly complicated the processes of inserting and modifying records.

Adding a Node

To add a node, you need to perform two operations: insert into the tree nodes table and the links table.

For example, to add a child node with an identifier 2 to a parent node with an identifier 1, execute the following commands:

INSERT INTO test2 (id) VALUES (2);

INSERT INTO test2_link (id_from, id_to, distance)
VALUES (2, 1, 1);

Creating Triggers

To automate the maintenance of tree relationships, it's advisable to create two triggers.

The first trigger fires after inserting a record into the test2 table and adds a self-referential link with a distance of 0 into test2_link:

CREATE TRIGGER test2_ai FOR test2
AFTER INSERT
POSITION 32000
AS
BEGIN
  INSERT INTO test2_link (id_from, id_to, distance)
  VALUES (NEW.id, NEW.id, 0);
END;

The second trigger fires after inserting a record into the test2_link table.

Its purpose is to create links between the new node and its descendants (if adding a subtree) and all ancestors of the parent node:

CREATE TRIGGER test2_link_ai FOR test2_link
AFTER INSERT
POSITION 32000
AS
DECLARE VARIABLE AnID INTEGER;
DECLARE VARIABLE ADistance INTEGER;
BEGIN
  IF (NEW.distance = 1) THEN
  BEGIN
    INSERT INTO test2_link (id_from, id_to, distance)
    SELECT down.id_from, up.id_to,
           down.distance + up.distance + 1
    FROM test2_link up, test2_link down
    WHERE up.id_from = NEW.id_to
      AND down.id_to = NEW.id_from
      AND down.distance + up.distance > 0;
  END
END;

Moving a Node

To move a node (or subtree) from one parent to another, follow these steps:

Remove the link to the current parent, detaching the subtree:

   DELETE FROM test2_link
   WHERE id_from = <ID of the node or subtree>
     AND id_to = <ID of the current parent>
     AND distance = 1;

Add the subtree to the new parent:

   INSERT INTO test2_link (id_from, id_to, distance)
   VALUES (
     <ID of the node or subtree>,
     <ID of the new parent>,
     1
   );

The previously created insert trigger will handle the new relationships automatically.

Create a trigger that, upon deleting a link with a distance of 1, removes all links between the nodes of the subtree and the nodes along the path from the parent node to the tree root:

CREATE TRIGGER test2_link_bd FOR test2_link
BEFORE DELETE
POSITION 32000
AS
DECLARE VARIABLE AnIDFrom INTEGER;
DECLARE VARIABLE AnIDTo INTEGER;
BEGIN
  IF (OLD.distance = 1) THEN
  BEGIN
    FOR
      SELECT down.id_from, up.id_to
      FROM test2_link down
      JOIN test2_link up
        ON up.id_from = OLD.id_from
       AND up.distance + down.distance > 1
       AND down.id_to = OLD.id_from
      INTO :AnIDFrom, :AnIDTo
    DO
      DELETE FROM test2_link
      WHERE id_from = :AnIDFrom
        AND id_to = :AnIDTo;
  END
END;

Deleting a Node

Deleting a node doesn't require special handling, as foreign keys in the test2_link table are configured for cascading deletions.

When a node is deleted from the test2 table, all related records in test2_link are automatically removed:

DELETE FROM test2 WHERE id = <Node ID>;

Ensuring Data Integrity

To maintain logical data integrity and adherence to our model, create two additional triggers.

The first trigger prevents editing records in the test2_link table:

CREATE EXCEPTION tree_e_incorrect_operation
  'Incorrect operation';

CREATE TRIGGER test2_link_au FOR test2_link
BEFORE UPDATE
POSITION 32000
AS
BEGIN
  EXCEPTION tree_e_incorrect_operation;
END;

The second trigger deletes a node if a record with a distance of 0 is removed from the links table:

CREATE TRIGGER test2_link_ad FOR test2_link
AFTER DELETE
POSITION 32000
AS
BEGIN
  IF (OLD.distance = 0) THEN
  BEGIN
    DELETE FROM test2 WHERE id = OLD.id_from;
  END
END;

These measures will help maintain data integrity and ensure the correct tree structure within the database.

Composite Path

It's not necessary to use a separate table to store the list of nodes leading to the root of a tree. Instead, you can concatenate the ordered list of node identifiers into a string, using a chosen delimiter, and store it directly within the node records table. For example:

CREATE TABLE test3 (
  id INTEGER NOT NULL,
  parent INTEGER,
  path VARCHAR(60) NOT NULL,

  PRIMARY KEY (id),
  FOREIGN KEY (parent) REFERENCES test3 (id)
    ON UPDATE CASCADE
    ON DELETE CASCADE
);

To optimize retrieval operations using the STARTING WITH operator, it's essential to create the following index to enhance performance:

CREATE INDEX test3_x_path ON test3 (path);

If you choose a forward slash (/) as the delimiter, the test3 table for the tree depicted in the figure above would be populated as follows:

id	parent	path
A	NULL	/
B	A	/A/
C	B	/A/B/
D	B	/A/B/
E	A	/A/
F	E	/A/E/
G	E	/A/E/
H	A	/A/

Retrieving Subtree Nodes

To retrieve all nodes in the subtree rooted at node P, execute the following query:

SELECT *
FROM test3
WHERE path STARTING WITH
  (SELECT path FROM test3 WHERE id = :P);

If you wish to exclude the root node itself from the results, modify the query accordingly:

SELECT *
FROM test3
WHERE path STARTING WITH
  (SELECT path FROM test3 WHERE id = :P)
  AND id <> :P;

Manipulating Tree Data

Similar to the simplest method of organizing hierarchical data discussed earlier, adding, moving, or deleting a node can be accomplished using standard SQL commands: INSERT, UPDATE, or DELETE.

The automatic generation of the path string can be managed by implementing the following triggers:

CREATE TRIGGER test3_bi FOR test3
  BEFORE INSERT
  POSITION 32000
AS
  DECLARE VARIABLE P INTEGER;
BEGIN
  NEW.path = '';
  P = NEW.parent;
  WHILE (NOT :P IS NULL) DO
  BEGIN
    NEW.path = CAST(:P AS VARCHAR(60)) || '/'
      || NEW.path;
    SELECT parent
    FROM test3
    WHERE id = :P
    INTO :P;
  END
  NEW.path = '/' || NEW.path;
END;

CREATE EXCEPTION tree_e_incorrect_operation2
  'Incorrect operation';

CREATE TRIGGER test3_bu FOR test3
  BEFORE UPDATE
  POSITION 32000
AS
  DECLARE VARIABLE P INTEGER;
  DECLARE VARIABLE T INTEGER;
  DECLARE VARIABLE NP VARCHAR(60);
  DECLARE VARIABLE OP VARCHAR(60);
BEGIN
  IF((NEW.parent <> OLD.parent)
    OR (NEW.parent IS NULL AND NOT OLD.parent IS NULL)
    OR (NOT NEW.parent IS NULL
      AND OLD.parent IS NULL)) THEN
  BEGIN
    NEW.path = '';
    P = NEW.parent;
    WHILE (NOT :P IS NULL) DO
    BEGIN
      NEW.path = CAST(:P AS VARCHAR(60)) || '/'
        || NEW.path;
      SELECT parent
      FROM test3
      WHERE id = :P
      INTO :P;
    END
    NEW.path = '/' || NEW.path;

    /* Check for circular references */
    IF (POSITION(('/' ||
      CAST(NEW.id AS VARCHAR(60)) || '/')
      IN NEW.path) > 0) THEN
      EXCEPTION tree_e_incorrect_operation2;

    NP = NEW.path ||
      CAST(NEW.id AS VARCHAR(60)) || '/';
    OP = OLD.path ||
      CAST(OLD.id AS VARCHAR(60)) || '/';
    T = LENGTH(OP) + 1;

    UPDATE test3
    SET path = :NP || SUBSTRING(path FROM :T FOR 60)
    WHERE path STARTING WITH :OP;
  END
END;

Storing the composite path from the tree root to the current node imposes a limitation on the maximum tree depth, approximately equal to L div (K + 1), where L is the length of the string field used to store the path, and K is the average length of the string representation of the record key.

Go to the part 2...

Using Node's built-in test runner with Turborepo

Andrej Kirejeŭ — Sun, 22 Dec 2024 11:00:15 +0000

It takes only five easy steps to add unit tests to a Turborepo monorepo. No external testing frameworks, such as Jest or Mocha, are needed.

1. Add tsx at the monorepo’s root level to run TypeScript code without issues related to different module systems:

npm add --dev tsx

2. Add a test task to turbo.json:

{
  ...
  "tasks": {
    "test": {
      "outputs": []
    },
    ...
  },
  ...
}

3. Add a test script in every package.json where necessary:

  "scripts": {
    ...
    "test": "tsx --test"
  },

4. Add a test script into the monorepo's root package.json:

  "scripts": {
   ... 
   "test": "turbo run build && turbo run test"
  }

The build step is optional, but it ensures the project compiles successfully before running tests. Alternatively, add a dependency on the build step in turbo.json.

5. That is it. Now add your first test using the built-in Node.js test runner functionality:

add.ts

  export function add(a: number, b: number) {
    return a + b;
  };

add.test.ts

import assert from 'node:assert/strict';
import { describe, it } from 'node:test';
import { add } from './add.ts';

describe('Test addition', () => {
  it('should add two positive numbers', () => {
    assert.equal(add(2, 2), 4);
  });

  it('should add two negative numbers', () => {
    assert.equal(add(-2, -2), -4);
  });
});

Finally, give it a try by running:

npm run test

What about starting a new platform?

Andrej Kirejeŭ — Tue, 30 Jan 2024 20:58:45 +0000

Twenty-five years ago, a significant leap in software development speed occurred when Borland Delphi entered the market with an entirely new approach: component-driven visual programming. Around that time, ideas for integrating relational databases into the object-oriented world evolved into robust ORM concepts. While numerous developers were stuck with either the laborious task of C++ development using the complicated MFC library or the clumsy and limited Visual Basic interpreter, we were eager to dive into this brave new world. With a small team of ten developers, we won a competition over more formidable and larger rivals, becoming one of the IT leaders in our country for the next two decades.

Over the last two years, I have dedicated my time to studying new technologies and observing the trends in modern software development. Now, I have a strong sense of déjà vu. We are once again on the cusp of a new technological shift, and those who quickly board this accelerating train will reap the most significant benefits, vastly outperforming competitors who cling to obsolete platforms and tools.

If I were to start a new platform right now, I would undoubtedly use the following strategies:

Full-stack development using Node.js, Next.js, and React Native. This approach is a significant time and effort saver. In our recent projects, we often have developers single-handedly implementing a complete feature—including server-side code, web application, and mobile applications for both Android and iOS—in just one to two weeks. I know teams that adhere to the classic back-end/front-end separation and spend more time just discussing data exchange protocol details.
Decouple the ER model from the physical database. This flexibility allows easy switching and mixing of SQL/NoSQL databases. When we were laying the first modules of our Gedemin platform in the late '90s, the low-level integration with the database driver code allowed us to utilize every bit of memory (which was scarce at that time) and maximize CPU cycle efficiency. Times have changed, and computational power is much cheaper now; there's no need to be as frugal as we once were.
Avoid manually drawing the UI. Well, JSX is great, but instead of mixing your code with HTML tags, build an engine that renders screens and forms based on ER entities and provided rules. This approach allows you to change the program's look and feel as easily and quickly as a snake sheds its skin during molting.
Eliminate even the slightest compatibility concerns. Embrace every novel feature CSS/HTML/JS has to offer without fear. By the time your program hits the market, these technologies will be implemented in even the most basic devices.
Integrate AI services extensively. Functions like natural language queries, human-like summaries and reports, and interpretation of commands and instructions, once the stuff of science fiction books 15–20 years ago, are now just a matter of making calls to the OpenAI API.

Zod — валидация и вывод типов на основании схемы данных

Andrej Kirejeŭ — Mon, 23 Jan 2023 13:33:05 +0000

Обычно, когда речь заходит о валидации данных на стороне клиента, имеют в виду корректность заполнения пользователем полей в форме ввода. На проверку данных, прочитанных с сервера или подготовленных для отправки, мало кто тратит время. Да и зачем? Ведь разработчик бэка классный специалист и мы на прошлом митинге согласовали с ним структуру объекта, который он будет возвращать через REST запрос. Только жизнь часто вносит свои коррективы в эту идиллию.

Недавно пришлось делать ревью кода, где в одном из полей сервер передавал данные как объект, а фронт считал, что там должна быть строка. Проект при этом рабочий. Возможно данное поле уже утратило актуальность и нигде не используется. Или разработчик фронта сделал преобразование по месту, прямо перед рендером на экран. А может, скрытая ошибка еще ждет своего времени, чтобы появиться в самый ответственный момент презентации важному клиенту.

При этом бэк и фронт из моего примера используют типизированные языки (C# и Typescript), но как в том детском стишке: однако, за время пути, собака могла подрасти! Сетевого зазора, когда данные существуют в виде текста JSON, достаточно для возникновения разногласий.

Выход — проверять данные сразу после их получения на клиенте и перед отправкой на сервер. При этом не только сверять базовые типы, но и с максимальной строгостью проверять соответствие правилам предметной области и бизнес-логики приложения.

Прямолинейный подход предусматривает, что мы сначала определяем тип, а затем создаем функцию валидации, которая принимает на вход объект (обычно, после парсинга из JSON), проверяет его и приводит к этому типу.

Прелесть Typescript заключается в его способности конструировать тип данных (type inference) на основе действий над этим данными. Т.е. мы можем начать с написания функции валидации и воспользоваться типом данных, который получится на ее выходе.

Такой подход применяется в библиотеке Zod, пример использования которой мы покажем ниже. Ради простоты, чтобы не возиться с установкой зависимостей для запуска кода мы будем использовать Deno. Исходники примеров из этой статьи можно взять здесь.

Пусть некоторое клиентское корпоративное приложение получает данные о сотруднике предприятия, состоящие из идентификатора и полного имени. Оба — строковые поля.

Мы начинаем с определения схемы данных и получаем на выходе объект с функцией валидации:

import { z } from "https://deno.land/x/zod/mod.ts";

const Employee = z.object({
  id: z.string(),
  name: z.string()
});

которую можем использовать для проверки данных следующим образом:

const employee = Employee.parse({
  id: 'eceffe7a-bbe6-41e9-bdc3-09b2970a0d8c',
  name: 'James Bond'
});

console.log(employee);
// { id: "eceffe7a-bbe6-41e9-bdc3-09b2970a0d8c", name: "James Bond" }

попытка передать данные неверного типа:

Employee.parse({
  id: 1234,
  name: 'James Bond'
});

приведет к законному исключению:

error: Uncaught ZodError: [
  {
    "code": "invalid_type",
    "expected": "string",
    "received": "number",
    "path": [
      "id"
    ],
    "message": "Expected string, received number"
  }
]

Обратите внимание, насколько детально сообщает zod о выявленном несоответствии в данных.

Не всегда удобно ловить исключения. На этот случай предусмотрен метод safeParse, который при любом исходе вернет объект: успех и проверенные данные, или неудачу и информацию об ошибке.

Пока неплохо, но где же здесь тип? Zod позволяет нам получить его автоматически на основании схемы валидации:

type Employee = z.infer<typeof Employee>;
// type Employee = {
//    id: string;
//    name: string;
//}

Мы справились с проверкой на базовые типы, но в реальном мире данные подчиняются дополнительным ограничениям, накладываемым применяемыми технологиями (например, строковое поле в базе данных имеет свой предельный размер), предметной областью или бизнес-логикой предприятия.

Усложним задачу:

id — это идентификатор в формате UUID.
name — это имя и фамилия, имя может быть двойным, записано латинскими символами, первая буква заглавная, остальные малые, минимальная длина имени и фамилии 2 символа. Максимальная длина всей строки 60 символов.

const Employee = z.object({
  id: z.string().uuid(),
  name: z.string()
    .regex(/(([A-Z]{1}[a-z]{1,40})(\s[A-Z]{1}[a-z]{1,40})?)\s([A-Z]{1}[a-z]{1,40})/)
    .max(60)
});

Тип объекта при этом не изменится. Его поля по-прежнему просто строки. Прописанные выше правила применяются в момент проверки при вызове методов parse или safeParse.

Zod позволяет прописать сложные проверки на основании данных из нескольких полей объекта. Усложним наш объект, добавив следующие поля и правила:

Дата рождения. Может отсутствовать. Если указана, то должна быть в диапазоне от 1.01.1950 до 31.12.2100. Именно так! Проверка на верхнюю границу никогда не будет лишней. Мы однажды всей компанией чуть с ума не сошли от одной ошибки у нашего клиента. Данные на экране были корректны, но в отчет не попадали. Пока не догадались вывести четыре цифры для года. Оказалось, что каким-то чудом оператор ввел в поле даты не двухтысячный, а трехтысячный год.
Дата приема на работу. Если дата рождения присутствует, то дата приема должна быть не менее, чем дата рождения плюс шестнадцать лет (мы не используем труд несовершеннолетних!), но в любом случае в диапазоне от 2000 до 2100 года.
Дата увольнения. Если присутствует, то должна быть не ранее даты приема на работу.

Обратите внимание, что для правила проверки данных мы можем указать сообщение, которое поможет разработчику быстрее понять если что-то пошло не так:

const Employee = z.object({
  id: z.string().uuid(),
  name: z.string()
    .regex(/(([A-Z]{1}[a-z]{1,40})(\s[A-Z]{1}[a-z]{1,40})?)\s([A-Z]{1}[a-z]{1,40})/)
    .max(60),
  dob: z.date()
    .min(new Date('01-01-1950Z'))
    .min(new Date('31-12-2100Z'))
    .optional(),
  employmentDate: z.date()
    .min(new Date('01-01-2000Z'))
    .min(new Date('31-12-2100Z')),
  dismissalDate: z.date()
    .min(new Date('01-01-2000Z'))
    .min(new Date('31-12-2100Z'))
    .optional()
})
.refine( 
  obj => typeof(obj.dob) === 'undefined' || 
    (obj.employmentDate.getTime() - obj.dob.getTime()) > 16 * 365 * 24 * 60 * 60 * 1000,
    {
      message: 'Too young to work!'
    } 
  )
.refine( 
  obj => typeof(obj.dismissalDate) === 'undefined' || 
    (obj.dismissalDate.getTime() >= obj.employmentDate.getTime()),
    {
      message: 'Dismissal date is before employment!'
    } 
  );

Посмотрим на тип объекта после добавленных правил валидации:

type Employee = {
    dob?: Date | undefined;
    dismissalDate?: Date | undefined;
    id: string;
    name: string;
    employmentDate: Date;
}

Думаю к этому моменту основная идея библиотеки zod — вывод типа данных на основе правил валидации — и принципы построения таких правил уже понятны читателю. Для закрепления, покажем как zod справляется с перечислениями и коллекциями объектов.

Добавим следующие данные:

Уровень сотрудника. Опциональное поле. Перечисление из следующих элементов: J1, J2, J3, M1, M2, M3, S1, S2, S3.
Набор навыков сотрудника. Опциональное поле. Задается непустым массивом объектов, каждый из которых содержит ИД навыка и его название. Название -- не пустая строка максимальной длиной 60 символов. Конкретный навык может встречаться в массиве только один раз. Максимальное количество навыков -- не более 100.

const Levels = ['J1', 'J2', 'J3', 'M1', 'M2', 'M3', 'S1', 'S2', 'S3'] as const;

const Skill = z.object({
  id: z.string().uuid(),
  name: z.string().min(1).max(60)
}).strict();

const Employee = z.object({
  id: z.string().uuid(),
  name: z.string()
    .regex(/(([A-Z]{1}[a-z]{1,40})(\s[A-Z]{1}[a-z]{1,40})?)\s([A-Z]{1}[a-z]{1,40})/)
    .max(60),
  dob: z.date()
    .min(new Date('01-01-1950Z'))
    .min(new Date('31-12-2100Z'))
    .optional(),
  employmentDate: z.date()
    .min(new Date('01-01-2000Z'))
    .min(new Date('31-12-2100Z')),
  dismissalDate: z.date()
    .min(new Date('01-01-2000Z'))
    .min(new Date('31-12-2100Z'))
    .optional(),
  level: z.enum(Levels).optional(),
  skills: Skill
    .array()
    .min(1)
    .max(100)  
    .refine( s => (new Set(s.map( i => i.name ))).size === s.length, 'Duplicate skills are not allowed!')
    .optional()
})
.refine( 
  obj => typeof(obj.dob) === 'undefined' || 
    (obj.employmentDate.getTime() - obj.dob.getTime()) > 16 * 365 * 24 * 60 * 60 * 1000,
    {
      message: 'Too young to work!'
    } 
  )
.refine( 
  obj => typeof(obj.dismissalDate) === 'undefined' || 
    (obj.dismissalDate.getTime() >= obj.employmentDate.getTime()),
    {
      message: 'Dismissal date is before employment!'
    } 
  )
.strict();

Обратите внимание, как мы пресекли возможность неучтенным полям просочиться в наш объект, добавив правило strict в схему валидации.

Кроме валидации, zod позволяет определить правила преобразования данных, которые особенно удобны в ситуации, когда спецификации бэка меняются по ходу разработки, а ресурсов и времени, на адаптацию фронта к этим изменениям нет.

Например, если вместо строки сервер начал возвращать информацию об имени и фамилии сотрудника объектом, мы можем после парсинга преобразовать объект в данные, понятные клиентскому приложению:

const Employee = z.object({
  id: z.string().uuid(),
  name: 
    z.object({
      firstName: z.string().regex(/(([A-Z]{1}[a-z]{1,40})(\s[A-Z]{1}[a-z]{1,40})?)/),
      lastName: z.string().regex(/[A-Z]{1}[a-z]{1,40}/)
    })
    .transform( ({firstName, lastName}) => `${firstName} ${lastName}` ),   
  ...

zod предоставляет и альтернативный вариант, когда данные могут быть трансформированы до этапа парсинга и валидации. Какой из двух подходов применять — дело вкуса и специфики конкретного приложения.

Для данных, которые следуют за объектно-ориентированной моделью приложения, zod предоставляет возможности:

Расширять схему родителя дополнительными полями и правилами потомка.
Сливать несколько схем в одну.
Создавать новую схему на основе части полей из существующей схемы (pick/omit), а также изменять свойства полей (partial/required).

Продолжая наш пример с сотрудником предприятия мы можем ввести тип данных для менеджера проектов, добавив поле с названием проекта:

const PM = Employee.extend({
  projectName: z.string()
});

Обратите внимание, что расширять схему, которая включает проверки и преобразования структур данных не получится. В этом случае родительскую схему следует разделить на чисто схему и схему с проверками.

Некоторые из возможностей библиотеки остались за скобками настоящей статьи (асинхронная валидация, рекурсия схем, создание схем для функций и др.). Библиотека zod активно развивается. Список самых свежих изменений и примеров применения можно найти в рабочем репозитории.

Move NodeJS server from Windows to Linux VPS

Andrej Kirejeŭ — Tue, 27 Sep 2022 21:47:40 +0000

Recently, we experienced a crash of hard drive in one of our server. Thanks to the RAID, data weren't lost, but this incident was a great impetus to move our NodeJS-based chatbots to the VPS server of a reliable cloud provider. As there is always a lack of time, the challenge was set to fit in one hour. The task was exacerbated by me being totally Windows guy, with only basic knowledge of Linux. I need to google almost every command. Even ls. I'm not joking.

The first step was to set up NodeJS. Obvious apt install nodejs got me stable but ancient version 12.x. I turned to the internet and learned about Snapcraft application store. There is a snap command to install NodeJS, which turned out to be a costly mistake, as I had lost half of the allotted time just to realize that snap somehow breaks the ability of PM2 to span new processes.

Eventually, I had found the command to download and install the latest version. Quite unpronounceable, though, from a Windows perspective:

curl -sL https://deb.nodesource.com/setup_18.x | sudo -E bash -
apt install nodejs

Then came the Yarn, our package manager of choice:

npm install -g yarn

The whole folder of the chatbot was zipped (excluding node_modules), transferred via FTP and unpacked. A bit of correction was needed to replace system environment variables with dotenv package and .env text file.

As Windows and Linux line endings differ, git was told to ignore it:

git config --global core.autocrlf true

On Windows, we used system Task Scheduler for launching chatbot server at system startup. Here, PM2 process manager comes to the help.

npm install -g pm2

I tried to compel it working with yarn start command, but quickly abandoned futile attempts and retreated to calling compiled JS instead.

pm2 start dist/server.js --name chat-bot --watch

Strange, that of two NodeJS servers, one refused to work with --watch flag. I didn't have enough time to dive into the problem and just switched off watching for the project. It is not updated often, anyway.

Don't forget to start up projects automatically upon VPS reboot, as it could happen at any moment:

pm2 startup

That is it. Transferring NodeJS servers from Windows to Linux turned out to be not so difficult and long.

How to find useless foreign keys in a database

Andrej Kirejeŭ — Thu, 16 Jun 2022 11:01:51 +0000

With times, old databases tend to overgrow with unnecessary or just empty fields. Whilst regular fields are not much of a concern, those with foreign key constraints, especially in tables with millions of records, would needlessly inflate the database file and put performance penalty on every insert/update/delete operation, as appropriate index need to be updated.

For example, an index on a field that holds nothing more than NULL values over a table with 100 000 000 records has a size approximately of 600 MB (true for Firebird 3 database file structure).

The query below helps to spot fields with foreign keys, which contain no more than a given count of unique values (by default, the variable maxuniqvalues is set to 1 to find all fields which are empty or contain exactly one value). Additionally, the condition on minimal record count in a table in question could be set through variable minreccnt.

The result data set includes the following columns: name of the relation, number of records in the relation, name of the field, value (only the first value is shown), and a list of objects dependent on the field. The latter will help a lot if the field to be deleted later.

Though the code is written in Firebird SQL flavor, it could be easily adapted to any modern SQL server.

EXECUTE BLOCK
  RETURNS(
    rn VARCHAR(31),
    reccnt INTEGER,
    fkfieldname VARCHAR(31),
    val INTEGER,
    dependent VARCHAR(8192)
  )
AS
  DECLARE VARIABLE minreccnt DOUBLE PRECISION = 1000000;
  DECLARE VARIABLE maxuniqvalues DOUBLE PRECISION = 1;
BEGIN
  FOR
    SELECT
      rc.rdb$relation_name,
      CAST((1 / idx.rdb$statistics) AS INTEGER),
      idxsfk.rdb$field_name,
      (SELECT
         LIST(TRIM(d.rdb$dependent_name))
         FROM rdb$dependencies d
         WHERE 
           d.rdb$depended_on_name = rc.rdb$relation_name
           AND 
           d.rdb$field_name = idxsfk.rdb$field_name)
    FROM
      rdb$relation_constraints rc
      JOIN rdb$indices idx
        ON idx.rdb$index_name = rc.rdb$index_name
      JOIN rdb$index_segments idxs
        ON idxs.rdb$index_name = idx.rdb$index_name
        AND idxs.rdb$field_position = 0
      JOIN rdb$relation_constraints rcfk
        ON rcfk.rdb$constraint_type = 'FOREIGN KEY'
        AND rcfk.rdb$relation_name = rc.rdb$relation_name
      JOIN rdb$indices idxfk
        ON idxfk.rdb$index_name = rcfk.rdb$index_name
      JOIN rdb$index_segments idxsfk
        ON idxsfk.rdb$index_name = idxfk.rdb$index_name
        AND idxsfk.rdb$field_position = 0
    WHERE
      rc.rdb$constraint_type = 'PRIMARY KEY'
      AND
      (idx.rdb$statistics > 0 
        AND idx.rdb$statistics < (1.0 / :minreccnt))
      AND
      idxfk.rdb$statistics >= (1.0 / :maxuniqvalues)
    ORDER BY
      idx.rdb$statistics ASC
    INTO
      :rn, :reccnt, :fkfieldname, :dependent
  DO BEGIN
    EXECUTE STATEMENT 
      'SELECT FIRST 1 ' || 
      :fkfieldname || 
      ' FROM ' || 
      :rn
      INTO :val;
    SUSPEND;
  END
END