WhiSQL

Go database/sql adapter influenced by Spring Jdbc Template

Caveats

• Requires Go 1.26+ (minimal redesign will be done in Go 1.27 with generic methods)
• This library is a wrapper of Go std database/sql so every underneath implementation is available
• Partially influenced by Spring JDBC Template

Installation

go get -u codeberg.org/caskstrength/whisql

Details

• The whisql.Template has a property *sql.DB inside. So is possible to work with the plain implementation of database/sql if needed.
• Query for a single value, query for a single object (optional returns).
• Migrate functionality works with scripts in text format, file format or directory scan path.
• Inserts, updates, deletes, prepared statements and transactional support.
• Context could be set by query.
• RowMapper and ResultSetExtrator functions are somehow influenced by Spring JDBC Template. So making one to many or many to many mappings are more easy to set.

Example

More examples here

packagemainimport("cmp""fmt""slices""time""codeberg.org/caskstrength/whisql""codeberg.org/caskstrength/whisql/migrate""github.com/google/uuid"_"github.com/mattn/go-sqlite3")typeUserstruct{IDuintCodeuuid.UUIDUsernamestringEmailstringAverage*float64Info*stringStatusstringCreateDatetime.TimeEnabled*boolPermissionPermission}typePermissionstruct{IDuintNamestringRoles[]Role}typeRolestruct{IDuintNamestring}funcmain(){template:=whisql.MustConnect("sqlite3","file::memory:?cache=shared")template.MustMigrate(migrate.ScriptStr(`
		create table if not exists permissions (
			id integer primary key autoincrement,
			name text unique not null
		);
		create table if not exists roles (
			id integer primary key autoincrement,
			name text unique not null
		);
		create table if not exists permissions_roles (
			permission_id integer not null,
			role_id integer not null,
			primary key(permission_id, role_id),
			foreign key(permission_id) references permissions(id),
			foreign key(role_id) references roles(id)
		);
		create table if not exists users (
			id integer primary key autoincrement,
			code text not null,
			username text not null,
			email text not null,
			info text,
			average real,
			status text not null,
			created_by text not null,
			create_date date not null,
			enabled bool,
			permission_id integer not null,
			foreign key(permission_id) references permissions(id)
		);
		insert into permissions(name) values ('PERM_1');
		insert into permissions(name) values ('PERM_2');
		insert into roles(name) values ('ROLE_1');
		insert into roles(name) values ('ROLE_2');
		insert into roles(name) values ('ROLE_3');
		insert into permissions_roles (permission_id, role_id) values (1, 1);
		insert into permissions_roles (permission_id, role_id) values (1, 2);
		insert into permissions_roles (permission_id, role_id) values (2, 2);
		insert into permissions_roles (permission_id, role_id) values (2, 3);
		insert into users (code, average, username, email, status, created_by, create_date, permission_id) 
			values ('e60d5b12-921d-4f1e-966a-2d4e743a164b', 20.1, 'javi', 'javi@mail.com','ACTIVE', 'javi', CURRENT_TIMESTAMP, 1);
		insert into users (code, username, email, status, created_by, create_date, enabled, permission_id) 
			values ('e60d5b12-921d-4f1e-966a-2d4e743a164a', 'johan', 'johan@mail.com', 'ACTIVE', 'javi', CURRENT_TIMESTAMP, 1, 2);
	`))typeUserTaggedstruct{IDuint`whisql:"id"`Codeuuid.UUID`whisql:"code"`Usernamestring`whisql:"username"`Emailstring`whisql:"email"`Info*string`whisql:"info"`Statusstring`whisql:"status"`CreateDatetime.Time`whisql:"create_date"`AuditTagged}typeAuditTaggedstruct{Creatorstring`whisql:"created_by"`}query:="select * from users where id = 1ドル"// NOTE: To use this sql query fields must contain whisql tags as UserTaggedopt,err:=whisql.QueryOne(template,query,whisql.Load[UserTagged](),1)iferr!=nil{panic(err)}opt.IfValue(func(uUser){fmt.Printf("%+v",u)})// Query one returns an optional value and accepts a RowMapperopt,err=whisql.QueryOne(template,"select * from users where id = 1ドル",func(rswhisql.ResultSet,uuint)(*User,error){return&User{ID:rs.Uint("id"),Code:rs.UUID("code"),Username:rs.String("username"),Email:rs.String("email"),Info:rs.StringPtr("info"),Average:rs.Float64Ptr("average"),Status:rs.String("status"),CreateDate:rs.Time("create_date"),Enabled:rs.BoolPtr("enabled"),},nil},1)iferr!=nil{panic(err)}opt.IfValue(func(uUser){fmt.Printf("User 1 => %+v\n\n",u)})// Accepts a Extractor and can match all relationsqueryWithRelations:=`SELECT u.id, u.code, u.username, u.email, u.info, u.average, 
	u.status, u.create_date, u.enabled, u.permission_id, p.name, r.id, r.name
	FROM users u inner join permissions p on p.id = u.permission_id
	inner join permissions_roles pr on pr.permission_id = p.id
	inner join roles r on pr.role_id = r.id`users,err:=whisql.QueryExtractor(template,queryWithRelations,func(rswhisql.ResultSet)([]User,error){userMap:=make(map[uint]*User)forrs.Next(){iferr:=rs.Error();err!=nil{returnnil,err}id:=rs.Uint(1)user,ok:=userMap[id]if!ok{user=&User{ID:id,Code:rs.UUID(2),Username:rs.String(3),Email:rs.String(4),Info:rs.StringPtr(5),Average:rs.Float64Ptr(6),Status:rs.String(7),CreateDate:rs.Time(8),Enabled:rs.BoolPtr(9),Permission:Permission{ID:rs.Uint(10),Name:rs.String(11),},}userMap[id]=user}role:=Role{rs.Uint(12),rs.String(13)}user.Permission.Roles=append(user.Permission.Roles,role)}users:=make([]User,0,len(userMap))for_,user:=rangeuserMap{users=append(users,*user)}slices.SortFunc(users,func(a,bUser)int{returncmp.Compare(a.ID,b.ID)})returnusers,nil})iferr!=nil{panic(err)}fori:=rangeusers{fmt.Printf("User with relations nro %d => %+v\n",i+1,users[i])}// Query single return valueoptional,err:=whisql.QuerySingle[string](template,"select username from users where id = 1ドル",2)iferr!=nil{panic(err)}optional.IfValue(func(sstring){fmt.Println("Username:",s)})res,err:=template.Execute("insert into roles (name) values ('INSERT')")iferr!=nil{panic(err)}last,err:=res.LastInsertId()iferr!=nil{panic(err)}fmt.Println("ID inserted:",last)affected,err:=res.RowsAffected()iferr!=nil{panic(err)}fmt.Println("Insert Rows affected:",affected)res,err=template.Execute("update roles set name = 'UPDATE' where name = 'INSERT'")affected,err=res.RowsAffected()iferr!=nil{panic(err)}fmt.Println("Update Rows affected:",affected)res,err=template.Execute("delete from roles where name = 'UPDATE'")affected,err=res.RowsAffected()iferr!=nil{panic(err)}fmt.Println("Delete Rows affected:",affected)}