strapi database.js / multiple database configs

Question

1. Strapi works locally if im running sqlite in the database.js but not if I'm running postgres / I've found online that I can specify that npm run develop uses sqlite, and production should use postgres.

For REF - I found this answer here: https://github.com/strapi/strapi/discussions/6832

Can anyone show me how to set this up as I am really finding it hard to read the docus for this issue.

Currently in the file structure:

config/database.js

I have these two set ups (for local and heroku) - I comment out the postgres set up for heroku to work locally

module.exports = ({ env }) => ({
  defaultConnection: 'default',
  connections: {
    default: {
      connector: 'bookshelf',
      settings: {
        client: 'sqlite',
        filename: env('DATABASE_FILENAME', '.tmp/data.db'),
      },
      options: {
        useNullAsDefault: true,
      },
    },
  },
});


module.exports = ({ env }) => ({
  defaultConnection: 'default',
  connections: {
    default: {
      connector: 'bookshelf',
      settings: {
        client: 'postgres',
        host: env('DATABASE_HOST', '127.0.0.1'),
        port: env.int('DATABASE_PORT', 27017),
        database: env('DATABASE_NAME', 'strapi'),
        username: env('DATABASE_USERNAME', ''),
        password: env('DATABASE_PASSWORD', ''),
      },
      options: {
        ssl: false,
      },
    },
  },
});

---

2. When I pushed my strapi project to Heroku the correct structure is there i.e. names of articles (SHOWN IN THE BELOW IMAGE) - but none of the content is there: i.e. posts, images etc

What I imagine is happening is because the local strapi has been built using sqlite and heroku required me to use postgres as a database, the databases are not the same so the data is not being read correctly?

(I could be wrong about that....)

In this case: how do you move your local dev (recommended quick start set up) to your production site?

• Do you npm run build? and then host strapi on your normal front end site?

• is there a way to move the data from one database to another?

Sorry for asking many small questions here - I might have missed a concept which ties it all together.

Thanks in advance for any help, Wally

Answer 1

I found a very useful Youtube video that actually explains this process for step 1)

How to run a different database depending if it is being used by dev or production.

Short answer is in the database.js file you write an if statement to see if your using dev or production:

Once I have implemented this I will write a full answer :)

VIDEO LINK: https://www.youtube.com/watch?v=xNE0TrI5OKk

---

PART 2: Can you migrate the work you have done in strapi to a production database, for example Heroku....?

Simple answer is sadly (and hella frustrating) - NO!

This is from Strapis site:

Does Strapi handle deploying or migrating of content? Strapi does not currently provide any tools for migrating or deploying your data changes between different environments (ie. from development to production). With the exception being the Content-Manager settings, to read more about this option please see the following CLI documentation.

FOUND HERE: https://strapi.io/documentation/v3.x/getting-started/troubleshooting.html#frequently-asked-questions

---

I've also been speaking with a really helpful rep on the Strapi Slack page to figure out WTF I've been doing wrong (turns out allot.....).

Anyway:

• Create your content types in your local strapi
• Push them to your online strapi (Heroku)
• Add all content online NOT locally.....

SMALL RAY OF HOPE?

I have not done this yet but apparently you can manually transfer the data and convert it from sqlite to postgres by using:

You will have to do a data dump using some local DB client (DBeaver supports SQLite) then import that data onto the PG server (you can also use DBeaver there) Strapi doesn't have any tooling or suggestions for content migration between environments currently.

Well I hope this helps anyone else who comes across this issue like me....

Wally :)