Where is the form data stored in the database?

[eloi]

Hi everyone!

We have a instance of ODK Central running on our own servers.

Being a DBA I would like to access the collected form data directly from PostgreSQL db. I searched in all tables but I could not find out where the form data is stored in the db. Could someone bring me some light on this? Thanks!

Kinds regards,

Eloi

[Ri_Ri]

Hi,

It can be a bit tricky to get your data directly into PostgreSQL.
With ODK Central, your data are stored in PostgreSQL, but it might not be immediately usable. Sorry

I'm not sure why there isn't a directly usable table/geom table with Postgis.

You can follow these GitHub repositories for guidance:
https://github.com/mathieubossaert/central2pg?tab=readme-ov-file using directly Postgresql.
https://github.com/mathieubossaert/pl-pyodk using pyodk on Postgresql DB

Thanks to @mathieubossaert for developing these programs.

[yanokwa]

We don't recommend connecting to the database directly and instead recommend you use the API. The reasons for that are detailed below.

Remotely connect to the ODK central default database

We don't recommend connecting to the database directly because it's not a stable interface. We reserve the right to change the structure, names, and types in the database without telling you. If you want to interact with data in the database, use the Central API, which is a stable and documented interface. If there is something missing from the API, then let us know so we can add it. Independent of that, making any database accessible to the Internet is not a great idea because it leaves you vu…

[eloi]

Thanks Ri_Ri,

I'll have a look into the links in your message.

Cheers,

Eloi

[eloi]

Hi Yanokwa,

It's not that I want to manage the data from the database. I just want to know where the data is so I feel I'm in control of the data. Knowing that the data is in the database is already a relieve, because we have backups of the db. If it was in Kubernetes PVC (Persistent Volume Claim) then we wouldn't have a backup. My message was to clear my doubts on this.

By the way, the pictures are also stored in the db?

Thanks for the quick reply!

Cheers,

Eloi

[eloi]

I see that pictures are in 'blobs' table.
Eloi

[ktuite]

@eloi The form data and submission data is stored as XML in its original format in the form_defs and submission_defs table, which you can think of as storing specific versions of forms or submissions. There are some things that we extract from forms and submissions and keep in the database (such as attachments stored as blobs), but for the most part, Central stores/works with raw data as much as possible. For example, when you export your data to a CSV, or make an OData API call, it is parsing all the relevant submissions on the fly. Like Yaw said, we don't promise to keep the database schema stable so it's not a good idea to depend on it, but hopefully that gives you more insight into where your data is actually being stored.

[eloi]

Thanks @ktuite, very useful info.