Ang Go database/sql package kay generic interface para sa SQL databases. Inubanan ug driver (SQLite, PostgreSQL, MySQL, bisag unsa), pwede ka mag-create, magbasa, mag-update, ug mag-delete ug data. Anhi nga chapter, atong gamiton ang SQLite as example. Simple lang, practical.
Ang relational databases kay organized into tables naay rows ug columns. Mura rag Excel pero mas professional. Ang Go database/sql interface mo-work sa daghang backends. Ang examples nato kay mogamit ug SQLite via github.com/mattn/go-sqlite3 driver.
Ang database/sql package nagdefine ug interface. Ang drivers nag-implementar para specific databases. I-register ang driver pinaagi sa blank identifier import:
import (
"database/sql"
_ "github.com/mattn/go-sqlite3" // SQLite driver
_ "github.com/lib/pq" // PostgreSQL driver
_ "github.com/go-sql-driver/mysql" // MySQL driver
)
Gamit ug sql.Open para maghimo ug database handle (dili pa ni actual connection ha, promise). Gamit ug db.Ping para i-verify kung buhi ba ang connection:
db, err := sql.Open("sqlite3", "./data.db")
if err != nil {
log.Fatal(err)
}
defer db.Close()
err = db.Ping()
if err != nil {
log.Fatal("cannot connect:", err)
}
fmt.Println("connected to database")
I-execute ang DDL statements (CREATE TABLE, etc.) gamit db.Exec:
createTable := `
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT NOT NULL UNIQUE,
age INTEGER
);`
_, err := db.Exec(createTable)
if err != nil {
log.Fatal(err)
}
Gamit ug parameterized queries (dili jud string interpolation!) para maiwasan ang SQL injection. Dili ta ganahan ma-hack:
result, err := db.Exec(
"INSERT INTO users (name, email, age) VALUES (?, ?, ?)",
"Alice", "alice@example.com", 30,
)
if err != nil {
log.Fatal(err)
}
id, _ := result.LastInsertId()
fmt.Println("inserted id:", id)
Goal: Paghimo ug numbers table naay id ug value column, dayon i-insert ang numbers 1 hangtod 10.
package main
import (
"database/sql"
"fmt"
"log"
_ "github.com/mattn/go-sqlite3"
)
func main() {
db, _ := sql.Open("sqlite3", ":memory:")
defer db.Close()
db.Exec(`CREATE TABLE numbers (id INTEGER PRIMARY KEY, value INTEGER)`)
for i := 1; i <= 10; i++ {
db.Exec("INSERT INTO numbers (value) VALUES (?)", i)
}
fmt.Println("Inserted 10 numbers")
}
Mag-query ug multiple rows gamit db.Query or single row gamit db.QueryRow:
// Multiple rows
rows, err := db.Query("SELECT id, name, age FROM users WHERE age > ?", 25)
if err != nil {
log.Fatal(err)
}
defer rows.Close()
for rows.Next() {
var id, age int
var name string
rows.Scan(&id, &name, &age)
fmt.Println(id, name, age)
}
// Single row
var name string
var age int
err = db.QueryRow("SELECT name, age FROM users WHERE id = ?", 1).Scan(&name, &age)
Gamit ug db.Exec para UPDATE statements — i-update ang existing data:
result, err := db.Exec(
"UPDATE users SET age = ? WHERE name = ?",
31, "Alice",
)
if err != nil {
log.Fatal(err)
}
affected, _ := result.RowsAffected()
fmt.Println("updated", affected, "rows")
Delete rows gamit parameterized DELETE queries. Ayawg gamit ug DELETE without WHERE kung dili ka sure:
result, err := db.Exec("DELETE FROM users WHERE id = ?", 5)
if err != nil {
log.Fatal(err)
}
affected, _ := result.RowsAffected()
fmt.Println("deleted", affected, "rows")
Goal: I-store ang prime numbers nga ubos sa 50 sa database, dayon i-retrieve ug i-print. Math + database = powerful combo.
package main
import (
"database/sql"
"fmt"
"log"
_ "github.com/mattn/go-sqlite3"
)
func isPrime(n int) bool {
if n < 2 { return false }
for i := 2; i*i <= n; i++ {
if n%i == 0 { return false }
}
return true
}
func main() {
db, _ := sql.Open("sqlite3", ":memory:")
defer db.Close()
db.Exec("CREATE TABLE primes (value INTEGER PRIMARY KEY)")
for i := 2; i < 50; i++ {
if isPrime(i) {
db.Exec("INSERT INTO primes (value) VALUES (?)", i)
}
}
rows, _ := db.Query("SELECT value FROM primes ORDER BY value")
defer rows.Close()
fmt.Print("Primes: ")
for rows.Next() {
var v int
rows.Scan(&v)
fmt.Printf("%d ", v)
}
fmt.Println()
}
Remove all rows with DELETE (without WHERE) or ang tibuok table gamit DROP:
// Remove all rows
db.Exec("DELETE FROM users")
// Remove the table entirely
db.Exec("DROP TABLE users")
Goal: Paghimo ug users table, i-insert ug 3 users, dayon query ug i-print ang tanang users nga sobra 25 ang edad.
package main
import (
"database/sql"
"fmt"
_ "github.com/mattn/go-sqlite3"
)
func main() {
db, _ := sql.Open("sqlite3", ":memory:")
defer db.Close()
db.Exec(`CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT, age INTEGER
)`)
db.Exec("INSERT INTO users (name, age) VALUES (?, ?)", "Alice", 30)
db.Exec("INSERT INTO users (name, age) VALUES (?, ?)", "Bob", 22)
db.Exec("INSERT INTO users (name, age) VALUES (?, ?)", "Carol", 28)
rows, _ := db.Query("SELECT name, age FROM users WHERE age > 25")
defer rows.Close()
for rows.Next() {
var name string
var age int
rows.Scan(&name, &age)
fmt.Printf("%s is %d\n", name, age)
}
}
Goal: Paghimo ug messages ug users tables, i-join para makit-an ang messages sa specific user. Relational database in action.
package main
import (
"database/sql"
"fmt"
_ "github.com/mattn/go-sqlite3"
)
func main() {
db, _ := sql.Open("sqlite3", ":memory:")
defer db.Close()
db.Exec(`CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)`)
db.Exec(`CREATE TABLE messages (
id INTEGER PRIMARY KEY, user_id INTEGER, content TEXT,
FOREIGN KEY(user_id) REFERENCES users(id)
)`)
db.Exec("INSERT INTO users VALUES (1, 'Alice'), (2, 'Bob')")
db.Exec("INSERT INTO messages VALUES (1, 1, 'Hello!'), (2, 2, 'Hi!'), (3, 1, 'How are you?')")
rows, _ := db.Query(`
SELECT u.name, m.content FROM messages m
JOIN users u ON m.user_id = u.id
WHERE u.name = ?`, "Alice")
defer rows.Close()
for rows.Next() {
var name, content string
rows.Scan(&name, &content)
fmt.Printf("%s: %s\n", name, content)
}
}
database/sql nagprovide ug standard SQL interface naay driver-based backends? placeholders) para maiwasan ang SQL injection — di ta tangadb.Exec para INSERT, UPDATE, DELETE, ug DDLdb.Query para SELECT na daghan resultsdb.QueryRow para single-row results