QueryStore on database level

[martijnschermers]

I'm thinking of a way to save and load queries to and from a table in the database. From how I understand it at the moment it is only possible to read queries from a folder on the file system. Is it an idea to read queries from a table in the database?

To implement this I'm thinking about using the following approach:

• Convert store.go to file_store.go
• Add database_store.go
• Make a store.go interface with generic methods
• Find a way to make a choice between file or database (probably through a command line option)
• Inject the correct type (file or database) in the application

The first four options are pretty basic to implement, but I'm struggling at the fifth option. Does anyone have some suggestion on how to inject the correct type?

[sosedoff]

Yes, queries are pulled from the local files, so its up to the use to provide what they need.
As far as implementation for the db-driven story, there are a few questions/considerations:

1. What database engine are you targeting here? sqlite? postgres? mysql? This will affect the distribution model quite a bit (cgo/no cgo)
2. How do you relate the query to a selected database? Any security considerations?
3. What's the use case for a db-driver query store?

[martijnschermers]

Thanks for your response.

To answer your questions:

1. postgres
2. the ConnectBackend option is used, so it is not possible to switch databases in a Session. The query is saved in a table (named saved_queries for example) that is in the database that is based on the Session. Based on a P.o.C. I created there will be extra information stored besides the query, such as a userId.
3. This is based on a requirement from a stakeholder. Based on the ConnectBackend feature, multi tenancy is implemented, so not every tenant should be able to view saved query's from another tenant, only their own saved query's.

Creating a P.o.C. for this feature, I ran into some issues:

• The use cases requires PgWeb to run in read-only mode. Saved query's are stored in the database, so there should be a INSERT statement to save a query. This is bypassed by checking if the table that will be inserted to is equal to saved_queries.
• Table to store saved_queries is created manually. Retrieving the rows from this table is also done with a hard coded SQL query that contains the table name and postgres schema.

Do you have some suggestions on refining this issues?

[sosedoff]

I would advise against creating saved_queries table in user space (ie their databases). There are a few reasons against it:

• Lack of permissions. You mentioned read-only mode + a lot of users connect to read-replicas which do not have ability to modify any schema or data.
• Polluting user space: no one wants to see some random app's tables. One workaround i can think of is by using a different schema, but again, the client might not be able to create it.
• Maintaining table schema for pgweb itself: we need to create table and maintain changes across versions, which means things could break.

Since you already use Connect Backend feature, you can explore an alternative source for the queries - HTTP API. So think of it as a Query Backend: you provide a pgweb-compatible endpoint and pgweb can read and write queries to it, just like it would to a postgres store. IMO it'll work great for your use case: query store is decoupled from any db engine, hidden away from consumers and easy to integrate with since you already provide HTTP endpoint.

[phoenisx]

Yes, queries are pulled from the local files, so its up to the use to provide what they need.

Hi I didn't understand this reply, apologies if I have any misunderstandings here.

@sosedoff Do you mean to say the queries we write are stored in local files, instead of localStorage?
I say this because I found our queries are being stored in localStorage, but have never been able to persist these queries anywhere in my local filesystem, and have lost a lot of my previous queries due to this 🥲

Is there a way to enable persistent storage in docker to support persisting our queries in a file?