# Directory Structure
```
├── cmd
│ └── db-mcp
│ └── main.go
├── docker-compose.yml
├── go.mod
├── go.sum
├── internal
│ ├── app
│ │ ├── app.go
│ │ ├── connect.go
│ │ ├── flavor.go
│ │ └── query.go
│ └── db
│ └── db.go
├── LICENSE
└── README.md
```
# Files
--------------------------------------------------------------------------------
/README.md:
--------------------------------------------------------------------------------
```markdown
1 | # db-mcp
2 |
3 | A small little utility that allows AI assistants that support the Model Context Protocol (MCP) to query databases.
4 |
5 | ## Features
6 |
7 | - Support for multiple database systems:
8 | - PostgreSQL
9 | - MySQL
10 | - SQLite
11 | - Simple database connection using DSN (Data Source Name)
12 |
13 | ### Supported Tools
14 |
15 | - `connect`: Connect to a database using a DSN.
16 | - `query`: Run a query on a database and return the results in CSV format.
17 | - `flavor`: Get the flavor of a connected database.
18 |
19 |
20 | ## Installation
21 |
22 | ```bash
23 | go install github.com/alx99/db-mcp/cmd/db-mcp@latest
24 | ```
25 |
26 | ## Running
27 |
28 | ```bash
29 | db-mcp
30 | ```
31 |
32 | ### Flags
33 |
34 | - `-default-dsn`: The DSN to use for the default database.
35 |
36 | ## FAQ
37 |
38 | - How can I construct a DSN?
39 | - [PostgreSQL](https://stackoverflow.com/a/20722229)
40 | - [MySQL](https://github.com/go-sql-driver/mysql?tab=readme-ov-file#dsn-data-source-name) (`mysql://` prefix needed)
41 | - [SQLite](https://github.com/mattn/go-sqlite3?tab=readme-ov-file#connection-string) (`sqlite://` prefix needed)
42 |
```
--------------------------------------------------------------------------------
/internal/app/flavor.go:
--------------------------------------------------------------------------------
```go
1 | package app
2 |
3 | import (
4 | "context"
5 |
6 | "github.com/mark3labs/mcp-go/mcp"
7 | )
8 |
9 | func (a *App) setupFlavorTool() {
10 | flavorTool := mcp.NewTool("flavor",
11 | mcp.WithDescription("Get the database flavor for a specific database"),
12 | mcp.WithString("database",
13 | mcp.Description("Which database to get the flavor for. If not provided, the default database will be used."),
14 | ),
15 | )
16 |
17 | a.server.AddTool(flavorTool, func(ctx context.Context, request mcp.CallToolRequest) (*mcp.CallToolResult, error) {
18 | db, err := a.getDBWithDefaultFallback(request.Params.Arguments["database"])
19 | if err != nil {
20 | return mcp.NewToolResultError(err.Error()), nil
21 | }
22 |
23 | return mcp.NewToolResultText(db.Flavor()), nil
24 | })
25 | }
26 |
```
--------------------------------------------------------------------------------
/cmd/db-mcp/main.go:
--------------------------------------------------------------------------------
```go
1 | package main
2 |
3 | import (
4 | "context"
5 | "flag"
6 | "log"
7 | "os"
8 | "os/signal"
9 | "syscall"
10 |
11 | "github.com/alx99/db-mcp/internal/app"
12 | "github.com/alx99/db-mcp/internal/db"
13 | )
14 |
15 | // Examples:
16 | // PostgreSQL: postgresql://postgres:postgres@localhost:5432/postgres
17 | // MySQL: mysql://[username[:password]@][protocol[(address)]]/dbname[?param1=value1&...¶mN=valueN]
18 | var (
19 | dbDSN = flag.String("default-dsn", "", "The DSN to use for the default database")
20 | )
21 |
22 | func main() {
23 | ctx, cancel := signal.NotifyContext(context.Background(), os.Interrupt, syscall.SIGQUIT, syscall.SIGTERM)
24 | defer cancel()
25 | flag.Parse()
26 |
27 | app, err := app.New(ctx, app.Options{
28 | DefaultDBOptions: db.Options{
29 | DSN: *dbDSN,
30 | },
31 | })
32 | if err != nil {
33 | log.Fatalf("Failed to create the application: %v", err)
34 | }
35 |
36 | if err := app.Run(); err != nil {
37 | log.Fatalf("Failed to run the application: %v", err)
38 | }
39 | }
40 |
```
--------------------------------------------------------------------------------
/internal/app/query.go:
--------------------------------------------------------------------------------
```go
1 | package app
2 |
3 | import (
4 | "context"
5 |
6 | "github.com/joho/sqltocsv"
7 | "github.com/mark3labs/mcp-go/mcp"
8 | )
9 |
10 | func (a *App) setupQueryTool() {
11 | queryTool := mcp.NewTool("query",
12 | mcp.WithDescription("Perform a query on a database"),
13 | mcp.WithString("query",
14 | mcp.Required(),
15 | mcp.Description("The query to perform"),
16 | ),
17 | mcp.WithString("database",
18 | mcp.Description("Which database to query. If not provided, the default database will be used."),
19 | ),
20 | )
21 |
22 | a.server.AddTool(queryTool, func(ctx context.Context, request mcp.CallToolRequest) (*mcp.CallToolResult, error) {
23 | query := request.Params.Arguments["query"].(string)
24 | db, err := a.getDBWithDefaultFallback(request.Params.Arguments["database"])
25 | if err != nil {
26 | return mcp.NewToolResultError(err.Error()), nil
27 | }
28 |
29 | result, err := db.Query(ctx, query)
30 | if err != nil {
31 | return mcp.NewToolResultError(err.Error()), nil
32 | }
33 | defer result.Close()
34 |
35 | conv := sqltocsv.New(result)
36 | csv, err := conv.WriteString()
37 | if err != nil {
38 | return mcp.NewToolResultError(err.Error()), nil
39 | }
40 |
41 | return mcp.NewToolResultText(csv), nil
42 | })
43 | }
44 |
```
--------------------------------------------------------------------------------
/docker-compose.yml:
--------------------------------------------------------------------------------
```yaml
1 | services:
2 | db:
3 | container_name: postgres
4 | image: postgres:latest
5 | environment:
6 | POSTGRES_USER: ${POSTGRES_USER:-postgres}
7 | POSTGRES_PASSWORD: ${POSTGRES_PASSWORD:-changeme}
8 | POSTGRES_DB: ${POSTGRES_DB:-mydb}
9 | PGDATA: /data/postgres
10 | volumes:
11 | - postgres_data:/data/postgres
12 | ports:
13 | - "5432:5432"
14 | restart: unless-stopped
15 | healthcheck:
16 | test: ["CMD-SHELL", "pg_isready -d $${POSTGRES_DB} -U $${POSTGRES_USER}"]
17 | interval: 10s
18 | timeout: 5s
19 | retries: 5
20 | start_period: 10s
21 |
22 | mysql:
23 | container_name: mysql
24 | image: mysql:8.0
25 | environment:
26 | MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD:-changeme}
27 | MYSQL_DATABASE: ${MYSQL_DATABASE:-mydb}
28 | MYSQL_USER: ${MYSQL_USER:-mysql}
29 | MYSQL_PASSWORD: ${MYSQL_PASSWORD:-changeme}
30 | volumes:
31 | - mysql_data:/var/lib/mysql
32 | ports:
33 | - "3306:3306"
34 | restart: unless-stopped
35 | healthcheck:
36 | test: ["CMD", "mysqladmin", "ping", "-h", "localhost", "-u", "root", "-p$${MYSQL_ROOT_PASSWORD}"]
37 | interval: 10s
38 | timeout: 5s
39 | retries: 5
40 | start_period: 30s
41 |
42 | volumes:
43 | postgres_data:
44 | mysql_data:
```
--------------------------------------------------------------------------------
/internal/app/connect.go:
--------------------------------------------------------------------------------
```go
1 | package app
2 |
3 | import (
4 | "context"
5 | "fmt"
6 |
7 | "github.com/alx99/db-mcp/internal/db"
8 | "github.com/mark3labs/mcp-go/mcp"
9 | )
10 |
11 | func (a *App) setupConnectTool() {
12 | connectTool := mcp.NewTool("connect",
13 | mcp.WithDescription("Connect to a database using a DSN"),
14 | mcp.WithString("database_dsn",
15 | mcp.Required(),
16 | mcp.Description("The DSN of the database to connect to"),
17 | ),
18 | mcp.WithString("name",
19 | mcp.Required(),
20 | mcp.Description("A name to identify this database connection. This database name can be used in subsequent queries."),
21 | ),
22 | )
23 |
24 | a.server.AddTool(connectTool, func(ctx context.Context, request mcp.CallToolRequest) (*mcp.CallToolResult, error) {
25 | dsn := request.Params.Arguments["database_dsn"].(string)
26 | name, ok := request.Params.Arguments["name"]
27 | if !ok {
28 | return mcp.NewToolResultError("name is required"), nil
29 | }
30 |
31 | opts := db.Options{DSN: dsn}
32 |
33 | db, err := db.New(opts)
34 | if err != nil {
35 | return mcp.NewToolResultError(err.Error()), nil
36 | }
37 |
38 | nameString, res := mustString(name)
39 | if res != nil {
40 | return res, nil
41 | }
42 |
43 | if err := db.Ping(ctx); err != nil {
44 | return mcp.NewToolResultError(fmt.Sprintf("failed to ping the database: %v", err)), nil
45 | }
46 |
47 | a.mu.Lock()
48 | defer a.mu.Unlock()
49 | if existing, exists := a.databases[nameString]; exists {
50 | existing.Close()
51 | }
52 | a.databases[nameString] = db
53 |
54 | return mcp.NewToolResultText("Successfully connected to database with name: " + nameString), nil
55 | })
56 | }
57 |
```
--------------------------------------------------------------------------------
/internal/db/db.go:
--------------------------------------------------------------------------------
```go
1 | package db
2 |
3 | import (
4 | "context"
5 | "database/sql"
6 | "errors"
7 | "fmt"
8 | "strings"
9 |
10 | // for mysql
11 | _ "github.com/go-sql-driver/mysql"
12 | "github.com/jackc/pgx/v5"
13 | "github.com/jackc/pgx/v5/stdlib"
14 |
15 | // for sqlite
16 | _ "github.com/mattn/go-sqlite3"
17 | )
18 |
19 | // DB represents a database
20 | type DB struct {
21 | db *sql.DB
22 | flavor string
23 | }
24 |
25 | // Options represents the options for a DB
26 | type Options struct {
27 | // DSN is the DSN of the database
28 | DSN string
29 | }
30 |
31 | // New creates a new DB instance
32 | func New(opts Options) (*DB, error) {
33 | var db *sql.DB
34 | var flavor string
35 | var err error
36 |
37 | switch {
38 | case strings.HasPrefix(opts.DSN, "postgresql://"):
39 | config, err := pgx.ParseConfig(opts.DSN)
40 | if err != nil {
41 | return nil, err
42 | }
43 |
44 | db = stdlib.OpenDB(*config)
45 | flavor = "postgresql"
46 |
47 | case strings.HasPrefix(opts.DSN, "mysql://"):
48 | opts.DSN = strings.TrimPrefix(opts.DSN, "mysql://")
49 |
50 | db, err = sql.Open("mysql", opts.DSN)
51 | if err != nil {
52 | return nil, fmt.Errorf("failed to connect to mysql: %v", err)
53 | }
54 | flavor = "mysql"
55 |
56 | case strings.HasPrefix(opts.DSN, "sqlite://"):
57 | opts.DSN = strings.TrimPrefix(opts.DSN, "sqlite://")
58 |
59 | db, err = sql.Open("sqlite3", opts.DSN)
60 | if err != nil {
61 | return nil, fmt.Errorf("failed to connect to sqlite: %v", err)
62 | }
63 | flavor = "sqlite"
64 |
65 | default:
66 | return nil, errors.New("unsupported dsn scheme")
67 | }
68 |
69 | return &DB{db: db, flavor: flavor}, nil
70 | }
71 |
72 | // Close closes the database
73 | func (d *DB) Close() error {
74 | return d.db.Close()
75 | }
76 |
77 | // Flavor returns the flavor of the database
78 | func (d *DB) Flavor() string {
79 | return d.flavor
80 | }
81 |
82 | // Ping pings the database
83 | func (d *DB) Ping(ctx context.Context) error {
84 | return d.db.PingContext(ctx)
85 | }
86 |
87 | // Query executes a query
88 | func (d *DB) Query(ctx context.Context, query string, args ...any) (*sql.Rows, error) {
89 | return d.db.QueryContext(ctx, query, args...)
90 | }
91 |
```
--------------------------------------------------------------------------------
/internal/app/app.go:
--------------------------------------------------------------------------------
```go
1 | package app
2 |
3 | import (
4 | "context"
5 | "fmt"
6 | "sync"
7 |
8 | "github.com/alx99/db-mcp/internal/db"
9 | "github.com/mark3labs/mcp-go/mcp"
10 | "github.com/mark3labs/mcp-go/server"
11 | )
12 |
13 | // App represents the application
14 | type App struct {
15 | defaultDB *db.DB
16 | databases map[string]*db.DB
17 | mu sync.RWMutex
18 |
19 | server *server.MCPServer
20 | }
21 |
22 | // Options represents the options for the app
23 | type Options struct {
24 | // DefaultDBOptions are the options for the default database
25 | DefaultDBOptions db.Options
26 | }
27 |
28 | // New creates a new App
29 | func New(ctx context.Context, opts Options) (*App, error) {
30 | s := server.NewMCPServer(
31 | "Database Interactor",
32 | "1.0.0",
33 | server.WithResourceCapabilities(true, true),
34 | server.WithLogging(),
35 | )
36 |
37 | app := App{
38 | server: s,
39 | databases: make(map[string]*db.DB),
40 | mu: sync.RWMutex{},
41 | }
42 |
43 | app.setupQueryTool()
44 | app.setupConnectTool()
45 | app.setupFlavorTool()
46 |
47 | if opts.DefaultDBOptions.DSN != "" {
48 | var err error
49 | app.defaultDB, err = db.New(opts.DefaultDBOptions)
50 | if err != nil {
51 | return nil, err
52 | }
53 |
54 | if err := app.defaultDB.Ping(ctx); err != nil {
55 | return nil, err
56 | }
57 | }
58 |
59 | return &app, nil
60 | }
61 |
62 | // Run runs the application
63 | func (a *App) Run() error {
64 | if err := server.ServeStdio(a.server); err != nil {
65 | fmt.Printf("Server error: %v\n", err)
66 | }
67 | return nil
68 | }
69 |
70 | func (a *App) getDB(name string) (*db.DB, error) {
71 | a.mu.RLock()
72 | defer a.mu.RUnlock()
73 |
74 | db, ok := a.databases[name]
75 | if !ok {
76 | return nil, fmt.Errorf("database %q not found", name)
77 | }
78 |
79 | return db, nil
80 | }
81 |
82 | // getDBWithDefaultFallback gets a database. If the name is not provided, the default database will be used.
83 | func (a *App) getDBWithDefaultFallback(name any) (*db.DB, error) {
84 | getDefaultDB := func() (*db.DB, error) {
85 | if a.defaultDB == nil {
86 | return nil, fmt.Errorf("no default database configured, use the `connect` tool to connect to a database")
87 | }
88 |
89 | return a.defaultDB, nil
90 | }
91 |
92 | if name == nil {
93 | return getDefaultDB()
94 | }
95 |
96 | nameStr, ok := name.(string)
97 | if !ok {
98 | return nil, fmt.Errorf("provided value is not a string")
99 | }
100 | if nameStr == "" {
101 | return getDefaultDB()
102 | }
103 |
104 | a.mu.RLock()
105 | defer a.mu.RUnlock()
106 |
107 | db, ok := a.databases[nameStr]
108 | if !ok {
109 | return nil, fmt.Errorf("database %q not found", nameStr)
110 | }
111 |
112 | return db, nil
113 | }
114 |
115 | func mustString(v any) (string, *mcp.CallToolResult) {
116 | if v == nil {
117 | return "", mcp.NewToolResultError("value is nil")
118 | }
119 |
120 | if s, ok := v.(string); ok {
121 | return s, nil
122 | }
123 |
124 | return "", mcp.NewToolResultError("provided value is not a string")
125 | }
126 |
```