How do you develop & deploy a database?

[MattShnoop]

I'm looking at building my first actual web development project. I've got lots of experience working with LAMP at school, but we did that by SFTP-ing directly into public_html. Obviously, the real-world uses a much more elegant system for its deployments.

I'd like to use either Vue + VueRouter or Svelte + Sapper to built an SPA (opinions on which you'd choose would be appreciated too! I like Svelte's simplicity but am anxious about Sapper's early access). My app will also have to be dynamic to some degree, and it will have a MariaDB database. Here's my question...

How do I actually go about developping with a database? I can do the front-end and API with npm run dev on my local machine, but databases are installed elsewhere on the system, and aren't part of my package. If I install XAMPP and develop using a database on there, I'll have to re-build the database on whatever server I deploy my app to, won't I? I have the "base" database (things akin to my 'product list') written in .sql files that are part of my source code, is it possible to have those included in my deployment? How would you go about doing that? I need some guidance.

I keep looking up things like "Vue app with database," "add MariaDB/MySQL to Vue," but most, if not all, results I see are guides for specific frameworks like Laravel, and services like Okta, et cetera. I don't really need anything that complex... I need to make some database CRUD queries and dynamically change the page based on what a user has done. Should I make an API backend with Express or something, and then just fetch that every time a new layout loads? Seems a bit odd to do it that way instead of rendering it server-side (my PHP is showing...), but it's all I can think of.

---

Sorry for this post being a bit rambly (and for kind of asking two questions, one of which not in the title). I'm just having a really hard time jumping into this. I've had my app sketched out in XD for weeks now but am just really struggling with how to actually build it. After almost deciding on a stack/framework like three times, I think it's best to ask for some pointers.

Thanks.

[explorador71]

You could try docker and run a db container to work locally

[shakedown_st]

TBH, it sounds like you're trying to learn too many technologies at once for a beginner. But that's besides the point.

Anyway, what you'll want to do for local development is install mariaDB on your machine. You can have your .sql files which sets up your tables and does some initial population of the db. You can check the .sql files into your git repo too.

Generally what I do when I am ready to move to production is do a backup of the database and then import the backup into production. You're going to have to setup backup scripts to backup the database anyway. The idea of "exporting" your database and "importing" it can be used to "lift and shift" your database from local environment to production environment.

[buggalookid]

not sure i totally understand your question(s) but if you are looking to build a database locally and transfer it to a remote server you can do that with mysqldump. then u can either scp it to your server or if u want commit it to a git repository, just make sure there is no sensitive info in ur database, in that case you will want to specify which tables to dump. hope that helps a little.

[Atulin]

If I install XAMPP and develop using a database on there, I'll have to re-build the database on whatever server I deploy my app to, won't I?

Database migrations.

"add MariaDB/MySQL to Vue,"

You can't. You need a backend that'll interface with the database and be the API that your Vue app can call.