Store multiples databases with the same name

[ingdesarr1]

Good afternoon. In this moment, I have an app that works in many devices, computers, and each one have her own database, to register information about a process. I want to sync this databases with cloud, but, I want to keep each database separated, so that each client can consult the information only for their device. Is this possible, and if so, how can I do it?

Thank you

[andinux]

Hi @ingdesarr1 ,

Thanks for reaching out.

What you're describing is a perfect use case for OffSync in combination with Row-Level Security (RLS) and Access Tokens. These features are designed specifically to support secure, multi-device, and multi-tenant architectures—while maintaining clear data separation between clients.

---

✅ Recommended Approach

Instead of managing a separate database per device, we suggest using a single shared database with an additional column to track ownership. For example:

CREATE TABLE tasks (
  id TEXT PRIMARY KEY NOT NULL,
  user_id TEXT,
  title TEXT,
  status TEXT
);

With this setup:

• OffSync enables automatic bidirectional syncing between the device and the cloud.
• RLS ensures each client only has access to their own records, based on the user_id field and their authenticated access token.

---

🔐 Example RLS Policies

Here’s how you could define policies to enforce per-user data access:

1. Users can only see their own records

   -- SELECT policy
   user_id = auth_userid()

2. Users can only insert records for themselves

   -- INSERT policy
   NEW.user_id = auth_userid()

3. Users can only update their own records

   -- UPDATE policy
   OLD.user_id = auth_userid()

4. Users can only delete their own records

   -- DELETE policy
   OLD.user_id = auth_userid()

---

⚙️ Why This Matters

This approach scales far better than managing multiple databases or tables—especially as your user base grows. With a single table and secure row-level access, your app logic becomes much simpler and more maintainable.

Let me know if you’d like guidance setting up OffSync or configuring RLS for your use case.

Best regards,
Andrea Donetti
SQLite Cloud

[ingdesarr1]

Thanks for your answer. Each client's database has different registered users, and the information managed by each team is unique and cannot be mixed with other databases, as this would also result in a mix-up of information in the records within the database and the software that manages it. What other options can be used or implemented to avoid sharing databases?

[andinux]

Thanks for the follow-up — your clarification is helpful.

If your requirement is to ensure strict separation between each client's dataset — to the point where each client has its own users and schema, and no data or logic should ever be shared — then yes, using completely separate databases per client is a valid and supported approach.

---

🔒 Two Approaches You Can Choose From

1. Single Cloud Database + Row-Level Security (Recommended for Most Multi-Tenant Apps)

With this model:

• You maintain a single cloud database, while each local database on a user's device contains only their own data.
• Any query on the cloud database made by a token-authenticated user is automatically filtered by the Row-Level Security (RLS) module, so each user can only access their own data — even when connecting directly to the cloud node.
• Each row in the cloud is tagged with an owner_id or client_id.
• Row-Level Security ensures clients can only read, write, or sync their own data.
• Access Tokens further isolate users at sync time.

This setup is secure, scales well, and simplifies management — although all data resides in the same logical cloud database (with strict RLS enforcement).

Properly configured, this model prevents any data mixing or leakage, even with shared tables and infrastructure.

2. Fully Isolated Databases per Client

If you prefer complete physical and logical separation, you can:

• Create one SQLite Cloud database per client, each with its own schema and dataset.
• Use different access tokens and connection configurations per team or device.

This provides:

• Maximum isolation.
• Flexibility for each client to have a different schema or data model.
• Clear separation of backup, restore, and access control policies.

The trade-off is higher operational overhead — especially as the number of clients increases — since you'll need to manage multiple databases individually.

---

✅ Recommendation

If the teams truly require hard separation (distinct users, schema, and logic per client), then using a separate database per client is appropriate.

However, if the concern is primarily about accidental data exposure or data mix-ups, a single cloud database with Row-Level Security and Access Tokens provides strong isolation guarantees — with significantly less complexity.

Best regards,
Andrea Donetti
SQLite Cloud

[danielebriggi]

I can say that if the number of client's databases/installations is limited, you can sync each client's local database to a dedicated database on the cloud. In this way you can keep a backup of the client's users data and interact with those data from the cloud database. With this solution you have isolated databases (as they are locally) and you don't need to implement Row Level Security policies.
On the other hand, if the number of clients may grow, this approach is not scalable and you should a single remote database, as centralized aggregator of your client's data, where all your client's databases sync to, and on which client's data is isolated via Row Level Security policies.