Intensive reading "Sqorn Source code"

1 Introduction

Front-end Intensive reading the handwritten SQL compiler series describes how SQL is used to generate syntax trees, and some libraries are designed to generate SQL statements from the syntax tree.

In addition, there is a library that generates SQL based on the programming language. Sqorn is one such library.

One might ask, what is the point of generating SQL using a programming language? There is no syntax tree specification, nor is it as straightforward as writing SQL generic. Yes, there are pros and cons, these libraries do not follow the syntax tree, but quickly generate SQL with a simplified object model, resulting in increased code abstraction. and the code abstraction level is improved, the first advantage is easy to read, the second advantage is easy to operate.

Database is particularly easy to abstract into an object-oriented model, while the operation of the database statement-SQL is a structured query statement, can only describe a period of query, and the object-oriented model is suitable for describing a whole, the database of multiple tables in series.

For example, using Typeorm, we can a b describe two tables with two classes, and use the adorner to modify the ManyToMany a b two fields individually, building a many-to-many association , and this mapping to SQL The structure is three tables, there is an intermediate table ab , and the query involves the left JOIN operation, and in Typeorm, a find statement can be associated with the query of many-to-many relationship.

This is the value of generating SQL libraries using programming languages, so this week we analyze the source code of the Sqorn library and see what steps are required to generate SQL using the object model.

2 Overview

Let's take a look at Sqorn's grammar first.

const sq = require("sqorn-pg")();const Person = sq`person`,  Book = sq`book`;// SELECTconst children = await Person`age < ${13}`;// "select * from person where age < 13"// DELETEconst [deleted] = await Book.delete({ id: 7 })`title`;// "delete from book where id = 7 returning title"// INSERTawait Person.insert({ firstName: "Rob" });// "insert into person (first_name) values (‘Rob‘)"// UPDATEawait Person({ id: 23 }).set({ name: "Rob" });// "update person set name = ‘Rob‘ where id = 23"

The first line sqorn-pg tells us that Sqorn is split into packets of different classifications according to the SQL type, because the dialects supported by different databases are different, and Sqorn want to have the syntax to erase the differences between the databases.

Second, Sqorn is also using object-oriented thinking, the above example by sq`person` generating a person instance, actually corresponds to the person table, and then Person`age < ${13}` represents the query:select * from person where age < 13

Above is the use of ES6 template string function to implement a simplified where query function, Sqorn mainly use some functions to complete the SQL statement generation, such as where delete insert , and so on, more typical is the following Example:

sq.from`book`.return`distinct author`  .where({ genre: "Fantasy" })  .where({ language: "French" });// select distinct author from book// where language = ‘French‘ and genre = ‘Fantsy‘

So we read the Sqorn source code to explore how to make use of the above functions.

3 Intensive Reading

We start from four aspects, to understand how Sqorn's source code is organized, and how to meet the above features.

Dialect

In order to implement the various SQL dialects, you need to split the code into kernel Code and Extension code before implementing the functionality.

Kernel code is sqorn-sql to expand the code is sqorn-pg to expand the code itself as long as the implementation of the PG database itself special logic, coupled sqorn-sql with the core capabilities provided, can form a complete PG SQL generation function.

Implementing a database connection

Sqorn not only generate query statements, but also participate in database connection and operation, so an important function of dialect library is to do database connection. Sqorn uses pg This library to realize the function of connection pooling, disconnecting, querying, and transaction.

Overwrite interface functions

Kernel code wants to have the ability to expand, exposing some sqorn-xx of the interface to overwrite is very basic.

Context

In kernel code, the most important thing is the context property, because the human is accustomed to write the code step-by-step, and the resulting query statement is coherent, so this context object updateContext stores every piece of information:

{  name: ‘limit‘,  updateContext: (ctx, args) => {    ctx.lim = args  }}{  name: ‘where‘,  updateContext: (ctx, args) => {    ctx.whr.push(args)  }}

This is called, for example, Person.where({ name: ‘bob‘ }) because the ctx.whr.push({ name: ‘bob‘ }) where condition is an array, so it is used here push , but limit generally there is only one, so the context lim stores only one for the object.

Other operations, such as where delete insert with from converting to a similar updateContext , are eventually updated to the context.

Create Builder

Don't be too concerned with the sqorn-xx package name details below, the main purpose of this section is to show how to implement chained calls in the Demo, as to which module is not important (if you want to build a wheel, you need to study the author's naming method carefully).

sqorn-core builder sqorn-sql methods We can use this syntax by creating an object in code that will create a merge into it sq.where . And why can sq.where().limit() it be called continuously? You can look at the following code:

for (const method of methods) {  // add function call methods  builder[name] = function(...args) {    return this.create({ name, args, prev: this.method });  };}

This will where delete insert with from Wait for the methods merge into the builder object, and when it finishes executing, it this.create() builder completes the chain call function by returning a new one.

Generate query

The above three points to understand how to support the dialect, user code content is collected in the context, and we also created a chain call object for builder user-friendly invocation, then only the last step, is to generate query.

In order to use the context to generate query, we need to write the corresponding function for each key to do the processing, take an limit example:

export default ctx => {  if (!ctx.lim) return;  const txt = build(ctx, ctx.lim);  return txt && `limit ${txt}`;};

From the context.lim fetch limit configuration, combine limit xxx the strings and return to the right.

The

build function is a tool function, and if Ctx.lim is an array, it will be spliced with commas.

Most operations such as delete from having simple processing can be, but it where will be relatively complex, because the inside contains the condition sub-syntax, attention to use and stitching.

The last is the order and it needs to be determined in the code:

export default {  sql: query(sql),  select: query(wth, select, from, where, group, having, order, limit, offset),  delete: query(wth, del, where, returning),  insert: query(wth, insert, value, returning),  update: query(wth, update, set, where, returning)};

This means that a select statement wth, select, from, where, group, having, order, limit, offset invokes the handler function in the order in which it is returned, which is the final query.

4 Summary

Through the source analysis, you can see that making a library has three steps:

1. Create a Context store structured query information.
2. Create builder for user-chained writing code to populate the context at the same time.
3. Several SQL sub-processing functions are combined with several main statement functions to concatenate them together to generate a final query.

Finally, in the design of the SQL dialect, you can split the module into core, SQL, a number of dialect libraries, dialect library based on the core library to do the expansion can be.

5 more discussions

discussion address is: Intensive reading "Sqorn source" · Issue #103 · Dt-fe/weekly

If you would like to participate in the discussion, please click here, every week there are new themes, weekends or Monday release.

Original address: 1190000016419523

Intensive reading "Sqorn Source code"