HN Explorer

Ask HN: How do you maintain local database for development?

12 points by raviojha ↗ HN
Since last 3 years, I'm working at the company where every new developer faces this problem. We use Django and keeping the models and database tables in sync and have some data to work with in those tables is a tough job. I have seen new developers spend hours on setting up database for local development.

We tried to have a single aws instance where the tables are in sync and has some random data to play with, but it makes local development quite slow. Atleast not as fast as how it worked when we all had a local machine database.

How do you maintain local databases for development?

12 comments

[ 3.4 ms ] story [ 44.4 ms ] thread
informatimago
Well, I didn't have the problem in the case of multiple developers, but then I didn't have git.

One simple way is to enforce the rule that the database definition (schema) should be in the repository, along with a script creating and filling the test database.

When a new version of the database schema is commited, both the code to update dynamically the user database in the software, and the script to create the test database shall be commited along.

Then when you update your sandbox, you can either:

- run the script to reset your test database to the current version (for the revision that is checked out).

- compile and run the software to upgrade your old test database (any version) to the current version.

symfony_
Well... maybe you could go old-school and just put down a physical server in your local network which hosts the DB?

So basically you have the same setup as with your AWS instance, minus the network latency.

Everyone wants to go fancy and host everything in the cloud nowadays, but a local server for development can be very useful still!

douche
We're mostly a MS shop, so we tend to use MS SQL Express instances on our dev machines. For the bulk of what we need to do day-to-day, this is exactly the same as if we were dealing with a full SQL server instance, a SQL cluster, or an Azure SQL database instance.

One of the few times I will use a cloud database instance in development is to doublecheck performance of database queries, as the cloud instances will have considerably more latency than you'd ever see running a local database on a beefy workstation.

raviojha
Yeah, we are trying to have a local machine to which everyone can connect to. Actually, some developers also work from home and hence we thought let's keep a local database in every machine. So, I'm exploring what everyone else is doing.
sanderson1
One common mistake is thinking that you need a fully-fleshed-out dataset with real data. You don't. You just need the bare-bones structure with some generic data, which can easily be accomplished with a seed file. Django calls these fixtures: https://docs.djangoproject.com/en/1.10/howto/initial-data/.

Once the initial data is seeded, you then just need to implement migrations to update the database as the structure is modified. These too are already built into Django: https://docs.djangoproject.com/en/1.10/topics/migrations/.

With those in place, every developer can have their own local environment with its own database. I personally use Nanobox (https://nanobox.io) to spin up dev environments with local databases. I then can console into the environment, run the seeding process and any migrations I need too. You just need to set up a version control workflow to keep everybody's codebase in sync.

raviojha
Yeah, idea to have fixtures is good. We recently migrated from django 1.4 to 1.10 and this time we are tracking all migrations in repo, so atleast the part of maintaining the schema is not a pain anymore. Thanks for mentioning nanobox, will explore.
techspring
I typically start a docker container running the DB that I need and then import a backup of any existing data. This could be taken even further by keeping an image with the current version of the DB in a private registry, so developers can pull down the latest and spin up within a few minutes.
dyeje
The way Rails does this is with timestamped migrations. I assume Django has something similar.
Avalaxy
- ALWAYS have a local database. No dependencies on remote database where other people are changing data, changing tables, etc.

- Make sure there is a create script with change scripts checked into source control that brings your local database to the latest state. For ASP.NET specifically I use a database project which contains XML files that describe the state of the database. You can then just use the compare tool to bring your own DB up to the same state, and reversely you can merge changes in your own DB back to the database project to deploy to other people.

- Every developer can fill his local database with his own database. Whatever that may be.

mod
My process is:

1) Run same db locally as production (postgresql for me)

2) Use migrations

3) Use seeds/fixtures

It's really that simple. Usually I just use fake, example data on my local, but sometimes, if required, I dump the remote and import it in my local.

I've never really had any major issues with this problem. Setting up your environment the first time can take a while, but subsequently you're better at it and you cut that time down to a small amount, especially if you've maintained the migrations and seeds well.

cauterized
There are some tools you can use to populate a local database with dummy data. Django's support for model introspection makes it especially easy. I'm on my phone and can't remember the name of the tool I last used for that, but you can probably pull something up on google.

Another common approach (if you already have a production environment) is to write a script to sanitize production data. You replace sensitive personal info with dummy data and replace all email addresses and phone numbers and the like with ones your company owns so that people don't receive communications from Dev environments. Then you just import that database, and can refresh it as needed.