Syntax error on IfNotExists() when using sqlite dialect

[Rambatino]

Problem

When trying to add a new column if it doesn't exist, when using the sqlite shim, there exists a sytax error in the SQL. Works for PSQL. Just not SQLITE.

Example

                // any model that's been inserted potentially with the column added (in the case of specs)
		_, err := db.NewAddColumn().
			Model(new(models.User)).
			ColumnExpr("beta_code VARCHAR").
			IfNotExists().
			Exec(ctx)

Error

SQL logic error: near "EXISTS": syntax error (1)

[estromenko]

Facing the same issue. Seems like SQLite does not support "IF NOT EXISTS" expression. I think the good solution would be to not add this expression to the final query and notify user about it.

Example:

// query_column_add.go AddColumnQuery.AppendQuery()

if q.ifNotExists {
	if q.Dialect().Name() == dialect.SQLite {
		log.Warn("Warning: SQLite dialect does not support IfNotExists() method, so it will be ignored")
	} else {
		b = append(b, "IF NOT EXISTS"...)	
	}
}

[github-actions]

This issue has been automatically marked as stale because it has not had activity in the last 30 days. If there is no update within the next 7 days, this issue will be closed.

[grandwizard28]

I'd like to reopen this. Running into the same issue!

[grandwizard28]

I think the solution here should to be do some sort of check for sqlite:

if column exists; 
  do nothing
else 
  run alter table add column...

The above behaviour is the intended behaviour when one is using IfNotExists().

[grandwizard28]

Hi @j2gg0s,
Would it be possible to reopen this issue?

[j2gg0s]

Hi @j2gg0s, Would it be possible to reopen this issue?

Yes, I will handle it next week.

Maybe we could add new feature flag for this?

[grandwizard28]

That would work. Here's the workaround I have implemented if it helps:

var (
	ErrNoExecute = errors.New("no execute")
)

func WrapIfNotExists(ctx context.Context, db *bun.DB, table string, column string) func(q *bun.AddColumnQuery) *bun.AddColumnQuery {
	return func(q *bun.AddColumnQuery) *bun.AddColumnQuery {
		if db.Dialect().Name() != dialect.SQLite {
			return q.IfNotExists()
		}

		var result string
		err := db.
			NewSelect().
			ColumnExpr("name").
			Table("pragma_table_info").
			Where("arg = ?", table).
			Where("name = ?", column).
			Scan(ctx, &result)
		if err != nil {
			if err == sql.ErrNoRows {
				return q
			}
			return q.Err(err)
		}

		return q.Err(ErrNoExecute)
	}
}

And this is how the function is being used:

	if _, err := db.
		NewAddColumn().
		Table("my_table").
		ColumnExpr("some_column INTEGER DEFAULT 0").
		Apply(WrapIfNotExists(ctx, db, "my_table", "some_column")).
		Exec(ctx); err != nil && err != ErrNoExecute {
		return err
	}

[j2gg0s]

I’ve added an error handling logic first.

It would be great if an adaptation solution could be provided for dialects that do not support ADD COLUMN IF NOT EXISTS,
PR is always welcome.
We can also add some IF conditions when using it, which is not mandatory.

[grandwizard28]

I had one minor request on the PR. Instead of returning fmt.Errorf("bun: add column with if not exist does not support current dialect"), can we return a dedicated error? A very rudimentary example:

ErrFeatureNotSupported

From a consumer perspective, it helps me do something meaningful if this happens. I can choose or not choose to do something on bun.ErrFeatureNotSupported.

[Rambatino]

yeah @grandwizard28 ,

		if db.Dialect().Name() != dialect.SQLite {
			return q.IfNotExists()
		}

was the solution we went with in the end.

And +1 to the ErrFeatureNotSupported error type

[j2gg0s]

@Rambatino @grandwizard28

I added an error type that includes the specific feature.