
Ask HN: How do you maintain local database for development? - raviojha
Since last 3 years, I&#x27;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.<p>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.<p>How do you maintain local databases for development?
======
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.

------
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/](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/](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](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.

------
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.

------
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.

------
cdnsteve
Are you using Django migrations?
[https://docs.djangoproject.com/en/1.10/topics/migrations/](https://docs.djangoproject.com/en/1.10/topics/migrations/)

python manage.py migrate

------
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.

------
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.

