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 :)

Answer 2

You can point both Strapis (dev and production) to use the same database, in your case Heroku's Postgres. If the both uses the same database, then modifications made to datastructure and content in dev-strapi are available also in production. Datastructure (for ex. new content type) won't be available until you push a new version to Heroku, but the new content using the old datastructures will be available in production-strapi also (both strapis use the same database -> same data).

EDIT: following lines are not valid anymore, the newest version of strapi uses little bit different approach for configuring database connection (see video link from another answer)

You can define database connections in dev-strapi:
\config\env\development\database.json
\config\env\production\database.json

EDIT ENDS

In Heroku's Postgres I believe you have made env-variables for database connection. You can make same variables for dev-version by creating .env-file (you'll find all info neede from Heroku's variables):

DATABASE_HOST=xxx-yyy-zzz.eu-west-1.compute.amazonaws.com
DATABASE_PORT=XXYY
DATABASE_NAME=xxxyyyxxx
DATABASE_USERNAME=xxxxxxx
DATABASE_PASSWORD=123123123xyzxyz

Answer 3

sounds like you are actually wanting to migrate your data between environments... this is not currently supported by strapi. you can try finding or writing your own db seed file to move the data between environments.

dont use the same db from different environments.

this was already answered correctly, not sure why no correct answer has been selected.