
Ask HN: Advice for a first-time DBA on a self-built e-commerce app? - aerovistae
I&#x27;ve been a developer for 7 years, primarily front-end, and have managed to avoid doing any work with databases up until now because they&#x27;re just not something I personally find interesting or enjoyable.<p>But now I&#x27;ve built an ecommerce site that takes customer orders for a product, and I have to set up and manage my own database, and I&#x27;m nervous.<p>I know how easy it is to make a mistake as simple as a typo and screw up your schema or delete records, and with customers&#x27; money on the line I&#x27;m afraid to be in the position of making such mistakes.<p>In front-end development, I&#x27;m fairly well-versed on best practices and how to build a site that&#x27;s sturdy and flexible and secure. I have no idea how to do any of those things with a database and am worried about falling into pits I don&#x27;t know exist yet. SQL injection is the only pit I know to keep an eye out for.<p>How can I make the best of this? How can I become a competent DBA for myself when money&#x27;s already on the line without screwing anyone over with amateur mistakes? Just looking for advice from anyone else who&#x27;s had to do anything similar.
======
hakanderyal
\- In my projects, I rarely run ad-hoc insert/update/delete queries on live
production database. Instead, I write scripts using the language I used for
the app for all database operations. I test the script on a local database,
make sure there is no unintended side effects, than run the script on the
production database, thus avoiding any chance of typos or wrong queries
messing with the database. In any case, I also take a backup right before I
run the script on production.

\- For SQL injection, if you are using an ORM or library, it should have safe
parameter binding for queries, make sure to follow its guidelines.

\- It's mandatory to have an automated backup system for your database to
ensure you can recover from any data loss scenario. Remember, a backup is only
valid if you have tested to restore your database from it. So ideally your
automated backup system should also check if the backup is working properly. I
realize it's a lot of work, but it'll pay off when (not if) you encounter a
disaster scenario that requires you to restore from backup.

At the very least, create an automated backup solution, and test it manually
periodically to make sure it's working properly.

Store your backups in two or more separate places, if possible. Always encrypt
your backups. You can check out Tarsnap.

------
segmondy
Hire a DBA to give you a crash course. Use managed DB like AWS RDS where they
backup and take care of installs.

DB's are generally very stable and don't need much maintenance. Most of your
work really is from the dev side, schema design, good index placement.

------
rajeshamara
1)Have stored procedures with parameters for all you backend transactions
2)Make sure you have foreign key constraints between tables 3)Log everything
so that you can track 4)Daily backup your db so that you can restore in case
something happens 5)Create primary keys for each table 6)For all you order
make sure if is between transaction and roll back in case of failures 7)After
successful, payment transaction, send out email notification to the end
customer. You can cc yourself too. Again this if for auditing purpose. What I
came up is a very quick list. Again you need to thoroughly analyze your req.
and come up appropriate solutions

