Ask HN: Advice for a first-time DBA on a self-built e-commerce app? | Hacker News Reader

Ask HN: Advice for a first-time DBA on a self-built e-commerce app?

3 pointsaerovistae3 comments

I'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're just not something I personally find interesting or enjoyable.

But now I'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'm nervous.

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' money on the line I'm afraid to be in the position of making such mistakes.

In front-end development, I'm fairly well-versed on best practices and how to build a site that'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't know exist yet. SQL injection is the only pit I know to keep an eye out for.

How can I make the best of this? How can I become a competent DBA for myself when money's already on the line without screwing anyone over with amateur mistakes? Just looking for advice from anyone else who's had to do anything similar.

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

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.

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

Reply on news.ycombinator.com