Query To App

Generates the endpoints (or a whole app) from a mapping (SQL query -> URL)

:warning: This is a proof of concept at this moment. Until it reaches a stable version, it might (and will) break a compatibility.

How to use

1. Navigate to a project catalog

   $ mkdir new-project
   $ cd new-project

2. Create a mapping file endpoints.yaml

   $ vim endpoints.yaml
   - path: /v1/categories
     get_list:
       query: >-
         SELECT id, name, name_ru, slug
           FROM categories
          LIMIT :q.limit
     post:
       query: >-
         INSERT INTO categories(name, slug, created_at, created_by, updated_at, updated_by)
         VALUES (:b.name, :b.slug, NOW(), :b.user_id, NOW(), :b.user_id)
   
   - path: /v1/categories/:categoryId
     get:
       query: >-
         SELECT id, name, name_ru, slug
           FROM categories
          WHERE id = :p.categoryId
     put:
       query: >-
         UPDATE categories
            SET name = :b.name, name_ru = :b.name_ru, slug = :b.slug, updated_at = NOW(), updated_by = :b.user_id
          WHERE id = :p.categoryId
     delete:
       query: >-
         DELETE
           FROM categories
          WHERE id = :p.categoryId

   Note that the queries use a little unusual named parameters: :b.name, :p.categoryId, etc The prefixes q (query), b (body) and p (path) are used here in order to bind to parameters from the appropriate sources. The prefixes are needed only during code generation and they will absent from the resulted code.

3. Generate code

   | Language | Command | Generated files | Dependencies | | -----------| ----------------------------| ---------------------------| ------------ | | JavaScript | npx query2app --lang js | app.jsroutes.jspackage.jsonDockerfile | expressmysql | | TypeScript | npx query2app --lang ts | app.tsroutes.tspackage.jsontsconfig.jsonDockerfile | expressmysql | | Golang | npx query2app --lang go | app.goroutes.gogo.modDockerfile | go-chi/chigo-sql-driver/mysqljmoiron/sqlx | | Python | npx query2app --lang python | app.pydb.pyroutes.pyrequirements.txtDockerfile | FastAPIUvicornpsycopg2 |

4. Run the application

   | Language | Commands | | -----------| ---------| | JavaScript | $ npm install$ export DB_NAME=my-db DB_USER=my-user DB_PASSWORD=my-password$ npm start | | TypeScript | $ npm install$ npm run build$ export DB_NAME=my-db DB_USER=my-user DB_PASSWORD=my-password$ npm start | | Golang | $ export DB_NAME=my-db DB_USER=my-user DB_PASSWORD=my-password$ go run *.goor$ go build -o app$ ./app | | Python | $ pip install -r requirements.txt$ export DB_NAME=my-db DB_USER=my-user DB_PASSWORD=my-password$ uvicorn app:app --port 3000 |

   ---

   :bulb: NOTE

   While the example used export for setting up the environment variables, we don't recommend export variables that way! This was provided as an example to illustrate that an application follows The Twelve Factors and can be configured by passing environment variables. In real life, you will use docker, docker-compose, Kubernetes or other ways to run an app with required environment variables.

   ---

   :bulb: NOTE

   An app also supports other environment variables:

   • PORT: a port to listen (defaults to 3000)
   • DB_HOST a database host (defaults to localhost)

   ---

5. Test that it works

   $ curl -i http://localhost:3000/v1/categories \
       --json '{"name":"Sport","name_ru":"Спорт","slug":"sport","user_id":100}' 
   HTTP/1.1 204 No Content
   ETag: W/"a-bAsFyilMr4Ra1hIU5PyoyFRunpI"
   Date: Wed, 15 Jul 2020 18:06:33 GMT
   Connection: keep-alive
   
   $ curl http://localhost:3000/v1/categories
   [{"id":1,"name":"Sport","name_ru":"Спорт","slug":"sport"}]
   
   $ curl -i -X PUT http://localhost:3000/v1/categories/1 \
       --json '{"name":"Fauna","name_ru":"Фауна","slug":"fauna","user_id":101}'
   HTTP/1.1 204 No Content
   ETag: W/"a-bAsFyilMr4Ra1hIU5PyoyFRunpI"
   Date: Wed, 15 Jul 2020 18:06:34 GMT
   Connection: keep-alive
   
   $ curl http://localhost:3000/v1/categories/1
   {"id":1,"name":"Fauna","name_ru":"Фауна","slug":"fauna"}
   
   $ curl -i -X DELETE http://localhost:3000/v1/categories/1
   HTTP/1.1 204 No Content
   ETag: W/"a-bAsFyilMr4Ra1hIU5PyoyFRunpI"
   Date: Wed, 15 Jul 2020 18:06:35 GMT
   Connection: keep-alive