Why you should not use a two-tier architecture in developing client-server applications

Thank my friend for telling me about his new laboratory work, at the university, in the discipline related to databases. Otherwise, the article simply would not have seen the light.

The essence of this lab was to write the nth number of validations, stored procedures and triggers in MS SQL. All triggers, validations and stored procedures are very simple, but this is only the first laboratory and it will be worse, there will be multi-storey sql queries and stored procedures of a very large amount.

It seems to me to spend time studying such things in 2018 is not very practical, namely that no one uses just a bare database and immediately output to the client. All applications have business logic and are constantly scaling and changing. And tie everything to the database is not correct as there may be a number of problems that cannot be solved using only the DBMS as the server side.

Examples of such problems include:
')

• The need to use another database
• The need to output data in several forms for different client sides (JSON, HTML, XML, etc.)
• Adding and modifying tables, columns or changing their names, which will entail a change in the whole associated with each table or column
• The need to receive data in the same way from other databases installed on the server, or even to receive data from other services

Consider a typical example of what we need to send an email

What it looks like if you send a message via MS SQL

DECLARE @tableHTML  NVARCHAR(MAX) ;  

SET @tableHTML =  
    N'<H1>Work Order Report</H1>' +  
    N'<table border="1">' +  
    N'<tr><th>Work Order ID</th><th>Product ID</th>' +  
    N'<th>Name</th><th>Order Qty</th><th>Due Date</th>' +  
    N'<th>Expected Revenue</th></tr>' +  
    CAST ( ( SELECT td = wo.WorkOrderID,       '',  
                    td = p.ProductID, '',  
                    td = p.Name, '',  
                    td = wo.OrderQty, '',  
                    td = wo.DueDate, '',  
                    td = (p.ListPrice - p.StandardCost) * wo.OrderQty  
              FROM AdventureWorks.Production.WorkOrder as wo  
              JOIN AdventureWorks.Production.Product AS p  
              ON wo.ProductID = p.ProductID  
              WHERE DueDate > '2004-04-30'  
                AND DATEDIFF(dd, '2004-04-30', DueDate) < 2   
              ORDER BY DueDate ASC,  
                       (p.ListPrice - p.StandardCost) * wo.OrderQty DESC  
              FOR XML PATH('tr'), TYPE   
    ) AS NVARCHAR(MAX) ) +  
    N'</table>' ;  

EXEC msdb.dbo.sp_send_dbmail @recipients='yourfriend@Adventure-Works.com',  
    @subject = 'Work Order List',  
    @body = @tableHTML,  
    @body_format = 'HTML' ;  

, ?

. , , . , , , . , html . .

, . , .

, . . websockets, , , websockets , .

:

, .

, . - , .

SQL. -- , , .

• — , , .
• — .
• INSERT — , .
• — NodeJS .
• — , .

, .

, , , .

, , . SQL . . , .

, , .

UPD 13.02.2018
speshuric