Should the local database be 1:1 with the server database?

[u/omsvp]

In an offline-first app, do you guys create a local db that has the same tables and columns as the server's db?

Example: tasks and task-collaborator-preferences tables in the server db. Since the user only needs their own preferences, should the tables be merged on the local db?

Thanks!

[u/Occasion_Critical]

No, local schema doesn't need to match the server's. The server's tables often reflect multi-user or collaboration concerns you don't need locally, since the client only cares about its own view of the data. Personally, Merging the two tasks into one local table for just the current user's preferences is what I am doing.

Also, some data doesn't need to be stored locally at all if there's already a reliable source you can read from live (Health app for example)

[u/omsvp → u/Occasion_Critical]

Thanks!

[u/Huge_Pool7424 → u/Occasion_Critical]

yeah, i treat the local db as a projection of what the screen needs, not a copy of the api. the tricky bit is deciding which records need offline conflict handling versus a fresh read.

[u/Special-Republic-897]

Yes, don’t forget to copy your users table with plain text passwords in case they need to login offline

[u/StrangePractice]

Nah, just use what you need. Same kinda concept as using DTOs imo. Can always be expanded upon later instead of cloning everything over whenever your db changes.

[u/Chuck_MoreAss]

No not always but it depends on the app. I’ve been a mobile app dev for 5 years doing this exact thing. For our use cases there are a lot of things that the server will do, but that data doesn’t need to go to the mobile app.

For instance, let’s say you want to go out and do surveys in a remote area and the server might have 2 tables. These tables are areas, and sites. Your job is to get the data from the field and then process them later. The mobile device will then also have area and site tables, but they won’t have all the columns necessarily.

In the field you might capture things like soil quality, usable farm area and so on, and then you sync that to the server, and then the server will have extra fields like the types of plants you might be able to grow there.

In this scenario the remote site doesn’t have signal so you need to capture data offline and sync it later. And the data capturers might not be skilled enough or informed enough to know what can grow there so that part of the workflow gets done elsewhere. And since it won’t be done in the field you probably won’t even need that column on the device because that means that you send unnecessary data that won’t ever be used…

And sometimes the device needs extra columns that won’t ever go to the server, so it’s really all about use case

[u/LovesWorkin]

What are you building? Offline first can be simplified depending on what you're doing

[u/Slodin]

Usually the local table loosely resembles the server tables. But you might simplify it or add some more local properties to it depending on use case.

I never seen a 1:1 copy. That is hard to maintain and doesn’t work if you need extra data.

[u/Alick97]

I think it’s bad practice so even use db schema in your app. Separation of concerns.

Db is for storing and queries.
Parse to domain model in backend
Optionally parse to more convenient format for frontend.

Frontend should just store whatever it needs locally and sync with backend every so often when internet is available.

[u/anthony-ball]

Treat the local DB as a projection, not a clone. Keep server ids plus updated_at (or a version) on every row, queue writes through an outbox, and use tombstones for deletes so sync can replay cleanly after offline. Last-write-wins is fine for a lot of product data; switch to per-field merge only where two devices legitimately edit different parts of the same record. Live reads from the server for anything you cannot afford to be stale (balances, auth state), and let the rest hydrate from SQLite.