DBCore

Rapidly prototype applications powered by your database.

This software is in ALPHA development.

Star

What makes DBCore special?

DBCore reads your database and generates high-quality, statically typed Go code (for the API) and TypeScript (for the UI) based on templates.

Not happy with the built-in templates? Write your own to generate any kind of application you want.

Screenshot of the examples/todo application.

Generating a Go API

In your project root folder create a dbcore.yml: 

project: $project-name
database:
  # Or 'mysql'
  dialect: postgres

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

  # Omit if 5432 for postgres or 3306 for mysql
  port: $port

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

api:
  template: go
  outDir: $outDir

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

  audit:
    # Disabled by default
    enabled: true
    createdAt: $createdAtColumn
    updatedAt: $updatedAtColumn
    deletedAt: $deletedAtColumn

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

  extra:
    repo: $your-repo

  # Configuration that is read only at runtime and can be modified
  # with only a restart not a regeneration.
  runtime:
    # Or use '$username:$password@tcp($host:$port)/$database?sql_mode=ANSI', sql_mode=ANSI is required
    dsn: postgres://$username:$password@$host:$port/$database?sslmode=disable

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

Clone the repo and run make && make install within the repo root. You will need Docker, only.

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

API Specification

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"}

Authorization

Allow lists per endpoint and method are specified in ANSI SQL. If you only include a WHERE clause (omitting the WHERE token) it will default to applying this search on the endpoint's table.

Allowances are specified in the api.runtime.auth.allow section and are a dictionary mapping table labels to a dictionary mapping methods to filters.

Request variables can be interpolated into the filter for session-based authorization.

Built-in request variables

Parameter Definition Example
$req_username Username of the current session admin
$req_object_id Id of the current object being acted on, depends on the type of the primary key. Null if not relevant. 1

Example

api:
  runtime:
    auth:
      allow:
        notes:
	  # Must be public or tied to the current user's organization.
          get: |
            is_public IS TRUE OR
            created_by IN (
              SELECT id
              FROM users
              WHERE organization IN (
                SELECT organization
                FROM users
                WHERE username = $req_username
              )
            )
          # Must be created by the user or tied to the current user's organization and an admin.
          put: &ownedOrOrgAdmin |
            created_by IN (
              SELECT id
              FROM users
              WHERE
                organization IN (
                  SELECT organization
                  FROM users
                  WHERE username = $req_username
                ) AND
                (is_admin IS TRUE OR username = $req_username)
            )
          # Same as edit (put)
          delete: *ownedOrOrgAdmin
          # Must be in the same org
          post: |
            SELECT id
            FROM users
            WHERE
              organization IN (
                SELECT organization
                FROM users
                WHERE username = $req_username
              )

Filters are only applied if the key exists and is not the empty string. For an in-depth example. See the [organization-oriented example note-taking app in the repo](https://github.com/eatonphil/dbcore/tree/master/examples/notes).

Get many rows from a table

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

Query parameters

Parameter Definition Example
limit Number of rows to return limit=25
offset Number of rows to skip offset=0
sortColumn Column to sort on sortColumn=id
sortOrder Order to sort (one of asc or desc) sortOrder=desc
filter SQL where filter to eliminate results filter=id>3

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.

Example

$ curl -X POST -d '{"username": "alex", "password": "alex", "name": "Alex"}' localhost:9090/v1/users
{"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

Generating a TypeScript/React UI

Using the same configuration as for the API, after running dbcore . you can run yarn start in browser/ to start the application at http://localhost:9091.

Use browser.defaultRoute to override the default home page.