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
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/
How do I know the budget / timeline for my project?
1. Create a project concept
2. Send us your concept paper
3. We will prepare a commercial proposal with details by modules
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
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.