Share
Embed
pREST - A RESTful API from any PostgreSQL database
A RESTful API from any PostgreSQL database
Felipe Oliveira
December 15, 2016
Transcript
40 slides
-
12/16/2016 pREST http://localhost:3999/pREST/prest.slide#1 1/40 pREST A RESTful API from any
PostgreSQL database 15 December 2016 Felipe Paes de Oliveira Go Developer at Nuveo -
12/16/2016 pREST http://localhost:3999/pREST/prest.slide#1 2/40 Sobre mim? Estudo Go a 2
anos Trabalho com Go desde Setembro de 2016 Antes trabalhava com Java na Caelum Desde Novembro de 2016 na Nuveo -
12/16/2016 pREST http://localhost:3999/pREST/prest.slide#1 3/40 Como surgiu o pREST? Nuveo v2
Remodelando para uma estrutura menos rigida Nuveo v1 em Go e MongoDB Nuveo v2 Go e PostgreSQL com suporte a JSONB Reescrever todas as queries de todos os sistemas postgREST (https://github.com/begri s/postgrest, Haskell), di cil de manter em produção -
12/16/2016 pREST http://localhost:3999/pREST/prest.slide#1 10/40 Como saber todas essas queries? Todas
elas disponiveis no client do PostgreSQL psql -E -
12/16/2016 pREST http://localhost:3999/pREST/prest.slide#1 12/40 SELECT (rows) GET /{DATABASE}/{SCHEMA}/{TABLE} SELECT *
FROM {DATABASE}.{SCHEMA}.{TABLE} -
12/16/2016 pREST http://localhost:3999/pREST/prest.slide#1 13/40 SELECT - Problemas Tabelas com estruturas
di rentes Montar JSON para essas estruturas -
12/16/2016 pREST http://localhost:3999/pREST/prest.slide#1 14/40 Solução - Mapas (map[string]interface{}) count :=
len(columns) tableData := make([]map[string]interface{}, 0) values := make([]interface{}, count) valuePtrs := make([]interface{}, count) for rows.Next() { for i := 0; i < count; i++ { valuePtrs[i] = &values[i] } rows.Scan(valuePtrs...) entry := make(map[string]interface{}) for i, col := range columns { var v interface{} val := values[i] b, ok := val.([]byte) if ok { v = string(b) } else { v = val } entry[col] = v } tableData = append(tableData, entry) } jsonData, err = json.Marshal(tableData) -
12/16/2016 pREST http://localhost:3999/pREST/prest.slide#1 15/40 Filtros (WHERE) - Premissas Receber ltro
por query params Dar suporte a ltros por campos do tipo JSONB -
12/16/2016 pREST http://localhost:3999/pREST/prest.slide#1 16/40 Filtros (WHERE) - Problemas Sitaxe diferente
entre campos comuns e JSONB = e ->> respectivamente O PostgreSQL usa $1, $2, $3... e não ? -
12/16/2016 pREST http://localhost:3999/pREST/prest.slide#1 17/40 Solução Sintaxes difrentes ?FIELD=VALUE (padrão) ?FIELD->>JSONFIELD:jsonb=VALUE
(jsonb) 2 []string{}, para as chaves e outro para os valores -
12/16/2016 pREST http://localhost:3999/pREST/prest.slide#1 18/40 Código func WhereByRequest(r *http.Request, initialPlaceholderID int)
(whereSyntax string, values []interface{}, err whereKey := []string{} whereValues := []string{} u, _ := url.Parse(r.URL.String()) pid := initialPlaceholderID for key, val := range u.Query() { if !strings.HasPrefix(key, "_") { keyInfo := strings.Split(key, ":") if len(keyInfo) > 1 { switch keyInfo[1] { case "jsonb": jsonField := strings.Split(keyInfo[0], "->>") whereKey = append(whereKey, fmt.Sprintf("%s->>'%s'=$%d", jsonField[0], jsonField[1], pid) whereValues = append(whereValues, val[0]) default: whereKey = append(whereKey, fmt.Sprintf("%s=$%d", keyInfo[0], pid)) whereValues = append(whereValues, val[0]) } continue } whereKey = append(whereKey, fmt.Sprintf("%s=$%d", key, pid)) whereValues = append(whereValues, val[0]) pid++ } } for i := 0; i < len(whereKey); i++ { if whereSyntax == "" { -
12/16/2016 pREST http://localhost:3999/pREST/prest.slide#1 19/40 whereSyntax += whereKey[i] } else {
whereSyntax += " AND " + whereKey[i] } values = append(values, whereValues[i]) } return } -
12/16/2016 pREST http://localhost:3999/pREST/prest.slide#1 20/40 Filtros - (Paginação) func PaginateIfPossible(r *http.Request)
(paginatedQuery string, err error) { u, _ := url.Parse(r.URL.String()) values := u.Query() if _, ok := values[pageNumberKey]; !ok { paginatedQuery = "" return } pageNumber, err := strconv.Atoi(values[pageNumberKey][0]) if err != nil { return } pageSize := defaultPageSize if size, ok := values[pageSizeKey]; ok { pageSize, err = strconv.Atoi(size[0]) if err != nil { return } } paginatedQuery = fmt.Sprintf("LIMIT %d OFFSET(%d - 1) * %d", pageSize, pageNumber, pageSize) return } -
12/16/2016 pREST http://localhost:3999/pREST/prest.slide#1 21/40 DELETE (rows) DELETE /{DATABASE}/{SCHEMA}/{TABLE} DELETE FROM
{DATABASE}.{SCHEMA}.{TABLE} Mesmo comportamento do SELECT para os ltros -
12/16/2016 pREST http://localhost:3999/pREST/prest.slide#1 22/40 Código func Delete(database, schema, table, where
string, whereValues []interface{}) (jsonData []byte, err error) { var result sql.Result var rowsAffected int64 sql := fmt.Sprintf("DELETE FROM %s.%s.%s", database, schema, table) if where != "" { sql = fmt.Sprint( sql, " WHERE ", where) } db := Conn() result, err = db.Exec(sql, whereValues...) if err != nil { return } rowsAffected, err = result.RowsAffected() if err != nil { return } data := make(map[string]interface{}) data["rows_affected"] = rowsAffected jsonData, err = json.Marshal(data) return } -
12/16/2016 pREST http://localhost:3999/pREST/prest.slide#1 25/40 Solução type Request struct { Data
map[string]string `json:"data"` } { "data": { "FIELD1": "string value", "FIELD2": 1234567890 } } -
12/16/2016 pREST http://localhost:3999/pREST/prest.slide#1 26/40 Código func Insert(database, schema, table string,
body api.Request) (jsonData []byte, err error) { var result sql.Result var rowsAffected int64 fields := make([]string, 0) values := make([]string, 0) for key, value := range body.Data { fields = append(fields, key) values = append(values, value) } colsName := strings.Join(fields, ", ") colPlaceholder := "" for i := 1; i < len(values)+1; i++ { if colPlaceholder != "" { colPlaceholder += "," } colPlaceholder += fmt.Sprintf("$%d", i) } sql := fmt.Sprintf("INSERT INTO %s.%s.%s (%s) VALUES (%s)", database, schema, table, colsName, colPlaceho db := Conn() stmt, err := db.Prepare(sql) if err != nil { return } valuesAux := make([]interface{}, 0, len(values)) for i := 0; i < len(values); i++ { valuesAux = append(valuesAux, values[i]) } -
12/16/2016 pREST http://localhost:3999/pREST/prest.slide#1 27/40 result, err = stmt.Exec(valuesAux...) if err
!= nil { return } rowsAffected, err = result.RowsAffected() if err != nil { return } data := make(map[string]interface{}) data["rows_affected"] = rowsAffected jsonData, err = json.Marshal(data) return } -
12/16/2016 pREST http://localhost:3999/pREST/prest.slide#1 28/40 UPDATE (rows) PUT ou PATCH /{DATABASE}/{SCHEMA}/{TABLE}
UPDATE {DATABASE}.{SCHEMA}.{TABLE} SET -
12/16/2016 pREST http://localhost:3999/pREST/prest.slide#1 29/40 UPDATE - Problemas Os mesmos enfrentados
nos SECLECTS e INSERTS Já estavam resolvidos :) -
12/16/2016 pREST http://localhost:3999/pREST/prest.slide#1 30/40 Código func Update(database, schema, table, where
string, whereValues []interface{}, body api.Request) (jsonData []b var result sql.Result var rowsAffected int64 fields := []string{} values := make([]interface{}, 0) pid := len(whereValues) + 1 // placeholder id for key, value := range body.Data { fields = append(fields, fmt.Sprintf("%s=$%d", key, pid)) values = append(values, value) pid++ } setSyntax := strings.Join(fields, ", ") sql := fmt.Sprintf("UPDATE %s.%s.%s SET %s", database, schema, table, setSyntax) if where != "" { sql = fmt.Sprint( sql, " WHERE ", where) values = append(values, whereValues...) } db := Conn() stmt, err := db.Prepare(sql) if err != nil { return } valuesAux := make([]interface{}, 0, len(values)) for i := 0; i < len(values); i++ { -
12/16/2016 pREST http://localhost:3999/pREST/prest.slide#1 31/40 valuesAux = append(valuesAux, values[i]) } result,
err = stmt.Exec(valuesAux...) if err != nil { return } rowsAffected, err = result.RowsAffected() if err != nil { return } data := make(map[string]interface{}) data["rows_affected"] = rowsAffected jsonData, err = json.Marshal(data) return -
12/16/2016 pREST http://localhost:3999/pREST/prest.slide#1 33/40 Testando funções simples func TestPaginateIfPossible(t *testing.T)
{ Convey("Paginate if possible", t, func() { r, err := http.NewRequest("GET", "/databases?dbname=prest&test=cool&_page=1&_page_size=20", nil) So(err, ShouldBeNil) where, err := PaginateIfPossible(r) So(err, ShouldBeNil) So(where, ShouldContainSubstring, "LIMIT 20 OFFSET(1 - 1) * 20") }) } -
12/16/2016 pREST http://localhost:3999/pREST/prest.slide#1 36/40 Testando funções complexas func TestGetTablesByDatabaseAndSchema(t *testing.T)
{ router := mux.NewRouter() router.HandleFunc("/{database}/{schema}", GetTablesByDatabaseAndSchema).Methods("GET") server := httptest.NewServer(router) defer server.Close() Convey("Get tables by database and schema without custom where clause", t, func() { doValidGetRequest(server.URL+"/prest/public", "GetTablesByDatabaseAndSchema") }) Convey("Get tables by database and schema with custom where clause", t, func() { doValidGetRequest(server.URL+"/prest/public?t.tablename=test", "GetTablesByDatabaseAndSchema") }) Convey("Get tables by database and schema with custom where clause and pagination", t, func() { doValidGetRequest(server.URL+"/prest/public?t.tablename=test&_page=1&_page_size=20", "GetTablesByData }) } -
12/16/2016 pREST http://localhost:3999/pREST/prest.slide#1 37/40 O que o pREST não tem?
(vai ter) OR JOIN Con guração por arquivo TOML RPC -
12/16/2016 pREST http://localhost:3999/pREST/prest.slide#1 38/40 pREST É Open Source Contribuições são
bem vindas!!! https://github.com/nuveo/prest (https://github.com/nuveo/prest) -
12/16/2016 pREST http://localhost:3999/pREST/prest.slide#1 39/40 Thank you Felipe Paes de Oliveira
Go Developer at Nuveo [email protected] (mailto:[email protected]) https://www.felipeweb.net.br (https://www.felipeweb.net.br) @_felipeweb (http://twitter.com/_felipeweb)