tbls is a CI-Friendly tool for 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
- Configuration
- 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 k1low/tbls doc postgres://dbuser:dbpass@hostname:5432/dbname
Install
deb:
Use dpkg-i-from-url
$ export TBLS_VERSION=X.X.X
$ curl -L https://git.io/dpkg-i-from-url | bash -s -- https://github.com/k1LoW/tbls/releases/download/v$TBLS_VERSION/tbls_$TBLS_VERSION-1_amd64.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 tap:
$ brew install k1LoW/tap/tbls
manually:
Download binary from releases page
go get:
$ go get github.com/k1LoW/tbls
docker:
$ docker pull ghcr.io/k1low/tbls:latest
temporary:
$ source <(curl https://git.io/use-tbls)
$ curl -sL https://git.io/use-tbls > /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 master
View the document on GitHub.
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<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. [email protected] |
| 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 diffshows 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.
- Commit the document using
tbls doc. - Update the database schema in the development cycle.
- Check for document updates by running
tbls diffortbls lintin CI. - 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 commandcurl -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
--sortoption.
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
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 Datasource
tbls support following databases/datasources.
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
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
export GOOGLE_APPLICATION_CREDENTIALSorenv GOOGLE_APPLICATION_CREDENTIALS- 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
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
export GOOGLE_APPLICATION_CREDENTIALSorenv GOOGLE_APPLICATION_CREDENTIALS- 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
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
- Use default credential provider chain of AWS SDK for Go
- 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
JSON:
The JSON file output by the tbls out -t json command can be read as a datasource.
---
# .tbls.yml
dsn: json://path/to/testdb.json
HTTP:
---
# .tbls.yml
dsn: https://hostname/path/to/testdb.json
---
# .tbls.yml
dsn:
url: https://hostname/path/to/testdb.json
headers:
Authorization: token GITHUB_OAUTH_TOKEN
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 image format
# Default is `svg`
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
# ER diagram (png/jpg) font (font name, font file, font path or keyword)
# Default is "" ( system default )
font: M+
It is also possible to personalize the output by providing your own templates. See the Personalized Templates section below.
Lint
tbls lint work as linter for database.
# .tbls.yml
lint:
# require table comment
requireTableComment:
enabled: true
# all commented, or all uncommented.
allOrNothing: false
# require column comment
requireColumnComment:
enabled: true
# all commented, or all uncommented.
allOrNothing: true
# exclude columns from warnings
exclude:
- id
- created_at
- updated_at
# exclude tables from warnings
excludeTables:
- logs
- comment_stars
# require index comment
requireIndexComment:
enabled: true
# all commented, or all uncommented.
allOrNothing: false
# exclude indexes from warnings
exclude:
- user_id_idx
# exclude tables from warnings
excludeTables:
- logs
- comment_stars
# require constraint comment
requireConstraintComment:
enabled: true
# all commented, or all uncommented.
allOrNothing: false
# exclude constrains from warnings
exclude:
- unique_user_name
# exclude tables from warnings
excludeTables:
- logs
- comment_stars
# require trigger comment
requireTriggerComment:
enabled: true
# all commented, or all uncommented.
allOrNothing: false
# exclude triggers from warnings
exclude:
- update_count
# exclude tables from warnings
excludeTables:
- logs
- comment_stars
# find a table that has no relation
unrelatedTable:
enabled: true
# all related, or all unrelated.
allOrNothing: 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
# checks if labels are in BigQuery style ( https://cloud.google.com/resource-manager/docs/creating-managing-labels#requirements )
labelStyleBigQuery:
enabled: true
exclude:
- schema_migrations
Filter tables
include: and exclude: are used to filter target tables from tbls *.
# .tbls.yml
include:
- some_prefix_*
exclude:
- some_prefix_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. [email protected]
# labels for tables
labels:
- privary data
- backup:true
-
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
-
table: posts
# index comments
indexComments:
posts_user_id_idx: user.id index
# constraints comments
constraintComments:
posts_id_pk: PRIMARY KEY
# triggers comments
triggerComments:
update_posts_updated: Update updated when posts update
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
Automatically detect relations
detectVirtualRelations: if enabled, automatically detect relations from table and column names.
detectVirtualRelations:
enabled: true
Dictionary
dict: is used to replace title/table header of database document
# .tbls.yml
---
dict:
Tables: テーブル一覧
Description: 概要
Columns: カラム一覧
Indexes: INDEX一覧
Constraints: 制約一覧
Triggers: トリガー
Relations: ER図
Name: 名前
Comment: コメント
Type: タイプ
Default: デフォルト値
Children: 子テーブル
Parents: 親テーブル
Definition: 定義
Table Definition: テーブル定義
Personalized Templates
It is possible to provide your own templates to personalize the documentation generated by tbls by adding a templates section to your configuration. For example:
templates:
dot:
schema: 'templates/schema.dot.tmpl'
table: 'templates/table.dot.tmpl'
puml:
schema: 'templates/schema.puml.tmpl'
table: 'templates/table.puml.tmpl'
md:
index: 'templates/index.md.tmpl'
table: 'templates/table.md.tmpl'
A good starting point to design your own template is to modify a copy the default ones for Dot, PlantUML and markdown.
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 doccan loadschema.jsonas 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:
-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: svg
-f, --force force
-h, --help help for doc
--sort sort
--when string command execute condition
--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
![[sqlite] tbls doc error - near](https://avatars.githubusercontent.com/u/301732?v=4)
![[FEATURE REQUEST] Support service account impersonation with Cloud Spanner](https://avatars.githubusercontent.com/u/1175560?v=4)
So, it is inconvenient to read the table columns in this way.
Thank you!
