dberd

README

DBerd

DBerd is a Go library and command-line tool that helps you extract database schemas and generate diagrams. It provides a flexible and extensible way to work with different database sources and output formats.

Features

• Extract database schemas from various sources
• Format schemas into different representations
• Generate diagrams using multiple diagramming tools
• Use as a library in your Go applications or as a standalone CLI tool
• Extensible architecture for adding new sources and targets

Installation

As a Library

go get github.com/holydocs/dberd

As a CLI Tool

# Using Go
go install github.com/holydocs/dberd/cmd/dberd@latest

Supported Sources

Currently, DBerd supports the following database sources:

• PostgreSQL: Extract schema from PostgreSQL databases using the postgres source type;
• MySQL: Extract schema from MySQL databases using the mysql source type;
• CockroachDB: Extract schema from CockroachDB databases using the cockroach source type;
• ClickHouse: Extract schema from ClickHouse databases using the clickhouse source type;
• MongoDB: Extract collections from MongoDB databases using the mongodb source type.

Supported Targets

DBerd supports multiple output formats and diagramming tools:

• D2: Generate/render diagrams using the D2 diagramming language
• PlantUML: Generate diagrams using PlantUML
• Mermaid: Generate diagrams using Mermaid JS
• JSON: Output schema in JSON format

Usage

As a Library

Here's a simple example of how to use DBerd as a library to extract a schema from CockroachDB and generate a D2 diagram:

package main

import (
	"context"
	"log"
	"os"

	"github.com/holydocs/dberd/source/cockroach"
	"github.com/holydocs/dberd/target/d2"
)

func main() {
	cockroachSource, err := cockroach.NewSource("postgres://user@host:port/db?sslmode=disable")
	if err != nil {
		log.Fatalf("creating new cockroach source: %v", err)
	}
	defer cockroachSource.Close()

	ctx := context.Background()

	schema, err := cockroachSource.ExtractSchema(ctx)
	if err != nil {
		log.Fatalf("extracting cockroach schema: %v", err)
	}

	d2Target, err := d2.NewTarget()
	if err != nil {
		log.Fatalf("creating d2 target: %v", err)
	}

	formattedSchema, err := d2Target.FormatSchema(ctx, schema)
	if err != nil {
		log.Fatalf("formatting cockroach schema into d2: %v", err)
	}

	diagram, err := d2Target.RenderSchema(ctx, formattedSchema)
	if err != nil {
		log.Fatalf("rendering cockroach schema into d2: %v", err)
	}

	err = os.WriteFile("out.svg", diagram, 0600)
	if err != nil {
		log.Fatalf("writing file: %v", err)
	}
}

As a CLI Tool

DBerd can be used as a command-line tool to extract and visualize database schemas:

# Extract schema and generate D2 diagram
dberd --source cockroach \
      --target d2 \
      --format-to-file schema.d2 \
      --render-to-file schema.svg \
      --source-dsn "postgres://user@host:port/db?sslmode=disable"

For example, if a Cockroach database has a schema like:

CREATE TABLE users (
	id INT PRIMARY KEY,
	name VARCHAR(255) NOT NULL,
	email VARCHAR(255) NOT NULL,
	created_at TIMESTAMP DEFAULT current_timestamp()
);

CREATE TABLE roles (
	id INT PRIMARY KEY,
	name VARCHAR(50) NOT NULL,
	description STRING,
	created_at TIMESTAMP DEFAULT current_timestamp()
);

CREATE TABLE user_roles (
	user_id INT NOT NULL,
	role_id INT NOT NULL,
	assigned_at TIMESTAMP DEFAULT current_timestamp(),
	PRIMARY KEY (user_id, role_id),
	FOREIGN KEY (user_id) REFERENCES users(id),
	FOREIGN KEY (role_id) REFERENCES roles(id)
);

CREATE TABLE posts (
	id INT PRIMARY KEY,
	user_id INT NOT NULL,
	title VARCHAR(255) NOT NULL,
	content STRING,
	created_at TIMESTAMP DEFAULT current_timestamp(),
	FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE categories (
	id INT PRIMARY KEY,
	name VARCHAR(100) NOT NULL,
	description STRING,
	parent_id INT,
	created_at TIMESTAMP DEFAULT current_timestamp(),
	FOREIGN KEY (parent_id) REFERENCES categories(id)
);

CREATE TABLE post_categories (
	post_id INT NOT NULL,
	category_id INT NOT NULL,
	PRIMARY KEY (post_id, category_id),
	FOREIGN KEY (post_id) REFERENCES posts(id),
	FOREIGN KEY (category_id) REFERENCES categories(id)
);

CREATE TABLE comments (
	id INT PRIMARY KEY,
	post_id INT NOT NULL,
	user_id INT NOT NULL,
	content STRING NOT NULL,
	created_at TIMESTAMP DEFAULT current_timestamp(),
	FOREIGN KEY (post_id) REFERENCES posts(id),
	FOREIGN KEY (user_id) REFERENCES users(id)
);

COMMENT ON COLUMN users.email IS 'User email address';
COMMENT ON COLUMN roles.description IS 'Role description and permissions';
COMMENT ON COLUMN categories.parent_id IS 'Self-referencing foreign key for category hierarchy';

The resulting schema.d2 will be:

direction: right

# Tables
public.users: {
  shape: "sql_table"
  id: "INT8 NOT NULL" { constraint: [primary_key] }
  name: "VARCHAR(255) NOT NULL"
  email: "VARCHAR(255) NOT NULL"
  created_at: "TIMESTAMP DEFAULT current_timestamp()"
}
public.roles: {
  shape: "sql_table"
  id: "INT8 NOT NULL" { constraint: [primary_key] }
  name: "VARCHAR(50) NOT NULL"
  description: "STRING"
  created_at: "TIMESTAMP DEFAULT current_timestamp()"
}
public.user_roles: {
  shape: "sql_table"
  user_id: "INT8 NOT NULL" { constraint: [primary_key] }
  role_id: "INT8 NOT NULL" { constraint: [primary_key] }
  assigned_at: "TIMESTAMP DEFAULT current_timestamp()"
}
public.posts: {
  shape: "sql_table"
  id: "INT8 NOT NULL" { constraint: [primary_key] }
  user_id: "INT8 NOT NULL"
  title: "VARCHAR(255) NOT NULL"
  content: "STRING"
  created_at: "TIMESTAMP DEFAULT current_timestamp()"
}
public.categories: {
  shape: "sql_table"
  id: "INT8 NOT NULL" { constraint: [primary_key] }
  name: "VARCHAR(100) NOT NULL"
  description: "STRING"
  parent_id: "INT8"
  created_at: "TIMESTAMP DEFAULT current_timestamp()"
}
public.post_categories: {
  shape: "sql_table"
  post_id: "INT8 NOT NULL" { constraint: [primary_key] }
  category_id: "INT8 NOT NULL" { constraint: [primary_key] }
}
public.comments: {
  shape: "sql_table"
  id: "INT8 NOT NULL" { constraint: [primary_key] }
  post_id: "INT8 NOT NULL"
  user_id: "INT8 NOT NULL"
  content: "STRING NOT NULL"
  created_at: "TIMESTAMP DEFAULT current_timestamp()"
}

# References
public.categories.parent_id -> public.categories.id
public.comments.post_id -> public.posts.id
public.comments.user_id -> public.users.id
public.post_categories.category_id -> public.categories.id
public.post_categories.post_id -> public.posts.id
public.posts.user_id -> public.users.id
public.user_roles.role_id -> public.roles.id
public.user_roles.user_id -> public.users.id

And the resulting schema.svg will be:

Documentation

Overview

Package dberd provides functionality for database schema extraction, formatting, and rendering.

Index

• func NewUnsupportedFormatError(given, expected TargetType) error
• type Column
• type FormattedSchema
• type Reference
• type Schema
  • func (s *Schema) Sort()
• type SchemaExtractor
• type SchemaFormatter
• type SchemaRenderer
• type Source
• type Table
• type TableColumn
• type Target
• type TargetCapabilities
• type TargetType
• type UnsupportedFormatError
  • func (err *UnsupportedFormatError) Error() string

Functions

func NewUnsupportedFormatError

func NewUnsupportedFormatError(given, expected TargetType) error

NewUnsupportedFormatError creates a new UnsupportedFormatError.

Types

type Column

type Column struct {
	Name       string `json:"name"`
	Comment    string `json:"comment,omitempty"`
	Definition string `json:"definition"`
	IsPrimary  bool   `json:"is_primary"`
}

Column represents a database table column.

type FormattedSchema

type FormattedSchema struct {
	Type TargetType `json:"type"`
	Data []byte     `json:"data"`
}

FormattedSchema represents a formatted database schema.

type Reference

type Reference struct {
	Source TableColumn `json:"source"`
	Target TableColumn `json:"target"`
}

Reference represents a foreign key relationship between two table columns.

type Schema

type Schema struct {
	Tables     []Table     `json:"tables"`
	References []Reference `json:"references"`
}

Schema represents a complete database schema with tables and their references.

func (*Schema) Sort

func (s *Schema) Sort()

Sort sorts the schema's tables and references in a consistent order.

type SchemaExtractor

type SchemaExtractor interface {
	ExtractSchema(ctx context.Context) (Schema, error)
}

SchemaExtractor defines the interface for extracting database schema.

type SchemaFormatter

type SchemaFormatter interface {
	FormatSchema(ctx context.Context, s Schema) (FormattedSchema, error)
}

SchemaFormatter defines the interface for formatting database schema.

type SchemaRenderer

type SchemaRenderer interface {
	RenderSchema(ctx context.Context, fs FormattedSchema) ([]byte, error)
}

SchemaRenderer defines the interface for rendering formatted database schema.

type Source

type Source interface {
	SchemaExtractor
	io.Closer
}

Source defines the interface for database schema sources that can extract schema information.

type Table

type Table struct {
	Name    string   `json:"name"`
	Columns []Column `json:"columns"`
}

Table represents a database table with its columns.

type TableColumn

type TableColumn struct {
	Table  string `json:"table"`
	Column string `json:"column"`
}

TableColumn represents a reference to a specific column in a table.

type Target

type Target interface {
	SchemaFormatter
	SchemaRenderer
	Capabilities() TargetCapabilities
}

Target defines the interface for database schema targets that can format and render schema information.

type TargetCapabilities

type TargetCapabilities struct {
	Format bool
	Render bool
}

TargetCapabilities represents the capabilities of a Target implementation.

type TargetType

type TargetType string

TargetType represents the type of language for describing database schema.

type UnsupportedFormatError

type UnsupportedFormatError struct {
	// contains filtered or unexported fields
}

UnsupportedFormatError represents an error when an unsupported format is provided.

func (*UnsupportedFormatError) Error

func (err *UnsupportedFormatError) Error() string

Error implements the error interface for UnsupportedFormatError.