MySQL and Postgresql as standalone apps

[girish]

Interesting... I wonder if the database app should have the database OR if it should just be a frontend to our existing addons. The former approach has advantage that we can have databases with different versions. We can also have database settings which are more flexible that with our addons (the addons are restricted by what all the apps can support).

[ChristopherMag]

@girish Definitely think the first option is the way to go, can have different versions, plugins, etc. and not have any negative impact on addons infrastructure which works great and I would hate to make that harder to maintain.

[timka]

@girish I had the same need and was surprised that there's no option. So I Would really appreciate it! PostgreSQL /MySQL, MongoDB and REDIS would be perfect

[eddowding]

So much yes to all of this. I keep thinking I'm doing something wrong because it MUST be there, right?! and then I read https://forum.cloudron.io/topic/2630/lamp-stack-with-postgres and realise I don't understand enough, and give up. Which is sad.

So massive +100000 to this.

[jan.reinhardt]

@eddowding said in MySQL and Postgresql as standalone apps:

So much yes to all of this. I keep thinking I'm doing something wrong because it MUST be there, right?! and then I read https://forum.cloudron.io/topic/2630/lamp-stack-with-postgres and realise I don't understand enough, and give up. Which is sad.

So massive +100000 to this.

And another +1000000 from my side

[msbt]

@timka said in MySQL and Postgresql as standalone apps:

@girish I had the same need and was surprised that there's no option. So I Would really appreciate it! PostgreSQL /MySQL, MongoDB and REDIS would be perfect

also Elastic/OpenSearch

[felixwttr]

+10000 from me too. A standalone database app would be very useful.

[Lanhild]

Agreed, even just for random testing or data analysis/DB schema testing, having a standalone PostgreSQL/MongoDB, etc. app would be quite useful

[ChristopherMag]

Fyi, what I have ended up doing is creating a custom cloudron app with the postgresql feature and then hosting a nodejs app that communicates directly with the database to alleviate the need for generic direct access to postgresql.

@Lanhild If you want direct access to a postgresql database for testing and DB analysis stuff then you can use a custom app with the postgresql feature enabled and girish's post here to connect pgadmin to that custom app's postgresql instance.

[Lanhild]

@ChristopherMag Thanks for the info!

Do you mind sharing your custom application code? I'm very interested in that.

[ChristopherMag]

@Lanhild I don't think I can share the full code but here is a relevant snippet if you wanted to get stated with using postgresql from within a nodejs cloudron app:

import Koa from 'koa'
import cors from '@koa/cors'
import Router from '@koa/router'
import { koaBody } from 'koa-body'
import postgres from 'postgres'

var sql = postgres(process.env.CLOUDRON_POSTGRESQL_URL)

let app = new Koa()
let router = new Router()

app.use(cors());

router.get('/',(ctx, next) => {
  ctx.body = "Health Check Success";
})
.get('/someurltogetdatafrom', async (ctx, next) => {
  ctx.body = JSON.stringify((await sql`
    select somePostgresqlFunction( SomeParameterName => ${ctx.request.query.someparameternamefromquerystringparameter}) as SomeName
    ;
  `)[0].somename)
})

app
  .use(router.routes())
  .use(router.allowedMethods());

app.listen(8234);

[Lanhild]

@ChristopherMag Thanks for sharing

I've made a very simple custom app to deploy a psql DB using the Cloudron addon @ https://github.com/Lanhild/postgresql-cloudron

[timconsidine]

@Lanhild neat
so you just connect to that custom app as you would normally to a remote hosted postgres db ?

[Lanhild]

@timconsidine Exactly, spin up the app, grep the environment variables for the DB credentials and connect to it

[timconsidine]

Definitely worth adding to the AppStore.
Not much additional maintenance associated with having it there.

[ChristopherMag]

@Lanhild If you want to take this a step further an option I wanted to explore but am not sure whether it would work would be to use something like node-tcp-proxy as the application itself with 5432 as source and destination port such that someone could connect to the app's name on port 5432 and the app would proxy that tcp session back to the postgresql database on port 5432.

There might be issues with this as I cannot think of another app example on cloudron that uses tcp without http on top so there may be some proxy stuff being done by cloudron itself before the connection makes it to the app that prevents this from working but it was on my list of things to try at some point.

[ChristopherMag]

@Lanhild Fyi, for me long term I think implementing PostgREST as a cloudron app is what I will eventually be aiming for as for me I am focused on postgresql and this app would eliminate the need for any code maintained by me in the cloudron app and just have my code in postgresql itself with the app dynamically building rest endpoints based on the code in postgresql.

The tcp proxy solution if possible would be useful separately as it would allow us access to MySQL, Mongodb, Postgresql, Redis, etc. directly via proxying the respective cloudron app service.

[eddowding]

Nudge on this! It looks like there are some good solutions which can easily add a lot of value!

[canadaduane]

Would love to see this. Self-hosted database makes n8n much more powerful.

[robi]

Does PocketBase fill that gap?