is an embedded SQL database engine. It's very similar to well-known SQLite but is designed for OLAP-style workloads. DuckDB supports a variety of data types and features. With its ability to handle high-speed analytics in a memory-centric environment, it has quickly gained popularity among data scientists and analysts. In this blog post, we will explore using DuckDB with Go. DuckDB SQL OLAP Databases Online Analytical Processing ( ) databases are specially designed to support complex analytical and ad-hoc queries, data manipulation, and multidimensional analysis. These databases are optimized for read-heavy workloads, unlike Online Transaction Processing (OLTP) databases which are more suited for write-heavy, transactional operations. OLAP databases are the backbone of many Business Intelligence (BI) tools, allowing decision-makers to analyze data from multiple dimensions and perspectives. OLAP OLAP databases are highly efficient at handling operations like data summarization, complex calculations, trend analysis, and data modeling. They enable users to drill down into datasets to view data from different time periods, categories, or other dimensions, offering valuable insights and supporting strategic decisions. DuckDB is designed to support OLAP-style workloads efficiently. Embedded Databases Embedded databases are specialized database management systems (DBMS) that run within the same process as the application. Unlike standalone database servers, which run as separate processes and require inter-process communication, embedded databases interact directly with the application, thereby offering significant performance benefits and simplifying application architecture. They are typically lightweight, use less resources, and require minimal setup and administration. Examples of popular embedded databases include SQLite, RocksDB, and DuckDB. An embedded database is particularly useful in applications that need to manage data but don't require the full functionality of a standalone DBMS. These could range from desktop applications and small web applications to IoT devices and mobile apps. They are also utilized in server applications for tasks such as caching, temporary data manipulation, or when the data to be stored is application-specific and does not need to be shared across different services. Major Benefits of DuckDB DuckDB primarily operates in-memory, but also supports out-of-memory execution. This allows it to perform computations very quickly and efficiently. In-Memory Execution: DuckDB supports a wide range of SQL features, which makes it very flexible for various types of data operations. Full SQL Support: DuckDB supports transactions, which is a critical feature for maintaining data integrity and consistency in many applications. Transaction Support: DuckDB uses vectorized query execution, which leads to better CPU utilization and improved performance. Vectorized Execution: DuckDB provides APIs for multiple programming languages, including Python, R, C++, Rust, Java, and Go. This makes it easier to integrate DuckDB into existing workflows and systems. Easy Integration: DuckDB is open source, which means its source code is freely available for modification or enhancement. This allows for community-driven improvements and adaptability to specific use cases. Open Source: Getting Started Before you can start using DuckDB with Go, you need to install the DuckDB Go driver. You can download it using Go's package manager. Run the following command in your terminal: go get github.com/marcboeker/go-duckdb Connecting to DuckDB from Go With the driver installed, you can now establish a connection to DuckDB from your Go application. Here's a basic example: package main


import (
  "database/sql"
  "log"

  _ "github.com/marcboeker/go-duckdb"
)


func main() {
  // Empty datasource means, that DB will be solely in-memory, otherwise you could specify a filename here
  db, err := sql.Open("duckdb", "")
  if err != nil {
    log.Fatal("Failed to connect to database:", err)
  }
  defer db.Close()
} The function is used to connect to DuckDB, and the empty data source name denotes that we are using an in-memory database. sql.Open() Create Table and Insert Data Now that the connection has been established, you can perform various database operations. Here's a simple way to create a table: // Create table
_, err = db.Exec(`
  CREATE TABLE employee (
    id INTEGER,
    name VARCHAR(20),
    start_dt TIMESTAMP,
    is_remote BOOLEAN
  )`)
if err != nil {
  log.Fatal(err)
} Next, to insert data into the table, you could use the function with an SQL command: employee Exec INSERT INTO // Insert some data in table
_, err = db.Exec(`
  INSERT INTO employee (id, name, start_dt, is_remote)
  VALUES
    (1, 'John Doe', '2022-01-01 09:00:00', true),
    (2, 'Jane Smith', '2023-03-15 10:00:00', false)`)
if err != nil {
  log.Fatal(err)
} When dealing with larger sets of data, consider using transactions and prepared statements for efficiency and safety. Remember, always handle errors and close your connections when done. Query Single or Multiple Rows To fetch data, you can use the function to select a single row: QueryRow() // Variables to store query result
var id int
var name string
var startDt time.Time
var isRemote bool


// Query single row
if err = db.QueryRow("SELECT id, name, start_dt, is_remote FROM employee WHERE id = ?", 1).Scan(&id, &name, &startDt, &isRemote); err != nil {
  if err == sql.ErrNoRows {
    log.Println("No rows found.")
  } else {
    log.Fatalf("unable to execute query: %v", err)
  }
} else {
  fmt.Println("Select 1 row result:\nID:", id, "Name:", name, "Start Datetime:", startDt, "Is Remote:", isRemote)
} Don't forget to handle any errors and properly close your connections and result sets, as shown above. To select multiple rows, you can use function: Query() // Query multiple rows
rows, err := db.Query("SELECT id, name, start_dt, is_remote FROM employee")
if err != nil {
  log.Fatal(err)
}
defer rows.Close()


// Print the results
fmt.Println("Results:")
for rows.Next() {
  err = rows.Scan(&id, &name, &startDt, &isRemote)
  if err != nil {
    log.Fatal(err)
  }
  fmt.Println("ID:", id, "Name:", name, "Start Datetime:", startDt, "Is Remote:", isRemote)
}

err = rows.Err()
if err != nil {
  log.Fatal(err)
} In this code: We're calling the function with an SQL command to select all records from the table. Query() employee We then enter a loop using , which iterates over each row returned by the query. rows.Next() Within the loop, we're using the function to copy the columns from the current row into the , , , and variables. Scan() id name workFrom isRemote We then print these variables using the function. fmt.Println() After the loop, we check for errors during the iteration with . If there's an error, we print it using . rows.Err() log.Fatal(err) Error Handling and Transactions In the real world, your Go code must be ready to handle errors and work with transactions. The package provides all the necessary tools: SQL // Error handling and transactions
tx, err := db.Begin()
if err != nil {
  log.Fatal(err)
}
defer tx.Rollback()


_, err = tx.Exec(`
  INSERT INTO employee (id, name, start_dt, is_remote)
  VALUES
    (3000000000, 'id int64 instead of int32', '2022-06-17 11:00:00', true)`)
if err != nil {
  log.Printf("ERROR: %s\n", err.Error()) // Do not fail, just print the error in output
}

err = tx.Commit()
if err != nil {
  log.Fatal(err)
} This code begins a transaction, tries to execute an insert statement, and then commits the transaction. If an error occurs during execution, it will rollback any changes made in that transaction. Conclusion DuckDB's support for Go allows developers to perform robust data analytics operations directly from their Go applications. This integration between a powerful data management system and a versatile, efficient programming language opens the door to more advanced data processing applications. With the fundamental knowledge provided in this article, you can start exploring these possibilities. The full source code for this example could be found on my GitHub repo: https://github.com/solontsev/go-cookbook/tree/main/duckdb

Too Long; Didn't Read

Making security fun one episode at a time

A Comprehensive Guide for Using DuckDB With Go

Too Long; Didn't Read

Sergey Olontsev

About Author

TOPICS

THIS ARTICLE WAS FEATURED IN...

A Comprehensive Guide for Using DuckDB With Go

Too Long; Didn't Read

Sergey Olontsev

About Author

TOPICS

THIS ARTICLE WAS FEATURED IN...

RELATED STORIES