Make TypeScript more “stylish” for developing backend services

TL; DR

  • Generate TS type files with Schemats based on the database table structure.
  • With TS-SQL-plugin directly in the code to write SQL statements, and directly connected to the database, with explain to test the correctness of SQL statements;
  • Using SKM_TS according to graphQL schema. GQL generate parameters of the TS type file;

Yeah, I’m pushing my two libraries.

The body of the

In my opinion, there are four main points to develop back-end services:

  1. Data structure: Comb business logic and refine data structure. The database DDL.
  2. Transaction Script: Crud-first design is a “Transaction Script” approach(quoted from 1). Usually it is a Model, but if you really understand the concept, you can see that it is just a series of functions, each of which completes a database transaction.
  3. Specific API: usually a transaction script corresponds to a modification API, and then adds the various query apis required by the client. For graphQL, mutation, query; Restful is more complex, such as get, POST, PUT, delete, and so on.
  4. (EXTRA) All kinds of timed or triggered tasks: timed closing of expired orders, timed generating of content recommendations, etc., can be done in the backend system, or can be done outside the system.

Then, in real development, it can be bottom-up (design the data structure first, then develop the API) or top-down (design the API first, then develop the implementation). In general it goes both ways; In addition, the actual business may also change frequently, so in this process, if javascript is used to develop the back end, there is no compile-time verification, it is very easy to add and delete fields, or when changing the field name, codes in other places forget to synchronize changes, and the final system is full of bugs. At this point, we need TypeScript.

However, TypeScript alone is not enough. Maintaining those types to always match frequently changing data structures and API interfaces becomes more and more difficult as the project progresses. It is common to find a bug after deployment, only to find that there is a place that still uses the old field name, which was not changed when the original.

If there are tools that help us generate types automatically, typescript can help us find more errors at compile time. So, we have:

  • Schemats generates ts files based on the database table structure.
  • Skm_ts generates ts type files of parameters according to graphQL schema. GQL;

The database is the external thing of the system, the interface is also the external side of the system, the two systems and the external bridge type, the rest of the system as long as the internal type is not wrong, most of the bugs will be found in the system compilation stage, the system will become very robust.

For example, schemats help us generate ts types for database table structures. If we can write some typesafe SQL Builder objects based on this type and use those objects to do SQL queries, That basically can ensure that their SQL statement is at least syntactically correct, there will not be a table name error, forget to change the field name of the situation.

For SQL Builders that don’t have typesafe, such as Knex and SQorn, table names can be miswritten and field names forgotten, but they are still very useful libraries.

There are several typesafe SQL Builders that you can evaluate for yourself: Type-SQL, @hediet/typed- SQL, and SQlizer concept evaluation

However, there is a simpler and more convenient way —-TypeScript Server Language Service Plugin—- ts-SQL-plugin.

This plugin can replace SQL parameters in our code with simulated parameter NULL at the stage of code editing, generate complete SQL statements, directly connect to the database explain this SQL, so as to determine whether the SQL statements are wrong. In addition, the code prompt can also be done in theory, but temporarily due to personal time and energy reasons, after all, one of the most important problems it needs to solve is the problem of rest assured.

The plugin also provides a small tool that allows you to write SQL statements directly in your code using ES6 template strings without worrying about SQL injection. There are several libraries like this (Slonik, SQUID).

This way of writing SQL statements directly provides the greatest flexibility. Compare this to:

  • Orms are the least flexible, requiring manual maintenance of those models, which means that there may be cases where the database table structure changes but the Model forgets to change
  • SQL Builder is flexible and generally acceptable to use, but there are some limitations to advanced SQL uses, such as window functions. It’s also possible to change the table structure but not the code, unless you use Schemats + typesafe SQL Builder, which I haven’t found yet. However, SQL Builder can have code hints, while writing SQL in template strings does not have code hints.
  • Template string writing SQL, flexibility is the most, plus ts-SQL-plugin is enough security. There are no code hints, though theoretically there could be.

conclusion

In fact, TL; DR is the summary. We use Schemats + TS-SQL-plugin + SKM_TS. The entire process from data structure to SQL to output API is typed and verified at compile time, so that we can refactor and iterate projects with ease. Finally, agile development is achieved.