MySQL, PostgreSQL

[akylkb]

Do you plan to add support for mysql or postgresql?

[ganigeorgiev]

No, at the moment only SQLite (in WAL mode) is supported and I don't plan adding other databases.
For SQLite replication Litestream is a great companion app .

Please also note that PocketBase is designed to be self-contained and run entirely on a single server so horizontal scaling is not supported (there is already Supabase or Nhost if you need that).

[mirusky]

@ganigeorgiev, maybe a solution should be a configurable repository? eg:

type IRepository interface {
 // Strong typed methods so anyone could implement your own db
 Name() string
}

// default -> embed SQLite implementation
type defaultRepository struct{}
func(*defaultRepository) Name() string {
  return "embed"
}

type postgresRepository struct{}
func(*postgresRepository ) Name() string {
  return "postgres"
}

In that way you don't need to implement postgres or other db and at same time opens to community creates their own implementations

[dz0ny]

Started PostgreSQL at dz0ny@3c672fc. Server starts, migrations run, needs couple of fixes for collection for queries and dynamic table creation.

DB:

docker run -e POSTGRES_USER=user -e POSTGRES_PASSWORD=pass -p 5432:5432  -e POSTGRES_DB=logs -it  postgres

Server

CGO_ENABLED=0 go build -tags pq -o server github.com/pocketbase/pocketbase/examples/base
env LOGS_DATABASE="postgresql://user:pass@localhost/logs?sslmode=disable" DATABASE="postgresql://user:pass@localhost/postgres?sslmode=disable" ./server serve

[zhenruyan]

Thanks for taking the time working on that.

Considering that PocketBase lives entirely on single server, is there any particular reason why you prefer using PosgreSQL instead of SQLite or it is just a personal dbms preference?

I experimented with several databases at the beginning (at some point I even considered https://github.com/fergusstrange/embedded-postgres) but I found out the performance of SQLite (in WAL mode) was far better (both for query execution and resource consumption).

https://github.com/rqlite/rqlite Do you consider using rqlite? The bottom layer is still SQLite, but it has better horizontal scalability and minimal changes

[ganigeorgiev]

@zhenruyan I'm familiar with rqlite but PocketBase is designed to work only on a single server and for now I don't plan adding support for horizontal scalling.

[zhenruyan]

@zhenruyan I'm familiar with rqlite but PocketBase is designed to work only on a single server and for now I don't plan adding support for horizontal scalling.

Thank you very much for your answer, but its potential in rapid development is exciting. At present, its online business is running very well

[ganeshkbhat]

@dz0ny I have been looking at your commit. While the code builds fine with the pg driver, the executable reverts to sqlite if the pg driver details are not provided. Any config issue or the connectdb function is supposed to be falling back to sqlite if there is an erro during runtime?

is this command supposed to be compulsary?

docker run -e POSTGRES_USER=user -e POSTGRES_PASSWORD=pass -p 5432:5432 -e POSTGRES_DB=logs -it postgres

CGO_ENABLED=0 go build -tags pq -o server github.com/pocketbase/pocketbase/examples/base env LOGS_DATABASE="postgresql://user:pass@localhost/logs?sslmode=disable" DATABASE="postgresql://user:pass@localhost/postgres?sslmode=disable" ./server serve

[AlperRehaYAZGAN]

@dz0ny I looked your commit and it compiled success. But this commit is too old if we consider new features of pocketbase. According to your commit I decided to fork current version v0.20.5 and implement Postgresql using its own library pocketbase/dbx.

I changed SQLite3 migrations with Postgres 15.2 compatible version and changed simple code snippets. Here is my current fork postgres alternative for Pocketbase.
alperreha/postgresbase

I saw this discussion and it helped me. So everybody could try this fork for testing. I hope it won't give any error 😅.

I am try to extend this fork and make pocketbase support as stateless app with following features: 🚀.
[✅] - Postgres Support for DB-scaling (I am adding with this fork project.).
[✅] - File Storage (Pocketbase already supports.).
[⏳] - Migration Files to S3 (Pocketbase stores runtime migrations in folder "/pb_migrations"). So we need to upload directory to S3 ensure all replicas be aware of migrations.
[⏳] - Postgres Backup (Currently Sqlite3 backups works fine. We need to add same mechanism to postgres with pg_dump and pg_basebackup).

While making all these improvements. I realised that simplicity is best but features makes this project awesome, Never forget the following quote. 😅.
'An idiot admires complexity, a genius admires simplicity.' - Terry A. Davis

[dz0ny]

One is a preference of PostgreSQL, the second one is I can then run it at any Paas (Heroku, Fly.io, Render...), thes mostly have ephemeral filesystems.

[ganeshkbhat]

I suddenly realised the presence of middlewares. Am I looking at after middleware supportive application here? Is that possible or am I looking at after CRUD/ DB action hook as the only possibility since only a before middleware exists? All other modes of logger like before middleware and then a error handler based architecture may fail.

echo.Route{
    Method: http.MethodGet,
    Path:   "/hello",
    Handler: func(c echo.Context) error {
        return c.String(200, "Hello world!")
    },
    Middlewares: []echo.MiddlewareFunc{
        apis.ActivityLogger(app),
    },
}

Would you wish to consider the before/ after middleware or atleast before/ after hooks?

before/ after middleware something like this app.get('/implement', beforeMiddleware, handler, afterMiddleware);

[before/ after middleware] > before + after hooks for table data > [read, save, update, delete item], db change > [CRUD Tables, etc]

[ganigeorgiev]

@ganeshkbhat I'm not sure that I understand what is your use case and what you are trying to do, but please consider filing a new discussion if you have other questions as I don't think it is related to the current one and unnecessary spam the participants.

For before/after app level db hooks you can use OnModel* hooks.

If you want to handle before/after on route level you can define that in a single middleware and place your code before or after the next(c) call in the echo.MiddlewareFunc.

[ganeshkbhat]

Yes nice. I am looking at this. Seems like this is an issue #317 Where are the docs for this? For webhooks, i am refering to something like this app.get('/implement', beforeMiddleware, handler, afterMiddleware); something like this before/ after middleware . Seems echo is not providing that. Raising this with echo for a check.

The model hooks are there which I did not notice and is grossly sufficient. Is that async?

[ganigeorgiev]

@ganeshkbhat #317 is for allowing specifying hook-like behavior from the Admin UI. For a route hook, you can use something like:

app.OnBeforeServe().Add(func(e *core.ServeEvent) error {
    e.Router.GET("/hello", func(c echo.Context) error {
        return c.String(200, "Hello")
    }, func(next echo.HandlerFunc) echo.HandlerFunc {
        return func(c echo.Context) error {
            // place your before code here...

            if err := next(c); err != nil {
                return err
            }

            // place your after code here...

            return nil
        }
    })

    return nil
})

But again, please consider opening a new discussion if you have further questions.

[ganeshkbhat]

Nice. Let me try this.

e.Router.GET("/hello", func(c echo.Context) error {
        return c.String(200, "Hello")
    }, func(next echo.HandlerFunc) echo.HandlerFunc {
        return func(c echo.Context) error {
            // place your before code here...

            if err := next(c); err != nil {
                return err
            }

            // place your after code here...

            return nil
        }
    })

This webhook feature should actually suffice for practically anything #317 . where do i find this in the docs for the implement? This https://pocketbase.io/docs/api-realtime/ ?

[gregwebs]

The potential utility for me of Pocketbase with Postgres is for creating an internal-facing administrative app to manage data that is end-user facing.
Postgres is needed for the end user interaction with the DB- for scalability and also reliability (failover), along with cloud-hosting as mentioned.
Pocketbase could be used to quickly create internal-facing applications, some of which perform administrative actions against data stored in Postgres accessed by end users. The fact that Pocketbase only scales vertically would not matter in this use case, and would not effect how the end-user app scales independently.

Of course, there are several other No-Code tools for this purpose, but they almost all lack in the ability to insert validation logic in front of the DB other than some auth restrictions- validation must be done in check constraints. Directus does have this ability, but it is TypeScript-based- PocketBase would uniquely allow Golang developers to quickly write CRUD apps against PG with validation logic.

[gregwebs]

judging from this commit it may not be that hard to support PG.

[gedw99]

Another cool reason for postgresql is that you could use it with GraphJin ( https://github.com/dosco/graphjin ) .

It could be synergistic in that:

• GraphJin automatically provides a GraphQL API over those records to allow composing larger API and data reuse.
• Pocketbase controls the per table records with a gui for user updates, etc

win win perhaps ?

GraphJin is also easy to embed in another program , just like pocketbase.
Example here: https://github.com/ichaly/go-api/blob/main/core/app/internal/base/engine.go

[gregwebs]

there's some overlap with the two projects, but GraphJin performs joins and it looks like Pocket Base does not? GraphJin returns JSON, not sure if it has a method to pack its data into golang structs. Graphjin is better at just reading data from the DB into JSON, whereas Pocket Base may be better for working with data and writing it.

[gedw99]

yes GraphJin performs joins. it does it via the underlying graphql engine.
It also has a graphql web gui.
You give it the sql table definitions, and the graphql web gui updates. Then you can use graphql to add data, query data, mutation data. It also does subscriptions over websockets. It also has validations via Cuelang or js.

Its similar to pocketbase, being a single import also. I don't think the web gui gets embedded into that go import though.

Its really nice but very much not well known. Support is good too - thanks @dosco

[pchasco]

Count me in as another who believes Postgres should be an option if only because it will enable users to host on platforms without persistent storage, or on platforms where configuring persistent storage is difficult or non-obvious.

[LP0101]

I'll also toss my hat in for postgres support. Getting rid of the need for persistent storage would allow this to be deployed as a stateless application, enabling HA deployments

[pchasco]

What I am trying to say is that the data store is only part of the problem. Once you have a HA database, you still have to deal with the fact that PocketBase itself is not written in a way that can be scaled horizontally and run in multiple instances. If you cannot run the server component in multiple instances across different machines, you have a single point of failure. And if you have a single point of failure, you do not meet the criteria of being HA. The event handlers that respond to changes in the database and the messaging component are not written in a distributed way, so any running instance of PocketBase can only send messages and respond to events for its direct HTTP clients, not clients that connected to another instance running on another server in a distributed environment. This means that attempting to scale PocketBase horizontally will break key features. One could overcome this problem by introducing new tech, or implementing some sort of message bus, but I believe this goes against the stated goals of the project. This project is intended to be a turnkey BaaS solution. There are other great solutions available if your project needs the additional complexity of HA or horizontal scaling.

[gedw99]

i already

What I am trying to say is that the data store is only part of the problem. Once you have a HA database, you still have to deal with the fact that PocketBase itself is not written in a way that can be scaled horizontally and run in multiple instances. If you cannot run the server component in multiple instances across different machines, you have a single point of failure. And if you have a single point of failure, you do not meet the criteria of being HA. The event handlers that respond to changes in the database and the messaging component are not written in a distributed way, so any running instance of PocketBase can only send messages and respond to events for its direct HTTP clients, not clients that connected to another instance running on another server in a distributed environment. This means that attempting to scale PocketBase horizontally will break key features. One could overcome this problem by introducing new tech, or implementing some sort of message bus, but I believe this goes against the stated goals of the project. This project is intended to be a turnkey BaaS solution. There are other great solutions available if your project needs the additional complexity of HA or horizontal scaling.

Its very easy to get it HA using NATS. Up to what you want though... you just need to understand CQRS and use PB in that way.

[pchasco]

Yes, I think you agree with me. Like I said, you could make PB suitable for HA by integrating some sort of message bus to enable instances to communicate with each other (and there is likely more to it than that, but it would require at least this and a HA data store), but that seems to go against the stated goals of the project.

[tigawanna]

Embedding a NATS server seems like it would solve most of your single point of failure concerns and make it easy to plug i to marmot to offer some sort of a distributed Pocketbase, the individual databases will still only scale vertically but for a small project this approaches intent would be resilience

[gedw99]

you can scale out all the DBs by just changing the mutation path to be into NATS. and then let nats consumers be the PB API.

[gedw99]

https://github.com/go-jet/jet might help. It can work with our without code gen.

Its light too and supports all the normal dbs.

[eqoram]

Also quite interesting in having this as I would love to connect neon.tech (serverless postgres) to pocketbase (:

[zhenruyan]

I forked a copy to support the cockroach database

Very interesting

[eqoram]

Do you have a link to the repo? :)

[zhenruyan]

For the time being, it is still a private warehouse because some JSON functions, pg, and sqlite, perform differently. I don't have enough testing.

[tigawanna]

👀 we don't mind it being not completely ready , that sounds absolutely awesome

[egonelbre]

I was looking, but is there a list of issues that makes it difficult to support Postgres (or other external database)? Or, is the issue more of cost of maintenance? Or supporting some specific features of the pocketbase?

Note: I do understand the stance of not wanting to support, I'm just wondering what are the technical hurdles.

[tigawanna]

Sweet , PG support is as good as it gets , any ETA on the code ?

[zhenruyan]

I haven't estimated yet. There may be some dialect differences in the retrieval of system information between Cockroachdb and PostgreSQL, but I believe this won't be a big problem

[zhenruyan]

https://github.com/zhenruyan/postgrebase

@tigawanna @egonelbre @eqoram Seeing that everyone didn't mind its instability, I decided to public code it

Thank you very much for the inspiration brought by @dz0ny

dz0ny@3c672fc

[paulpierre]

This is great, I just found your repo @zhenruyan .. Have you had any issues with stability? Anything adopters should be aware of before we commit :)

[paulpierre]

I forgot to ask @zhenruyan but do both the permission and subscription parts of the permissioned subscriptions still work?