Knex does not play nice with dot-notation for table names (sqlite)

[mshwery]

Environment

Knex version: v0.15.2 Database + version: sqlite3 OS: MacOS and Linux

Bug

1. Explain what kind of behaviour you are getting and how you think it should do

Knex splits table name variables on period characters, interpretting them as dot-notated identifiers (despite being used explicitly as a tableName variable). I've attempted various ways of escaping the entire table name, but it seems that those efforts are disregarded by a basic split on /\./g. Even when using a custom wrapIdentifier the dot-delimited words get split first. I couldn't find any documented way to keep the table name as a literal string. Doing this in sqlite directly is simple:

CREATE TABLE "my.table.name" (
  id int primary key,
  data text
);

Doing this the knex way fails:

knex.schema.createTable(`"my.table.name"`, table => {
  table.integer('id')
  table.text('data')
  // etc...
})

I've attempted to pass in tableName wrapped in double-quotes, but that does nothing.

2. Error message

'create table `"my`.`table`.`name"` (`id` int primary key, `data` text) - SQLITE_ERROR: near ".": syntax error'

3. Reduced test code: https://runkit.com/embed/emvu0aryey1m

[mshwery]

I'm not sure where this is explicitly unsupported by knex or not, but it definitely is supported by sqlite.

I did find a potential workaround by using:

knex.raw(`"my.table.name"`)

[kryten87]

This problem also arises when using the schema builder and the withSchema method.

I'm developing an API against a legacy MySQL database with tables spread over a number of different databases. I am writing unit tests against an SQLite3 mock of our database, using migrations and seeds to create an populate relevant tables. So, I want to write my migrations like this:

knex.schema.withSchema('mydb').hasTable('mytable').then((exists) =>
    exists || knex.schema.withSchema('mydb').createTable('mytable', (table) => {
        // create table columns here
    })
)

This will work on MySQL, but fails on SQLite3 because Knex builds an invalid table name that looks like 'mydb'.'mytable' (those single quotes should be backticks).

One approach is to use fully-qualified table names on MySQL and a table name without scheme on SQLite3, using a simple helper function to decide which to use at run time. The problem with this is that it breaks migrations on MySQL, since the hasTable schema method does not work with fully-qualified table names (at least on MySQL).

At this point, I'm stuck with having to write my migrations to use withSchema if the database client is MySQL and generating a "scheme-less" table name on SQLite3. This kind of subtracts from the portability that Knex promises.

[elhigu]

Knex does not support dots in table names, but that is not a bug, but by design. So if you are using dots in table or column names you will be better without knex.

Proper feature request getting support for that would be welcome though. Closing as not a bug.

[CountRochester]

found the answer: use wrapIdentifier: value => value in options when you initialize the knex:

const db = knex({
  client: 'sqlite',
  connection: {  
    host,
    user,
    password,
    database
  },
  wrapIdentifier: value => value
})

[elhigu]

@CountRochester some cases might work like that, but other places, which relay on parsing identifier with dot as <table_name>.<column_name> and then use those parts separately in other parts of the query may fail.

[gpetrov]

This is becoming more of a pressing issue that knex doesn't support dots in table names as we get more and more users having databases with dots in the table names using MS SQL, MySQL and SQLite where it is perfectly allowed.

Unfortunately knex tries to be too smart and splits the table name on dots while it shouldn't.

Maybe we need to reopen this and find more smart ways of handling table names? @kibertoad @OlivierCavadenti

[alexcrist]

Thank you @mshwery ! To add context to the workaround you mentioned- to create a table with a column name that uses dot notation-

exports.up = async (knex) => {
    await knex.schema.createTable("my_table", (table) => {
        table.integer(knex.raw('"position.x"'));
    });
};