Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
/tblsPublic

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

License

NotificationsYou must be signed in to change notification settings

k1LoW/tbls

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Repository files navigation


tbls

Build StatusGitHub releaseGo Report CardCoverageCode to Test RatioTest Execution Time

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

Key features oftbls 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 -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 k1LoW/tap/tbls

MacPorts:

$sudo port install tbls

aqua:

$aqua g -i k1LoW/tbls

Manually:

Download binary fromreleases page

go install:

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

Docker:

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

On GitHub Actions:

# .github/workflows/doc.ymlname:Documenton:push:branches:      -mainjobs:doc:runs-on:ubuntu-lateststeps:      -name:Checkout .tbls.ymluses:actions/checkout@v3      -uses:k1low/setup-tbls@v1      -name:Run tbls for generate database documentrun:tbls doc

:octocat: A GitHub Action for tbls ishere.

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 (ortbls.yml) file to your repository.

# .tbls.yml# DSN (Database Source Name) to connect databasedsn: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

Runtbls 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

sample

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 diffdiff 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

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.ymllint:requireColumnComment:enabled:trueexclude:      -id      -created      -updatedcolumnCount:enabled:truemax:10

Runtbls lint to check the database according tolint: rules

$tbls lintusers.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 coverageTable                       CoverageAll 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 usingtbls doc.
  2. Update the database schema in the development cycle.
  3. Check for document updates by runningtbls diff ortbls lint in CI.
  4. Return to1.

Example: Travis CI

# .travis.ymllanguage:goinstall:  -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 commandcurl -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.ymlname:mydatabase

Description

desc: is used to specify the database description.

# .tbls.ymldesc:This is My Database

Labels

labels: is used to label the database or tables.

label database:

# .tbls.ymllabels:  -cmdb  -analytics

label tables:

# .tbls.ymlcomments:  -table:userslabels:      -user      -privacy data

label columns:

# .tbls.ymlcomments:  -table:userscolumnLabels:email:        -secure        -encrypted

DSN

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

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

Support Datasource

tbls supports the following databases/datasources.

PostgreSQL:

# .tbls.ymldsn:postgres://dbuser:dbpass@hostname:5432/dbname
# .tbls.ymldsn: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.ymldsn:mysql://dbuser:dbpass@hostname:3306/dbname
# .tbls.ymldsn: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.ymldsn:mariadb://dbuser:dbpass@hostname:3306/dbname
# .tbls.ymldsn:maria://dbuser:dbpass@hostname:3306/dbname

SQLite:

# .tbls.ymldsn:sqlite:///path/to/dbname.db
# .tbls.ymldsn:sq:///path/to/dbname.db

BigQuery:

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

To setGOOGLE_APPLICATION_CREDENTIALS environment variable, you can use

  1. export GOOGLE_APPLICATION_CREDENTIALS orexport 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.getbigquery.tables.getbigquery.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 fromhttps://github.com/k1LoW/duration .

Cloud Spanner:

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

To setGOOGLE_APPLICATION_CREDENTIALS environment variable, you can use

  1. export GOOGLE_APPLICATION_CREDENTIALS orexport 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 fromhttps://github.com/k1LoW/duration .

Amazon Redshift:

# .tbls.ymldsn:redshift://dbuser:dbpass@hostname:5432/dbname
# .tbls.ymldsn:rs://dbuser:dbpass@hostname:5432/dbname

Microsoft SQL Server:

# .tbls.ymldsn:mssql://DbUser:SQLServer-DbPassw0rd@hostname:1433/testdb
# .tbls.ymldsn:sqlserver://DbUser:SQLServer-DbPassw0rd@hostname:1433/testdb
# .tbls.ymldsn:ms://DbUser:SQLServer-DbPassw0rd@localhost:1433/testdb

Amazon DynamoDB:

# .tbls.ymldsn:dynamodb://us-west-2
# .tbls.ymldsn: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.ymldsn:snowflake://user:password@myaccount/mydb/myschema

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

MongoDB:

# .tbls.ymldsn:mongodb://mongoadmin:secret@localhost:27017/test
# .tbls.ymldsn:mongodb://mongoadmin:secret@localhost:27017/test?sampleSize=20

If a field has multiple types, themultipleFieldType query can be used to list all the types.

# .tbls.ymldsn:mongodb://mongoadmin:secret@localhost:27017/test?sampleSize=20&multipleFieldType=true

ClickHouse:

# .tbls.ymldsn:clickhouse://dbuser:dbpass@hostname:9000/dbname

See also:https://pkg.go.dev/github.com/ClickHouse/clickhouse-go

JSON:

The JSON file output by thetbls out -t json command can be read as a datasource (JSON Schema ishere).

---# .tbls.ymldsn:json://path/to/testdb.json

HTTP:

---# .tbls.ymldsn:https://hostname/path/to/testdb.json
---# .tbls.ymldsn:url:https://hostname/path/to/testdb.jsonheaders:Authorization:token GITHUB_OAUTH_TOKEN

GitHub:

---# .tbls.ymldsn:github://k1LoW/tbls/sample/mysql/schema.json

External database driver

tbls can integrate with external database drivers. If an executable with the patterntbls-driver-* is on the PATH, tbls will recognize the corresponding scheme.

For example, if you have an executable namedtbls-driver-foodb, tbls will recognize thefoodb:// scheme.

tbls-driver-foodb receives the DSN at runtime via the environment variableTBLS_DSN. By outputtingschema.json via STDOUT, tbls will work with it.

Document path

tbls doc generates document in the directory specified bydocPath:.

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

Document format

format: is used to change the document format.

# .tbls.ymlformat:# Adjust the column width of Markdown format table# Default is falseadjust:true# Sort the order of table list and columns# Default is falsesort:false# Display sequential numbers in table rows# Default is falsenumber:false# The comments for each table in the Tables section of the index page will display the text up to the first double newline (first paragraph).# Default is falseshowOnlyFirstParagraph:true# Hide table columns without values# Default is falsehideColumnsWithoutValues:true# It can be boolean or array# hideColumnsWithoutValues: ["Parents", "Children"]

ER diagram

tbls doc generate ER diagram images at the same time.

# .tbls.ymler:# Skip generation of ER diagram# Default is falseskip:false# ER diagram image format (`png`, `jpg`, `svg`, `mermaid`)# Default is `svg`format:svg# Add table/column comment to ER diagram# Default is falsecomment:true# Hide relation definition from ER diagram# Default is falsehideDef:true# Show column settings in ER diagram. If this section is not set, all columns will be displayed (default).showColumnTypes:# Show related columnsrelated:true# Show primary key columnsprimary:true# Distance between tables that display relations in the ER# Default is 1distance: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 thePersonalized Templates section below.

Lint

tbls lint work as linter for database.

# .tbls.ymllint:# require table commentrequireTableComment:enabled:true# all commented, or all uncommented.allOrNothing:false# require column commentrequireColumnComment:enabled:true# all commented, or all uncommented.allOrNothing:true# exclude columns from warningsexclude:      -id      -created_at      -updated_at# exclude tables from warningsexcludeTables:      -logs      -comment_stars# require index commentrequireIndexComment:enabled:true# all commented, or all uncommented.allOrNothing:false# exclude indexes from warningsexclude:      -user_id_idx# exclude tables from warningsexcludeTables:      -logs      -comment_stars# require constraint commentrequireConstraintComment:enabled:true# all commented, or all uncommented.allOrNothing:false# exclude constrains from warningsexclude:      -unique_user_name# exclude tables from warningsexcludeTables:      -logs      -comment_stars# require trigger commentrequireTriggerComment:enabled:true# all commented, or all uncommented.allOrNothing:false# exclude triggers from warningsexclude:      -update_count# exclude tables from warningsexcludeTables:      -logs      -comment_stars# require table labelsrequireTableLabels:enabled:true# all commented, or all uncommented.allOrNothing:false# exclude tables from warningsexclude:      -logs# find a table that has no relationunrelatedTable:enabled:true# all related, or all unrelated.allOrNothing:true# exclude tables from warningsexclude:      -logs# check max column countcolumnCount:enabled:truemax:10# exclude tables from warningsexclude:      -user_options# require columnsrequireColumns:enabled:truecolumns:      -name:created      -name:updatedexclude:          -logs          -CamelizeTable# check duplicate relationsduplicateRelations:enabled:true# check if the foreign key columns have an indexrequireForeignKeyIndex:enabled:trueexclude:      -comments.user_id# checks if labels are in BigQuery style (https://cloud.google.com/resource-manager/docs/creating-managing-labels#requirements)labelStyleBigQuery:enabled:trueexclude:      -schema_migrations# checks if tables are included in at least one viewpointrequireViewpoints:enabled:trueexclude:      -schema_migrations

Filter tables

filter tables

include: andexclude: are used to filter target tables fromtbls *.

# .tbls.ymlinclude:  -some_prefix_*exclude:  -some_prefix_logs  -CamelizeTable

lintExclude: is used to exclude tables fromtbls lint.

# .tbls.ymllintExclude:  -CamelizeTable

Filter logic

  1. Add tables from include
  2. Remove tables from exclude
    • Check for include/exclude overlaps
    • If include is more specific than exclude (i.e.schema.MyTable >schema.* orschema.MyT* >schema.*), include the table(s). If include is equally or less specific than exclude, exclude wins.
  3. Result

Comments

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

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

Notice: Comments defined in.tbls.yml will override existing comments in the schema.

# .tbls.ymlcomments:  -table:users# table commenttableComment:Users table# column commentscolumnComments:email:Email address as login id. ex. user@example.com# labels for tableslabels:      -privary data      -backup:true  -table:post_commentstableComment:post and comments View tablecolumnComments:id:comments.idtitle:posts.titlepost_user:posts.users.usernamecomment_user:comments.users.usernamecreated:comments.createdupdated:comments.updated  -table:posts# index commentsindexComments:posts_user_id_idx:user.id index# constraints commentsconstraintComments:posts_id_pk:PRIMARY KEY# triggers commentstriggerComments:update_posts_updated:Update updated when posts update

Relations

relations: is used to add or override table relation to database document withoutFOREIGN KEY.

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

relations:  -table:logscolumns:      -user_idparentTable:usersparentColumns:      -id# Relation definition# Default is `Additional Relation`def:logs->users  -table:logscolumns:      -post_idparentTable:postsparentColumns:      -id  -table:logscolumns:      -comment_idparentTable:commentsparentColumns:      -id  -table:logscolumns:      -comment_star_idparentTable:comment_starsparentColumns:      -id

img

Override relations

If you want to override an existing relation, set theoverride: totrue.

relations:  -table:postscolumns:      -user_idcardinality:zero or oneparentTable:usersparentColumns:      -idparentCardinality:one or moreoverride:truedef:posts->users

Automatically detect relations

detectVirtualRelations: if enabled, automatically detect relations from table and column names.

detectVirtualRelations:enabled:truestrategy:default
Supported strategies
strategy namerelation fromrelation to
defaultsome_table.user_idusers.id
singularTableNamesome_table.user_iduser.id
identicalsome_table.user_idusers.user_id
identicalSingularTableNamesome_table.user_iduser.user_id

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 bytbls by adding atemplates: 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 forDot,PlantUML andmarkdown.

Required Version

TherequiredVersion setting defines a version constraint string. This defines which version of tbls can be used in the configuration.

requiredVersion:'>= 1.42, < 2'

Expand environment variables

All configuration values can be set by expanding the environment variables.

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

Viewpoints

Viewpoints of your database schema based on concerns of your domain and add description to them.You can also define groups of tables within viewpoints.

# .tbls.ymlviewpoints:  -name:comments on postdesc:Users can comment on each post multiple times and put a star on each comment.tables:      -users      -posts      -comments      -comment_stars      -post_comments      -post_comment_starsgroups:      -name:Commentsdesc:Tables about commentstables:          -posts          -comments          -post_comments      -name:Starsdesc:Tables about starstables:          -comment_stars          -post_comment_stars

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

Mermaid:

$tbls out -t mermaid -o schema.mmd

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 loadschema.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 usingtbls help [COMMAND].

$tblshelp 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  -b, --base-url string    base url for links  -c, --config string      config file path  -t, --er-format string   ER diagrams output format (png, svg, jpg, mermaid). default: svg  -f, --force              force  -h, --help               help for doc      --rm-dist            remove files in docPath before generating documents      --sort               sort      --when string        command execute condition      --without-er         no generate ER diagrams

Output Schema data

tbls doc also output schema data (schema.json) to same directory as the generated schema document.

To disable output of schema data, setdisableOutputSchema: totrue in.tbls.yml file.

Environment variables

tbls accepts environment variablesTBLS_DSN andTBLS_DOC_PATH

$env TBLS_DSN=my://root:mypass@localhost:3306/testdb TBLS_DOC_PATH=doc/schema tbls doc
[8]ページ先頭
©2009-2025 Movatter.jp