query2app

Query To App

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

⚠️ 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

   Example commands

   Language	Command	Generated files	Dependencies
   JavaScript	npx query2app --lang js	app.js routes.js package.json Dockerfile	express mysql
   TypeScript	npx query2app --lang ts	app.ts routes.ts package.json tsconfig.json Dockerfile	express mysql
   Golang	npx query2app --lang go	app.go routes.go go.mod Dockerfile	go-chi/chi go-sql-driver/mysql jmoiron/sqlx
   Python	npx query2app --lang python	app.py db.py routes.py requirements.txt Dockerfile	FastAPI Uvicorn psycopg2

4. Run the application

   Example commands

   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 *.go or $ 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

   ---

   💡 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.

   ---

   💡 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

   Examples for curl

   $ 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