go操作duckdb

什么是duckdb

DuckDB是一个开源的嵌入式SQL数据库管理系统。与传统的SQL数据库不同，DuckDB不需要一个服务器进程，
也不需要在系统上安装数据库软件。它被设计为轻量级和高性能，可以轻松嵌入到应用程序中，提供完整的SQL数据库功能。
以下是DuckDB的一些关键特性：

1. **轻量级**：DuckDB的二进制文件非常小，通常只有几百KB，这使得它非常适合嵌入式使用。

2. **高性能**：DuckDB利用现代硬件优化查询性能，支持索引、物化视图、查询优化等特性。

3. **标准SQL支持**：DuckDB遵循SQL标准，支持大多数SQL数据类型、函数和操作。

4. **跨平台**：DuckDB可以在多种操作系统上运行，包括Windows、macOS、Linux等。

5. **多种数据源**：DuckDB支持从CSV、Parquet、SQLite等不同数据源导入数据。

6. **并发控制**：DuckDB支持多线程查询，可以在多核处理器上并行执行。

7. **事务支持**：DuckDB支持ACID事务，确保数据的一致性和完整性。

8. **API支持**：DuckDB提供了多种编程语言的API，包括C、Python、R等，方便集成。

9. **无服务器模式**：DuckDB可以在无服务器环境中运行，适合云原生和容器化应用。

10. **可扩展性**：DuckDB允许用户通过扩展添加新的功能，如自定义函数、聚合函数等。

DuckDB适用于需要轻量级数据库解决方案的场景，如数据分析、数据科学、机器学习、嵌入式系统、Web应用等。
由于其轻量级和高性能的特点，DuckDB也适合作为教学和学习SQL的工具。

go操作库地址为 https://github.com/marcboeker/go-duckdb

main.go

package main

import (
	"context"
	"database/sql"
	"log"
	"time"

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

var db *sql.DB

type user struct {
	name    string
	age     int
	height  float32
	awesome bool
	bday    time.Time
}

func main() {
	var err error

	db, err = sql.Open("duckdb", "?access_mode=READ_WRITE") //设置读写权限

	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	check(db.Ping())

	setting := db.QueryRowContext(context.Background(), "SELECT current_setting('access_mode')")
	var am string
	check(setting.Scan(&am))
	log.Printf("DB opened with access mode %s", am)
    //普通操作，建表，插入数据 
	check(db.ExecContext(context.Background(), "CREATE TABLE users(name VARCHAR, age INTEGER, height FLOAT, awesome BOOLEAN, bday DATE)"))
	check(db.ExecContext(context.Background(), "INSERT INTO users VALUES('marc', 99, 1.91, true, '1970-01-01')"))
	check(db.ExecContext(context.Background(), "INSERT INTO users VALUES('macgyver', 70, 1.85, true, '1951-01-23')"))
    //查询
	rows, err := db.QueryContext(
		context.Background(), `
		SELECT name, age, height, awesome, bday
		FROM users
		WHERE (name = ? OR name = ?) AND age > ? AND awesome = ?`,
		"macgyver", "marc", 30, true,
	)
	check(err)
	defer rows.Close()
    //遍历打印
	for rows.Next() {
		u := new(user)
		err := rows.Scan(&u.name, &u.age, &u.height, &u.awesome, &u.bday)
		if err != nil {
			log.Fatal(err)
		}
		log.Printf(
			"%s is %d years old, %.2f tall, bday on %s and has awesomeness: %t\n",
			u.name, u.age, u.height, u.bday.Format(time.RFC3339), u.awesome,
		)
	}
	check(rows.Err())
    //删除
	res, err := db.ExecContext(context.Background(), "DELETE FROM users")
	check(err)

	ra, _ := res.RowsAffected()
	log.Printf("Deleted %d rows\n", ra)
    //事务
	runTransaction()
    //预操作
	testPreparedStmt()
}

func check(args ...interface{}) {
	err := args[len(args)-1]
	if err != nil {
		panic(err)
	}
}

func runTransaction() {
	log.Println("Starting transaction...")
	tx, err := db.Begin()
	check(err)

	check(
		tx.ExecContext(
			context.Background(),
			"INSERT INTO users VALUES('gru', 25, 1.35, false, '1996-04-03')",
		),
	)
	row := tx.QueryRowContext(context.Background(), "SELECT COUNT(*) FROM users WHERE name = ?", "gru")
	var count int64
	check(row.Scan(&count))
	if count > 0 {
		log.Println("User Gru was inserted")
	}

	log.Println("Rolling back transaction...")
	check(tx.Rollback())

	row = db.QueryRowContext(context.Background(), "SELECT COUNT(*) FROM users WHERE name = ?", "gru")
	check(row.Scan(&count))
	if count > 0 {
		log.Println("Found user Gru")
	} else {
		log.Println("Couldn't find user Gru")
	}
}

func testPreparedStmt() {
	stmt, err := db.PrepareContext(context.Background(), "INSERT INTO users VALUES(?, ?, ?, ?, ?)")
	check(err)
	defer stmt.Close()

	check(stmt.ExecContext(context.Background(), "Kevin", 11, 0.55, true, "2013-07-06"))
	check(stmt.ExecContext(context.Background(), "Bob", 12, 0.73, true, "2012-11-04"))
	check(stmt.ExecContext(context.Background(), "Stuart", 13, 0.66, true, "2014-02-12"))

	stmt, err = db.PrepareContext(context.Background(), "SELECT * FROM users WHERE age > ?")
	check(err)

	rows, err := stmt.QueryContext(context.Background(), 1)
	check(err)
	defer rows.Close()

	for rows.Next() {
		u := new(user)
		err := rows.Scan(&u.name, &u.age, &u.height, &u.awesome, &u.bday)
		if err != nil {
			log.Fatal(err)
		}
		log.Printf(
			"%s is %d years old, %.2f tall, bday on %s and has awesomeness: %t\n",
			u.name, u.age, u.height, u.bday.Format(time.RFC3339), u.awesome,
		)
	}
}

输出

2024/06/17 10:59:30 DB opened with access mode read_write
2024/06/17 10:59:30 marc is 99 years old, 1.91 tall, bday on 1970-01-01T00:00:00Z and has awesomeness: true
2024/06/17 10:59:30 macgyver is 70 years old, 1.85 tall, bday on 1951-01-23T00:00:00Z and has awesomeness: true
2024/06/17 10:59:30 Deleted 2 rows
2024/06/17 10:59:30 Starting transaction...
2024/06/17 10:59:30 User Gru was inserted
2024/06/17 10:59:30 Rolling back transaction...
2024/06/17 10:59:30 Couldn't find user Gru
2024/06/17 10:59:30 Kevin is 11 years old, 0.55 tall, bday on 2013-07-06T00:00:00Z and has awesomeness: true
2024/06/17 10:59:30 Bob is 12 years old, 0.73 tall, bday on 2012-11-04T00:00:00Z and has awesomeness: true
2024/06/17 10:59:30 Stuart is 13 years old, 0.66 tall, bday on 2014-02-12T00:00:00Z and has awesomeness: true