Could SQL-as-a-service be a viable approach for Harbour/FiveWin today?

[Otto]

Hello friends,

Is anyone already using Harbour/FiveWin this way — with SQL moved behind a microservice?

From what I can see, this approach seems to simplify the client side significantly by removing SQL client DLLs, network/TLS handling, and tight database coupling from the UI process.

--- A short note on performance (before architecture)

When the UI, the service, and the SQL database run on the same machine, local IPC (socket / JSON / HTTP over localhost) is very fast.

Based on practical measurements and typical setups, the service layer usually adds:

• 1–2 ms per request for JSON encode/decode and local IPC

A simple SQL query itself typically takes:

• 1–10 ms (often more, depending on indexes and data size)

In other words:

The service layer usually adds less than 10–20% of the total request time.
The SQL query itself remains the dominant cost.

In practice, the performance impact is often negligible,
while the gain in stability and isolation is very noticeable.

--- Why this matters

In the past, embedding SQL directly into the Harbour application was often unavoidable:

• no permanently running web servers
• no lightweight local services
• no clean separation between UI, business logic, and data access

In short, the client process itself effectively had to be the server.

--- What has changed

Today the situation looks very different:

• services and web servers run continuously
• local IPC is cheap and reliable
• SQL servers already run in their own processes
• expectations for stability and fault isolation are much higher

This opens a new option:

Use SQL without embedding SQL clients into the UI process.

--- The idea in one sentence

The client talks JSON to a service.
The service talks SQL to the database.

As a result:

• the client loads no SQL DLLs
• the client does not know table schemas
• SQL connectivity is no longer part of the UI runtime

--- From DBF thinking to service thinking Classic DBF-style access

Code: Select all | Expand

USE kunden
GO TOP
DO WHILE !EOF()
   ? name, ort
   SKIP
ENDDO

Service-based access

Code: Select all | Expand

hRes := ServiceCall( "kunden_list", { "limit" => 10 } )

FOR EACH hRow IN hRes["rows"]
   ? hRow["name"], hRow["ort"]
NEXT

Same usage pattern.
Different responsibility.

--- Minimal client-side code

Code: Select all | Expand

FUNCTION ServiceCall( cAction, hData )
   LOCAL hReq := { ;
      "action" => cAction, ;
      "data"   => hData ;
   }

   RETURN hb_jsonDecode( SendToService( hb_jsonEncode( hReq ) ) )

Code: Select all | Expand

FUNCTION SendToService( cJson )
   LOCAL oSock := hb_socketOpen()
   hb_socketConnect( oSock, "127.0.0.1", 9001 )
   hb_socketSend( oSock, cJson + hb_eol() )
   LOCAL cRes := hb_socketRecv( oSock, 8192 )
   hb_socketClose( oSock )
RETURN cRes

This is the entire client-side SQL interface.

--- What runs where Client (Harbour / FiveWin)

• UI
• business orchestration
• JSON in / JSON out

Service

• SQL execution
• prepared statements
• transactions
• error handling

Database

• SQLite / PostgreSQL / MySQL

SQL lives behind the service boundary.

--- What disappears from the client process

With a service-based approach, the following no longer need to be loaded into the UI process:

Database client DLLs

• libmysql.dll / libmysqlclient.dll
• MariaDB client libraries
• libpq.dll (PostgreSQL)
• ODBC drivers (odbc32.dll and vendor-specific drivers)

Crypto / TLS libraries

• libssl*.dll
• libcrypto*.dll
• parts of schannel.dll / bcrypt.dll (DB-related)

Runtime and ABI dependencies

• additional vcruntime*.dll required by DB clients
• extra C/C++ runtime layers pulled in by SQL libraries

Helper libraries

• zlib1.dll
• character set / ICU libraries (depending on the DB client)

What remains in the client:

• Harbour runtime
• FiveWin UI
• minimal socket/HTTP code
• JSON handling

--- Why this is not overengineering

• no SQL DLLs in the client
• no OpenSSL, ODBC, or DB runtimes in the UI process
• fewer ABI and runtime conflicts
• crashes are isolated by process boundaries

This does not add complexity —
it moves complexity out of the wrong place.

--- Why this fits Harbour well

• Harbour works naturally with hashes and JSON
• DBF-style access maps well to service calls
• SQLite feels similar to DBF (single file, easy backup)
• migration can be done step by step

For example:

Code: Select all | Expand

USE kunden  →  ServiceCall( "kunden_list" )

--- About the classic all-in-one client approach

The classic all-in-one client approach solved SQL at a time when the client effectively had to be the server and service-based isolation was hard or unavailable.

That constraint is largely gone today.

What remains is an opportunity to separate concerns more clearly:

• UI and orchestration in the client
• SQL and connectivity in a service

--- One-sentence summary

The all-in-one approach already solves SQL —
but today we finally have the option to solve it somewhere else.

That option simply did not exist when these integrations were originally designed.

--- Question to the community

Is anyone already using Harbour/FiveWin this way in production?
If so, I’d be very interested in hearing about real-world experiences — both positive and negative.

Best regards,
Otto

[Otto]

Before: classic all-in-one client

Code: Select all | Expand

+------------------------------+
| Harbour / FiveWin UI         |
|------------------------------|
| UI + Business Logic          |
| SQL Client                   |
|  - libmysql / ODBC           |
|  - OpenSSL / TLS             |
|  - DB protocol & cursors     |
|  - memory & ABI handling    |
+------------------------------+
               |
               |  SQL / TCP / TLS
               v
+------------------------------+
| SQL Server                   |
+------------------------------+

Characteristics:

• SQL runs inside the UI process
• many native DLLs loaded into the client
• network, TLS and SQL share the same address space
• one crash = whole application stops

--- After: SQL behind a service boundary

Code: Select all | Expand

+------------------------------+
| Harbour / FiveWin UI         |
|------------------------------|
| UI + Business Orchestration  |
| JSON in / JSON out           |
+------------------------------+
               |
               |  local IPC (socket / HTTP)
               v
+------------------------------+
| Service (SQL Gateway)        |
|------------------------------|
| SQL Client                   |
|  - prepared statements       |
|  - transactions              |
|  - error handling            |
+------------------------------+
               |
               |  SQL (local or remote)
               v
+------------------------------+
| SQL Server                   |
+------------------------------+

Characteristics:

• UI process no longer contains SQL logic
• no SQL client DLLs in the client
• crashes are isolated to the service
• SQL, TLS and DB protocols live where they belong

--- What disappears from the client

Code: Select all | Expand

❌ libmysql / libpq / ODBC
❌ OpenSSL / TLS stacks
❌ DB protocol parsers
❌ DB-specific memory layouts

What remains in the client

Code: Select all | Expand

✔ Harbour runtime
✔ FiveWin UI
✔ socket or HTTP
✔ JSON encode / decode

--- One-line summary

Before: the UI process is the database client
After: the UI process talks to a service — and nothing else

[Eroni]

Dear Otto
I've been following your brilliant work in building Mod Harbour, I've even tried to implement it on my systems, but I've never been able to configure the cloud service to work effectively.
I'm going to give a brief account of what works for me, since around 2003, when I decided it was time to abandon the DBF architecture.
As soon as the first DLLs that allowed Harbor/xHb access to SQL databases appeared, I dedicated a lot of time to re-writing/revising all my programming code, regarding data manipulation.
As I wanted my code to become unique for any database, I rewrote everything so that it was just the commands:
Insert...
Update...
Select...
In addition to the standard DDL commands in the ANSI SQL language, supported by all types of SQL databases.
In pure form, using standard SQL, without variations used for each specific manufacturer. To do this, I had to create some classes that would help me manipulate large records, processes, etc. Nowadays, my code no longer has any "use","seek", "do while","replace","append", etc. There is only pure standard SQL. Here in Brazil we have an expression that I don't know if it can be understood internationally: "You left the DBF but the DBF didn't leave you". I still see colleagues working with SQL but using xBase-type commands. I have systems running in the cloud for many years, without worrying about which operating system the database is on (Linux, Windows, etc.), Harbour just makes the access and all processing is done on the server. Each customer has the freedom to choose how they want to maintain their database, their provider, host, etc. I believe your approach to this topic is very important, I just wanted to share my experience.

[Otto]

Dear Eroni,

thank you very much for sharing your experience — I really appreciate your post.

What you described resonates strongly with my own journey.
Especially this sentence hit the nail on the head:

“You left the DBF but the DBF didn’t leave you.”

I still see the same pattern today: people using SQL, but thinking and coding as if they were still working with DBFs.

Your approach — rewriting everything around pure SELECT / INSERT / UPDATE, standard SQL and leaving the database choice open — is exactly the right direction. And you’re absolutely right: this already solved many problems long ago.

After working through this topic in depth, I think I’ve arrived at a solution that follows the same philosophy, but adapts it to today’s runtime environment.

--- Where I ended up

Conceptually, I fully agree with you:
Harbour should not care about the database vendor, operating system or hosting provider.
It should simply consume data and apply business logic.

The main difference is where SQL connectivity lives.

Instead of embedding SQL client libraries and C code directly into the Harbour process, I decided to move SQL access behind a small service layer (Node.js in my case), using JSON as the contract.

So on the Harbour side, the interface becomes as simple as:

Code: Select all | Expand

FUNCTION HttpPostJson( cUrl, cJson )

Harbour sends JSON, receives JSON — nothing more.

The Node.js service then:

• talks to MySQL / MariaDB / PostgreSQL
• handles drivers, TLS, pooling and concurrency
• returns clean JSON back to Harbour

--- Why I believe this fits today better

This is not about rejecting SQL inside Harbour —
it’s about moving it to a place that did not exist in a practical way 15–20 years ago.

Back then:

• Harbour often had to be the server
• external services were hard to deploy
• process isolation was expensive

Today:

• services run permanently
• local HTTP/JSON calls cost 1 ms or less
• SQL servers already run in separate processes
• Node.js is very good at exactly this kind of I/O work

The overhead of one extra JSON hop is negligible compared to:

• SQL execution time
• disk I/O
• UI rendering
• human interaction latency

What is reduced significantly is:

• native DLL complexity inside Harbour
• ABI and compiler coupling
• crash impact (SQL crashes no longer kill the UI process)

--- How this relates to your experience

I see this as a natural continuation of what you already described:

• you removed DBF thinking from application logic
• you embraced pure SQL
• you made the database backend replaceable

The service-based approach simply moves SQL connectivity to the right boundary.

Harbour remains:

• business logic
• legacy rules
• orchestration

The service handles:

• SQL
• networking
• infrastructure

From my perspective, this preserves everything that works well in your approach — while simplifying the Harbour side even further.

Thanks again for sharing your experience.
It helped confirm that this direction makes sense — just with today’s tools.

Best regards,
Otto

UI (Browser / FiveWin)
↓ HTTP + JSON
Proxy (PHP oder direkt)
↓ HTTP + JSON
Harbour Microservice
↓ HTTP + JSON
Node.js SQL Service
↓ SQL
MariaDB

Code: Select all | Expand

FUNCTION ReadKunden()

   LOCAL cReq := hb_jsonEncode( { ;
      "sql"    => "SELECT id, name, ort FROM kunden LIMIT ?", ;
      "params" => { 10 } ;
   } )

   LOCAL cResp := HttpPostJson( ;
      "http://127.0.0.1:8081/sql", ;
      cReq ;
   )

   RETURN cResp

Code: Select all | Expand

FUNCTION HttpPostJson( cUrl, cJson )
   LOCAL oSock
   LOCAL cHost := "127.0.0.1"
   LOCAL nPort := 8081
   LOCAL cPath := "/sql"
   LOCAL cReq, cResp := ""
   LOCAL cBuf := Space(4096)
   LOCAL nRead

   // 1) Socket öffnen
   oSock := hb_socketOpen()
   IF oSock == NIL
      RETURN '{"success":false,"error":"socket_open_failed"}'
   ENDIF

   // 2) Verbinden
   IF hb_socketConnect( oSock, cHost, nPort ) != 0
      hb_socketClose( oSock )
      RETURN '{"success":false,"error":"connect_failed"}'
   ENDIF

   // 3) HTTP-Request bauen
   cReq := ;
      "POST " + cPath + " HTTP/1.1" + CRLF + ;
      "Host: " + cHost + CRLF + ;
      "Content-Type: application/json" + CRLF + ;
      "Content-Length: " + LTrim(Str(Len(cJson))) + CRLF + ;
      "Connection: close" + CRLF + CRLF + ;
      cJson

   // 4) Senden
   hb_socketSend( oSock, cReq )

   // 5) Antwort lesen
   DO WHILE ( nRead := hb_socketRecv( oSock, @cBuf, Len(cBuf) ) ) > 0
      cResp += Left( cBuf, nRead )
   ENDDO

   hb_socketClose( oSock )

   // 6) HTTP-Header abschneiden
   IF CRLF + CRLF $ cResp
      cResp := SubStr( cResp, At( CRLF + CRLF, cResp ) + 4 )
   ENDIF

RETURN cResp

// sql-service.js
const express = require("express");
const mysql = require("mysql2/promise");

const app = express();
app.use(express.json());

const pool = mysql.createPool({
host: "127.0.0.1",
user: "msvc",
password: "test123",
database: "testdb"
});

app.post("/sql", async (req, res) => {
const { sql, params } = req.body;

try {
const [rows] = await pool.execute(sql, params || []);
res.json({ success: true, rows });
} catch (e) {
res.json({ success: false, error: e.message });
}
});

app.listen(8081);

[Mulyadi]

I have been using it for fingerprint cases for a long time.
Reading data directly from the fingerprint machine using JSON and managing it from a microservice, as well as recording data into an Oracle database.

[richard-service]

Dear Otto
I've been following your brilliant work in building Mod Harbour, I've even tried to implement it on my systems, but I've never been able to configure the cloud service to work effectively.
I'm going to give a brief account of what works for me, since around 2003, when I decided it was time to abandon the DBF architecture.
As soon as the first DLLs that allowed Harbor/xHb access to SQL databases appeared, I dedicated a lot of time to re-writing/revising all my programming code, regarding data manipulation.
As I wanted my code to become unique for any database, I rewrote everything so that it was just the commands:
Insert...
Update...
Select...
In addition to the standard DDL commands in the ANSI SQL language, supported by all types of SQL databases.
In pure form, using standard SQL, without variations used for each specific manufacturer. To do this, I had to create some classes that would help me manipulate large records, processes, etc. Nowadays, my code no longer has any "use","seek", "do while","replace","append", etc. There is only pure standard SQL. Here in Brazil we have an expression that I don't know if it can be understood internationally: "You left the DBF but the DBF didn't leave you". I still see colleagues working with SQL but using xBase-type commands. I have systems running in the cloud for many years, without worrying about which operating system the database is on (Linux, Windows, etc.), Harbour just makes the access and all processing is done on the server. Each customer has the freedom to choose how they want to maintain their database, their provider, host, etc. I believe your approach to this topic is very important, I just wanted to share my experience.

Dear Eroni,

Same as you

[Otto]

Why WebSocket Between Harbour and Node.js – An Architectural Decision 1. Background

In recent weeks, while working on a proof of concept, I repeatedly came back to the same question:

How can Harbour communicate with SQL in a clean, future-proof and maintainable way,
without breaking existing business logic or introducing heavy new dependencies?

The goal was not to replace existing systems,
but to extend them in a controlled and understandable way.

--- 2. Initial Situation

The existing environment looks like this:

• Stable Harbour applications with long-running business logic
• A mix of DBF-based data and SQL databases (for new features or integrations)
• A clear wish to avoid SQL drivers inside the Harbour process
• No intention to turn Harbour into a web server
• No desire to depend on complex socket libraries or platform-specific DLLs

In short:

Harbour works well as it is.
The question was how to connect it to SQL without damaging that stability.

--- 3. Key Observations 3.1 SQL does not belong inside the Harbour process

This is not a philosophical decision, but a practical one:

• SQL drivers change
• Database versions change
• Security updates are frequent
• Debugging SQL problems is easier outside Harbour

SQL behaves like a service, not like embedded logic.

--- 3.2 Harbour should not become a web server

Harbour is very good at:

• Business logic
• Data orchestration
• Process control

But it should not be responsible for:

• HTTP routing
• REST APIs
• Web frameworks

Keeping Harbour focused increases long-term stability.

--- 3.3 Node.js fits well as an SQL service

Node.js provides:

• Mature SQL drivers
• Connection pooling
• Clear error reporting
• Fast development cycles

At the same time:

Node.js should not contain business logic.
Its role is purely technical: access to SQL.

--- 4. Why not HTTP / REST?

HTTP and REST were considered carefully.

They work well for:

• Web applications
• Public APIs
• Stateless request/response scenarios

However, SQL communication is different:

• Many small commands
• Repeated calls
• Long-running processes
• A natural need for a persistent connection

Using HTTP here would introduce unnecessary overhead and complexity.

--- 5. Why WebSocket?

WebSocket matches the problem much better:

• Persistent connection
• Bidirectional communication
• Low overhead
• Command-oriented, not page-oriented
• Easy to debug with plain JSON messages

SQL commands are commands, not web pages.

--- 6. The Final Decision

The resulting architecture is simple and clear:

• Harbour

  • remains the owner of business logic
  • acts as a WebSocket client

• Node.js

  • acts as a WebSocket server

  • encapsulates all SQL access

• Communication

  • WebSocket

  • JSON messages

  • clearly defined commands

  • no free SQL from the client side

Each component does what it does best.

--- 7. What this approach deliberately avoids

• No ORM
• No free SQL execution
• No SQL logic inside Harbour
• No REST layer
• No rewrite of existing applications

This is an extension, not a replacement.

--- 8. Conclusion

The choice of WebSocket is not a trend decision.
It is the result of separating responsibilities clearly.

• Harbour stays focused on business logic
• SQL becomes a dedicated service
• Node.js handles database complexity
• The connection remains simple and controlled

This approach allows Harbour systems to grow
without losing their original strengths.

[Eroni]

Dear Otto.
It seems that their approach will be the next natural step, involving security and modernity.
Regards.