Docker: Local Databases

[Teodor]

You can connect to any database you like Brad. You can use a serverless database like sqlite locally, you can use your MAMP server database, you can use your remote server database - whatever you like. Same as your PHP projects.

[max_gb]

If it makes you feel any better not everyone uses Docker. I occasionally do for certain projects but for the majority of static sites or basic applications I would use a managed host (one up from shared hosting). Both for Node and PHP.

Most cPanel hosts allow Node applications now but its best to check with the host first.

In answer to your original question you can install MySQL or most other database flavours from the Docker Hub. This could be in a separate Docker contain to your Wappler application.

[George]

Brad, you can see docker as an instant MAMP/WAMP server for you project with automatic database and connections creator per project.

[brad]

Ok, I’ve had a chance to sit back, have a and try getting my head around this. First, thank you all for your patience.

So, this is where I am at now. Am I on track or still way out to lunch?

1: If I use Docker locally, I need to use a host like Digital Ocean that supports Docker remotely?
2: I don’t have to use Docker to develop in Node.js because Wappler has a built in server?
3: Built in server does not have a database but I can still use any local database I want such as my MAMP server?
4: If set up my project as a Custom Hosting I can FTP/Publish my site to any host same as my PHP sites?

[psweb]

Sort of correct in what you are saying but just to clarify, NodeJS can be used without Docker, but if you want Docker as well as NodeJS together then this is sort of the setup.

1. Before even opening Wappler, make sure you have the latest NodeJS installed (The LTS) version
2. Next make sure you have Docker for Desktop installed, but for me I just installed it, and did nothing inside it at all.
3. Open Wappler, create a new project, make sure to choose
   Hosting Type: Docker Hosting
   Server Model: NodeJS
4. Your new project will open, just hit Yes, which does all the initial setup for you.
5. Click the Gear icon for your Target, in this first General section, i leave all the defaults but make sure the
   User Uploads Folder: /public/whatever-folder-you-want-without-spaces
6. Under the Targets section, I leave my Development target as it is except
   Node Version: 14
   Database: MariaDB (Leave all default except)
   MariaDB Version 10.5.x
   No Sample Data: Toggle ON
   Then I go alter passwords if I want, otherwise just use what they give, and I change my database name.
7. Save

At this point I like to just add a title to the open page and save and Deploy, if I do not do this then the Database so far just says “Unable to Connect” as it needs to set the DB into the container etc, and can not do that without deploying it.
At this stage, after clicking refresh a few times in the database manager, it does connect, just be patient before trying to adjust, it does connect, although it’s empty because you removed the sample data.

You now have a perfectly setup local environment with Docker and NodeJS and can see your website by going to http://localhost:8100/, you can connect to your database using any third party tool such as Navicat, by entering the details as per the Database settings for the Target. When you have your database changed inside the third party tool then you need to refresh in Wappler Database Manager to get the changes.
It seems like a lot but realistically, its all very automated, no WAMP/MAMP etc.

Taking this live, well that is also pretty simple when you know the steps.
What you need is a Domain Name, like you always did, a DNS provider OR Hosting provider to point your records out to wherever you want, and a Cloud based Docker host like Digital Ocean.

Open your Wappler targets and add a second target, Add a (I will only list what i change)

1. Target Name: MyWebsiteRemote
2. Usage: Development
3. Connection: Remote
4. In the Docker Remote section Click Manage, then the + button
5. Name: MyWebsiteRemoteServer
6. Provider: Digital Ocean
7. Set your Region, Size, OS (I left mine all default but turned off IPv6)
8. Click Create Account, click Sign Up, and do your initial setup, or sign in to an existing account
9. If you did the Sign Up, then when you get Let's Build Something, then click Skip this and go to the control panel
10. Click API, then under Tokens/Keys, click Generate New Token, enter a token name and make sure it is Read and Write, then click Generate Token, copy the generated Token, and paste that into Wappler, and click Create.
11. Wait for it to complete, it can take some time, you can see progress in the Digital Ocean dashboard, but it takes a bit longer in Wappler.
    I will admit mine often fails with Error creating machine: Error running provisioning: Unable to verify the Docker daemon is listening: Maximum number of retries (10) exceeded
Error Creating Docker Machine!
    I just click Back To List, hit the refresh button, and my machine appears with an error State, I select it, and click Restart The Docker Machine and after a little while it says restarted successfully and the state changes to running.
    I do not install Portainer or Traefil at this stage, until I have my Domain setup. I just click Select
12. Back in the Wappler Targets window now, the Host and Port and Cert Folders have all been set.
    The Web Section i change to Node Version: 14
    The Database section I change MariaDB Version: 10.5.x and No Sample Data: Toggle ON
13. Click Save

Back in the main Wappler App, I now do a last deploy with my target still as local, then switch to the remote target and it asks to Deploy again, this time to Digital Ocean. Wait a moment for this to complete.

In my web browser I can now see my site at http://localhost:8100/ and the IPv4 address provided by Digital Ocean in the control panel, or by clicking the View in Browser from Wappler.

I now have a local as well as a remote Docker/NodeJS all set and working, I can connect to both databases, through Wappler by clicking refresh a few times, also click db and then refresh, sometimes that helps. I can connect to both databases in Navicat as well.
My only issue I have had at this stage is getting my entire database to replicate to Docker, this is my own fault because I do most of my database design though Navicat or the likes, and Wappler keeps no record of these changes, so I can not open the Changes area and just push the latest change. I assume if I fully used Wappler, then this would be the best way.

In Wappler with my local target set i refresh my database to get all the latest alterations made, then next to the deploy button I click the icon, save database data and structure this saves a file inside the hidden .wappler folder then I used Navicat on the remote database and imported the file from myWebsite/.wappler/targets/Development/db_init/db_save.sql which I am pretty sure is not the intended way, but it worked for me.

Almost at the end now, we want to make this all work on a real domain name, in my Digital Ocean dashboard I click the three dots to the right of my docker, select Add a Domain, I enter the domain name, click Add Domain.

1. I enter @ in the first Hostname box, and select my Docker in the Will Direct To box, Create Record
2. I enter * in the first Hostname box, and select my Docker in the Will Direct To box, Create Record

This creates 2 A records as well as the 3 existing ns (name server) records, take note of all the NS records as we are going to use them in the next step.

In my DNS providers website, I went to manage my DNS for a particular domain, and all I did was change the nameservers from their own to the Digital Ocean ones, for me I did not even need to add the IP addresses, just the ns1… ns2… ns3… etc.

Saved, waited a while, watching https://dnschecker.org/ till I could see everything was the correct IP address.

Back in Wappler I now want to use the real Domain Name, so I open my targets, select the remote one pointing to Digital Ocean, and make the following changes.
DO NOT change the DOCKER REMOTE portion, this is good as is with the IP Address

1. Web Server URL: https://www.example.com
2. Port: LEAVE BLANK

Now you can use the DOCKER REMOTE section, click Manage, select your Docker Machine, click the spanner icon Manage Services, you want to install both of these regarless if you only need one, or Wappler will fail, so just do both services.

1. Portainer
   Dashboard URL: portainer.example.com
2. Traefik
   Let’s Encrypt Email: you@example.com
   Dashboard: ON
   Secure Dashboard: ON
   User: whatever-username-you-want
   Password: whatever-password-you-want
   Dashboard URL: traefik.example.com

Apply

Obviously wherever I use example.com change to the real URL of your domain. Portainer is a nice way to manage your Docker containers, and Traefik is how you get the https SSL Certificate all working.

Once Successful, save all and close back to Wappler,

1. In your web browser open portainer.example.com and in the login screen enter the details you created earlier for the username and password. Click Login
2. In your web browser open traefik.example.com, there is no login for this.

If all seems to be working at this point, try your website with the new https address

1. In your browser go to https://www.example.com
   It should show your webpage with no warnings about security certificates not being valid etc.

And thats it, start to finish
Docker, NodeJS, Database, local and remote, hosting, DNS, SSL, and you can continue with your development.

Hope this helps.

[brad]

Wow, thanks @psweb (Paul)! I will crack open another beer and go through this. I very much appreciate your time.

[psweb]

No problem @brad, i know how daunting it was when I first ventured into it, but honestly, i will not go back to PHP now, it really is pretty awesome, I do not know exactly why, but there are just little differences that somehow just make it better. Access server data without a server connect when needed is just one of them, adding your SEO tags without jumping through manual code, a second.

[ben]

@brad

Just revisiting a couple Docker sandbox sites I started playing with a few months ago.

1: Can you create a local database with Docker? If so, how?
2: If answer to first question is ‘Yes’, can the local database be accessed with a third party app such as Navicat? With my current MAMP Pro set up I can easily back up my remote database at the end of each day to my local machine by dragging the tables to my local server. Can Docker be used in this way?

Hi Brad,

I have been battling with the same problems. We are used to our own environments when coming from Dreamweaver. We have our Wamp/Mamp server and we can create and manipulate the database using (in my case) phpMyAdmin.

When creating a site in Docker, nothing much changes with the exception that in this case Docker supplies its own Wamp/Mamp and database servers. The problem is, that when deploying the site, we enter a strange territory for us Dreamweavers. For this reason I only use Docker locally when I want to quickly create a dynamic site. The result can still be uploaded to our normal host; the SQL in Docker can be used to populate the remote database. Personally, I would do this only in extreme circumstances.

I have found NodeJS to be a great option. NodeJS replaces PHP that we are so used to. The result is a much better environment than PHP and the future looks even rosier. You can still use the local database that is created in Wamp/Mamp. With NodeJS we stay in familiar territory although there is no need to create the site within the Wamp/Mamp www or similar directory. NodeJS has its own very capable server.

My suggestion based on the type of sites that we (you and I) produce: for new projects go NodeJS and enjoy, especially the templating and routing systems.

When Wappler expands the NodeJS features - and there are numerous - you will appreciate this advice.

[Mozzi]

thanks for this Paul!!! you saved my ass!!! good and clear instructions… and easy to understand… im also still old school and want to use WAMP to manage my databases… BUT… I know that with time as i get to know this better within wappler… it will get better… stupid question… i see you used MariaDB? why not MySQL? or is nodejs using MariaDB?

[Mozzi]

Hi Ben… if i do the database with docker… and it has data in it “locally” and I update the “remote” docker… what happens to the online “data” already in the database … and will my “sample” test data then not overwrite my legit online database? eish… sorry stupid questions… just trying to get my head around this…

[Sorry_Duh]

Look into seeds my local database is empty but seeds maybe what you are looking for here as far as I knew they do data and just the changes do the tables structure (I maybe wrong not something ive needed to look into much yet)

What is seeds in the database section?

What is it used for?

[Mozzi]

thanks…

[Dave]

@Mozzi

what happens to the online “data” already in the database

Hey @Mozzi your data remains within the database and there is no change to its content. Nothing is overwritten.

[Mozzi]

thanks Dave… so only the stucture will update… tables if changed…

So how would i dump my data from my current mysql using phpmyadmin to the docker one… i guess this is not a simple answer…

maybe connect to that database that docket generats via navicat or somethig then do a datadump… and then refresh in wappler…? anyway dont want to waste your time… as there is lots of content on this site… im sure ill find the answers somewhere…

[Dave]

@Mozzi

dont want to waste your time

No time wasted Mozzi! We all have to help each other.

Dump via your favorite DB Manager or phpmyadmin. Connect to your new DB within the container, using the containers IP and whatever port you set for your database, import. Try to stay with whatever version of whatever database you are using initially. When you get things up and running you can always update that side if you wish to. All should be very simple sir.

[Dave]

@Mozzi

so only the stucture will update… tables if changed…

Sorry forgot to answer that.

Yes, BUT, whatever you do ALWAYS backup first before applying changes, and get in to the habbit of it if you can. Things can go wrong. Can’t count how many times backups have saved my skinny little behind!

[Mozzi]

thanks Dave… small steps… but ill get there… thanks for the heads up… and all the help

[psweb]

@Mozzi

i see you used MariaDB? why not MySQL? or is nodejs using MariaDB?

Hi Mozzi, you can use either with Node to be honest, I just prefer MariaDB because I have found it to be faster, however it is really just what I am used to at this stage, so no real solid reason.

[bpj]

If you plan to develop on a Mac with M1 chip, MySQL doesn’t yet (afik) have a compatible image so choosing Maria covers all CPU types

[Mozzi]

eish cant afford even half an apple… not even to speak of one with the M1 chip… some seriously well of developers here… hahahahhaah… i must be doing something wrong (— must be the php holding me back i hear @JonL say)