DuckDB Golang: A Comprehensive Guide to Installation and Usage

Author

Reads 1.2K

Detailed view of internal hard drive platters and read/write heads for data storage technology.
Credit: pexels.com, Detailed view of internal hard drive platters and read/write heads for data storage technology.

DuckDB is a modern, column-store database that's gaining popularity in the Golang community.

First, let's talk about why you'd want to use DuckDB with Golang. It's because DuckDB provides a high-performance data store that's designed to work seamlessly with Golang's concurrency features.

To get started with DuckDB Golang, you'll need to install the DuckDB SQL parser and compiler, which can be done via the go get command.

DuckDB supports various data types, including integers, floating point numbers, and strings, making it a versatile choice for a wide range of applications.

Installation

To install go-duckdb, you must build your binaries with CGO_ENABLED=1. This is necessary because go-duckdb uses CGO to make calls to DuckDB.

CGO is a tool that allows Go programs to call C code, and in this case, it's used to interact with the DuckDB database.

On a similar theme: Golang vs Go

A unique perspective: Watch Pinocchio

API

The DuckDB API provides several useful features for working with the database in Go. You can use the DuckDB Appender API to obtain a new Appender by passing a DuckDB connection to NewAppenderFromConn().

On a similar theme: Golang Rest

Credit: youtube.com, Stop Struggling with DataFrames – Try DuckDB for SQL on Pandas

The Appender API can be used to create a new Appender from a DuckDB connection. This is done by supplying a DuckDB connection to NewAppenderFromConn().

To use the DuckDB Profiling API, you need to obtain a connection, enable profiling for the connection, execute a query, and then retrieve any available profiling information.

Here's a step-by-step guide to using the Profiling API:

  • Obtain a connection
  • Enable profiling for the connection
  • Execute a query
  • Retrieve any available profiling information

The DuckDB driver conforms to the built-in database/sql interface.

Functions

Functions are a crucial part of DuckDB's Golang interface, and understanding them is essential for building efficient and scalable applications.

Scalar functions, for instance, are user-defined functions that must implement two key functions: Config and Executor. This interface is defined in the ScalarFunc type, which was added in version 1.8.1.

The Config function is used to configure the scalar function, while the Executor function is responsible for executing the function. By implementing these two functions, developers can create custom scalar functions that are tailored to their specific use cases.

Register Scalar UDF

Credit: youtube.com, Creating Scalar UDFs: Syntax and Basics

Registering a scalar UDF is a straightforward process. You'll need to use the `RegisterScalarUDF` function, which takes ownership of the scalar function, so be sure to pass it as a pointer.

The `RegisterScalarUDF` function requires a SQL connection and the function name. You'll also need to implement the `ScalarFunc` interface, which includes a `Config` function and an `Executor` function.

To register a set of user-defined scalar functions, you can use the `RegisterScalarUDFSet` function. This allows for overloading of scalar functions, enabling multiple implementations for a single function name.

The `RegisterScalarUDFSet` function requires a SQL connection, the function name, and a set of `ScalarFunc` functions. This makes it easy to add multiple implementations for a function like `my_length()`, which could handle different data types like lists or strings.

Here's an interesting read: Golang Set Env Variable

*Stmt) Bind

The *Stmt) Bind function is a low-level API that should be used with caution. It's used to bind parameters to a statement.

This function is a crucial step before executing a bound query. You can't use ExecBound or QueryBound without first calling Bind.

Credit: youtube.com, Episode 6: Statements, Variables, and Scoping

The Bind function is added in version 1.8.4 of the interface, which is a significant update. This suggests that this feature is relatively new and might not be widely adopted yet.

The Bind function is a prerequisite for using ExecBound, which executes a bound query that doesn't return rows, such as an INSERT or UPDATE. Similarly, Bind must be called before using QueryBound, which executes a bound query that may return rows, like a SELECT.

Additional reading: Golang Mod Update

GetSize in 1.7.1

GetSize in 1.7.1 is a game-changer for handling data chunks. This function returns the internal size of the data chunk.

The GetSize function is added in version 1.7.1, making it a relatively new addition to the functionality. It's a simple yet powerful tool for developers.

To get the size of a data chunk, you can use the GetSize function, which is straightforward and easy to use. Just call the function on your DataChunk object, and you'll get the size.

An artist's illustration of artificial intelligence (AI). This image represents storage of collected data in AI. It was created by Wes Cockx as part of the Visualising AI project launched ...
Credit: pexels.com, An artist's illustration of artificial intelligence (AI). This image represents storage of collected data in AI. It was created by Wes Cockx as part of the Visualising AI project launched ...

The GetSize function is useful for understanding how much data you're working with, which is essential for many applications. You can use this information to make informed decisions about your data management.

DataChunk objects are the foundation of many data-intensive applications, and being able to get their size is crucial for optimization and performance. GetSize helps you achieve that.

Error Handling

Error Handling is an essential part of any database application, and DuckDB's Golang implementation is no exception.

The DuckDB Golang library provides a way to retrieve the last error that occurred using the Error method of the Appender type. This method returns the last error that happened during the execution of the appender.

To use the Error method, you need to have an instance of the Appender type, which is typically obtained by calling the NewAppender function. This function returns a new appender object that you can use to execute queries and retrieve errors.

The Error method is a simple way to diagnose issues with your database application, and it's often the first step in debugging problems. By checking the last error that occurred, you can quickly identify the source of the issue and take corrective action.

For your interest: Golang Application

Query Execution

Credit: youtube.com, Querying JSON Documents with DuckDB

Query Execution is a crucial part of working with DuckDB in Go. You can execute queries that don't return rows, such as INSERT or UPDATE, using the ExecContext method.

The ExecContext method can be used on both Conn and Stmt objects. It's a low-level API that should be used with caution.

For queries that do return rows, such as SELECT, you can use the QueryContext method. This method prepares statements, executes them, and returns the result as an Apache Arrow array.RecordReader.

Conn Prepare

Conn Prepare is a crucial step in query execution. It returns a prepared statement bound to the connection, implementing the driver.Conn interface.

To prepare a statement, you can use the Prepare method, which is a low-level API. This method is essential for executing queries efficiently.

By preparing a statement, you can reuse it multiple times with different parameters, reducing the overhead of parsing and compiling the query each time. This can significantly improve the performance of your application.

For example, if you have a query that needs to be executed with different parameter values, you can prepare the statement once and then execute it multiple times with the new parameters.

*Stmt) QueryBound

Software Engineer Standing Beside Server Racks
Credit: pexels.com, Software Engineer Standing Beside Server Racks

QueryBound is a low-level API that executes a bound query that may return rows, such as a SELECT. It can only be used after Bind has been called, and it's a part of the driver.Stmt interface.

Using QueryBound can be a powerful way to execute queries, but it's essential to note that it's a deprecated function, and you should consider using ExecContext instead.

To use QueryBound, you need to first bind the parameters to the statement with the Bind function, which can only be called after the statement has been prepared.

QueryBound is designed to execute queries that may return rows, and it returns an Apache Arrow array.RecordReader as a result of the last executed statement. This is similar to how QueryContext works, but with QueryBound, you need to bind the parameters first.

In practice, I've found that using QueryBound can be a bit more complex than using ExecContext, but it can also provide more control over the query execution process. However, it's crucial to use it with caution, as it's a low-level API that can be prone to errors if not used correctly.

Query Context

Credit: youtube.com, Resolving DataFrame Accessibility Issues in dbt-duckdb Contexts

Query Context is a crucial aspect of working with DuckDB in Golang. It allows you to execute queries that may return rows, such as SELECT statements.

You can use the QueryContext method on a Conn object to execute a query, which implements the driver.QueryerContext interface. This method is available since version 1.8.4.

The QueryContext method on a Stmt object also executes a query that may return rows, available since version 1.8.4. It implements the driver.StmtQueryContext interface.

If you need to prepare statements before executing them, you can use the PrepareContext method on a Conn object, which returns a prepared statement bound to the connection. This method is available since version 1.8.4.

The Arrow type has a QueryContext method that prepares statements, executes them, and returns an Apache Arrow array.RecordReader as a result of the last executed statement. Arguments are bound to the last statement, and this method is available since version 1.6.0.

Intriguing read: Golang Version Manager

Statement

The Statement API in DuckDB's Golang driver is a low-level interface that requires caution when used.

Credit: youtube.com, Cgo is Not Go | compile golang + duckDB app

To bind parameters to a statement, you can use the Bind method, which was added in version 1.8.4. This method should be used with care.

The Bind method allows you to execute a bound query that may return rows, such as a SELECT statement. You can use the QueryBound method after calling Bind.

The ParamType method returns the expected type of a parameter at a given index, which is 1-based. This can be useful when working with complex queries.

You can determine the type of a statement using the StatementType method.

*Stmt) Exec Deprecated

The (*Stmt) Exec method has been deprecated since version 1.8.4.

You should use the ExecContext method instead, which is the recommended replacement.

The Exec method has been marked for removal, so it's a good idea to update your code to use the newer method.

Using ExecContext will help you avoid any potential issues or errors that may arise from using the deprecated method.

In general, it's a good practice to avoid using deprecated methods and functions whenever possible.

*Stmt) Param in

Detailed image of illuminated server racks showcasing modern technology infrastructure.
Credit: pexels.com, Detailed image of illuminated server racks showcasing modern technology infrastructure.

The Stmt type has some interesting properties when it comes to parameters. The ParamType function returns the expected type of the parameter at the given index, which is 1-based.

This means you need to add 1 to the index of the parameter you're interested in to get the correct result. For example, if you want to get the type of the second parameter, you would call ParamType with an index of 2.

The ParamType function is a useful tool for understanding the structure of a statement, and it's especially helpful when working with complex code.

*Stmt) in

The *Stmt type is a crucial part of understanding statements in programming. The *Stmt type is a pointer to a Stmt, which is a statement in the Go programming language.

You can use the StatementType method to determine the type of a statement. The StatementType method returns the type of the statement.

The StatementType method was added in Go version 1.8.4. This method is useful for determining the type of a statement in your code.

In Go, the *Stmt type is used to represent a statement in the abstract syntax tree (AST).

A fresh viewpoint: Golang Go

Data Management

Credit: youtube.com, DuckDB in 100 Seconds

Data Management is a crucial aspect of working with DuckDB in Golang.

DuckDB's columnar storage allows for efficient storage and retrieval of data, making it ideal for large datasets.

This is particularly useful when working with high-cardinality columns, such as strings or dates, which can be stored in a compact binary format.

In practice, this means that queries can be executed much faster than with traditional row-based storage systems.

For example, a query that would take several minutes to execute on a traditional database might take only a few seconds with DuckDB.

Overall, DuckDB's data management capabilities make it a powerful tool for working with data in Golang.

Explore further: Golang Azure Blob Storage

Apache Arrow Interface

The Apache Arrow Interface is a powerful tool for data management, and it's surprisingly easy to use.

You can obtain a new Arrow by passing a DuckDB connection to NewArrowFromConn(), which is a convenient way to get started.

The Arrow interface is a heavy dependency, so if you don't need it, you can disable it by passing -tags=no_duckdb_arrow to go build, which can save you some extra processing power.

Credit: youtube.com, Apache Arrow: A Cross-language Development Platform for In-memory Data | Ursa Labs

To use the Arrow interface, you'll need to prepare statements, execute them, and then return the result as an Apache Arrow array.RecordReader, which is exactly what the QueryContext function does.

The QueryContext function is a game-changer, allowing you to bind arguments to the last statement and execute it with ease, making data management a breeze.

Set Chunk Value

The Set Chunk Value method is a useful tool for writing a single value to a column in a data chunk. This is a key part of data management, especially when working with large datasets.

You can use SetChunkValue instead of chunk.SetValue, which requires casting the value to any. This makes SetChunkValue a more efficient option for data management tasks.

SetChunkValue is especially useful when working with custom ENUM types, which must be passed as strings. This is an important consideration when managing data with unique value sets.

By using SetChunkValue, you can simplify your data management workflow and reduce errors caused by incorrect value casting.

Set Value

Credit: youtube.com, Using data management to generate value

Setting a value in a data chunk requires attention to detail. The `SetValue` function, added in version 1.7.1, writes a single value to a column, but note that custom ENUM types must be passed as strings.

To set a value, you'll need to cast the type for each invocation, which can be a bit tedious. This is because the `SetValue` function doesn't automatically handle custom types.

In some cases, you might prefer to use `SetChunkValue` instead. This function, added in version 1.8.1, writes a single value to a column without requiring casting to `any`. Custom ENUM types still need to be passed as strings, however.

Setting a value in a data chunk can be a bit tricky, but with the right approach, you can get the job done efficiently. Be sure to check the version history to ensure you're using the most up-to-date function.

SetSize for DataChunk in 1.7.1

Setting the size of a data chunk is a crucial step in data management. The SetSize method is used to set the internal size of a data chunk.

Modern data center corridor with server racks and computer equipment. Ideal for technology and IT concepts.
Credit: pexels.com, Modern data center corridor with server racks and computer equipment. Ideal for technology and IT concepts.

The SetSize method is available in version 1.7.1 or later of the data management system. This means you can use it if you're working with data in a system that has been updated to this version.

Setting the size of a data chunk cannot exceed the capacity of the chunk. You can check the capacity of a chunk using the GetCapacity method.

In practice, this means you should set the size of a data chunk to a value that is less than or equal to the capacity of the chunk. This will help prevent errors and ensure that your data is properly managed.

Chunk Table

A Chunk Table is a powerful tool in data management, allowing you to bind a function to return a Chunk Table Source. This is made possible with the addition of ChunkTableFunction in version 1.8.3.

ChunkTableFunction is a type that can be bound to return a ChunkTableSource, making it a versatile and essential component of data management systems.

The internal size of a data chunk can be determined using the GetSize method, which returns the size of the chunk. This method was added in version 1.7.1, providing developers with a convenient way to measure their data.

Data Retrieval

Credit: youtube.com, Querying SQLite databases with DuckDB

Data Retrieval is a crucial part of working with DuckDB in Golang.

The `GetValue` function, added in version 1.7.1, returns a single value of a column. This makes it easy to extract specific data points from your database.

To retrieve data, you can use this function in combination with other methods to get the exact information you need.

Get Value Inv1.7.1

You can use the GetValue method to retrieve a single value from a column.

The GetValue method is available in DataChunk, which is a data structure used in duckdb.

In duckdb, a DataChunk is a collection of columns, each containing a vector of values.

GetValue returns a single value of a column.

This method is particularly useful when you need to extract a specific value from a large dataset.

NewArrowFromConn

Creating a new Arrow from a connection is a straightforward process. You can use the NewArrowFromConn function to achieve this.

This function is specifically designed for use with DuckDB driver connections, as mentioned in the documentation. It returns a new Arrow from the connection, making it a convenient option for data retrieval.

The NewArrowFromConn function is a helpful tool for working with data in various formats, including those supported by DuckDB.

Data Representation

Credit: youtube.com, SQLite vs DuckDB - Which Database Managemet System Is BETTER In 2025? (FULL COMPARISON!)

In duckdb, data is represented by the concept of a "row", which is a fundamental building block of the database.

A row in duckdb represents one row in the database, referencing the internal vectors that store the data.

This is a key concept to understand when working with duckdb, as it affects how data is stored and manipulated.

Row IsProjected Inv1

In data representation, it's essential to understand how rows are projected. The Row IsProjected function was added in Inv1.8.3.

IsProjected returns a boolean value indicating whether a column is projected. This function is a useful tool for developers to check the projection status of a column.

In practical terms, knowing whether a column is projected can help you optimize your data processing. For instance, if a column is not projected, you can choose to project it to improve performance.

Struct Info in 1.8.1

Struct Info in 1.8.1 is a significant update that allows for more detailed information about STRUCT type entries.

On a similar theme: Golang Copy Struct

Credit: youtube.com, Go Structs Explained: Data Structures, Empty Struct Use Cases & Tags with Reflection

NewStructInfo is the function that returns STRUCT type information, and it was added in version 1.8.1.

This function takes the STRUCT entries as input parameters, providing a more comprehensive view of the data.

The NewStructInfo function is a valuable tool for developers working with STRUCT data in version 1.8.1 and later.

Dynamic Library

You can dynamically link DuckDB by passing -tags=duckdb_use_lib to go build.

This requires having a copy of libduckdb available on your system, which can be downloaded from the DuckDB releases page.

For Linux, you'll need a .so file, while for macOS, you'll need a .dylib file.

Here's a brief rundown of the file types you'll need for different operating systems:

By dynamically linking DuckDB, you can avoid the increased binary size that comes with statically linking the libraries.

Connector

When working with DuckDB in Golang, you'll often need to establish a connection to the database. To do this, you can use the NewConnector function, which opens a new Connector for a DuckDB database.

The NewConnector function is a crucial part of getting started with DuckDB in Golang. You must close the Connector if it's not passed to the sql.OpenDB function, otherwise, sql.DB will close the Connector when calling sql.DB.Close().

ColumnInfo

Credit: youtube.com, 22 - DuckDB Internals (CMU Advanced Databases / Spring 2023)

ColumnInfo is a crucial concept in DuckDB Golang that holds the logical column type.

This means it contains the actual data type of the column, such as integer or string.

In addition to the column type, ColumnInfo also includes a callback function to write column values.

This callback function is essential for writing data to the column, and it's a key feature of ColumnInfo.

ColumnInfo also has additional helper fields that can be used to further customize its behavior.

Profiling

Profiling is an essential feature in DuckDB's Golang implementation, allowing you to obtain detailed information about your queries. This information can help you optimize your queries for better performance.

To start profiling, you need to obtain a connection, which is a necessary step in using the DuckDB Profiling API. You can then enable profiling for the connection, which will collect profiling information for each subsequent query.

Profiling information is connection-local, meaning it's specific to the connection you're using. This is why it's essential to enable profiling for the correct connection.

For more insights, see: Gcloud Api Using Golang

Credit: youtube.com, GopherCon 2019: Two Go Programs, Three Different Profiling Techniques - Dave Cheney

Once you've enabled profiling, you can execute a query and retrieve the profiling information. The ProfilingInfo type contains all available metrics, which can be obtained using the GetProfilingInfo function.

The ProfilingInfo type is a recursive type containing metrics for each node in DuckDB's query plan. This includes the top-level node, known as the QUERY_ROOT, and individual operators in the query plan, known as OPERATOR nodes.

Here's a summary of the steps to obtain profiling information:

  • Obtain a connection.
  • Enable profiling for the connection.
  • Execute a query.
  • Retrieve the profiling information using the GetProfilingInfo function.

By following these steps, you can gain valuable insights into your queries and optimize them for better performance.

Jennie Bechtelar

Senior Writer

Jennie Bechtelar is a seasoned writer with a passion for crafting informative and engaging content. With a keen eye for detail and a knack for distilling complex concepts into accessible language, Jennie has established herself as a go-to expert in the fields of important and industry-specific topics. Her writing portfolio showcases a depth of knowledge and expertise in standards and best practices, with a focus on helping readers navigate the intricacies of their chosen fields.

Love What You Read? Stay Updated!

Join our community for insights, tips, and more.