Some observations on MySQL to sqlite migration & compatibility

I’m experimenting with sqlite as backend database for orchestrator. While orchestrator manages MySQL replication topologies, it also uses MySQL as backend. For some deployments, and I’m looking into such one, having MySQL as backend is a considerable overhead.

This sent me to the route of looking into a self contained orchestrator binary + backend DB. I would have orchestrator spawn up its own backend database instead of connecting to an external one.

Why even relational?

Can’t orchestrator just use a key-value backend?

Maybe it could. But frankly I enjoy the power of relational databases, and the versatility they offer has proven itself multiple times with orchestrator, being able to answer interesting, new, complex questions about one’s topology by crafting SQL queries.

Moreover, orchestrator is already heavily invested in the relational model. At this time, replacing all SQL queries with key-value reads seems to me as a significant investment in time and risk. So I was looking for a relational, SQL accessible embeddable database for orchestrator.

Why sqlite?

I am in particular looking at two options: sqlite (via the go-sqlite3 binding) and TiDB. sqlite does not need much introduction, and I’ll just say it’s embeddable within the golang-built binary.

TiDB is a pure-Go, MySQL dialect compatible server which provides relational model on top of key-value store. The store could be local or distributed, and the TiDB project cleverly separates involved layers.

Of the two, sqlite is mature, alas uses a different SQL dialect and has different behavior. TiDB’s compatibility with MySQL is an impressive feat, but still ongoing.

Both require adaptations of SQL code. Here are some observations on adaptations required when moving an existing app from MySQL backend to sqlite backend.

Differences

Just to answer an obvious question: can’t everything be abstracted away by an ORM that speaks both dialects?

I don’t think so. I always exploit SQL beyond the standard insert/delete/update/select, exploits that ORMs just don’t support.

Here’s an incomplete list of differences I found. Some purely syntactical, some semantical, some behavioral, and some operational.

• Data types: no CHARACTER SET clause
• Data types: you can’t associate UNSIGNED to any int type
• Data types: no enum. However there’s an alternative in the form of:
  race text check (race in ('human', 'dog', 'alien'))
• auto_increment is called integer
• Data types: timestamps are not a thing. There’s no timezone info.
• TIMESTAMP has no ON UPDATE clause.
• No after clause for adding columns
• Indexes are not part of table creation. Only PRIMARY KEY is. The rest of indexes are created via CREATE INDEX statement
• Indexes have unique names across the schema. This is unfortunate, since it forces me to use longer names for indexes so as to differentiate them. For example, in MySQL I can have an index named name_idx in two different tables; in sqlite I append table name for “namespacing”
• Temporal values and functions: poor support for time arithmetic.
  • Getting the diff between two datetimes is non-trivial (what’s the diff in months for a leap year?)
    
  • INTERVAL keyword not respected. Appending/subtracting dates can be done via:
    datetime('now', '-3 hour')
    Can you see the problem in the above? What is the number 3 is a positional argument? In MySQL I would use NOW() - INTERVAL ? HOUR. To make positional arguments work in sqlite, the above gets to be datetime('now', printf('-%d hour', ?)). How would you even translate NOW() - INTERVAL (? * 2) SECOND?
  • UNIX_TIMESTAMP not respected. Instead using strftime('%s', 'now'), I dislike the use of string functions to generate times.
• insert ignore turns to insert or ignore
• replace into remains replace into. Yay!
• insert on duplicate key update has no equivalent. It’s worthwhile noting a replace into does not replace (pun intended) an insert ... on duplicate key as the latter can choose to only update a subset of column in the event of a constraint violation. It’s really very powerful.
• IS TRUE and IS FALSE are not respected.
• ALTER TABLE:
  • When adding a not null column one must specify the default value (e.g. 0 for an int)
  • You cannot add a timestamp column that defaults to CURRENT_TIMESTAMP. You can have such column in your CREATE TABLE definition, but you cannot add such a column. The reason being that CURRENT_TIMESTAMP is not a constant value. When adding a column to a table, sqlite does not actually apply the change to all existing rows, but only to newly created ones. It therefore does not know what value to provide to those older rows.
  • You cannot DROP COLUMN. I’ll say it again. You cannot DROP COLUMN
  • You cannot modify a PRIMARY KEY
  • You cannot rename a column or change its datatype.
  • In fact, the only supported ALTER TABLE statements are ADD COLUMN and RENAME (renaming the table itself)

Regarding the ALTER TABLE limitations, the advice for dropping/changing/renaming columns or changing the primary key is to “create a new table with the new format, copy the rows, drop the old table, rename”. This is certainly feasible, but requires a substantial overhead from the user. And it’s non atomic. It requires a change in the state of mind but also a change in state of operations, the latter being non-trivial when moving from one DBMS to another, or when wishing to support both.

I’m still looking into this, and trying to work my way around differences with cheap regular expressions for as much as possible. I’m mainly interested right now in finding all semantic/logic differences that would require application changes. So far the TIMESTAMP behavior is such, and so is the INSERT ... ON DUPLICATE KEY statement.