`db.Exec` not returning errors for `SQLITE_CONSTRAINT_CHECK` errors.

[sjunepark]

Hi,

Thanks in advance for checking out. I've asked this on the turso discord as well, and if you've come across it as a duplicate spam, apologies. Wanted to reach out to a larger audience about this.

Problem

• db.Exec won't return an error when there are sqlite constraint errors. (SQLITE_CONSTRAINT_CHECK to be specific, but I've checked that it's the same for SQLITE_CONSTRAINT_PRIMARYKEY errors as well)
• (I'm not an advanced and developer, and I'm not sure if this is a go-libsql issue or database/sql issue. Please let me know if someone knows.)

Environment

• local, using a local .db file created with turso dev --db-file local.db

Manual Query

If I run a SQL query directly, as below, it fails due to CONSTRAINTS. (It fails for the 3rd column since I gave a string with length of 3 EMDNumber TEXT NOT NULL CHECK (length(EMDNumber) = 5),)

INSERT INTO locations (BJDNumber,
                       SGGNumber, EMDNumber, RoadNumber, UndergroundFlag, BuildingMainNumber, BuildingSubNumber, SDName,
                       SGGName, EMDName, RoadName, BuildingName, PostalNumber, Long, Lat, Crs, X, Y, ValidPosition,
                       BaseDate, DatetimeAdded)
VALUES ('3611010100', '36110', '101', '2000002', 0, 1811, 0, '세종특별자치시', '', '반곡동', '한누리대로', '수루배마을5단지 상가동', '30145',
        127.31348634049063, 36.4974913911321, 'EPSG:5179', 983296.172464, 1833330.968984, 1, '2023-12-31T15:00:00Z',
        '2024-02-13 00:51:35.589909 +0900 m=+0.005833793')

[2024-02-13 01:21:32] [19] [SQLITE_CONSTRAINT_CHECK] A CHECK constraint failed (CHECK constraint failed: length(EMDNumber) = 5)

Using database/sql

However, if i use database/sql's db.Exec, no error occurs. Also, sql.Result returns 0 rows affected, meaning that the INSERT operation failed.

// Am going to pass a invalid types.Location to this function
func PersistFirstToDb(db *sql.DB, l types.Location) error {
    query := `INSERT INTO locations (
        BJDNumber, SGGNumber, EMDNumber, RoadNumber, UndergroundFlag, BuildingMainNumber, BuildingSubNumber, 
        SDName, SGGName, EMDName, RoadName, BuildingName, PostalNumber, Long, Lat, Crs, X, Y, ValidPosition, 
        BaseDate, DatetimeAdded
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`

    res, err := db.Exec(
        query,
        l.BJDNumber, l.SGGNumber, l.EMDNumber, l.RoadNumber, l.UndergroundFlag,
        l.BuildingMainNumber, l.BuildingSubNumber, l.SDName, l.SGGName, l.EMDName, l.RoadName,
        l.BuildingName, l.PostalNumber, l.Long, l.Lat, l.Crs, l.X, l.Y, l.ValidPosition,
        l.BaseDate.Format(time.RFC3339), l.DatetimeAdded.Format(time.RFC3339),
    )
    if err != nil {
        return fmt.Errorf("failed to insert data: %w", err)
    }

    lastID, err := res.LastInsertId()
    if err != nil {
        return fmt.Errorf("failed to get the last inserted ID: %w", err)
    }
    rowCnt, err := res.RowsAffected()
    if err != nil {
        return fmt.Errorf("failed to get the number of rows affected: %w", err)
    }

    // Both returns 0
    log.Printf("ID = %d, affected = %d\n", lastID, rowCnt)
    return nil
}

// Init code just for reference. nothing special.
func InitTursoDB() (*sql.DB, *libsql.Connector) {
    dbName := os.Getenv("TURSO_LOCAL_LOCATION_DB_PATH")
    db, err := sql.Open("libsql", "file:"+dbName)
    if err != nil {
        panic(err)
    }
    return db, nil
}

[sjunepark]

Just for reference, I'm currently using a workaround to throw an error when sql.Db.RowsAffected() returns 0, but this is just a workaround since there are non error situations included.

[keenanwl]

Hey @sjunepark I'm evaluating Turso, and just wondered if this was still an issue for you?

I can't seem to recreate with the following demo:

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/tursodatabase/go-libsql"
    "log"
    "os"
)

func main() {
    url := "http://127.0.0.1:8080"

    db, err := sql.Open("libsql", url)
    if err != nil {
        fmt.Fprintf(os.Stderr, "failed to open db %s: %s", url, err)
        os.Exit(1)
    }
    defer db.Close()

    // Create the users table if it doesn't exist
    _, err = db.Exec(`CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT CHECK (length(name) = 5)
    )`)
    if err != nil {
        log.Fatal(err)
    }

    _, err = db.Exec(`INSERT INTO users (name) VALUES (?)`, "Alice")
    if err != nil {
        log.Fatal("should not fail: ", err)
    } else {
        log.Println("insert success: expected")
    }

    _, err = db.Exec(`INSERT INTO users (name) VALUES (?)`, "Alice-should-fail")
    if err != nil {
        log.Fatal("should fail: ", err)
    } else {
        log.Println("insert success: unexpected")
    }

}