Making query generation less dependant on model.

[elliotcourant]

I'm currently migrating a large project from go-pg to bun. As I would love to be able to add support for multiple databases for self-hosted options. To that end, I need to convert my current SQL-based migrations (which are PostgreSQL focused) to go based migrations that might need to take into account differences in databases. For example:

			createLoginTable := db.NewCreateTable().
				Table("logins")

			switch db.Dialect().Name() {
			case dialect.PG:
				createLoginTable.ColumnExpr(`"login_id" BIGSERIAL NOT NULL`)
			case dialect.MySQL:
				createLoginTable.ColumnExpr(`"login_id" BIGINT NOT NULL AUTO_INCREMENT`)
			case dialect.SQLite:
				createLoginTable.ColumnExpr(`"login_id" BIGINT NOT NULL AUTOINCREMENT`)
			default:
				return errors.Errorf("unsupported dialect")
			}

			createLoginTable.ColumnExpr(`"email" TEXT NOT NULL`)
			createLoginTable.ColumnExpr(`"password_hash" TEXT NOT NULL`)

But this type of approach isn't currently supported by bun, as it required a model struct be provided in order to generate the queries.

I'm wondering if there would be a way to expose more basic versions of methods like addField publically so they can be leveraged programmatically rather than a strictly typed struct?

To that end it would let me migrate all of my existing migrations to bun nearly line for line (albeit in golang instead of SQL) and achieve an identical final DB state when compared to go-pg. While also supporting other databases like SQLite.

Other databases might need additional nuances as well. For example; a 00000000000000_init.go migration might be different between databases. In Postgres it might need to install extensions; in SQLite it might need to run PRAGMA foreign_keys = ON;

I might be overcomplicating things a bit and if I am please let me know; I really just want to be able to support all 3 databases with a single code base and as similar a migration structure between the DBs as possible.

Curious what your thoughts are @vmihailenco

[vmihailenco]

But this type of approach isn't currently supported by bun, as it required a model struct be provided in order to generate the queries.

We can definitely make model optional.

I'm wondering if there would be a way to expose more basic versions of methods like addField publically so they can be leveraged programmatically rather than a strictly typed struct?

I guess we would need to support something like this

col1 := bun.NewColumn("col1").Type(sqltype.Blob).AutoIncrement()
db.NewCreateTable().AddColumn(col1)

So drivers have a chance to translate sqltype.Blob into a local dialect.

Other databases might need additional nuances as well. For example; a 00000000000000_init.go migration might be different between databases. In Postgres it might need to install extensions; in SQLite it might need to run PRAGMA foreign_keys = ON;

I guess it could work for some common cases if you are willing to make some compromises in order to support different DBMS.

Which brings the question - do you really need to support different DBMS and are ready to invest some time into it? :) Because many simple things become complicated when you want to do it for all 3 DBMS...

[elliotcourant]

Making the model optional, and having a way to build the column queries like that would be absolutely perfect.

It isn't my highest priority right now to support multiple databases; but long term I definitely want to. The application I'm working on, I want it to be able to be self hosted on top of the cloud hosting I provide. For self-hosted use-cases I'd like to be able to offer that flexibility for someone who is more advanced to use MySQL or Postgres in an HA configuration; or for someone who wants to keep things simple and just deploy a single container with SQLite.

To that end I'll keep experimenting with it as I can, and happy to contribute anything to help achieve that level of multi-database parody.

[elliotcourant]

@vmihailenco Do you think the NewAddColumnQuery should be adjusted to take a Column object?

This way we could create a Column struct that can be used in the NewCreateTable as well as the AddColumn ALTER query.

[elliotcourant]

Something like this is pretty simple to add:

package bun

type ColumnFlags uint8

const (
	NotNull ColumnFlags = 1 << iota
	AutoIncrement
)

type Column struct {
	columnName  string
	columnType  string
	columnFlags ColumnFlags
}

func NewColumn(name string) *Column {
	return &Column{
		columnName:  name,
		columnType:  "",
		columnFlags: 0,
	}
}

func (c *Column) Type(sqlType string) *Column {
	c.columnType = sqlType
	return c
}

func (c *Column) AutoIncrement() *Column {
	c.columnFlags |= AutoIncrement
	return c
}

func (c *Column) NotNull() *Column {
	c.columnFlags |= NotNull
	return c
}

But it doesn't make sense (to me at least) for this type to have an AppendQuery method on it as it is not representing a query itself.

Do you think that having the AppendQuery method on the CreateTable and the AddColumn structs instead would be best? And simply store the Column struct on those? Then each one would have it's own implementation to generate the query?

[vmihailenco]

I think it should not have AppendQuery and instead just carry information about the column, .e.g. something like
https://pkg.go.dev/database/sql#ColumnType . Then each method can use the passed information as it wants.

[elliotcourant]

On a slightly separate note; using sqltypes to have a single value to represent a type across the databases is good. But do you think the provided types are exhaustive enough? Or could something be implemented on the dialect level for some nuanced types? Like json in postgres would have a specific type, but in SQLite it might be best to just store as blob and document the differences?

[vmihailenco]

I think there should be sqltype.JSON and sqlite should convert it to sqltype.BLOB. Some types like PostgreSQL arrays won't supported at all...

I am not sure how to support VARCHAR(100) or NUMERIC(precision, scale). Go already has ColumnType but it is not usable on its own... but interfaces look promising.