tbls is a CI-Friendly tool for document a database, written in Go.
Key features of tbls are:
- Document a database automatically in GitHub Friendly Markdown format
- Single binary
- CI-Friendly
- Work as linter for database
Document a database with one command.
$ tbls doc postgres://dbuser:dbpass@hostname:5432/dbnamehomebrew tap:
$ brew install k1LoW/tap/tblsmanually:
Download binany from releases page
go get:
$ go get github.com/k1LoW/tblsAdd .tbls.yml file to your repogitory.
# .tbls.yml
# DSN (Databaase Source Name) to connect database
dsn: postgres://dbuser:dbpass@localhost:5432/dbname
# Path to generate document
# Default is `dbdoc`
docPath: doc/schemaNotice: 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 docCommit .tbls.yml and the document.
$ git add .tbls.yml doc/schema
$ git commit -m'Add database document'
$ git push origin masterView the document on GitHub.
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 TABLEtbls 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
Notice:
tbls diffshows the difference Markdown documents only.
Add linting rule to .tbls.yml following
# .tbls.yml
lint:
requireColumnComment:
enabled: true
exclude:
- id
- created
- updated
columnCount:
enabled: true
max: 10Run 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 detectedContinuous 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 lintTips: 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.
DSN: (Data Srouce Name) is used to connect to database.
# .tbls.yml
dsn: my://dbuser:dbpass@hostname:3306/dbnameDSN: can expand environment variables.
# .tbls.yml
dsn: my://${MYSQL_USER}:${MYSQL_PASSWORD}@localhost:3306/${MYSQL_DATABASE}tbls support following databases.
PostgreSQL:
# .tbls.yml
dsn: postgres://dbuser:dbpass@hostname:5432/dbname# .tbls.yml
dsn: pg://dbuser:dbpass@hostname:5432/dbnameMySQL:
# .tbls.yml
dsn: mysql://dbuser:dbpass@hostname:3306/dbname# .tbls.yml
dsn: my://dbuser:dbpass@hostname:3306/dbnameSQLite:
# .tbls.yml
dsn: sqlite:///path/to/dbname.db# .tbls.yml
dsn: sq:///path/to/dbname.dbtbls doc generates document in the directory specified by docPath:.
# .tbls.yml
# Default is `dbdoc`
docPath: doc/schemadocPath: can expand environment variables.
# .tbls.yml
docPath: ${DOC_PATH}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: falseIf you can use Graphviz dot command, 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: svgtbls 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_optionscomments: 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]
-
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.updatedcomments: 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
tbls subcommands ( doc,diff, etc) accepts arguments and options
$ tbls doc my://root:mypass@localhost:3306/testdb doc/schemaYou 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 diagramstbls accepts envirionment variables TBLS_DSN and TBLS_DOC_PATH
$ env TBLS_DSN=my://root:mypass@localhost:3306/testdb TBLS_DOC_PATH=doc/schema tbls doc