pduckdb

README

go-pduckdb is a PureGO driver for DuckDB

Introduction

A DuckDB module for Go which doesn't require CGO. Uses purego to interface with DuckDB's native library.

Features

• Pure Go implementation - no CGO required
• Support for most DuckDB data types including DATE, TIME, TIMESTAMP, and DECIMAL
• SQL query execution and result handling
• Database access through standard database/sql interface
• Clear error reporting and propagation
• Cross-platform compatibility
• Parameter binding with automatic type conversion
• Support for prepared statements with parameter type inference
• Transaction support

Installation

go get github.com/fpt/go-pduckdb

Also, make sure to install DuckDB on your platform:

macOS

brew install duckdb

Typically, /opt/homebrew/lib/libduckdb.dylib is installed.

Linux (Ubuntu/Debian)

curl -sSL https://github.com/duckdb/duckdb/releases/download/v1.2.2/libduckdb-linux-amd64.zip -o archive.zip
sudo unzip -j archive.zip libduckdb.so -d /usr/local/lib
sudo ldconfig
rm archive.zip

You can find a download URL in official releases of DuckDB. Assets starting with libduckdb- contains glibc build of libduckdb.so.

For other Linux, Check official instruction: Building DuckDB.

Windows

Download the DuckDB CLI from the official website and place the DLL in your system path.

Library Path Configuration

go-pduckdb searches for the DuckDB library in several locations. You can configure the search path using environment variables:

• DUCKDB_LIBRARY_PATH - specify the exact path to the DuckDB library file
• DYLD_LIBRARY_PATH - on macOS, specify directories to search for the DuckDB library
• LD_LIBRARY_PATH - on Linux, specify directories to search for the DuckDB library

Example usage:

# Specify exact library path
DUCKDB_LIBRARY_PATH=/path/to/libduckdb.dylib ./your_program

# Or specify directory to search (macOS)
DYLD_LIBRARY_PATH=/path/to/lib ./your_program

# Or specify directory to search (Linux)
LD_LIBRARY_PATH=/path/to/lib ./your_program

If no environment variables are set, the library will be searched in standard system locations.

Usage Examples

Using Standard database/sql Interface

go-pduckdb implements the Go standard database/sql interface, allowing you to work with DuckDB like any other SQL database in Go:

package main

import (
	"database/sql"
	"fmt"
	"log"
	
	_ "github.com/fpt/go-pduckdb" // Import for driver registration
)

func main() {
	// Open a database connection
	db, err := sql.Open("duckdb", "example.db")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()
	
	// Create a table
	_, err = db.Exec(`CREATE TABLE IF NOT EXISTS users (
		id INTEGER PRIMARY KEY, 
		name VARCHAR, 
		email VARCHAR
	)`)
	if err != nil {
		log.Fatal(err)
	}
	
	// Insert data
	_, err = db.Exec(`INSERT INTO users (id, name, email) VALUES (?, ?, ?)`, 
		1, "John Doe", "john@example.com")
	if err != nil {
		log.Fatal(err)
	}
	
	// Query data
	rows, err := db.Query("SELECT id, name, email FROM users")
	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()
	
	// Process results
	for rows.Next() {
		var id int
		var name, email string
		if err := rows.Scan(&id, &name, &email); err != nil {
			log.Fatal(err)
		}
		fmt.Printf("User %d: %s (%s)\n", id, name, email)
	}
}

For a more comprehensive example, see the database/sql example.

Parameter Binding and Type Conversion

go-pduckdb features a sophisticated type conversion system that automatically handles type conversions for prepared statement parameters:

// Prepare a statement
stmt, err := conn.Prepare("INSERT INTO users (id, name, created_date) VALUES (?, ?, ?)")
if err != nil {
    log.Fatal(err)
}
defer stmt.Close()

// Execute with different parameter types
// The driver will automatically convert these to the appropriate types
err = stmt.Execute(
    1,                                 // int -> INTEGER
    "John Doe",                        // string -> VARCHAR
    time.Date(2025, 5, 3, 0, 0, 0, 0, time.UTC),  // time.Time -> DATE
)

Supported conversions include:

• Go bool -> DuckDB BOOLEAN
• Go numeric types -> DuckDB numeric types with range validation
• Go string -> Various DuckDB types based on content
• Go []byte -> DuckDB BLOB
• Go time.Time -> DuckDB DATE, TIME, or TIMESTAMP
• Custom Date, Time, and Interval types for precise control

For more examples, check the example directory.

API Documentation

Standard database/sql Interface

go-pduckdb registers itself as a driver named "duckdb" with the standard database/sql package, supporting:

• Connection management (Open, Close)
• Query execution (Exec, Query)
• Prepared statements
• Transactions
• Context handling
• Parameter binding

Native API

go-pduckdb also provides a native API for more direct interaction with DuckDB:

• DuckDB: Represents a database instance
• DuckDBConnection: Handles connections to the database
• DuckDBResult: Manages query results
• DuckDBDate, DuckDBTime, DuckDBTimestamp: Date and time types

Date and Time Handling

go-pduckdb provides native Go type conversions for DuckDB's date and time types:

// Get date value
dateVal, hasValue := result.ValueDate(columnIndex, rowIndex)
if hasValue {
    fmt.Println("Date:", dateVal.Format("2006-01-02"))
}

// Get timestamp value
tsVal, hasValue := result.ValueTimestamp(columnIndex, rowIndex)
if hasValue {
    fmt.Println("Timestamp:", tsVal.Format("2006-01-02 15:04:05.000000"))
}

Limitations

Unsupported types (yet)

• List
• Struct

Unsupported types due to purego limitation

These types use struct return value which is not supported by purego in some platform.

• Blob
• Interval

Project Structure

This project follows the standard Go project layout:

go-pduckdb/
├── conn.go          # Connection handling
├── driver.go        # database/sql driver implementation
├── error.go         # Error handling
├── extensions.go    # DuckDB extensions support
├── pduckdb.go       # Core functionality
├── result.go        # Result processing
├── *_test.go        # Unit tests
├── example/         # Example code
│   ├── columntypes/     # Column type demonstration
│   ├── databasesql/     # database/sql usage examples
│   ├── databasesql2/    # Additional database/sql examples
│   ├── enhancedtypes/   # Enhanced type support examples
│   ├── json/            # JSON handling examples
│   └── simple/          # Simple API usage examples
├── internal/        # Internal implementation
│   ├── convert/         # Type conversion utilities
│   ├── duckdb/          # Low-level DuckDB bindings
│   └── integ/           # Integration test infrastructure
└── types/           # Custom type definitions
    ├── datetime.go      # Date/time type implementations
    ├── json.go          # JSON support
    ├── slice.go         # Slice handling
    └── sql.go           # SQL specific types

Contributing

Contributions are welcome! Please read our contributing guidelines before submitting a pull request.

License

This project is licensed under the MIT License - see the LICENSE file for details.

References

DuckDB

• Official Documentation
• C API Source
• C Header

Documentation

Overview

Package pduckdb provides a pure Go DuckDB driver

Index

• type Conn
  • func (c *Conn) Begin() (driver.Tx, error)
  • func (c *Conn) BeginTx(ctx context.Context, opts driver.TxOptions) (driver.Tx, error)
  • func (c *Conn) Close() error
  • func (c *Conn) ExecContext(ctx context.Context, query string, args []driver.NamedValue) (driver.Result, error)
  • func (c *Conn) Ping(ctx context.Context) error
  • func (c *Conn) Prepare(query string) (driver.Stmt, error)
  • func (c *Conn) PrepareContext(ctx context.Context, query string) (driver.Stmt, error)
  • func (c *Conn) QueryContext(ctx context.Context, query string, args []driver.NamedValue) (driver.Rows, error)
• type Driver
  • func (d *Driver) Open(dsn string) (driver.Conn, error)
• type DuckDB
  • func NewDuckDB(path string) (*DuckDB, error)
  • func (d *DuckDB) Close()
  • func (d *DuckDB) Connect() (*duckdb.Connection, error)
• type ErrDuckDB
  • func (e ErrDuckDB) Error() string
• type Result
  • func (r *Result) LastInsertId() (int64, error)
  • func (r *Result) RowsAffected() (int64, error)
• type Rows
  • func (r *Rows) Close() error
  • func (r *Rows) ColumnTypeDatabaseTypeName(index int) string
  • func (r *Rows) ColumnTypeNullable(index int) (nullable, ok bool)
  • func (r *Rows) ColumnTypePrecisionScale(index int) (precision, scale int64, ok bool)
  • func (r *Rows) ColumnTypeScanType(index int) reflect.Type
  • func (r *Rows) Columns() []string
  • func (r *Rows) Next(dest []driver.Value) error
• type Stmt
  • func (s *Stmt) Close() error
  • func (s *Stmt) Exec(args []driver.Value) (driver.Result, error)
  • func (s *Stmt) ExecContext(ctx context.Context, args []driver.NamedValue) (driver.Result, error)
  • func (s *Stmt) NumInput() int
  • func (s *Stmt) Query(args []driver.Value) (driver.Rows, error)
  • func (s *Stmt) QueryContext(ctx context.Context, args []driver.NamedValue) (driver.Rows, error)
• type Tx
  • func (tx *Tx) Commit() error
  • func (tx *Tx) Rollback() error

Types

type Conn

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

Conn implements database/sql/driver.Conn

func (*Conn) Begin

func (c *Conn) Begin() (driver.Tx, error)

Begin starts and returns a new transaction.

func (*Conn) BeginTx

func (c *Conn) BeginTx(ctx context.Context, opts driver.TxOptions) (driver.Tx, error)

ConnBeginTx implements driver.ConnBeginTx

func (*Conn) Close

func (c *Conn) Close() error

Close closes the connection.

func (*Conn) ExecContext

func (c *Conn) ExecContext(ctx context.Context, query string, args []driver.NamedValue) (driver.Result, error)

ExecContext executes a query without returning any rows. Implements driver.ExecerContext

func (*Conn) Ping

func (c *Conn) Ping(ctx context.Context) error

Ping implements driver.Pinger

func (*Conn) Prepare

func (c *Conn) Prepare(query string) (driver.Stmt, error)

Prepare returns a prepared statement, bound to this connection.

func (*Conn) PrepareContext

func (c *Conn) PrepareContext(ctx context.Context, query string) (driver.Stmt, error)

PrepareContext returns a prepared statement with context support. Implements driver.ConnPrepareContext

func (*Conn) QueryContext

func (c *Conn) QueryContext(ctx context.Context, query string, args []driver.NamedValue) (driver.Rows, error)

QueryContext executes a query that may return rows. Implements driver.QueryerContext

type Driver

type Driver struct{}

Driver implements database/sql/driver.Driver

func (*Driver) Open

func (d *Driver) Open(dsn string) (driver.Conn, error)

Open returns a new connection to the database. The dsn is a connection string for the database.

type DuckDB

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

DuckDB represents a DuckDB database instance

func NewDuckDB

func NewDuckDB(path string) (*DuckDB, error)

NewDuckDB creates a new DuckDB instance

func (*DuckDB) Close

func (d *DuckDB) Close()

Close closes the database and releases resources

func (*DuckDB) Connect

func (d *DuckDB) Connect() (*duckdb.Connection, error)

Connect creates a new connection to the database

type ErrDuckDB

type ErrDuckDB struct {
	Message string
}

ErrDuckDB represents an error from DuckDB operations

func (ErrDuckDB) Error

func (e ErrDuckDB) Error() string

type Result

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

Result implements driver.Result

func (*Result) LastInsertId

func (r *Result) LastInsertId() (int64, error)

LastInsertId returns the database's auto-generated ID.

func (*Result) RowsAffected

func (r *Result) RowsAffected() (int64, error)

RowsAffected returns the number of rows affected.

type Rows

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

Rows implements database/sql/driver.Rows

func (*Rows) Close

func (r *Rows) Close() error

Close closes the rows iterator.

func (*Rows) ColumnTypeDatabaseTypeName

func (r *Rows) ColumnTypeDatabaseTypeName(index int) string

ColumnTypeDatabaseTypeName returns column type information. Implements RowsColumnTypeDatabaseTypeName

func (*Rows) ColumnTypeNullable

func (r *Rows) ColumnTypeNullable(index int) (nullable, ok bool)

ColumnTypeNullable returns column type information. Implements RowsColumnTypeNullable

func (*Rows) ColumnTypePrecisionScale

func (r *Rows) ColumnTypePrecisionScale(index int) (precision, scale int64, ok bool)

ColumnTypePrecisionScale returns column precision and scale. Implements RowsColumnTypePrecisionScale

func (*Rows) ColumnTypeScanType

func (r *Rows) ColumnTypeScanType(index int) reflect.Type

ColumnTypeScanType returns column type information. Implements RowsColumnTypeScanType

func (*Rows) Columns

func (r *Rows) Columns() []string

Columns returns the names of the columns.

func (*Rows) Next

func (r *Rows) Next(dest []driver.Value) error

Next is called to populate the next row of data into the provided slice.

type Stmt

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

Stmt implements database/sql/driver.Stmt

func (*Stmt) Close

func (s *Stmt) Close() error

Close closes the statement.

func (*Stmt) Exec

func (s *Stmt) Exec(args []driver.Value) (driver.Result, error)

Exec executes a query that doesn't return rows, such as an INSERT or UPDATE.

func (*Stmt) ExecContext

func (s *Stmt) ExecContext(ctx context.Context, args []driver.NamedValue) (driver.Result, error)

StmtExecContext implements driver.StmtExecContext

func (*Stmt) NumInput

func (s *Stmt) NumInput() int

NumInput returns the number of placeholder parameters.

func (*Stmt) Query

func (s *Stmt) Query(args []driver.Value) (driver.Rows, error)

Query executes a query that may return rows, such as a SELECT.

func (*Stmt) QueryContext

func (s *Stmt) QueryContext(ctx context.Context, args []driver.NamedValue) (driver.Rows, error)

StmtQueryContext implements driver.StmtQueryContext

type Tx

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

Tx implements database/sql/driver.Tx

func (*Tx) Commit

func (tx *Tx) Commit() error

Commit commits the transaction.

func (*Tx) Rollback

func (tx *Tx) Rollback() error

Rollback aborts the transaction.