Skip to content
tbls is a CI-Friendly tool for document a database, written in Go.
Go Makefile Other
Branch: master
Clone or download
Latest commit 310e341 Feb 24, 2020

Files

Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
.github bonsai Nov 2, 2019
cmd Add --distance option to `tbls out` Feb 23, 2020
config Add Table.CollectTablesAndRelations() Feb 23, 2020
datasource Add --distance option to `tbls out` Feb 23, 2020
docker Update default shell Feb 20, 2020
drivers Rename `schema.Relation.IsAdditional` -> `schema.Relation.Virtual` Feb 18, 2020
output Add Table.CollectTablesAndRelations() Feb 23, 2020
sample Add Table.CollectTablesAndRelations() Feb 23, 2020
schema Add Table.CollectTablesAndRelations() Feb 23, 2020
testdata Add config.ER.distance Feb 22, 2020
version Use goreleaser Mar 9, 2019
.codecov.yml Revert bonsai Feb 19, 2020
.editorconfig Fix Column.MarshalJSON Nov 21, 2018
.gitignore Add .tbls.yml (tbls config file) output format Nov 20, 2019
.goreleaser.yml Bump up version number Feb 24, 2020
CHANGELOG.md Bump up version number Feb 24, 2020
CREDITS Bump up version number Feb 24, 2020
LICENSE cobra init github.com/k1LoW/tbls May 17, 2018
Makefile Add `doc` task to `ci` task Feb 22, 2020
README.md Merge pull request #171 from k1LoW/distance Feb 23, 2020
client_secrets.json.gpg Fix decrypt secrets Nov 2, 2019
docker-compose.yml Analyze tables and columns Jun 1, 2019
go.mod go get -u & go mod tidy Feb 24, 2020
go.sum go get -u & go mod tidy Feb 24, 2020
logo.png Add logo May 4, 2019
main.go Analyze tables and columns Jun 1, 2019
use Update `use` script Apr 8, 2019

README.md


tbls

Build Status GitHub release codecov Go Report Card Docker Build

tbls is a CI-Friendly tool for document a database, written in Go.

Key features of tbls are:

Table of Contents


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 k1low/tbls doc postgres://dbuser:dbpass@hostname:5432/dbname

Install

homebrew tap:

$ brew install k1LoW/tap/tbls

manually:

Download binary from releases page

go get:

$ go get github.com/k1LoW/tbls

docker:

$ docker pull k1low/tbls:latest

Getting Started

Document a database

Add .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 master

View the document on GitHub.

Sample document

sample

Diff database and document

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<br>Multi-line<br>table<br>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

Notice: tbls diff shows the difference Markdown documents only.

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

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://git.io/use-tbls)
script:
  - tbls diff
  - tbls lint

Tips: If your CI based on Debian/Ubuntu (/bin/sh -> dash), you can use following install command curl -sL https://git.io/use-tbls > 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

DSN

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

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

DSN: can expand environment variables.

# .tbls.yml
dsn: my://${MYSQL_USER}:${MYSQL_PASSWORD}@hostname:3306/${MYSQL_DATABASE}

Support Database

tbls support following databases.

PostgreSQL:

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

MySQL:

# .tbls.yml
dsn: mysql://dbuser:dbpass@hostname:3306/dbname
# .tbls.yml
dsn: my://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 env GOOGLE_APPLICATION_CREDENTIALS
  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

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 env GOOGLE_APPLICATION_CREDENTIALS
  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

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

Document path

tbls doc generates document in the directory specified by docPath:.

# .tbls.yml
# Default is `dbdoc`
docPath: doc/schema

docPath: can expand environment variables.

# .tbls.yml
docPath: ${DOC_PATH}

Table format

format: is used to change the document format.

# .tbls.yml
format:
  # Adjust the column width of Markdown format table
  # Default is false
  adjust: true
  # Sort the order of table list and  columns
  # Default is false
  sort: false

ER diagram

tbls doc generate ER diagram images at the same time.

# .tbls.yml
er:
  # Skip generation of ER diagram
  # Default is false
  skip: false
  # ER diagram format
  # Default is `png`
  format: svg
  # Add table/column comment to ER diagram
  # Default is false
  comment: true
  # Distance between tables that display relations in the ER
  # Default is 1
  distance: 2

Lint

tbls lint work as linter for database.

# .tbls.yml
lint:
  # require table comment
  requireTableComment:
    enabled: true
  # require column comment
  requireColumnComment:
    enabled: true
    # exclude columns from warnings
    exclude:
      - id
      - created_at
      - updated_at
    # exclude tables from warnings
    excludedTables:
      - logs
      - comment_stars
  # find a table that has no relation
  unrelatedTable:
    enabled: true
    # exclude tables from warnings
    exclude:
      - logs
  # check max column count
  columnCount:
    enabled: true
    max: 10
    # exclude tables from warnings
    exclude:
      - user_options
  # require columns
  requireColumns:
    enabled: true
    columns:
      -
        name: created
      -
        name: updated
        exclude:
          - logs
          - CamelizeTable
  # check duplicate relations
  duplicateRelations:
    enabled: true
  # check if the foreign key columns have an index
  requireForeignKeyIndex:
    enabled: true
    exclude:
      - comments.user_id

Exclude tables

exclude: is used to exclude tables from tbls *.

# .tbls.yml
exclude:
  - logs
  - CamelizeTable

lintExclude: is used to exclude tables from tbls lint.

# .tbls.yml
lintExclude:
  - CamelizeTable

Comments

comments: is used to add table/column comment to database document without ALTER TABLE.

For example, you can add comment about VIEW TABLE or SQLite tables/columns.

# .tbls.yml
comments:
  -
    table: users
    # table comment
    tableComment: Users table
    # column comments
    columnComments:
      email: Email address as login id. ex. user@example.com
  -
    table: post_comments
    tableComment: post and comments View table
    columnComments:
      id: comments.id
      title: posts.title
      post_user: posts.users.username
      comment_user: comments.users.username
      created: comments.created
      updated: comments.updated

Relations

relations: is used to add table relation to database document without FOREIGN KEY.

You can create ER diagrams with relations without having foreign key constraints.

relations:
  -
    table: logs
    columns:
      - user_id
    parentTable: users
    parentColumns:
      - id
    # Relation definition
    # Default is `Additional Relation`
    def: logs->users
  -
    table: logs
    columns:
      - post_id
    parentTable: posts
    parentColumns:
      - id
  -
    table: logs
    columns:
      - comment_id
    parentTable: comments
    parentColumns:
      - id
  -
    table: logs
    columns:
      - comment_star_id
    parentTable: comment_stars
    parentColumns:
      - id

img

Output formats

tbls out output in various formats.

Markdown:

$ tbls out -t md -o schema.md

DOT:

$ tbls out -t dot -o schema.dot

PlantUML:

$ tbls out -t plantuml -o schema.puml

Image (svg, png, jpg):

$ tbls out -t svg --table users --distance 2 -o users.svg

JSON:

$ tbls out -t json -o schema.json

Tips: tbls doc can load schema.json as DSN.

$ tbls doc json:///path/to/schema.json

YAML:

$ tbls out -t yaml -o schema.yaml

Excel:

$ tbls out -t xlsx -o schema.xlsx

.tbls.yml:

$ tbls out -t config -o .tbls.new.yml

Command arguments

tbls subcommands ( doc,diff, etc) accepts arguments and options

$ tbls doc my://root:mypass@localhost:3306/testdb doc/schema

You can check available arguments and options using tbls help [COMMAND].

$ tbls help doc
'tbls doc' analyzes a database and generate document in GitHub Friendly Markdown format.

Usage:
  tbls doc [DSN] [DOC_PATH] [flags]

Flags:
  -a, --add config         additional schema data path (deprecated, use config)
  -j, --adjust-table       adjust column width of table
  -c, --config string      config file path
  -t, --er-format string   ER diagrams output format [png, svg, jpg, ...]. default: png
  -f, --force              force
  -h, --help               help for doc
      --sort               sort
      --without-er         no generate ER diagrams

Environment variables

tbls accepts environment variables TBLS_DSN and TBLS_DOC_PATH

$ env TBLS_DSN=my://root:mypass@localhost:3306/testdb TBLS_DOC_PATH=doc/schema tbls doc
You can’t perform that action at this time.