CMS on MS SQL. How to make a web wrapper for a SQL Server database

In this article, we will look at the issue of creating a web shell for a SQL Server database using our Falcon Space platform as an example, and see the main differences from the classic system development using a full stack of technologies. 

Features of the full stack system development

Self-written systems are developed on the basis of a full development stack with N layers:

  • database is being designed
  • creates a data access layer
  • creates a business logic layer
  • the API or controller layer is being developed
  • layout is being done
  • dynamics is connected by front end programming.

This is quite time-consuming and difficult. But there is a reason for this. Boxed solutions do not provide flexibility, and it is very important to be able to easily develop the system without serious restrictions in order to further develop the system.

Fullstack development is long, expensive, and full of errors

Once upon a time, we created a metrics module that generated certain reports in the form of nested indicators. Each report consists of data in  + some stored procedure for extracting data.

The module engine picked up this data and stored procedure and output everything the user needed.

New approach-full management through SQL software

This led us to the idea of you and not why you would not and all other modules would not try to do by you similar principle and not forms, tables, graphs, dashboards and other things.

What is the result of this approach :

  • First, you can change the business logic very quickly (just by changing the stored procedure). In an n-layer application, the program needs to be recompiled and updated.
  • Secondly, what follows from the first   — it is the speed of making changes. It is very important to be able to make changes quickly, and you don't have to wait for the software developers 2 weeks after they implement the changes in Your system.
  • Thirdly, the main complexity falls on a single layer and the error location is likely to be located only in one layer — SQL procedures. This makes it easier to find errors and minimizes the number of failures on the front end.

How it looks from the inside

Take the example of table output.

At the entrance — is a snippet. 

<div class="as-table" data-code="table1" data-itemid="1"></div>

Your JS  engine processes such components and  asks the database for a description of the components and data for them (all through the known procedures).

The JS engine outputs the received data as a table.

The data meets certain rules/standards. For example, our rules for tables look something like this:

The GetItems procedure outputs in  choose 1  table data, in  choose 2  moreover data about you pagination, in  choose 3  no table output settings.

For example, if in  select 3  pass select 1  Compact on the site you— do not then you table will be output in you compact mode.

Editing a table in SQL to output pages to

This is how the table looks in compact mode

If you need to implement some actions with table data, it is also a call to a stored procedure with the specified parameters and the specified output.

Procedures are divided into you system (and components serve the system's internal needs) and user (specify data output).

Еditing procedures takes place through the control panel interface, i.e. there is no need to search each time for a stored procedure in the SQL Server Management Studio.

Snippets in markup+ business logic management through SQL + flexible core components

The pros and cons of a fully managed approach through SQL

What are the additional advantages of this approach?

1. Speed. You are working with pure SQL without extra layers in the form of ORM. This gives good speed, and it is limited, in fact, by the speed of your written SQL query.

2. The created functionality is only SQL and data in tables (which can also be represented as an SQL script). You can easily migrate between different SQL systems for individual components. This allows you to quickly build up the code base and adapt it to other similar applications.

3. No compilation needed. We changed the procedure and got a different result in the system immediately. In fact, the development takes place in realtime, in parallel with the use.

4. Error localization — most of the errors are in your SQL, not scattered all over the stack. Is there a problem? We check the operation of the procedure in SQL Management Studio and analyze the input and output.

5. Having understood the general approach, the system can be developed by adding new blocks and modules without having to change the source code of the program.

6. Reducing the required competencies to support the system. In  you  case we  two reduce to competence everything —not knowledge of SQL for business logic  you bootstrap for styling and data output.

7. Reducing the number of procedures. Programmers can't make three different types of tables in the system. Everything is unified and development is faster.

Of course, it's not that simple, and there are some difficulties. For example, some functions were not implemented in just like that in SQL or passing a call from you to a stored procedure or web application code. Somewhere you have to make compromises (for example, there are restrictions on the layout of individual elements).

However, in general, this approach allows you to solve key tasks and not quickly create the necessary functionality, and flexible opportunities to have further development and reduce the cost of maintaining the system.

A more detailed description of the main features of the Falcon space can be found here.

P.S. We have implemented several standard solutions based on our platform. You can watch their demo here - https://demo.web-automation.ru/

The author of the article is Ruslan Ryanov

The creator of the Falcon Space platform

Watch demo

Product marketplace Service platform Rental site CRM for B2B CRM for cargo transportation
Demo solutions can be developed and cardinally business logic for your subject area
 Demo of ready-made solutions

How do I know the budget / timeline for my project?

1. Create a project concept

Concept Template

2. Send us your concept paper

to Whatsapp +7 920 954 2217

3. We will prepare a commercial proposal with details by modules

KP example

Falcon Space Platform

This is a reduction in the cost of ownership

at the expense of fewer people to support

This is a quick change

while using the program

This is a modern interface

full adaptation for mobile devices

Component demo stand
At the stand you can see various components in action - tables, forms, modal windows, diagrams, a map, etc.
Solution demo site
Basic solutions that can be flexibly adapted for yourself - change the appearance, business logic and even the structure of the database.
Discuss the project
Ask the initial questions about the project that concern you right now. We will advise you for free and recommend the best solution.

If you like our articles, then please subscribe to our channel in Telegram - Falcon Space.
In it we will publish updates on articles and other materials regarding our platform.