Go database/sql design thinking

Transcript

1. database/sql package design thinking CA.go #14 2024/05 Hoang Minh Quang
2. 1. The motivation behind this talk 2. Deep dive into database/sql package design 3. Make your own driver
3. About me HOANG MINH QUANG •Joined CyberAgent / Hanoi DevCenter in 2023/08 •アプ運用カンパニー / 大福山チーム Backend Engineer •Ramen / RDBMS / Go / Design •before: Classi Corp. Backend Engineer -> freee K.K Database Reliability Engineer •Japan 3 years @minhquang4334 @Quang4334 @quang.peter.7 #times-hanoi-devcenter ふく流らーめん
4. #times-hanoi-devcenter
5. The motivation behind this talk
6. Have you ever thought of Go standard package design？ Question
7. What is the design thinking of package? And the good points of that design? Question
8. Today Design thinking of a very common Go standard package: database/sql
9. Python example import mysql.connector conn = mysql.connector.connect( host="<your-host>", user="<your-user>", passwd="<your-password>" ) res = "" cursor = conn.cursor() cursor.execute("SELECT CONCAT(VERSION(), ' | ', CURRENT_USER());") res = cursor.fetchall() print(res[0]) conn.close() import psycopg2 conn = psycopg2.connect( user="<your-user>", passwd="<your-password>" host="<your-host>", port="<your-port>", database="<your-database>" ) cursor = conn.cursor() cursor.execute("SELECT datname || ' | ' || datid FROM pg_stat_activity WHERE state = 'active';") res = cursor.fetchall() print(res[0]) conn.close() MySQL PostgreSQL
10. Ruby example require 'mysql2' client = Mysql2::Client.new(hostname: 'localhost', username: 'root', password: '1234', database: 'ruby') res = client.query("SHOW TABLES FROM DB") require 'pg' conn = PGconn.connect("localhost", 5432, '', '', "ruby", "root", "1234") res = conn.exec('select tablename, tableowner from pg_tables') ActiveRecord gem with common interface but only support MySQL, PostgreSQL, SQLite. Refs: https://guides.rubyonrails.org/active_rec ord_querying.html
11. Go Example package main import ( "database/sql" _ "github.com/go-sql-driver/mysql" ) func main() { db, err := sql.Open("mysql", "dsn") if err != nil { panic(err.Error()) } defer db.Close() _, err = db.Query("SELECT * FROM users") if err != nil { panic(err.Error()) } } package main import ( "database/sql" _ "github.com/lib/pq" ) func main() { db, err := sql.Open("postgres", "dsn") if err != nil { panic(err.Error()) } defer db.Close() _, err = db.Query("SELECT * FROM users") if err != nil { panic(err.Error()) } } package main import ( "database/sql" _ "github.com/uber/athenadriver/go" ) func main() { db, err := sql.Open("awsathena", "dsn") if err != nil { panic(err.Error()) } defer db.Close() _, err = db.Query("SELECT * FROM users") if err != nil { panic(err.Error()) } } MySQL PostgreSQL AWS Athena
12. Question What is the good points of database/sql package design？
13. Extensibility and Simplicity
14. Extensibility Support various types of database drivers • NewSQL (Athena, CockroachDB etc.) and NoSQL (DynamoDB etc.) and Analytics DB (Athena, Snowflake etc.) • User only needs import database/sql with simple syntax • Support database edge cases ◦ e.g. Scan postgreSQL Array, JSONB types Refs: https://go.dev/wiki/SQLDrivers
15. Extensibility But, did you know, database/sql can support query from CSV, Excel or JSON? ◦ demo on the last section Refs: https://go.dev/wiki/SQLDrivers
16. But, If you can design database/sql package from scratch, HOW? Question Extensibility and Simplicity
17. Deep dive into database/sql package design
18. The simplest Go-like approach Application Backend DB <<interface>> MySQL Driver PostgreSQL Driver Athena Driver use implement • define a DB <<interface>> provides all user-capabilities • database driver: a different package that implement all methods of common db <<interface>> • Application Backend: Use database driver directly Backend Engineer (User) Driver Maintainer
19. Implementation Example // database/sql package package sql import "context" type DB interface { Begin() (*Tx, error) BeginTx(ctx context.Context, opts *TxOptions) (*Tx, error) Close() error Conn(ctx context.Context) (*Conn, error) Exec(query string, args ...any) (Result, error) ExecContext(ctx context.Context, query string, args ...any) (Result, error) Ping() error //。。。。。 } package mysqldriver import “database/sql” type MySQLDriver struct { // } var _ sql.DB = &MySQLDriver{} func (d *MySQLDriver) Begin() (*Tx, error) {} func (d *MySQLDriver) BeginTx(ctx context.Context, opts *TxOptions) (*Tx, error) {} func (d *MySQLDriver) Close() error {} func (d *MySQLDriver) Conn(ctx context.Context) (*Conn, error) {} func (d *MySQLDriver) Exec(query string, args ...any) (Result, error) {} func (d *MySQLDriver) ExecContext(ctx context.Context, query string, args ...any) (Result, error) {} func (d *MySQLDriver) Ping() error {} DB <<interface>> mysqldriver The simplest Go-like approach
20. Problem①: Adding user-facing capabilities is difficult Application Backend DB <<interface>> MySQL Driver PostgreSQL Driver Athena Driver use implement BulkInsert()が欲しいの で、入れてくれ！！ Backend Engineer (User) Driver Maintainer Adding user-facing capabilities forces breaking change to all database driver implement the interface. interfaceに入れ ます！！ 困るよ The simplest Go-like approach
21. Problem①: Adding user-facing capabilities is difficult Application Backend DB <<interface>> MySQL Driver PostgreSQL Driver Athena Driver use implement Backend Engineer (User) Driver Maintainer • Common DB <<interface>> serves both database users and driver maintainers. ◦ But, different concerns between High-level and Low-Level • Common DB <<interface>> also introduces a coupling between users and drivers. The simplest Go-like approach
22. Problem②: Duplicated common handling MySQL Driver • Duplicate and waste effort to implement on each driver • Each driver has their own version of common stuffs ◦ no standardization ◦ introduce inconsistency Mysql database client implement Connection pooling Concurrency handling Go typecasting PostgreSQL Driver PostgreSQL database client implement Connection pooling Concurrency handling Go typecasting The simplest Go-like approach
23. Problem③: Fat interfaces • Encapsulating functionality making common functions for all database types is very difficult task ◦ Various types of database have countless use cases • DB <<interface>> will becomes too fat ◦ DB query methods ◦ Connection handle methods ◦ Session handle methods ◦ Rows handle methods etc. ◦ Error handling // database/sql package package sql import "context" type DB interface { Begin() (*Tx, error) BeginTx(ctx context.Context, opts *TxOptions) (*Tx, error) Close() error Conn(ctx context.Context) (*Conn, error) Exec(query string, args ...any) (Result, error) ExecContext(ctx context.Context, query string, args ...any) (Result, error) Ping() error // Connection Pooling handling methods // Session handling methods // Rows handling methods // TODO: add more } The simplest Go-like approach
24. How database/sql package solves these problems?
25. database/sql package design Application Backend Backend Engineer (User) database/sql (*sql.DB, *sql.Tx, *sql.Stmt etc.) Database Driver Driver Maintainer database/sql/driver (driver.Driver, driver.Queryer, driver.Conn, etc.) use implement Refs: https://pkg.go.dev/std database/sql package design approach struct{} <<interface>>
26. database/sql package design Application Backend database/sql/driver (driver.Driver, driver.Queryer, driver.Conn etc..) MySQL Driver PostgreSQL Driver Athena Driver use implement Backend Engineer (User) Driver Maintainer database/sql (*sql.DB, *sql.Tx, *sql.Stmt etc.) use struct{} <<interface>> database/sql package design approach
27. database/sql package design Application Backend database/sql/driver (driver.Driver, driver.Queryer, driver.Conn etc..) MySQL Driver PostgreSQL Driver Athena Driver use implement Backend Engineer (User) Driver Maintainer database/sql (*sql.DB, *sql.Tx, *sql.Stmt etc.) use HOW? struct{} <<interface>> database/sql package design approach
28. database/sql package design package sql type DB struct { // Total time waited for new connections. waitDuration atomic.Int64 freeConn []*driverConn // free connections ordered by returnedAt oldest to newest } // driverConn wraps a driver.Conn with a mutex, to // be held during all calls into the Conn. (including any calls onto // interfaces returned via that Conn, such as calls on Tx, Stmt, // Result, Rows) type driverConn struct { db *DB createdAt time.Time sync.Mutex // guards following ci driver.Conn } Wrapping ！！ database/sql package design approach
29. Problem① Solved: Adding user-facing capabilities is difficult Application Backend database/sql/driver (driver.Driver, driver.Queryer, driver.Conn etc..) MySQL Driver PostgreSQL Driver Athena Driver use implement Backend Engineer (User) Driver Maintainer database/sql (*sql.DB, *sql.Tx, *sql.Stmt etc.) use ここだけで変更 変更なくても良い 何もやっても良い database driver packages don't have a direct dependency on user-facing packages, changes to the user-facing package won't necessarily require updates to all database driver packages bulkInsert()が欲 しいので、入れ てくれ！！ sql.DBに入 れます〜 database/sql package design approach
30. Problem② Solved: Duplicated common handling MySQL Driver Mysql database client implement Connection pooling Concurrency handling Go typecasting PostgreSQL Driver PostgreSQL database client implement Connection pooling Concurrency handling Go typecasting MySQL Driver Mysql database client implement PostgreSQL Driver PostgreSQL database client implement How to eliminate duplicate common handling from database drivers? database/sql package design approach
31. Problem② Solved: Duplicated common handling Application Backend database/sql/driver (driver.Driver, driver.Queryer, driver.Conn etc..) MySQL Driver PostgreSQL Driver Athena Driver use implement database/sql (*sql.DB, *sql.Tx, *sql.Stmt etc.) use Connection pooling Concurrency handling Go typecasting Retry The database/sql package becomes a central hub for implementing common database interactions Individual database drivers can then focus on the specifics of their database type, including native functionality and handling their edge cases. database/sql package design approach
32. Problem② Solved: Duplicated common handling Application Backend database/sql/driver (driver.Driver, driver.Queryer, driver.Conn etc..) MySQL Driver PostgreSQL Driver Athena Driver use implement database/sql (*sql.DB, *sql.Tx, *sql.Stmt etc.) use Connection pooling Concurrency handling Go typecasting Retry A layered structure: • database/sql/driver provides the foundation • database/sql builds upon it with common interaction logic. database/sql package design approach
33. Problem② Solved: Duplicated common handling Application Backend database/sql/driver (driver.Driver, driver.Queryer, driver.Conn etc..) MySQL Driver PostgreSQL Driver Athena Driver use implement database/sql (*sql.DB, *sql.Tx, *sql.Stmt etc.) use Connection pooling Concurrency handling Go typecasting Retry • database/sql package define optional interface to handle database edge cases database/sql package design approach optional interface optional interface optional interface optional interface
34. Problem② Solved: Duplicated common handling database/sql package design approach var id int var username string _ = rows.Scan(&id, &username) var email sql.NullString _ = rows.Scan(&id, &username, &email) var activities []string _ = rows.Scan(&id, &username, &email, pq.Array(&activities)) package sql // Scanner is an interface used by [Rows.Scan]. type Scanner interface { Scan(src any) error } • database/sql package define a sql.Scanner interface, that is designed to be optional. • database drivers can handle their own type conversions internally, if that type implementing sql.Scanner interface • E.g PostgreSQL Array or JsonB optional interface sql.Scanner
35. Problem③ Solved: Fat interfaces Application Backend database/sql/driver (driver.Driver, driver.Queryer, driver.Conn etc..) MySQL Driver PostgreSQL Driver Athena Driver use implement database/sql (*sql.DB, *sql.Tx, *sql.Stmt etc.) use The database/sql/driver interface is designed for internal use within the database/sql package. This separation keeps user-facing code clean and avoids unnecessary dependencies on the driver implementation details. database/sql package design approach <<interface>> struct{}
36. Problem③ Solved: Fat interfaces Application Backend database/sql/driver (driver.Driver, driver.Queryer, driver.Conn etc..) MySQL Driver PostgreSQL Driver Athena Driver use implement database/sql (*sql.DB, *sql.Tx, *sql.Stmt etc.) use The database/sql/driver interface can focus on a limited set of methods essential for database/sql functionality. This promotes stability, as changes to these methods are less frequent compared to user-facing APIs. database/sql package design approach <<interface>> struct{}
37. Problem③ Solved: Fat interfaces Application Backend database/sql/driver (driver.Driver, driver.Queryer, driver.Conn etc..) MySQL Driver PostgreSQL Driver Athena Driver use implement database/sql (*sql.DB, *sql.Tx, *sql.Stmt etc.) use To promote further reusability and simplicity, the database/sql/driver interface divided into smaller, more specific interfaces. driver.Driver, driver.Queryer, driver.Conn etc.driver.Driver, driver.Queryer, driver.Conn database/sql package design approach <<interface>> struct{}
38. Design thinking Refs: https://github.com/golang/go/blob/master/src/database/sql/doc.txt Design thinking
39. Design thinking • Hide the complexity, make user-facing I/F simple, but deep • Clean separation of concerns • Focus on stability for core functionalities • Balance between providing a generic interface for common database interactions and database-specific features Design thinking
40. Make your own driver
41. Make simplest CSV driver package package main import ( "database/sql" "fmt" "log" _ "github.com/minhquang4334/democsvdriver/csvdriver" ) func main() { db, err := sql.Open("csvdriver", "./testdata/test.csv") if err != nil { log.Fatalf("Error %s when opening DB\n", err) } defer db.Close() if err := selectAll(db); err != nil { log.Fatalf("Error %s when selecting\n", err) } } • Open a csv by file path and registered csvdriver • Make a connection to csv file
42. Make simplest CSV driver package func selectAll(db *sql.DB) error { rows, err := db.Query("SELECT * FROM csv") if err != nil { return err } defer rows.Close() columns, err := rows.Columns() if err != nil { return err } for rows.Next() { var f1, f2, f3 string err := rows.Scan(&f1, &f2, &f3) if err != nil { return err } fmt.Printf("%s=%s, %s=%s, %s=%s\n", columns[0], f1, columns[1], f2, columns[2], f3) } return nil } ➜ csv-sql-driver git:(main) ✗ go run main.go Drivers=[]string{"csvdriver"} name=Quang, age=18, birth=1997 name=Kurotaku, age=26, birth=1998 name=Hoang, age=28, birth=1996 name=Nghia, age=35, birth=1989 Demo repo: https://github.com/minhquang4334/de mo-csvsqldriver
43. Thank you
44. References • https://eli.thegreenplace.net/2019/design-patterns-in-gos-databases ql-package/ • https://izumisy.work/entry/2021/08/06/113955 • https://github.com/golang/go/blob/master/src/database/sql/doc.txt