Custom SQL: when standard queries fall short, hand complex queries to the database

Most business systems run fine on standard dataset APIs early on. But the moment you get into reports, analytics, or cross-table queries, problems appear immediately: the frontend fires multiple requests, the backend grows a pile of assembly code — all just to produce one summary table or one leaderboard.

Custom SQL targets exactly this kind of problem: standard queries aren't enough, but it's not worth rewriting a backend for it. It lets developers push complex queries down to the database, then manage and invoke them centrally through the Lovrabet platform.

What is custom SQL

Custom SQL is a data query extension capability provided by Lovrabet. You save a piece of SQL on the platform and call it on demand from the frontend, the server side, or a Backend Function.

It doesn't replace the standard APIs — it covers the part they aren't good at, such as:

• Cross-table join queries
• Aggregate statistics and grouped analysis
• Multi-condition combined filtering
• Leaderboards, trend charts, and business reports

In other words, standard APIs handle routine data reads and writes, while custom SQL handles the more complex, analytics-oriented queries.

What problems does it solve

What actually hurts in an enterprise is rarely "fetch one record" — it's "get a clear answer, in one pass, from business data spread across many tables."

Common scenarios include:

• An order list that needs customer names, product names, and payment status alongside the orders themselves
• Sales reports that aggregate by time, region, and product
• Operations analytics that flexibly combine date ranges, statuses, keywords, and other conditions

With standard APIs alone, these requirements usually mean multiple requests, frontend assembly, and repeated computation. As the logic gets complex, the code balloons and query performance degrades.

Custom SQL takes a more direct route: let the database compute the result in one pass, and let the application layer simply display and consume it.

Why you need it

In terms of product capability, standard APIs already cover plenty of day-to-day scenarios. But the data results that really matter in enterprise systems often come from complex queries — joins, aggregation, statistics, filtering. That's exactly why custom SQL is the key addition that takes many projects from "workable" to "great to work with."

Its value comes down to four things:

• More direct queries: data that used to require multiple requests and manual assembly comes back in one call
• Better-fitting performance: aggregation and joins run in the database layer, usually more efficient than assembling in the frontend or business layer
• Easier reuse: one piece of SQL, managed centrally on the platform, reused across pages and interfaces
• Clearer boundaries: standard APIs handle generic reads and writes, custom SQL handles complex queries — the responsibilities never blur

So custom SQL isn't "one more way to write things" — it's the right tool for complex query scenarios.

A typical demo

Take a sales leaderboard. The business wants the top 10 best-selling products within a given date range.

With ordinary APIs, you'd typically query orders, then order items, then product info, and finally do the aggregation and sorting yourself in the application layer.
With custom SQL, you hand the whole thing to the database:

SELECT
  p.product_name,
  SUM(oi.quantity) AS totalQuantity,
  SUM(oi.subtotal) AS totalAmount
FROM dataset_order_items oi
JOIN dataset_orders o ON oi.order_id = o.id
JOIN dataset_products p ON oi.product_id = p.id
WHERE o.order_date >= #{startDate}
  AND o.order_date <= #{endDate}
GROUP BY p.product_name
ORDER BY totalAmount DESC
LIMIT 10

The frontend or server side just invokes it:

const result = await client.sql.execute({
  sqlCode: "sales-ranking",
  params: {
    startDate: "2024-01-01",
    endDate: "2024-12-31",
  },
});

if (result.execSuccess && result.execResult) {
  console.log(result.execResult);
}

The page then receives a leaderboard result that's ready to display — not a pile of raw data that still needs processing.

Closing thoughts

Most enterprises don't lack data — they lack an efficient way to extract complex results from it. That's what Custom SQL is about: it frees this capability from tedious data assembly, letting developers use the SQL they already know to query out the results with real business value.

This article only answers "what it is, what it solves, and why you need it." For the specifics — how to create one, how to write parameters, dynamic SQL, invocation details, and troubleshooting — continue with the follow-up articles.

Keep reading

• Custom SQL tutorial - everything from creating to invoking
• SQL API reference - method signatures, parameters, and return values
• Report page: sales statistics with SQL - a real business case
• Syntax sugar: sqlSafe - simplified error handling