Repository: stephenafamo/bob (go)

Issue:
SQLite generation fails due to implicit foreign key column
I was trying to set up bob in my existing project, when I ran into the following cryptic error:

`unable to fetch table data: sql: Scan error on column index 4, name "to": converting NULL to string is unsupported`

I found that this error was coming from the foreign key list unmarshaling [here](https://github.com/stephenafamo/bob/blob/891dd5a56ba2731ab942801e1f95b5ae544f214f/gen/bobgen-sqlite/driver/sqlite.go#L374). It was caused by my tables having implicitly defined columns on my foreign keys; something like this:

```sql
CREATE TABLE instance(
  id INTEGER PRIMARY KEY
);
CREATE TABLE endpoint(
  id INTEGER PRIMARY KEY,
  instance_id REFERENCES instance
);
```

I know this isn't best practice to begin with and have since switched my schema to explicitly define the column, but perhaps a clearer error might help other users who run into a similar issue.

Thanks for this cool library!


Discussion:
Thanks for finding this bug.

I did not account for implicit foreign keys when creating the SQLite plugin so I would look into that and fix. I checked and this syntax is also valid for Postgres, so I would have to check that too.

Whether or not it is *best practice*, it is perfectly valid and not deprecated, so **Bob** should definitely support it.

Patch:
diff --git a/CHANGELOG.md b/CHANGELOG.md
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -12,6 +12,10 @@ and this project adheres to [Semantic Versioning](https://semver.org/spec/v2.0.0
 - Add `bobgen-sql` a code generation driver for SQL schema files. Supports PostgreSQL and SQLite.
 - Add new properties `compare_expr` and `compare_expr_imports` to the `types` configuration. This is used when comparing primary keys and in testing.
 
+### Fixed
+
+- Properly detect reference columns for implicit foreign keys in SQLite.
+
 ## [v0.25.0] - 2024-01-20
 
 ### Changed
diff --git a/gen/bobgen-sqlite/driver/sqlite.go b/gen/bobgen-sqlite/driver/sqlite.go
--- a/gen/bobgen-sqlite/driver/sqlite.go
+++ b/gen/bobgen-sqlite/driver/sqlite.go
@@ -7,6 +7,7 @@ import (
 	"sort"
 	"strings"
 
+	"github.com/aarondl/opt/null"
 	helpers "github.com/stephenafamo/bob/gen/bobgen-helpers"
 	"github.com/stephenafamo/bob/gen/drivers"
 	"github.com/stephenafamo/scan"
@@ -366,12 +367,13 @@ func (d driver) foreignKeys(ctx context.Context, schema, tableName string) ([]dr
 	fkeyMap := make(map[int]drivers.ForeignKey)
 	for rows.Next() {
 		var id, seq int
-		var ftable, col, fcol string
+		var ftable, col string
+		var fcolNullable null.Val[string]
 
 		// not used
 		var onupdate, ondelete, match string
 
-		err = rows.Scan(&id, &seq, &ftable, &col, &fcol, &onupdate, &ondelete, &match)
+		err = rows.Scan(&id, &seq, &ftable, &col, &fcolNullable, &onupdate, &ondelete, &match)
 		if err != nil {
 			return nil, err
 		}
@@ -381,6 +383,17 @@ func (d driver) foreignKeys(ctx context.Context, schema, tableName string) ([]dr
 			fullFtable = fmt.Sprintf("%s.%s", schema, ftable)
 		}
 
+		fcol, _ := fcolNullable.Get()
+		if fcol == "" {
+			fcol, err = stdscan.One(
+				ctx, d.conn, scan.SingleColumnMapper[string],
+				fmt.Sprintf("SELECT name FROM '%s'.pragma_table_info('%s') WHERE pk = ?", schema, ftable), seq+1,
+			)
+			if err != nil {
+				return nil, fmt.Errorf("could not find column %q in table %q: %w", col, ftable, err)
+			}
+		}
+
 		if d.skipKey(fullFtable, fcol) {
 			continue
 		}


Test patch:
diff --git a/test/files/sqlite/schema.sql b/test/files/sqlite/schema.sql
--- a/test/files/sqlite/schema.sql
+++ b/test/files/sqlite/schema.sql
@@ -25,7 +25,7 @@ create table video_tags (
 	tag_id   int not null,
 
 	primary key (video_id, tag_id),
-	foreign key (video_id) references videos (id),
+	foreign key (video_id) references videos, -- Implicitly references videos(id)
 	foreign key (tag_id) references tags (id)
 );