Hi,
I have an app that i’ve built in development and it relies heavily on the database for the structure of the app. Basically the majority of what has been built requires the exact data I have in my dev database.
Other than the obvious, export the dev database and import to production, are there alternative simpler methods that could allow me to copy the state of my local database to production and potentially vice versa later on?
What database system are you using. If SQLite then just copying the file from dev to production might be sufficient. Just keep in mind that Rails does track what environment a database was created for so you might need to tweak that data.
I’m hard-pressed to think of a simpler method than a database dump and reload.
What problem(s) are you trying to avoid?
It’s a PostgreSQL database.
Just trying to see if there’s a faster quicker method.
Eg, I remember when I used to use Heroku years ago you could literally just run rails db:push and it would push your dev database to production.
Just wondering if there was something similar I could use as even the dumping the DB and reloading it (as fast as that can be), can take a while and be a pain.
If you are looking for a rake or rails command to help you do that, I don’t think there is one 
I have done this a few times, not only from dev to prod but from one prod db to another, and the fastest way I have found is to use the PostgreSQL copy command with binary format. If you are transferring a large amount of data, that must be the fastest way. What you need is a shell that has access to both databases, so you could run something like this to pump data from one database to another:
psql -h SOURCE -d db -c "COPY schema.table TO STDOUT WITH (FORMAT binary)" \
| psql -h DEST -d db -c "COPY schema.table FROM STDIN WITH (FORMAT binary)"
You have to repeat it for every table.
Another thing to try could be to see if you can construct the same data using a seed.rb file, but probably won’t be the fastest way.
Hmmm, ok.
May just go back to using dump and restore instead. There are quite a few tables so table by table won’t be any faster.
I have the feeling that relying on data in your development database is not a good strategy to start with - do you have backups for this database? What happens if you work on a different machine or yours needs to be replaced due to loss/damage?
If the data is that crucial it should probably be versioned and potentially can be created via database seeds which is a proven and recommended approach to prepare the database for the application.
This sounds like a good use case for database seeding: Active Record Migrations — Ruby on Rails Guides
Load your db/seeds.rb with what is needed to be up and running in production and that makes the process of setting up a new production environment nicely documented. If you have a lot of records to add you can load them in via a YAML file or something like that from within your seeds file. Ideally your seeds file should be idempotent so that you can run it multiple times and it only adds the desired records if they’re missing.
It’s tricky to explain why it’s like this.
This is an inherited app that I am updating and will then push live.
Basically the structure of the site is 80% in the database, the names of Pages, content, templates, the lot. I’m not saying it’s a great idea but it’s what I ahve to work with.
Therefore pages in the DB rely on files in the app that reference specific names. If i push the app with an empty DB then yes the home will load but it literally is pointless.
A seed file could work I guess but some tables have 50,000+ records of text based content and a similar amount of Active Storage records too.
It’s basically a way to push the current state of development so I can share with others. A sort of staging environment. If i’ve made changes to the DB content whilst developing then the DB also needs to be updated on the staging version otherwise layouts and stuff may not look correct.
Seeds are still the recommended way of doing this, but they’re not designed for more than a couple of hundred records or so - think categories for articles.
Categories and articles is a great example though. While you might have to bring over all records of the categories table, you usually don’t want to push the articles too as they might be just test data and your production site will receive it’s own articles.
In that case you probably have to go back to dumping the DB where you can also select which tables to dump and which not (for postgres at least)
This sounds like outside the scope of something maintainable in a seed file. But your needs also sound outside the scope of something Rails provides out-of-the-box so likely something you might need to roll yourself.
I did see somewhat recently on the Kamal boards another person doing the same thing. The ended up sharing a shell script and eventual a tool they created. If not something you can use directly, perhaps something you can at least adapt from.
It sounds like the db is the code here. So, perhaps start treating db content like code.
So; when you change db content, you have a rake task that dumps it to a file within the app directory
/config/data/dump.sql
(this is in source control - because it is the app)
When you deploy - that runs a rake task which loads the latest dump into the db.
Are you thinking of yaml_db? It has unfortunately fallen out of date (seeking new maintainers), but if the functionality is important enough to your project, you might be able to find a working fork (at least for Postgres).
I use the Seedbank gem for data that is consistent in all environments e.g. geodata, user groups, categories, tags. Unlike a database dump, this populates data through your ActiveRecord models. It will not be nearly as fast as data dump and reload.