← All posts

The database is the application

· 4 min read · Rick Hoek
architecturet-sql

Most business software spends its life moving data out of a database and then putting it back. A query becomes a DTO, the DTO becomes JSON, the JSON becomes a component, the component becomes a form, and the form becomes an UPDATE — with validation rules written twice on the way, once in the browser and once on the server, and a good chance they disagree.

TSQL.APP starts from a different premise: the data already lives in a database that can hold logic, enforce permissions and run transactions. Build the application there.

What that looks like

Here is a complete, working Hello World — a title, an input, a button, validation and feedback. All of it is T-SQL:

sql
DECLARE @Name NVARCHAR(MAX);
DECLARE @GreetButton NVARCHAR(MAX);

EXEC sp_api_modal_get_value @name = N'@Name',        @value = @Name       OUT;
EXEC sp_api_modal_get_value @name = N'@GreetButton', @value = @GreetButton OUT;

EXEC sp_api_modal_text  @text = N'Hello World Example';

EXEC sp_api_modal_input @name        = N'@Name',
                        @value       = @Name OUT,
                        @placeholder = N'Enter your name';

EXEC sp_api_modal_button @name     = N'@GreetButton',
                         @value    = N'Greet',
                         @valueout = @GreetButton OUT,
                         @class    = N'btn-primary';

IF @GreetButton IS NOT NULL
BEGIN
    IF LEN(TRIM(ISNULL(@Name, N''))) = 0
        EXEC sp_api_toast @text = N'Please enter your name', @class = N'btn-warning';
    ELSE
    BEGIN
        DECLARE @Greeting NVARCHAR(MAX) = CONCAT(N'Hello, ', @Name, N'!');
        EXEC sp_api_toast @text = @Greeting, @class = N'btn-success';
    END
END

There is no HTML here, and no JavaScript. There is no controller, no route, no view model, no build step. The script declares what it wants on screen and reads back what the user did. That is the whole interface.

Two databases, and why your data stays put

The obvious objection is that nobody wants a framework rewriting their production schema. So TSQL.APP does not touch it.

An application is two databases. Your existing one — call it app — keeps your tables exactly as they are. A second one, app_proj, holds the framework: the sp_api_* procedures and the api_* metadata tables that describe cards, fields, buttons and permissions. It reaches your data through synonyms.

Business data on one side, presentation and configuration on the other, and a clean line between them. Point the framework at a database that has been running for fifteen years and it will render it without asking you to migrate anything.

Everything else is a messenger

Around the database sit three small things, and none of them is allowed an opinion.

The .NET API flattens every HTTP request into a single four-column table and hands it to one stored procedure. It contains no domain knowledge at all — you can read the whole thing in an afternoon and learn nothing about the business it serves, which is the point.

The React client renders whatever named result sets come back. It does not know what a sales order is. It knows how to draw a list, a detail view and a modal, and it is told which to draw.

The dispatcher performs the IO that SQL Server cannot do for itself: email, HTTP fetches, Excel, PDF, SFTP, printing. Work is queued through Service Broker and picked up asynchronously.

So when someone asks how a feature works, the answer is always a stored procedure or an action script. Never C#, never JavaScript. There is exactly one place to look.

The part that surprises people

Here is the mechanism that makes a multi-step wizard work, and it is not what almost anyone guesses.

An action script has no resume point. It is re-executed from the top on every round trip. There are no coroutines, no saved instruction pointer, no server-side run state, and no step counter anywhere.

The answers the user has given so far travel as a JSON bag, out through the browser and back again with the next request. Each collector — an input, a button, a picker — prefers the posted answer over its own default. Before the click, @GreetButton is NULL; after it, it holds the caption, and the IF below it fires.

Which means "step 3 of 5" is never stored anywhere. It is emergent: step N is simply the first question whose name is not yet in the bag.

Once that clicks, a lot of the framework stops looking clever and starts looking inevitable. It is also why a script must be safe to run repeatedly from the top — easily the most important thing to internalise before writing a complicated one.

Where this goes

This blog is where the details get written down properly: how cards turn metadata into generated SQL, what the transaction and severity channel actually guarantee, the sharp edges worth knowing about before you find them yourself, and what we learn running all of this in production for real customers.

If you would rather see it than read about it, there is documentation and a programming reference online, and you can always book a demo.