how to ... postgres

[j2l]

Hello, I noticed that postgres is an option, which is fantastic! For the container, I added the typeorms for postgres and got an error at the npm install pg --save step about missing python for gyp. Do you have a dockerfile for this option?

[paulfitz]

We don't, but could talk you through it. I'd just like to clarify, postgres is an option for the database containing metadata about documents, workspaces, users, etc. Individual documents themselves are currently always stored as sqlite. If you're still interested in the postgres setup, let me know and I'll write it up.

[b42thomas]

Hi,

I would also like to know how to setup grist with postgres. @paulfitz you said its for the metadatabase, so are there two databases that run then? sorry if thats a dumb question lol

[paulfitz]

@b42thomas each individual document is itself a database (stored in individual sqlite files). The organization of documents into collections (workspaces and sites) is stored in a database called the "home" database (which can be sqlite or postgres). There is also a store for session information, which can be in a (trivial) sqlite database, or in redis.

To use Grist with Postgres:

• Follow the instructions for building Grist in the top-level README.
• Add the node package for postgres: yarn add pg
• Create an empty database in your postgres instance (hopefully you have one, if not there are a lot of tutorials out there for this part).
• set the following environment variables pointing to that empty database:

Variable	Purpose
TYPEORM_TYPE	set to 'postgres'
TYPEORM_DATABASE	database name
TYPEORM_HOST	host for db
TYPEORM_PASSWORD	password to use
TYPEORM_PORT	port number for db if not the default for that db type
TYPEORM_USERNAME	username to connect as
TYPEORM_LOGGING	set to 'true' to see all sql queries

• run yarn start as usual.
• Grist should come up as always, and you should see over a dozen tables appear in the database.

[j2l]

Thank you @paulfitz for these details. As explained the Dockerfile doesn't include python, so the add pg step fails.

Never mind, I'm looking for data to be using postgres.

Do you have plans to support data and metadata on postgres?

[paulfitz]

@j2l for data, we don't have plans to use postgres. As a hybrid spreadsheet/database application, it is helpful to have a file format that can be downloaded, uploaded, emailed around, accessed from other tools, and compared with other versions of itself. All that could be done with postgres and some mappings, but it is a lot simpler with sqlite.

That said, for database applications where having a file format isn't important (apart from backup/restores), I could definitely see the utility of backing a specific document in Grist with an external database. The main thing that would need changing would be app/server/lib/DocStorage.ts, and then turning off some functionality that wouldn't make much sense with such a back end. But this isn't a priority for us. I'd be interested in hearing your use-case though.

About the add pg, the docker image is stripped down and isn't ideal for building in - you might want to tweak the Dockerfile to use a single stage, rather than having a second stage that omits all the build dependencies. But otherwise, I think you could make yarn add pg work in a regular Grist container by doing:

• apt update
• apt install python sqlite3 build-essential
• yarn add pg

(Although I realize that since you are looking for data in postgres, this isn't actually useful any more).

[j2l]

@paulfitz I agree that sqlite is great to take away and it makes sense that grist focuses on this.

Personally, I have sqilte files and various spreadsheets that have many to many relationships (so not really spreadsheets) and I need them to become tables in a database that can scale and be shared as single point of truth, but the data will continue to evolve greatly.

So my use case is simple: Getting a grist-like spreadsheet for postgres (not speaking about functions here, simply a way to fill, manipulate data as in a spreadsheet), because the one-record only (card or line by line) approach of databases is sometimes not useful at all. Especially when you select a column or multiple cells to be changed at once. Hope my explanation makes sense :)

I think Airtable nailed the point here, but your data is barely yours anymore since you can't GET and aggregate linked records or even export them as is (csv are flatten, no more links).

If you know of other solutions to manage postgres data like a spreadsheet, please let me know.

I think we can close this one for now. Please, if you know changes of app/server/lib/DocStorage.ts for postgres, please let me know here :)

[asitemade4u]

May I bring my 2 cents to the discussion?

We should IMO distinguish between:

1. the database which is used as a native backend to Grist
2. the ability for Grist to serve any data hosted on any major database platform (eg Postgres or MYSql)

• There are LOTS of self-hosted tools that allow Nr.2 but they do it quite wrong, at least until now: they add tables to the database, they are barely relational, they are generally very slow (I won't quote names, but I have tested them all). I have chosen to stick with a fairly old tool to publish the database we host, PHP Generator for Postgresql, which is by far the most comprehensive tool I have found. IMO, it would be an error for Grist to get into that competition: Grist is more of a method to display relational data than a mere interface builder. Its simplicity and streamlined interface are its main assets.
• As per Nr.1 I guess it would not be too much of a hassle for Grist's developers to plug in any other kind of database engine. However @paulfitz is correct when writing that SQLite is a great tool -- which has saved my work while I was testing Grist in docker: I could easily back up my data then re-inject them into Grist, on another platform (self hosted docker, Grist's website, etc.)

[skamensky]

@paulfitz

I'd have loved to use grist for our company!

Use case to add support for postgres:

We already have a lot of legacy procedures and triggers to automate data writted in plpgsql (postgres' procedural language) and I've been on the hunt for a stellar frontend. We'd primarily be using the data entry features for its combined ease-of-use and respect of data types, and less for the formula and computations.

Ideally we'd do some sort of migration to fit the data to however grist wants to see it and keep out triggers/procedures.

I'm not sure it's reasonable for us to migrate to sqlite.

[paulfitz]

@skamensky your use-case totally makes sense. I'm sure someone will generalize the backend eventually. I've recently been using other flavors of SQLite support with Grist (better-sqlite3 and sql.js vs the existing node-sqlite3 code) and those changes were fairly straightforward. Postgres changes would be deeper, but not a quagmire I think. Still, for now it'll have to wait for the stars to align, when someone with both the need, the time, and the capacity comes along...

Thanks for chiming in, appreciate your perspective.