Generate a Go REST API from a PostgreSQL database with pagination, sorting, filtering, and auth

DBCore

Code generation powered by your database.

• Generate standardized REST APIs with:
  • GET (one, many), POST, DELETE, PUT endpoints
  • Filtering using a SQL-like DSL
  • Pagination, sorting
  • JWT-based authentication
• Write your own templates to generate database-driven code for any language
• MIT license

This software is in ALPHA development.

Star

In your project root folder create a dbcore.yml :

project: $project-name
database:
  dialect: postgres

  # Omit if localhost
  host: $host-or-ip

  # Omit if 5432
  port: $port

  database: $database-name
  username: $username
  password: $password

api:
  template: go
  outDir: go

  # e.g. "v1/", for URLs
  routerPrefix: router-prefix

  auth:
    enabled: true
    table: users
    # Column for username field
    username: username
    # Column for password field, bcrypt hash is stored
    password: password

  extra:
    repo: $your-repo

The dbcore.yml file controls code generation. You'll also need an app.yml file for runtime behavior:

database:
  dialect: postgres
  dsn: postgres://$username:$password@$host/$database?sslmode=disable

address: :9090

session:
  duration: 2hr
  secret: my-secret-signing-key

Clone the repo and run dotnet run $path-to-your-project within the repo root.

Then go to your project directory and run go run cmd/main.go to start the server.

Authentication

When authentication is enabled, make a JSON POST request with {"username": "$your-username", "password": "$your-password"} to /$version/session/start to generate a token. It is valid for 2 hours by default.

Passwords are stored as BCrypt hashes.

You can store this token in the au cookie or you can submit it as bearer token by setting the Authorization header to BEARER $your-token .

Example

$ curl -X POST -d '{"username": "alex", "password": "alex"}' localhost:9090/v1/session/start
{"token":"eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJleHAiOjE1OTA3MjkyNjMsImlhdCI6MTU5MDcyMjA2MywibmJmIjoxNTkwNzIyMDYzLCJ1c2VybmFtZSI6InBoaWwifQ.4AAveeFRpXckn3cRFyCQew2V7jmcU4OOYH68wcv6afI"}

Get many rows from a table

Make a GET request to /$version/$table .

Query parameters

Example

$ curl 'localhost:9090/v1/users?limit=25&offset=0&sortColumn=id&sortOrder=desc&filter=id=1'
{
  "total": 1,
  "data": [
    {
      "id": 1,
      "username": "alex",
      "password": "<REDACTED>",
      "name": "Alex"
    },
  ]
}

Create a new row

Make a POST request to /$version/$table/new .

Example

$ curl -X POST -d '{"username": "alex", "password": "alex", "name": "Alex"}' localhost:9090/v1/users/new
{"id":1,"username":"alex","password":"<REDACTED>","name":"Alex"}

Get a row

Make a GET request to /$version/$table/$id .

This endpoint is only available if the table has a primary key.

Example

$ curl localhost:9090/v1/users/1
{"id":1,"username":"alex","password":"<REDACTED>","name":"Alex"}

Update a row

Make a PUT request to /$version/$table/$id .

This endpoint is only available if the table has a primary key.

Example

$ curl -X PUT -d '{"id": 1, "username": "alex", "password": "alex", "name": "Alex K"}' localhost:9090/v1/users/1
{"id":1,"username":"alex","password":"<REDACTED>","name":"Alex K"}

Delete a row

Make a DELETE request to /$version/$table/$id .

This endpoint is only available if the table has a primary key.

Example

$ curl -X DELETE localhost:9090/v1/users/1