# Wanted to create database

**URL:** <https://discourse.nodered.org/t/wanted-to-create-database/17591>\
**Category:** General\
**Created:** [6 November 2019 13:14 UTC](https://discourse.nodered.org/t/wanted-to-create-database/17591 "2019-11-06T13:14:23Z")\
**Posts on this page:** 17\
**Page:** 1

<div class="post-metadata">

**Author:** ![pranavthakkar](https://avatars.discourse-cdn.com/v4/letter/p/e99b99/32.png) [@pranavthakkar](https://discourse.nodered.org/u/pranavthakkar)\
**Post date:** [6 November 2019 13:14 UTC](https://discourse.nodered.org/t/wanted-to-create-database/17591/1 "2019-11-06T13:14:23Z")

</div>

Hi there , I want to dump a data (at this moment single data) (i'm new to database)  
in mysql or postgresql(which needs lots of work like docker or something)

can any one explains how to start with working on database??  
Any step by step process for getting and dumping data to my raspberry pi 3 model b+.

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [6 November 2019 13:29 UTC](https://discourse.nodered.org/t/wanted-to-create-database/17591/2 "2019-11-06T13:29:10Z")

</div>

Google is your friend, do a search and maybe look at some youtube video's

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [6 November 2019 13:55 UTC](https://discourse.nodered.org/t/wanted-to-create-database/17591/3 "2019-11-06T13:55:43Z")

</div>

If it is a simple database for storing only a few thousand records then have a look at sqlite. It is very easy to use with node-red and does not need the installation of a db server.  
If you want to store time-series data, so for example temperature data stored over a period of time, then have a look at influxdb, also very easy to interface with node-red.  
If you want to store a large amount of data, something like stock control in a factory for example then you will need a database such as mysql or postgresql.

---

<div class="post-metadata">

**Author:** ![pranavthakkar](https://avatars.discourse-cdn.com/v4/letter/p/e99b99/32.png) [@pranavthakkar](https://discourse.nodered.org/u/pranavthakkar)\
**Post date:** [7 November 2019 12:59 UTC](https://discourse.nodered.org/t/wanted-to-create-database/17591/4 "2019-11-07T12:59:17Z")

</div>

I wanted work with mysql and i googled up all the details,  
I also installed the node-mysql in node red but the thing is that it is not connecting  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/c/c77f68f67dce6ca4508536f0012d40d40b061f66.png)

I checked all the things but i came with installation of mysql with php, can some explain that why php and why sql if i already install its node?

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [7 November 2019 13:45 UTC](https://discourse.nodered.org/t/wanted-to-create-database/17591/5 "2019-11-07T13:45:47Z")

</div>

Can you connect using the command line mysql tool?

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [7 November 2019 13:49 UTC](https://discourse.nodered.org/t/wanted-to-create-database/17591/6 "2019-11-07T13:49:27Z")

</div>

Let’s start by getting some basic info

1. Are you running MySQL on the Pi?
2. Have you defined your database?
3. have you created your table(s)?
4. have you installed phpMyAdmin and seen you can talk to your database and access the tables?

if you have done all that please export your flow and attach it to a reply after reading this thread [How to share code or flow json](https://discourse.nodered.org/t/how-to-share-code-or-flow-json/506)

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [7 November 2019 14:12 UTC](https://discourse.nodered.org/t/wanted-to-create-database/17591/7 "2019-11-07T14:12:38Z")

</div>

> [@pranavthakkar](#):
>
> why sql if i already install its node?

If you have to ask that question then that probably means you should not be using mysql. Setting up and managing a mysql database server is not trivial. You would probably be better to start with sqlite (which does not have a server so is much easier to setup and use) and once you get something going with that you can decide whether you need mysql.  
What sort of system are you building and what size of database are you expecting to have? Do multiple users/applications need to access the database at the same time?

---

<div class="post-metadata">

**Author:** ![pranavthakkar](https://avatars.discourse-cdn.com/v4/letter/p/e99b99/32.png) [@pranavthakkar](https://discourse.nodered.org/u/pranavthakkar)\
**Post date:** [8 November 2019 06:11 UTC](https://discourse.nodered.org/t/wanted-to-create-database/17591/8 "2019-11-08T06:11:57Z")

</div>

No , I'm at zero step nothing done,

1.for clarity I want to use mysql  
2. I seen sqllite but have large amount of data so it can not be useful  
3. I want to use mysql because after that i want to publish on heroku where it is supported.  
4. For installing process of mysql tool and all stuff i got an error like this

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/3/31512f322a677defaaa9d4153af62d8a0eba450b.png)

---

<div class="post-metadata">

**Author:** ![pranavthakkar](https://avatars.discourse-cdn.com/v4/letter/p/e99b99/32.png) [@pranavthakkar](https://discourse.nodered.org/u/pranavthakkar)\
**Post date:** [8 November 2019 06:14 UTC](https://discourse.nodered.org/t/wanted-to-create-database/17591/9 "2019-11-08T06:14:10Z")

</div>

for better clarification

1. Yes Wanted sql in rpi3 model b+
2. No ( I'm still on installation stage , lots of error while installing not a single installation is done.)  
3.same as above
3. same as above

As i mention i'm getting errors why is it so?

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/3/31512f322a677defaaa9d4153af62d8a0eba450b.png)

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [8 November 2019 06:30 UTC](https://discourse.nodered.org/t/wanted-to-create-database/17591/10 "2019-11-08T06:30:20Z")

</div>

If you read the error: mysql is not available/obsolete, use mariadb-server instead:

`sudo apt-get install mariadb-server`

---

<div class="post-metadata">

**Author:** ![pranavthakkar](https://avatars.discourse-cdn.com/v4/letter/p/e99b99/32.png) [@pranavthakkar](https://discourse.nodered.org/u/pranavthakkar)\
**Post date:** [8 November 2019 06:39 UTC](https://discourse.nodered.org/t/wanted-to-create-database/17591/11 "2019-11-08T06:39:03Z")

</div>

ya! but what after that? complete process so that there will be no future error.

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [8 November 2019 06:48 UTC](https://discourse.nodered.org/t/wanted-to-create-database/17591/12 "2019-11-08T06:48:48Z")

</div>

I don't understand your question.

There are [various guides](https://computingforgeeks.com/how-to-install-mariadb-on-debian-10-buster/) on how to install mariadb on a raspberry pi.

Databases are fields of study:

> I seen sqllite but have large amount of data so it can not be useful

Why can sqlite not it not be useful you think ? This all depends on your needs of the database capabilities, large amounts of data is not directly one of them, they are both just files with records inside it.

You say you are new to databases, sqlite might be an easier way forward.

---

<div class="post-metadata">

**Author:** ![pranavthakkar](https://avatars.discourse-cdn.com/v4/letter/p/e99b99/32.png) [@pranavthakkar](https://discourse.nodered.org/u/pranavthakkar)\
**Post date:** [8 November 2019 07:01 UTC](https://discourse.nodered.org/t/wanted-to-create-database/17591/13 "2019-11-08T07:01:52Z")

</div>

> [@Colin](#):
>
> If you want to store a large amount of data, something like stock control in a factory for example then you will need a database such as mysql or postgresql.

Actually I will have real time data which is suitable for mysql or postgresql.

> The end goal is to publish on online server(like AWS, Heoku etc) but for now I wanted to run it locally.

also for mysql installation i found that it uses phpmyadmin, which can be GUI (i don't know what it is)

> But It keeps throwing me error so I can not figure out How to install it for **mariadb-server**.  
> I searched but confused if it got wrong installation.

> [@bakman2](#):
>
> You say you are new to databases, sqlite might be an easier way forward.

> For this it is not supported in Heroku so wasting of time if mysql is supported.

> I even search for Postgresql but the thing is still same. I can not find how to install the postgre in command line.

So I'm moving to mysql.

> [@pranavthakkar](#):
>
> ya! but what after that? complete process so that there will be no future error.

\*\*

> Can I get the steps to the end so that i can not get this again.

\*\*

> [@pranavthakkar](#):
>
> I also installed the node-mysql in node red but the thing is that it is not connecting  
> ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/c/c77f68f67dce6ca4508536f0012d40d40b061f66.png)

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [8 November 2019 07:12 UTC](https://discourse.nodered.org/t/wanted-to-create-database/17591/14 "2019-11-08T07:12:57Z")

</div>

> Actually I will have real time data which is suitable for mysql or postgresql.

mysql/mariadb is similar to sqlite, except sqlite has no server and no multi-user environment.

For realtime (streaming?) data, both are not suitable out of the box. Then again, a raspberry pi is not either.  
For timestamped data you will be better off by using influxdb instead.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [8 November 2019 08:03 UTC](https://discourse.nodered.org/t/wanted-to-create-database/17591/15 "2019-11-08T08:03:09Z")

</div>

_Please_ give us more information on what your system is intended to do so that we can make appropriate suggestions. Otherwise we are just thrashing about in the dark.

---

<div class="post-metadata">

**Author:** ![pranavthakkar](https://avatars.discourse-cdn.com/v4/letter/p/e99b99/32.png) [@pranavthakkar](https://discourse.nodered.org/u/pranavthakkar)\
**Post date:** [8 November 2019 08:31 UTC](https://discourse.nodered.org/t/wanted-to-create-database/17591/16 "2019-11-08T08:31:14Z")

</div>

I want **mysql** to be connected to my local sysyem and I can make queries on it.

> Thing is it is showing error which i posted earlier.

if i successfully make out of mysql all the possible queries will go to online server like heroku or other,

So From RPi i will send data to online database and make queries from there.

> But from node red it is showing not connected.

So What type of line should user use because

```auto
sudo apt-get install mysql-server 

```

is giving me error.

> [@bakman2](#):
>
> `sudo apt-get install mariadb-server`

will this one line solve my issue? or any other thing i needed?

I just want all the steps so that i can not miss any installation, If any installation miss then I think i have give more time to it to recover (If I'm not wrong).

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [8 November 2019 09:09 UTC](https://discourse.nodered.org/t/wanted-to-create-database/17591/17 "2019-11-08T09:09:37Z")

</div>

> [@pranavthakkar](#):
>
> I want **mysql** to be connected to my local sysyem and I can make queries on it.

That is not what I meant, I meant what is the whole system doing? What sort of data? How much? How many users will be accessing the data concurrently? Why mysql? What do you want use the data for after storing it?  
You mentioned realtime data I think, is that things like temperature and so on?  
All these factors determine the best way to go. Mysql is generally not the best database for storing (and retrieving) real time process data.  
If you are convinced that you know enough about the subject to decide that mysql/mariadb is the way to go (mariadb is an opensource version of mysql) then you need to learn about mysql. That is nothing to do with node-red. Since you apparently are starting from zero knowledge of databases I think you should spend a couple of days working through some database primers and then move onto mysql tutorials.