Skip to main content

How to query a Database inside VPN

What This Pattern Does​

Allows services to send raw ElasticSearch/OpenSearch queries directly to the database through a standardized command handler, bypassing traditional API layers for complex queries.

How to Implement​

1. Add Raw Query Method to Your Service​

Add this method to your Elasticsearch service class:

async sendRawQuery(query: ElasticSearchQueryBody): Promise<unknown> {

const result = await this.search(query);

return result;

}

2. Create Command Handler​

Create a handler file (cmd.yourServiceRawQuery.execute.ts):

import { type Handler, type Message } from "@receeve-gmbh/sqs-handler";

import { MessageHandlerError } from "@receeve-gmbh/errors";

import type { ElasticSearchQueryBody } from "../entities/elasticsearch";

interface RawQueryMessage extends Message {

payload: ElasticSearchQueryBody;

}

export default class CommandYourServiceRawQueryHandler

implements Handler<unknown, RawQueryMessage>

{

public constructor(private readonly yourService: YourServiceClass) {}

public async processMessage(incomingMessage: RawQueryMessage): Promise<unknown> {

const { attributes, payload } = incomingMessage;

try {

return await this.yourService.sendRawQuery(payload);

} catch (innerError) {

throw new MessageHandlerError({ innerError, attributes });

}

}

}

3. Register Handler​

Add the handler to your message routing configuration.

How to Use​

Basic Query Example​

{

"query": {

"bool": {

"must": [

{ "term": { "clientId": "your-client-id" } },

{ "range": { "totalAmount": { "gte": 1000 } } }

]

}

},

"size": 100

}

Aggregation Example​

{

"query": {

"bool": {

"must": [{ "term": { "clientId": "your-client-id" } }]

}

},

"size": 0,

"aggs": {

"amounts_by_currency": {

"terms": { "field": "currency" }

}

}

}

When to Use​

  • ✅ Complex analytical queries
  • ✅ Custom aggregations
  • ✅ Debugging data issues
  • ✅ Advanced filtering beyond standard APIs

When NOT to Use​

  • ❌ Simple CRUD operations
  • ❌ User-facing features
  • ❌ Data modifications (read-only pattern)

Important Rules​

  1. Always include clientId filtering for data isolation
  2. **Use size: 0** for aggregation-only queries
  3. Add proper error handling in your handler
  4. Log queries for debugging and monitoring

That's it! This pattern gives you direct access to ElasticSearch capabilities while maintaining service architecture standards.