Ghost: Importer fails with sqlite unable to insert default values

[docrinehart]

Issue Summary

Tried importing a json from a GhostPro export, and got an error from the sqlite connector:

Import failed
sqlite does not support inserting default values. Specify values explicitly or use the useNullAsDefault config flag. (see docs http://knexjs.org/#Builder-insert).

MacOS High Sierra (10.13.3)
Chrome 65.0.3325.181
Export from GhostPro 1.22.1
Import to local install Ghost 1.22.3

NOTE: I have not yet tried downgrading local install to 1.22.1 and retrying import. (I haven't had enough time, but it's something I considered when gathering info for the bug report. I had assumed that GhostPro was running latest)

Steps to Reproduce

1. Exported content from hosted ghost blog as json
2. Install ghost locally
3. Add user/blog, and try to import the json from step 1
4. Import fails

Aside from trying to find the sqlite connector call and changing the code locally, I don't know if there's a way to set useNullAsDefault somewhere to try and re-import. Additionally, I think the error might be able to get caught and reworded for a friendlier user experience.

Technical details:

• Ghost CLI Ver: 1.7.1
• Ghost Version: 1.22.3
• Node Version: 8.11.1
• Browser/OS: Chrome 65.0.3325.181
• Database: sqlite (bundled with ghost cli)

[kirrg001]

@docrinehart see SQlite3 config example https://docs.ghost.org/docs/config#section-sqlite3.

Leaving this open. I need to understand if we have to set a default for this value. I thought it's only required for our test env and our test fixtures. I don't remember. Will check when i have time.

[docrinehart]

I think the message more specifically refers to a database field that isn't getting a default value. While most of the json fields have explicitly set null where appropriate, the connector doesn't know what to put into fields that aren't explicitly defined in the exported json.

Setting a value for the setting the message suggests (useNullAsDefault) on the connector call, would enable me to run locally anyway, despite some fields not being present. If I knew what fields were missing, I could manually add them to the json myself, but without digging into the code, I'm not even sure what I would need to add, or what defaulting them to null might have as consequences.

---

PS. I'll try setting it an importing and follow-up if it works.

PSS. Imported with some warnings about missing published-by user (not surprising since I used a different email) but only warnings. I do now see the bulk of the content I had as far as posts. I do notice that none of my images are available (also not surprising since it exported as json). Is there any way to export the images associated with each post during export, maybe exporting as a zip?
// Or maybe I should open a feature request for it?

[jloh]

I'm able to reproduce this at the moment with 1.22.5 installed locally via ghost install local and an export from a Ghost(Pro) blog.

Edit: I've got around this by adding "useNullAsDefault": true to my config.development.json and I'm able to import content. I do get a whole lot of errors like this though:

Post: Entry was imported, but we were not able to resolve the following user references: published_by. The user does not exist, fallback to owner user.

The database portion in my config.development.json file ends up looking like this:

"database": {
  "client": "sqlite3",
  "connection": {
    "filename": "/nobackup/ghost/content/data/ghost-local.db"
  },
  "useNullAsDefault": true
},

[kettle11]

To add to this, I run into this issue when trying to import the "Ghost Theme Unit Test" that's linked from the theme docs: https://github.com/nickisnoble/ghost-theme-unit-test

Edit:

It seems that ghost-theme-unit-test is probably outdated anyways, as it doesn't work after using jloh's workaround.

[kirrg001]

I do get a whole lot of errors like this though:
Post: Entry was imported, but we were not able to resolve the following user references: published_by. The user does not exist, fallback to owner user.

@jloh Yeah the importer is maybe a little bit too visible. Maybe a little confusing. This just describes that a post was published with a user, which the importer can't reference in the file nor in the existing database (maybe he was deleted).

[kirrg001]

The reason why people see this SQlite3 error is that missing fields of a post won't be added by the importer, not from the post model e.g. you import a post without the locale field.

We could either iterate over the post and check if fields are missing and if so, add the field and set it to null or simply use useNullAsDefault, which i think is easier and fine to use.

If there is a field which does not accept null as value, the database will complain, which is also fine. Would result in an error.