Is it possible to author a driver for a database that does not support auto-incrementing column types?

[tmc]

Some databases do not support auto-incrementing column types, Cloud Spanner comes to mind.

Could you provide some direction on the right way to write/support this type of database driver? I presume some extension of sqlgraph and an optionally implemented interface (or two) would be involved.

Please advise.

[crossworth]

Hello @tmc, I'm not sure if I understood your question, Ent has a lot of hooking points, the Driver is one of them, but is relative simple and most of the time only delegates to the database/sql driver.

What you probably want is to add another dialect if the database is not compatible with the wire protocol of the current supported databases. You may have to add a few checks to the Builder and last you will have to add support for the schema, I know that you are adding support for Spanner on Atlas, so this should be quite easy for you.

One way to visualize the operations (like Query/Insert/Update/Delete) is like this (not quite right, there are a few hooks here and there and few parts were omitted):

image generated using https://github.com/terrastruct/d2

Now, about the question related to auto-incrementing, Google has an article talking about it, and another article talking about how migrate from MySQL.

I would like to quote a few parts from the articles:

In fact, using monotonically increasing (or decreasing) values as row keys does not follow best practices in Spanner because it creates hotspots in the database, leading to a reduction in performance.

using monotonically increasing numbers as primary keys is an anti-pattern in Cloud Spanner. An alternative mechanism of generating a unique primary key is to use a random UUID.

With that in mind, I guess one of the solutions would be to disallow auto-increment and only support custom IDs (like UUID, XID, ULID).

Since Cloud Spanner itself don't impose an ID type, it's hard to say what the best option would be.

Also worth reading: Schema design best practices.

With all that said, if you really want to implement auto increment, one solution would to have a table to control and generate the ids, Cloud Spanner don't seem to support stored procedures, so you will have to do this on the application side, maybe you can use Ent hooks or implement as custom ID type.

[JoshGlazebrook]

@tmc Are you still working on this?

[tmc]

@JoshGlazebrook no not actively.