# 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
# db-mcp
A small little utility that allows AI assistants that support the Model Context Protocol (MCP) to query databases.
## Features
- Support for multiple database systems:
- PostgreSQL
- MySQL
- SQLite
- Simple database connection using DSN (Data Source Name)
### Supported Tools
- `connect`: Connect to a database using a DSN.
- `query`: Run a query on a database and return the results in CSV format.
- `flavor`: Get the flavor of a connected database.
## Installation
```bash
go install github.com/alx99/db-mcp/cmd/db-mcp@latest
```
## Running
```bash
db-mcp
```
### Flags
- `-default-dsn`: The DSN to use for the default database.
## FAQ
- How can I construct a DSN?
- [PostgreSQL](https://stackoverflow.com/a/20722229)
- [MySQL](https://github.com/go-sql-driver/mysql?tab=readme-ov-file#dsn-data-source-name) (`mysql://` prefix needed)
- [SQLite](https://github.com/mattn/go-sqlite3?tab=readme-ov-file#connection-string) (`sqlite://` prefix needed)
```
--------------------------------------------------------------------------------
/internal/app/flavor.go:
--------------------------------------------------------------------------------
```go
package app
import (
"context"
"github.com/mark3labs/mcp-go/mcp"
)
func (a *App) setupFlavorTool() {
flavorTool := mcp.NewTool("flavor",
mcp.WithDescription("Get the database flavor for a specific database"),
mcp.WithString("database",
mcp.Description("Which database to get the flavor for. If not provided, the default database will be used."),
),
)
a.server.AddTool(flavorTool, func(ctx context.Context, request mcp.CallToolRequest) (*mcp.CallToolResult, error) {
db, err := a.getDBWithDefaultFallback(request.Params.Arguments["database"])
if err != nil {
return mcp.NewToolResultError(err.Error()), nil
}
return mcp.NewToolResultText(db.Flavor()), nil
})
}
```
--------------------------------------------------------------------------------
/cmd/db-mcp/main.go:
--------------------------------------------------------------------------------
```go
package main
import (
"context"
"flag"
"log"
"os"
"os/signal"
"syscall"
"github.com/alx99/db-mcp/internal/app"
"github.com/alx99/db-mcp/internal/db"
)
// Examples:
// PostgreSQL: postgresql://postgres:postgres@localhost:5432/postgres
// MySQL: mysql://[username[:password]@][protocol[(address)]]/dbname[?param1=value1&...¶mN=valueN]
var (
dbDSN = flag.String("default-dsn", "", "The DSN to use for the default database")
)
func main() {
ctx, cancel := signal.NotifyContext(context.Background(), os.Interrupt, syscall.SIGQUIT, syscall.SIGTERM)
defer cancel()
flag.Parse()
app, err := app.New(ctx, app.Options{
DefaultDBOptions: db.Options{
DSN: *dbDSN,
},
})
if err != nil {
log.Fatalf("Failed to create the application: %v", err)
}
if err := app.Run(); err != nil {
log.Fatalf("Failed to run the application: %v", err)
}
}
```
--------------------------------------------------------------------------------
/internal/app/query.go:
--------------------------------------------------------------------------------
```go
package app
import (
"context"
"github.com/joho/sqltocsv"
"github.com/mark3labs/mcp-go/mcp"
)
func (a *App) setupQueryTool() {
queryTool := mcp.NewTool("query",
mcp.WithDescription("Perform a query on a database"),
mcp.WithString("query",
mcp.Required(),
mcp.Description("The query to perform"),
),
mcp.WithString("database",
mcp.Description("Which database to query. If not provided, the default database will be used."),
),
)
a.server.AddTool(queryTool, func(ctx context.Context, request mcp.CallToolRequest) (*mcp.CallToolResult, error) {
query := request.Params.Arguments["query"].(string)
db, err := a.getDBWithDefaultFallback(request.Params.Arguments["database"])
if err != nil {
return mcp.NewToolResultError(err.Error()), nil
}
result, err := db.Query(ctx, query)
if err != nil {
return mcp.NewToolResultError(err.Error()), nil
}
defer result.Close()
conv := sqltocsv.New(result)
csv, err := conv.WriteString()
if err != nil {
return mcp.NewToolResultError(err.Error()), nil
}
return mcp.NewToolResultText(csv), nil
})
}
```
--------------------------------------------------------------------------------
/docker-compose.yml:
--------------------------------------------------------------------------------
```yaml
services:
db:
container_name: postgres
image: postgres:latest
environment:
POSTGRES_USER: ${POSTGRES_USER:-postgres}
POSTGRES_PASSWORD: ${POSTGRES_PASSWORD:-changeme}
POSTGRES_DB: ${POSTGRES_DB:-mydb}
PGDATA: /data/postgres
volumes:
- postgres_data:/data/postgres
ports:
- "5432:5432"
restart: unless-stopped
healthcheck:
test: ["CMD-SHELL", "pg_isready -d $${POSTGRES_DB} -U $${POSTGRES_USER}"]
interval: 10s
timeout: 5s
retries: 5
start_period: 10s
mysql:
container_name: mysql
image: mysql:8.0
environment:
MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD:-changeme}
MYSQL_DATABASE: ${MYSQL_DATABASE:-mydb}
MYSQL_USER: ${MYSQL_USER:-mysql}
MYSQL_PASSWORD: ${MYSQL_PASSWORD:-changeme}
volumes:
- mysql_data:/var/lib/mysql
ports:
- "3306:3306"
restart: unless-stopped
healthcheck:
test: ["CMD", "mysqladmin", "ping", "-h", "localhost", "-u", "root", "-p$${MYSQL_ROOT_PASSWORD}"]
interval: 10s
timeout: 5s
retries: 5
start_period: 30s
volumes:
postgres_data:
mysql_data:
```
--------------------------------------------------------------------------------
/internal/app/connect.go:
--------------------------------------------------------------------------------
```go
package app
import (
"context"
"fmt"
"github.com/alx99/db-mcp/internal/db"
"github.com/mark3labs/mcp-go/mcp"
)
func (a *App) setupConnectTool() {
connectTool := mcp.NewTool("connect",
mcp.WithDescription("Connect to a database using a DSN"),
mcp.WithString("database_dsn",
mcp.Required(),
mcp.Description("The DSN of the database to connect to"),
),
mcp.WithString("name",
mcp.Required(),
mcp.Description("A name to identify this database connection. This database name can be used in subsequent queries."),
),
)
a.server.AddTool(connectTool, func(ctx context.Context, request mcp.CallToolRequest) (*mcp.CallToolResult, error) {
dsn := request.Params.Arguments["database_dsn"].(string)
name, ok := request.Params.Arguments["name"]
if !ok {
return mcp.NewToolResultError("name is required"), nil
}
opts := db.Options{DSN: dsn}
db, err := db.New(opts)
if err != nil {
return mcp.NewToolResultError(err.Error()), nil
}
nameString, res := mustString(name)
if res != nil {
return res, nil
}
if err := db.Ping(ctx); err != nil {
return mcp.NewToolResultError(fmt.Sprintf("failed to ping the database: %v", err)), nil
}
a.mu.Lock()
defer a.mu.Unlock()
if existing, exists := a.databases[nameString]; exists {
existing.Close()
}
a.databases[nameString] = db
return mcp.NewToolResultText("Successfully connected to database with name: " + nameString), nil
})
}
```
--------------------------------------------------------------------------------
/internal/db/db.go:
--------------------------------------------------------------------------------
```go
package db
import (
"context"
"database/sql"
"errors"
"fmt"
"strings"
// for mysql
_ "github.com/go-sql-driver/mysql"
"github.com/jackc/pgx/v5"
"github.com/jackc/pgx/v5/stdlib"
// for sqlite
_ "github.com/mattn/go-sqlite3"
)
// DB represents a database
type DB struct {
db *sql.DB
flavor string
}
// Options represents the options for a DB
type Options struct {
// DSN is the DSN of the database
DSN string
}
// New creates a new DB instance
func New(opts Options) (*DB, error) {
var db *sql.DB
var flavor string
var err error
switch {
case strings.HasPrefix(opts.DSN, "postgresql://"):
config, err := pgx.ParseConfig(opts.DSN)
if err != nil {
return nil, err
}
db = stdlib.OpenDB(*config)
flavor = "postgresql"
case strings.HasPrefix(opts.DSN, "mysql://"):
opts.DSN = strings.TrimPrefix(opts.DSN, "mysql://")
db, err = sql.Open("mysql", opts.DSN)
if err != nil {
return nil, fmt.Errorf("failed to connect to mysql: %v", err)
}
flavor = "mysql"
case strings.HasPrefix(opts.DSN, "sqlite://"):
opts.DSN = strings.TrimPrefix(opts.DSN, "sqlite://")
db, err = sql.Open("sqlite3", opts.DSN)
if err != nil {
return nil, fmt.Errorf("failed to connect to sqlite: %v", err)
}
flavor = "sqlite"
default:
return nil, errors.New("unsupported dsn scheme")
}
return &DB{db: db, flavor: flavor}, nil
}
// Close closes the database
func (d *DB) Close() error {
return d.db.Close()
}
// Flavor returns the flavor of the database
func (d *DB) Flavor() string {
return d.flavor
}
// Ping pings the database
func (d *DB) Ping(ctx context.Context) error {
return d.db.PingContext(ctx)
}
// Query executes a query
func (d *DB) Query(ctx context.Context, query string, args ...any) (*sql.Rows, error) {
return d.db.QueryContext(ctx, query, args...)
}
```
--------------------------------------------------------------------------------
/internal/app/app.go:
--------------------------------------------------------------------------------
```go
package app
import (
"context"
"fmt"
"sync"
"github.com/alx99/db-mcp/internal/db"
"github.com/mark3labs/mcp-go/mcp"
"github.com/mark3labs/mcp-go/server"
)
// App represents the application
type App struct {
defaultDB *db.DB
databases map[string]*db.DB
mu sync.RWMutex
server *server.MCPServer
}
// Options represents the options for the app
type Options struct {
// DefaultDBOptions are the options for the default database
DefaultDBOptions db.Options
}
// New creates a new App
func New(ctx context.Context, opts Options) (*App, error) {
s := server.NewMCPServer(
"Database Interactor",
"1.0.0",
server.WithResourceCapabilities(true, true),
server.WithLogging(),
)
app := App{
server: s,
databases: make(map[string]*db.DB),
mu: sync.RWMutex{},
}
app.setupQueryTool()
app.setupConnectTool()
app.setupFlavorTool()
if opts.DefaultDBOptions.DSN != "" {
var err error
app.defaultDB, err = db.New(opts.DefaultDBOptions)
if err != nil {
return nil, err
}
if err := app.defaultDB.Ping(ctx); err != nil {
return nil, err
}
}
return &app, nil
}
// Run runs the application
func (a *App) Run() error {
if err := server.ServeStdio(a.server); err != nil {
fmt.Printf("Server error: %v\n", err)
}
return nil
}
func (a *App) getDB(name string) (*db.DB, error) {
a.mu.RLock()
defer a.mu.RUnlock()
db, ok := a.databases[name]
if !ok {
return nil, fmt.Errorf("database %q not found", name)
}
return db, nil
}
// getDBWithDefaultFallback gets a database. If the name is not provided, the default database will be used.
func (a *App) getDBWithDefaultFallback(name any) (*db.DB, error) {
getDefaultDB := func() (*db.DB, error) {
if a.defaultDB == nil {
return nil, fmt.Errorf("no default database configured, use the `connect` tool to connect to a database")
}
return a.defaultDB, nil
}
if name == nil {
return getDefaultDB()
}
nameStr, ok := name.(string)
if !ok {
return nil, fmt.Errorf("provided value is not a string")
}
if nameStr == "" {
return getDefaultDB()
}
a.mu.RLock()
defer a.mu.RUnlock()
db, ok := a.databases[nameStr]
if !ok {
return nil, fmt.Errorf("database %q not found", nameStr)
}
return db, nil
}
func mustString(v any) (string, *mcp.CallToolResult) {
if v == nil {
return "", mcp.NewToolResultError("value is nil")
}
if s, ok := v.(string); ok {
return s, nil
}
return "", mcp.NewToolResultError("provided value is not a string")
}
```