pREST - A RESTful API from any PostgreSQL database

A RESTful API from any PostgreSQL database

Transcript

1. 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
2. 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
3. 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
4. 12/16/2016 pREST http://localhost:3999/pREST/prest.slide#1 4/40 pREST born (22 de Novembro de 2016)
5. 12/16/2016 pREST http://localhost:3999/pREST/prest.slide#1 5/40 Disa os de implementação
6. 12/16/2016 pREST http://localhost:3999/pREST/prest.slide#1 6/40 SELECT (all databases) GET /databases
7. 12/16/2016 pREST http://localhost:3999/pREST/prest.slide#1 7/40 SELECT (all schemas) GET /schemas
8. 12/16/2016 pREST http://localhost:3999/pREST/prest.slide#1 8/40 SELECT (all tables) GET /tables
9. 12/16/2016 pREST http://localhost:3999/pREST/prest.slide#1 9/40 SELECT (all tables based schema) GET /{DATABASE}/{SCHEMA}
10. 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
11. 12/16/2016 pREST http://localhost:3999/pREST/prest.slide#1 11/40 Operação em uma tabela
12. 12/16/2016 pREST http://localhost:3999/pREST/prest.slide#1 12/40 SELECT (rows) GET /{DATABASE}/{SCHEMA}/{TABLE} SELECT * FROM {DATABASE}.{SCHEMA}.{TABLE}
13. 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
14. 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)
15. 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
16. 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 ?
17. 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
18. 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 == "" {
19. 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 }
20. 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 }
21. 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
22. 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 }
23. 12/16/2016 pREST http://localhost:3999/pREST/prest.slide#1 23/40 INSERT (rows) POST /{DATABASE}/{SCHEMA}/{TABLE} INSERT INTO {DATABASE}.{SCHEMA}.{TABLE}
24. 12/16/2016 pREST http://localhost:3999/pREST/prest.slide#1 24/40 INSERT - Problemas De nir estrura do do json a receber
25. 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 } }
26. 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]) }
27. 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 }
28. 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
29. 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 :)
30. 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++ {
31. 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
32. 12/16/2016 pREST http://localhost:3999/pREST/prest.slide#1 32/40 Testes
33. 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") }) }
34. 12/16/2016 pREST http://localhost:3999/pREST/prest.slide#1 34/40 Como testar funções que precisam de info das rotas?
35. 12/16/2016 pREST http://localhost:3999/pREST/prest.slide#1 35/40 httptest.Server
36. 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 }) }
37. 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
38. 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)
39. 12/16/2016 pREST http://localhost:3999/pREST/prest.slide#1 39/40 Thank you Felipe Paes de Oliveira Go Developer at Nuveo felipeweb.programador@gmail.com (mailto:felipeweb.programador@gmail.com) https://www.felipeweb.net.br (https://www.felipeweb.net.br) @_felipeweb (http://twitter.com/_felipeweb)
40. 12/16/2016 pREST http://localhost:3999/pREST/prest.slide#1 40/40