If you're not living under a rock, you should have heard the recent rise of a new understanding of the function as a service. In the open source community, Alex Ellis's [Openfaas] (Https://github.com/openfaas/faas) project received high attention and [Amazon Lambda announces support for the Go language] (https:// aws.amazon.com/blogs/compute/announcing-go-support-for-aws-lambda/). These systems allow you to scale up on demand and invoke your CLI program in a way that is called by the API. # # # Lambda/faas behind the motives let us describe--the entire "No server" movement is the marketing behavior of the cloud platform, such as AWS, which allows you to transfer any server management to them, such as, ideally, a small portion of your revenue system. In specific terms, this means that AWS or other similar solutions host your app, run your app, and automatically maintain its hardware size on demand in their data center. But you might have known that already. But, do you know that this capability has long existed in CGI? Refer to Wikipedia, 1993, and in 1997 formally defined in RFC specification. All the old things have come back again. The purpose of the CGI (Common Gateway Interface) is to:> compute that the Universal Gateway Interface (CGI) provides a standard protocol for the Web server to execute the application as if it were a console application (also known as a command-line interface program) on a dynamic Web service. > > Source: [Wikipedia] (Https://en.wikipedia.org/wiki/Common_Gateway_Interface) > for the Go language, the simplest FASS/CGI service program requires only 10 The code around the line can be implemented. The Go language standard library already contains [net/http/cgi] (https://golang.org/pkg/net/http/cgi/) to complete all the difficult work. Implementing a PHP CGI requires only the following few lines: "' Gofunc Phphandler () func (w http. Responsewriter, R *http. Request) {return func (w http. Responsewriter, R *http. Request) {handler: = new (CGI.handler) Handler. Dir = "/var/www/" handler. Path = handler. Dir + "API" args: = []string{r.requesturi}handler. Args = Append (handler. args, args ...) Fmt. Printf ("%v", handler. Args) handler. Servehttp (W, R)}} ' calls this also very simple: ' ' gohttp. Handlefunc ("/api/", Phphandler ()) ' Of course, I don't know why you would use it. Because at the time before dawn, CGI ran into a performance problem, the biggest problem being the pressure it had on the system. For each request, an ' OS ' is initiated. Exec ' Call, and this is definitely not a system-friendly call. In fact, if you are doing any kind of service like real-time traffic, you would probably expect the number of calls to be 0. That's why CGI evolved to FastCGI. > FastCGI is a Common Gateway Interface (CGI) early change; The main purpose of FastCGI is to reduce the excessive connection between Web servers and CGI programs, allowing the server to process more Web requests at once > > sources: [Wikipedia] (https:// EN.WIKIPEDIA.ORG/WIKI/FASTCGI) I don't want to implement a FastCGI program now (there is also a [net/http/fcgi] in the standard library (https://golang.org/pkg/net/http/fcgi /), but I want to demonstrate the performance trap that this implementation will bring. Of course, when you run your program on AWS, you probably don't care much about it because they have the ability to scale your hardware according to your traffic. # # CGI Solutions If I have learned something in the past few years, it must be that most services are data-driven. This means that there must be some kind of database that holds vital data. According to a [Quora answer] (https://www.quora.com/Which-database-system-s-does-Twitter-use), Twitter uses at least 8 different kinds of databases, from MySQL, Cassandra and Redis, to other more complex databases. In fact, most of my work is about reading employee data from a database and then turning it into a JSON format and using RESTCalls are provided, and these queries are usually not implemented with just one SQL statement, which is, of course, possible in many cases. Well, why don't we write something that won't have an ' OS. Exec ' calls the cost of SQL scripts to implement some functionality, rather than using CGI programs to implement them? The challenge was accepted. # # with SQL as an API I don't want to turn this into a monster, although with SQL as the API is capable of becoming, but I do want to implement a remotely available version. I want to implement API calls by creating a. sql file on disk, and I want this API to invoke any parameter in the HTTP request. This means that we can filter the result set by passing parameters to the API. I chose MySQL and SQLX to accomplish this task. Recently I wrote some chat bots for twitch,slack,yotube,discord, and it looks like I'm going to write a teleganm version soon. In fact, their goal is to make similar connections to various channels, log messages, add aggregate statistics, and respond to commands or questions. For front-end Web sites written in Vue.js, we need to pass some data to them through the API. While not all APIs can be implemented with SQL, a large portion is available. For example: 1. List all channels 2. List channels by Channel ID These two calls are relatively similar and easy to implement, and I created two files specifically to provide this information: ' Api/channels.sql ' (corresponding to '/api/channels ') ' SELECT * from channels ' ' Api/channelbyid.sql ' (corresponds to '/api/channelbyid?id= ... ') ' SELECT * from channels where id=:id ' ' as you can see, use SQL queries to implement a new The API does not require much work. I try to design a system so that once you create ' api/name.sql ' You can access it immediately via ' api/name '. The parameters of all Http requests are encapsulated in ' map[string]interface{} ' and passed as a binding variable to the SQL query statement. The SQL driver is responsible for handling these parameters. I also designed the formatting of the error message. If you are unable to connect to your database, or if an API corresponds to a. sql file that does not exist, there will be an error message that reads: "' json{" error: {"Message": "Open api/messages.sql:no such file or directory"}} "uses the URL parameter in the SQL query to get the requested parameter in the go language, just the ' *url ' in the Request object. The Query () function is called on the URL ' struct body. This function returns the ' URL. Values ' object, which is an alias of type ' map[string][]string '. We need to convert this object and pass it to the SQLX statement. We need to create a ' map[string]interface{} '. Because we need to call the SQLX function at query time to accept parameters of this format ([SQLX. Namedstmt.queryx] (Https://godoc.org/github.com/jmoiron/sqlx#NamedStmt.Queryx)). Let's convert them and start the query: "' goparams: = Make (map[string]interface{}) Urlquery: = R.url. Query () for name, param: = range urlquery {Params[name] = param[0]}stmt, err: = db. Preparenamed (String (query)) If err! = Nil {return err}rows, err: = stmt. Queryx (params) if err! = Nil {return err} ' "We have not dealt with the ' rows ' variable, we can traverse it to get information for each row. We need to add them to a slice and encapsulate them in JSON in the final steps of the API. "' Gofor rows. Next () {row: = Make (map[string]interface{}) Err = rows. Mapscan (ROW) if err! = Nil {return err} ' ' Here's where things get interesting. The values contained in each row need to be translated into something that the JSON encoder can understand. Because the underlying type is ' []uint8 ', we first need to convert them to strings. If we do not do this, the JSON for this structure will automatically use Base64 encoding. Since the feedback of the query can be expressed as ' map[string]string ', and ' uint8 ' is of type ' byte 'Alias, we chose to use this conversion: "' gorowstrings: = Make (map[string]string) for name, val: = range Row {switch Tval: = val. (type) {case []uint 8:ba: = Make ([]byte, Len (tval)) for I, V: = Range Tval {ba[i] = byte (v)}rowstrings[name] = string (BA) Default:return FMT. Errorf ("Unknown column type%s% #v", name, spew. Sdump (val)}} "" Here we have a ' rowstrings ' object that represents each returned SQL line, which can be easily encoded into JSON. All we need to do is add them to a return result, encode it and return the encoded value. The complete (relatively short) code can be obtained here in [Titpetric/sql-as-a-service] (HTTPS://GITHUB.COM/TITPETRIC/SQL-AS-AN-API). # # # Use Note Although this approach has unique benefits as a database implementation of the API layer, there are many user scenarios to consider in order to make it suitable for wider invocation. For example: # # # to sort the results of this method cannot actually be sorted. Because we can't bind a query parameter to the ' order by ' argument because SQL doesn't allow it. The cleaning of the data is completely impossible, and you can't even use a function as a workaround. Such code is completely infeasible: ' Order By:column IF (: order = ' ASC ', ' ASC ', ' desc ') '. # # # parameter to create some sort of paging rule, MySQL provides ' LIMIT $offset, $length ' clauses. Although you can use these as query parameters, but here we can't bind them, or find a way to pass their values, the result we try is to get a similar error return message: "Undefined variable ..." # # # Multiple SQL queries Typically, we execute multiple SQL Statement to return a single result set. However, this needs to be configured on the database to be enabled, and this feature is usually forbidden, one of the main reasons is to prevent SQL injection attacks. In an ideal world, a statement like the following should be executable: ' ' Set@start =:start;set @length =:length;select * from channels ORDER BY id DESC limit @start, @length; "Unfortunately, it doesn't work in reality. The above statement cannot even be executed remotely. If you try to execute these statements in a MySQL client, it will error. These variables are defined, but they can neither be used in the ORDER BY clause nor in the limit clause. So, can't you split the page? There's one of the strangest curves I've ever seen, Twitch and Tumblr's APIs have a special feature that allows you to pass a value of ' since ' or ' previousid '. They can work like this in SQL: ' ' select * from messages where ID <:p REVIOUSID order BY id desc limit 0, 20 "' This allows you to follow a predetermined paging size to traverse a a table. This method requires the table to have a sortable primary key (such as the ID produced by the sonyflake/snowflake algorithm) or another sequential column that can be used for sorting. # # # Function SQL database is not a dumb beast, in fact they are very powerful, one of the most powerful sources is the ability to create functions, or processes. With this you can implement a complex business logic. It is easy for a traditional DBA, or a database programmer, to read or create a SQL function on its own. This is a good solution when the client cannot execute multiple SQL statement restrictions at once, but it requires that you implement all the business logic in the database. For most of the programmers I know, it's beyond their comfort zone, basically dealing with a whole bunch of IF statements. # # Conclusion If you really have some simple queries that can get the results you need through them, you can benefit greatly from this approach to SQL as an API, and in addition to saving overhead, you can provide a way to add system functionality to programs that are unfamiliar with the Go language but are familiar with SQL. As the need for an API to return specific results increases gradually, we need a scripting language that can achieve or exceed [PL/SQL] (Https://en.wikipedia.org/wiki/PL/SQL) restrictions, And there are different implementations in different relational databases. Or, you can always hook up a JavaScript virtual machine in your app as if [Dop251/goja ] (Https://github.com/dop251/goja) This way, then let your team of front-desk programmers come up with an attempt he/she may never forget. Now it also works with the LUA virtual machine implemented by the pure Go language, if you want some kind of "runtime" that is smaller than the entire ES5.1. # # # If I can meet you here ... If you could buy a book of mine, that would be great:-[API Foundations in Go] (https://leanpub.com/api-foundations)-[Factor Apps with Docker and Go] (http S://leanpub.com/12fa-docker-golang)-[The SaaS Handbook (Creative)] (Https://leanpub.com/saas-handbook) I promise you can learn more from it. Buying a book can support me in writing more similar articles. Say thank you for buying my book. If you want to book my consultant/writer service welcome [send me an email] (black@scene-si.org), I am good at Apis,go,docker,vuejs and system expansion, [and many other things] (https://scene-si.org/ About)
via:https://scene-si.org/2018/02/07/sql-as-an-api/
Author: Tit Petric Translator: Moodwu proofreading: polaris1119
This article by GCTT original compilation, go language Chinese network honor launches
This article was originally translated by GCTT and the Go Language Chinese network. Also want to join the ranks of translators, for open source to do some of their own contribution? Welcome to join Gctt!
Translation work and translations are published only for the purpose of learning and communication, translation work in accordance with the provisions of the CC-BY-NC-SA agreement, if our work has violated your interests, please contact us promptly.
Welcome to the CC-BY-NC-SA agreement, please mark and keep the original/translation link and author/translator information in the text.
The article only represents the author's knowledge and views, if there are different points of view, please line up downstairs to spit groove
288 Reads
