tbls (pronounced /ˈteɪbl̩z/) is a CI-Friendly tool to document a database, written in Go.

Key features of tbls are:

• Document a database automatically in GFM format. Output database schema in many formats.
• Single binary = CI-Friendly.
• Support many databases.
• Work as linter for database

Table of Contents

• Quick Start
• Install
• Getting Started
  • Document a database
  • Diff database and (document or database)
  • Lint a database
  • Measure document coverage
  • Continuous Integration
• Configuration
  • Name
  • Description
  • Labels
  • DSN
  • Support Datasource
  • Document path
  • Document format
  • ER diagram
  • Filter tables
  • Lint
  • Comments
  • Relations
  • Viewpoints
  • Dictionary
  • Personalized Templates
  • Required Version
• Expand environment variables
• Output formats
• Command arguments
• Environment variables

Quick Start

Document a database with one command.

$ tbls doc postgres://dbuser:dbpass@hostname:5432/dbname

Using docker image.

$ docker run --rm -v $PWD:/work -w /work ghcr.io/k1low/tbls doc postgres://dbuser:dbpass@hostname:5432/dbname

Install

deb:

$ export TBLS_VERSION=X.X.X
$ curl -o tbls.deb -L https://github.com/k1LoW/tbls/releases/download/v$TBLS_VERSION/tbls_$TBLS_VERSION-1_amd64.deb
$ dpkg -i tbls.deb

RPM:

$ export TBLS_VERSION=X.X.X
$ yum install https://github.com/k1LoW/tbls/releases/download/v$TBLS_VERSION/tbls_$TBLS_VERSION-1_amd64.rpm

Homebrew:

$ brew install tbls

MacPorts:

$ sudo port install tbls

aqua:

$ aqua g -i k1LoW/tbls

Manually:

Download binary from releases page

go install:

$ go install github.com/k1LoW/tbls@latest

Docker:

$ docker pull ghcr.io/k1low/tbls:latest

On GitHub Actions:

# .github/workflows/doc.yml
name: Document

on:
  push:
    branches:
      - main

jobs:
  doc:
    runs-on: ubuntu-latest
    steps:
      -
        name: Checkout .tbls.yml
        uses: actions/checkout@v3
      -
        uses: k1low/setup-tbls@v1
      -
        name: Run tbls for generate database document
        run: tbls doc

:octocat: A GitHub Action for tbls is here.

Temporary:

$ source <(curl https://raw.githubusercontent.com/k1LoW/tbls/main/use)

$ curl -sL https://raw.githubusercontent.com/k1LoW/tbls/main/use > /tmp/use-tbls.tmp && . /tmp/use-tbls.tmp

Getting Started

Document a database

Add .tbls.yml (or tbls.yml) file to your repository.

# .tbls.yml

# DSN (Database Source Name) to connect database
dsn: postgres://dbuser:dbpass@localhost:5432/dbname

# Path to generate document
# Default is `dbdoc`
docPath: doc/schema

Notice: If you are using a symbol such as # < in database password, URL-encode the password

Run tbls doc to analyzes the database and generate document in GitHub Friendly Markdown format.

$ tbls doc

Commit .tbls.yml and the document.

$ git add .tbls.yml doc/schema
$ git commit -m 'Add database document'
$ git push origin main

View the document on GitHub.

Sample document

Diff database and (document or database)

Update database schema.

$ psql -U dbuser -d dbname -h hostname -p 5432 -c 'ALTER TABLE users ADD COLUMN phone_number varchar(15);'
Password for user dbuser:
ALTER TABLE

tbls diff shows the difference between database schema and generated document.

$ tbls diff
diff postgres://dbuser:*****@hostname:5432/dbname doc/schema/README.md
--- postgres://dbuser:*****@hostname:5432/dbname
+++ doc/schema/README.md
@@ -4,7 +4,7 @@

 | Name | Columns | Comment | Type |
 | ---- | ------- | ------- | ---- |
-| [users](users.md) | 7 | Users table | BASE TABLE |
+| [users](users.md) | 6 | Users table | BASE TABLE |
 | [user_options](user_options.md) | 4 | User options table | BASE TABLE |
 | [posts](posts.md) | 8 | Posts table | BASE TABLE |
 | [comments](comments.md) | 6 | Comments

Multi-line

table

comment | BASE TABLE |
diff postgres://dbuser:*****@hostname:5432/dbname doc/schema/users.md
--- postgres://dbuser:*****@hostname:5432/dbname
+++ doc/schema/users.md
@@ -14,7 +14,6 @@
 | email | varchar(355) |  | false |  |  | ex. user@example.com |
 | created | timestamp without time zone |  | false |  |  |  |
 | updated | timestamp without time zone |  | true |  |  |  |
-| phone_number | varchar(15) |  | true |  |  |  |

 ## Constraints

And, tbls diff support for diff checking between database and other database

$ tbls diff postgres://dbuser:*****@local:5432/dbname postgres://dbuser:*****@production:5432/dbname

Notice: tbls diff shows the difference Markdown documents only.

Re-generating database documentation

Existing documentation can re-generated using either --force or --rm-dist flag.

--force forces overwrite of the existing documents. It does not, however, remove files of removed tables.

$ tbls doc --force

--rm-dist removes files in docPath before generating the documents.

$ tbls doc --rm-dist

Lint a database

Add linting rule to .tbls.yml following

# .tbls.yml
lint:
  requireColumnComment:
    enabled: true
    exclude:
      - id
      - created
      - updated
  columnCount:
    enabled: true
    max: 10

Run tbls lint to check the database according to lint: rules

$ tbls lint
users.username: column comment required.
users.password: column comment required.
users.phone_number: column comment required.
posts.user_id: column comment required.
posts.title: column comment required.
posts.labels: column comment required.
comments.post_id: column comment required.
comment_stars.user_id: column comment required.
post_comments.comment: column comment required.
posts: too many columns. [12/10]
comments: too many columns. [11/10]

11 detected

Measure document coverage

tbls coverage measure and show document coverage (description, comments).

$ tbls coverage
Table                       Coverage
All tables                  16.1%
 public.users               20%
 public.user_options        37.5%
 public.posts               35.3%
 public.comments            14.3%
 public.comment_stars       0%
 public.logs                12.5%
 public.post_comments       87.5%
 public.post_comment_stars  0%
 public.CamelizeTable       0%
 public.hyphen-table        0%
 administrator.blogs        0%
 backup.blogs               0%
 backup.blog_options        0%
 time.bar                   0%
 time.hyphenated-table      0%
 time.referencing           0%

Continuous Integration

Continuous integration using tbls.

1. Commit the document using tbls doc.
2. Update the database schema in the development cycle.
3. Check for document updates by running tbls diff or tbls lint in CI.
4. Return to 1.

Example: Travis CI

# .travis.yml
language: go

install:
  - source <(curl -sL https://raw.githubusercontent.com/k1LoW/tbls/main/use)
script:
  - tbls diff
  - tbls lint

Tips: If your CI based on Debian/Ubuntu (/bin/sh -> dash), you can use the following install command curl -sL https://raw.githubusercontent.com/k1LoW/tbls/main/use > use-tbls.tmp && . ./use-tbls.tmp && rm ./use-tbls.tmp

Tips: If the order of the columns does not match, you can use the --sort option.

Configuration

Name

name: is used to specify the database name of the document.

# .tbls.yml
name: mydatabase

Description

desc: is used to specify the database description.

# .tbls.yml
desc: This is My Database

Labels

labels: is used to label the database or tables.

label database:

# .tbls.yml
labels:
  - cmdb
  - analytics

label tables:

# .tbls.yml
comments:
  -
    table: users
    labels:
      - user
      - privacy data

label columns:

# .tbls.yml
comments:
  -
    table: users
    columnLabels:
      email:
        - secure
        - encrypted

DSN

dsn: (Data Source Name) is used to connect to database.

# .tbls.yml
dsn: my://dbuser:dbpass@hostname:3306/dbname

Support Datasource

tbls supports the following databases/datasources.

PostgreSQL:

# .tbls.yml
dsn: postgres://dbuser:dbpass@hostname:5432/dbname

# .tbls.yml
dsn: pg://dbuser:dbpass@hostname:5432/dbname

When you want to disable SSL mode, add "?sslmode=disable" For example:

dsn: pg://dbuser:dbpass@hostname:5432/dbname?sslmode=disable

MySQL:

# .tbls.yml
dsn: mysql://dbuser:dbpass@hostname:3306/dbname

# .tbls.yml
dsn: my://dbuser:dbpass@hostname:3306/dbname

When you want to hide AUTO_INCREMENT clause on the table definitions, add "?hide_auto_increment". For example:

dsn: my://dbuser:dbpass@hostname:3306/dbname?hide_auto_increment

MariaDB:

# .tbls.yml
dsn: mariadb://dbuser:dbpass@hostname:3306/dbname

# .tbls.yml
dsn: maria://dbuser:dbpass@hostname:3306/dbname

SQLite:

# .tbls.yml
dsn: sqlite:///path/to/dbname.db

# .tbls.yml
dsn: sq:///path/to/dbname.db

BigQuery:

# .tbls.yml
dsn: bigquery://project-id/dataset-id?creds=/path/to/google_application_credentials.json

# .tbls.yml
dsn: bq://project-id/dataset-id?creds=/path/to/google_application_credentials.json

To set GOOGLE_APPLICATION_CREDENTIALS environment variable, you can use

1. export GOOGLE_APPLICATION_CREDENTIALS or export GOOGLE_APPLICATION_CREDENTIALS_JSON
2. Add query to DSN
   • ?google_application_credentials=/path/to/client_secrets.json
   • ?credentials=/path/to/client_secrets.json
   • ?creds=/path/to/client_secrets.json

Required permissions: bigquery.datasets.get bigquery.tables.get bigquery.tables.list

Also, you can use impersonate service account using environment variables below.

• GOOGLE_IMPERSONATE_SERVICE_ACCOUNT: Email of service account
• GOOGLE_IMPERSONATE_SERVICE_ACCOUNT_LIFETIME: You can use impersonate service account within this lifetime. This value must be readable from https://github.com/k1LoW/duration .

Cloud Spanner:

# .tbls.yml
dsn: spanner://project-id/instance-id/dbname?creds=/path/to/google_application_credentials.json

To set GOOGLE_APPLICATION_CREDENTIALS environment variable, you can use

1. export GOOGLE_APPLICATION_CREDENTIALS or export GOOGLE_APPLICATION_CREDENTIALS_JSON
2. Add query to DSN
   • ?google_application_credentials=/path/to/client_secrets.json
   • ?credentials=/path/to/client_secrets.json
   • ?creds=/path/to/client_secrets.json

Also, you can use impersonate service account using environment variables below.

• GOOGLE_IMPERSONATE_SERVICE_ACCOUNT: Email of service account
• GOOGLE_IMPERSONATE_SERVICE_ACCOUNT_LIFETIME: You can use impersonate service account within this lifetime. This value must be readable from https://github.com/k1LoW/duration .

Amazon Redshift:

# .tbls.yml
dsn: redshift://dbuser:dbpass@hostname:5432/dbname

# .tbls.yml
dsn: rs://dbuser:dbpass@hostname:5432/dbname

Microsoft SQL Server:

# .tbls.yml
dsn: mssql://DbUser:SQLServer-DbPassw0rd@hostname:1433/testdb

# .tbls.yml
dsn: sqlserver://DbUser:SQLServer-DbPassw0rd@hostname:1433/testdb

# .tbls.yml
dsn: ms://DbUser:SQLServer-DbPassw0rd@localhost:1433/testdb

Amazon DynamoDB:

# .tbls.yml
dsn: dynamodb://us-west-2

# .tbls.yml
dsn: dynamo://ap-northeast-1?aws_access_key_id=XXXXXxxxxxxxXXXXXXX&aws_secret_access_key=XXXXXxxxxxxxXXXXXXX

To set AWS credentials, you can use

1. Use default credential provider chain of AWS SDK for Go
2. Add query to DSN
   • ?aws_access_key_id=XXXXXxxxxxxxXXXXXXX&aws_secret_access_key=XXXXXxxxxxxxXXXXXXX

Snowflake (Experimental):

---
# .tbls.yml
dsn: snowflake://user:password@myaccount/mydb/myschema

See also: https://pkg.go.dev/github.com/snowflakedb/gosnowflake

MongoDB:

```yaml

.t