Support converting struct to sql.Table for gorm

[chrskjoe]

Lots of ORM framework like gorm.io/gorm use struct to represent database table. It would be handy if there is a function to convert struct directly to sql.Table and we don't need to modify the sql.Schema each time when table ois updated. If this feature is needed, I am happy to contribute some pull requests 😃.

[fulghum]

Hey @liberix, thanks for checking out go-mysql-server!

I'm not familiar with gorm, but it sounds like it inspects go code, generates SQL DDL statements for creating the schema based on the structs it finds in the code, and then databases can use those DDL statements to create the schema. That should already work with go-mysql-server, so I'm not sure we'd need to add anything in order to support that. The caveat is that some SQL syntax isn't support yet. @VinaiRachakonda has been digging into support for ORMs like this and seeing how well they work with Dolt and go-mysql-sever, so this one might be an interesting one for him to test out. Let me know if I misunderstood what you were suggesting though.

If you try using gorm and run into syntax that doesn't work with go-mysql-server, we'd appreciate the feedback so we can get it working!

[chrskjoe]

Hi @fulghum, thanks for your reply!

What I want underscore is not how gorm interact(retrive data, update data etc) with database, it's only the definition of the database table.

I mainly use go-mysql-server to do unit tests. For example, We have struct：

type User struct {
    id int64 `gorm:"column:id;type:bigint"
    name string `gorm:"column:name;type:text"`    
} 

as the definition of table, gorm can use this struct to interact with the data in this table, like:

var user User
var u User  
// works because destination struct is passed in
db.Model(&User{}).Where("name =  John").First(&u)

But go-mysql-server has another rule for defining a table as shown in example:

table := memory.NewTable("user", sql.Schema{
    {Name: "id", Type: sql.Int64, Nullable: false, Source: tableName},
    {Name: "name", Type: sql.Text, Nullable: false, Source: tableName},
})

What I want to propose is maybe we could have a function or anything else can do things like:

table := memory.ParseGormStruct(&User{})

So we can just update the struct when the table definition is changed. Thanks 😃

[fulghum]

Ahhh, I think I see what you mean now. Thank you for the extra details. So instead of marshalling SQL statements over the server interface to a go-mysql-server in a separate process in order to create the tables that are modeled by structs, you'd like to do this all inside a single Go process to create the tables directly using the gorm syntax?

Out of curiosity, is there a specific use case you're thinking about? Something like embedding go-mysql-sever for structured data storage inside an application? What would the interface for storing/retrieving data look like?

My gut feeling is that it would be best to build this as a separate layer on top of go-mysql-server, to explore the idea further, see how it works, and see if it becomes a feature enough go-mysql-server customers would want to warrant pulling it into the core of go-mysql-server. If you do end up building something, we would love to stay in touch, see how it works, and of course we'd be happy to answer any go-mysql-server questions, too.

[zachmu]

This should already work over the wire (by issuing SQL statements to a running server) . If you find that it doesn't, that's a bug. Please let us know if you find any incompatibilities there.

If you wanted to build a compatibility layer for gorm that doesn't need to use a running server, we would accept a patch.