tbls

[](https://github.com/k1LoW/tbls/releases) [](https://goreportcard.com/report/github.com/k1LoW/tbls) `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](https://github.github.com/gfm/) format. Output database schema [in many formats](#output-formats).** - **Single binary = CI-Friendly.** - **[Support many databases](#support-datasource).** - **Work as linter for database** ### Table of Contents - [Quick Start](#quick-start) - [Install](#install) - [Getting Started](#getting-started) - [Document a database](#document-a-database) - [Diff database and (document or database)](#diff-database-and-document-or-database) - [Lint a database](#lint-a-database) - [Measure document coverage](#measure-document-coverage) - [Continuous Integration](#continuous-integration) - [Configuration](#configuration) - [Name](#name) - [Description](#description) - [Labels](#labels) - [DSN](#dsn) - [Support Datasource](#support-datasource) - [Document path](#document-path) - [Document format](#document-format) - [ER diagram](#er-diagram) - [Filter tables](#filter-tables) - [Lint](#lint) - [Comments](#comments) - [Relations](#relations) - [Viewpoints](#viewpoints) - [Dictionary](#dictionary) - [Personalized Templates](#personalized-templates) - [Required Version](#required-version) - [Expand environment variables](#expand-environment-variables) - [Output formats](#output-formats) - [Command arguments](#command-arguments) - [Environment variables](#environment-variables)
## Quick Start Document a database with one command. ```console $ tbls doc postgres://dbuser:dbpass@hostname:5432/dbname ``` Using docker image. ```console $ docker run --rm -v $PWD:/work -w /work ghcr.io/k1low/tbls doc postgres://dbuser:dbpass@hostname:5432/dbname ``` ## Install **deb:** ```console $ 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:** ```console $ 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:** ```console $ brew install tbls ``` **MacPorts:** ```console $ sudo port install tbls ``` **[aqua](https://aquaproj.github.io/):** ```console $ aqua g -i k1LoW/tbls ``` **Manually:** Download binary from [releases page](https://github.com/k1LoW/tbls/releases) **go install:** ```console $ go install github.com/k1LoW/tbls@latest ``` **Docker:** ```console $ docker pull ghcr.io/k1low/tbls:latest ``` **On GitHub Actions:** ```yml # .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](https://github.com/k1LoW/setup-tbls).** **Temporary:** ```console $ source <(curl https://raw.githubusercontent.com/k1LoW/tbls/main/use) ``` ```console $ 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. ```yaml # .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. ```console $ tbls doc ``` Commit `.tbls.yml` and the document. ```console $ git add .tbls.yml doc/schema $ git commit -m 'Add database document' $ git push origin main ``` View the document on GitHub. [Sample document](sample/postgres/README.md) ### Diff database and (document or database) Update database schema. ```console $ 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. ``` … ``` And, `tbls diff` support for diff checking between database and other database ```console $ 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. ```console $ tbls doc --force ``` `--rm-dist` removes files in docPath before generating the documents. ```console $ tbls doc --rm-dist ``` ### Lint a database Add linting rule to `.tbls.yml` following ```yaml # .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 ```console $ 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). ``` … ``` ### 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** ```yaml # .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. ```yaml # .tbls.yml name: mydatabase ``` ### Description `desc:` is used to specify the database description. ```yaml # .tbls.yml desc: This is My Database ``` ### Labels `labels:` is used to label the database or tables. **label database:** ```yaml # .tbls.yml labels: - cmdb - analytics ``` **label tables:** ```yaml # .tbls.yml comments: - table: users labels: - user - privacy data ``` **label columns:** ```yaml # .tbls.yml comments: - table: users columnLabels: email: - secure - encrypted ``` ### DSN `dsn:` (Data Source Name) is used to connect to database. ```yaml # .tbls.yml dsn: my://dbuser:dbpass@hostname:3306/dbname ``` #### SSL/TLS For PostgreSQL, MySQL, MariaDB and Microsoft SQL Server, the `tls:` section of `dsn:` configures SSL/TLS using certificate files: ```yaml # .tbls.yml dsn: url: my://dbuser:dbpass@hostname:3306/dbname tls: ca: /path/to/ca.pem cert: /path/to/client-cert.pem key: /path/to/client-key.pem verify: identity ``` - `ca` verifies the server certificate chain without hostname verification (like `mysql --ssl-mode=VERIFY_CA`). - `verify: identity` also verifies the hostname (like `mysql --ssl-mode=VERIFY_IDENTITY`). - `cert` and `key` present a client certificate, and must be set together. Without `ca` or `verify: identity` the connection is encrypted but the server is not verified. - Empty values are treated as absent, so settings like `ca: ${MYSQL_SSL_CA}` work unchanged when the environment variables are not set. Paired settings (`cert`/`key`) must be set or unset together. - Native TLS DSN parameters that contradict these settings (e.g. `sslmode=disable`, `encrypt=disable`, `trustservercertificate=true`, `tls=preferred`) are rejected with an error instead of being silently overridden. `tls=true` keeps full verification, and `tls=skip-verify` is upgraded to the requested verification level. #### Support Datasource tbls supports the following databases/datasources. **PostgreSQL:** ```yaml # .tbls.yml dsn: postgres://dbuser:dbpass@hostname:5432/dbname ``` ```yaml # .tbls.yml dsn: pg://dbuser:dbpass@hostname:5432/dbname ``` When you want to disable SSL mode, add "?sslmode=disable" For example: ```yaml dsn: pg://dbuser:dbpass@hostname:5432/dbname?sslmode=disable ``` The `dsn.tls` settings map to `sslrootcert`, `sslcert` and `sslkey`, with `sslmode=verify-ca` (or `sslmode=verify-full` when `verify: identity` is set). An explicit verifying `sslmode` (`require`, `verify-ca`, `verify-full`) is kept. Certificate paths containing spaces are not supported. **MySQL:** ```yaml # .tbls.yml dsn: mysql://dbuser:dbpass@hostname:3306/dbname ``` ```yaml # .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: ```yaml dsn: my://dbuser:dbpass@hostname:3306/dbname?hide_auto_increment ``` Each `--ssl-mode` of the mysql client maps to a DSN parameter or `dsn.tls` settings (see the SSL/TLS section) as follows: | `--ssl-mode` | tbls configuration | | --- | --- | | `DISABLED` | `?tls=false` | | `PREFERRED` | `?tls=preferred` | | `REQUIRED` | `?tls=skip-verify` | | `VERIFY_CA` | `tls:` with `ca: /path/to/ca.pem` | | `VERIFY_IDENTITY` | `tls:` with `ca: /path/to/ca.pem` and `verify: identity` | **MariaDB:** ```yaml # .tbls.yml dsn: mariadb://dbuser:dbpass@hostname:3306/dbname ``` ```yaml # .tbls.yml dsn: maria://dbuser:dbpass@hostname:3306/dbname ``` **SQLite:** ```yaml # .tbls.yml dsn: sqlite:///path/to/dbname.db ``` ```yaml # .tbls.yml dsn: sq:///path/to/dbname.db ``` **BigQuery:** ```yaml # .tbls.yml dsn: bigquery://project-id/dataset-id?creds=/path/to/google_application_credentials.json ``` ```yaml # .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:** ```yaml # .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_appl