matharmin

Conceptually that's not hard, but in practice an approach like that can significantly increase the complexity of the app:

1. Do you store the tree structure for every table in your app? If you have 20 tables that could be edited offline, do you re-implement it for each table, or try to have a generic implementation? 2. Do you design all your tables around the tree structure, or do you just store it in addition to your "normal" tables? 3. Every piece of code that modifies one of these tables need to do it via the tree structure - if you update your tables directly from any place it could effectively cause conflicts. 4. Do you build separate UI to resolve conflicts for every table? 5. Do you query and cache the tree structure on the device, or does it have to be online to resolve conflicts? 6. Do you expose the tree structure via external APIs, or keep it internal?

I find that "last-write-wins" is sufficient for a large percentage of cases, and much simpler to implement. Or in some cases, just doing conflict detection is sufficient (notify the user that the data has changed between loading and saving, and they need to re-apply their edits).

If you do need conflict resolution on a large scale (many different tables), I'd recommend using data structures designed for that. CRDTs is one example - while it is typically used for automatic conflict resolution, it often stores enough data to allow manual resolution if desired.

I have a recent example of rolling out IPv6 in AWS:

1. Create a new VPC, get an auto-assigned /56 prefix from AWS.

2. Create subnets within the VPC. Each subnet needs an explicitly-specified /64 prefix. (Maybe it can be auto-assigned by AWS, but you may still want to follow a specific pattern for your subnets).

3. Add those subnet prefixis to security / Firewall rules.

You can do this with a sufficiently-advanced config language - perhaps it has a built-in function to generate subnets from a given prefix. But in my experience, using a general-purpose programming language makes it really easy to do this kind of automation. For reference, I did this using Pulumi with TypeScript, which works really well for this.

As I read those caveats, it's pretty-much just "do all of this and you'll be fine". I practice, developers do often skip some steps, and it still works in 99% of cases, until you hit that 1% and have data loss.

I'm not sure what the actual takeaway is. Software that don't require all the manual configuration steps to work properly is better, but it's not always feasible (in this case it's requirements for how the app uses SQLite, which cannot be controlled by Litestream directly). Or maybe Litestream should fail with hard errors if it detects issues in the config (not sure if it's all possible to detect). Or maybe we need to have higher expectations of developers reading the docs properly?

I'd recommend doing these benchmarks with WAL mode and synchronous = NORMAL as well. It makes quite a big difference in performance especially with many small transactions (e.g. the first example that inserts without explicit transactions).

You don't need JSONB for this - doing this with plain JSON is simpler and already faster than individual inserts for most bindings in my experience.

I typically do bulk inserts using a single JSON argument like this:

    WITH ins AS (SELECT e.value ->> 'id', e.value ->> 'name', e.value ->> 'age' FROM json_each(?) e)
    INSERT INTO data (id, name, age)
    SELECT * FROM ins

The same approach can be used for bulk updates and deletes as well.

There are a couple of similar projects - ElectricSQL, cr-sqlite and PowerSync (which I'm working on) all support React Native.

SQLite does actually provide the mechanisms required to listen for changes via update hooks. It's unfortunate that many SQLite bindings don't expose that. I'm using it in a very simple way - automatically rerun the query if data in the underlying table changes. It's perhaps not as efficient as incrementally updating the results, but with how fast queries in SQLite usually are, I find that doesn't really matter.

Thanks for the link, I haven't come across SymmetricDS before. The list of supported databases is impressive.

It appears to be primarily aimed at server-to-server replication. It does support Android and iOS, but I don't see much documentation on that. Do you know if it works in practice to implement offline-first applications?

> How similar is this to MeteorJs minimongo collections?

> Does this provide an architecture where a client subscribes to a collection and the server keeps track of which data the client has and only sends minimal update messages?

It is similar in that PowerSync also supports local queries and real-time/streaming updates. However, I believe minimongo / MeteorJS uses an in-memory database, while PowerSync fully persists the data, allowing full offline capabilities.

The PowerSync client does subscribe to incremental changes, similar to MeteorJS (although the client keeps track of the state, not the server).

> Is this ideal for intense real-time applications (ex:Chat)?

Yes, PowerSync will work well for that. One caveat is that PowerSync may have slightly more latency than some other real-time systems (in the order of tens to hundreds of milliseconds), in favor of consistency and offline persistence of data. This means PowerSync will not be suitable for time-sensitive games as an example.

> Is self-hosted a future option?

Yes - see other comments for details on self-hosting and our open-source plans.

IndexedDB is fine - just use some wrapped that makes it easier to work with. Our current SQLite implementation actually stores the underlying data inside IndexedDB - mostly due to a lack of better options (but that's busy changing with OPFS).

The difficult parts are mostly related to keeping the local data in sync with the server, whether that uses SQLite, IndexedDB or some other database.

I just scanned through the video, but it seems to focus on persisting client-side changes locally when offline, which only covers one part of the bigger problem.

Difficulties quickly come in if you want to: 1. Persist large amounts of data on the client. 2. Keep the data in sync incrementally (too much data to re-download every time). 3. Keep the data up-to-date in realtime (streaming changes). 4. Keep the data consistent, especially across multiple tables / types.

Many "offline" solutions are actually closer to caching rather than offline-first, which introduce all the issues associated with cache invalidation.

Also, if you're just working with a single table, you might get by with just using updated_at timestamps and soft deletes to be able to get incremental changes from the server. Even then you need to be careful with consistency issues, e.g. making sure timestamps are always in order and without duplicates. And if you start adding more tables, the complexity quickly increases.

You're right that you'll have exactly the same issues if you use Date with a string.

However, with a string date you can bypass the Date class completely in many use cases.

My experience with this is anecdotal, but I've ran into many bugs due to conversion between day-precision values and Date objects. One example was a date picker library that returned values at 00:00 in the local timezone instead of UTC, which were then not correctly converted to UTC-based values before being persisted.

Once again, these are all preventable issues - just use UTC everywhere. I've just seen issues like that happen enough that I prefer never converting between dates and timestamps if I can avoid it.

I find this concept is important in understanding causal ordering for distributed systems, for example in the context of CRDTs. For events generated on a single device, you always have a complete ordering. But if you generate events on two separate devices while offline, you can't say one came before the other, and end up with a ≹ relationship between the two. Or put differently, the events are considered concurrent.

So you can end up with a sequence "d > b > a" and "d > c > a", but "c ≹ b".

Defining how tie-breaking for those cases are deterministically performed is a big part of the problem that CRDTs solve.

We've been in business for over 10 years, and the company (JourneyApps) is profitable - we're not going out of business any time soon.

Also see the other comments regarding our current status and plans around open source.

To expand on what prawnstar said - it's exactly because of this that we recommend using uuids as the primary key (uuid v4 specifically). While you can use integer ids, you need some workarounds such as pre-generating sets of unique ids per device.

Thanks for chiming in! ElectricSQL is great - we see it come up a lot in discussions since on the surface it solves the same problems (syncing between Postgres and SQLite), despite the details being very different. I love seeing all the innovation in the offline-first space!

You can do it in a consistent way, for example always store the timestamp at 00:00 UTC. But that's not obvious just by looking at the values, which is why it can be ambiguous.

And when parsing those values, you could do for example (JS) `new Date(ts*1000).getDay()`. Of course that's not correct - you need to use the UTC methods. But it's easy to miss, and may pass all your tests until you switch the timezone.

Those aren't massive issues - you just have to be careful with the parsing and serialization, and you need to be careful in any case. It just explains my personal preference.

> Also, for the replication piece is the SQLite bit actually important or could you actually support arbitrary backends and SQLite is just convenient? Eg could you support browser LocalStorage instead of WASM SQLite or is there some piece of functionality of SQLite you’re relying on?

SQLite is supported practically everywhere, so that's our first choice. We also modelled our sync rules on SQLite's type system and functions to a large extent.

However, there is nothing technical tying is to SQLite. The sync protocol works purely with JSON, and doesn't impose any restrictions on how the data is stored or queried.

For the JourneyApps platform where PowerSync was originally used, we actually have an implementation on top of IndexedDB for browsers, which ends up being more lightweight. It needs more work before we can expose it as a general library, so we just started with SQLite for now.

Personally I prefer the shortened ISO8601 format (YYYY-MM-DD). While it's not the most efficient, efficiency doesn't often matter on that level. It's unambiguous, can be parsed by practically any library, and easily human-readable.

I'd avoid using unix timestamps for day-resolution, since it's very easy to make a mistake with the timezone when parsing, which you only notice when changing between a positive and negative timezone.

Julian day is another option, but support for it is not quite as universal. If efficiency is important, that's the format I'd use.

I see what you mean about promise chain cancellation, I misunderstood that part. You can still structure Promises using nesting instead of chains to get the same effect as with callbacks, but in reality I just use async/await instead.

> Why would you pose the question if you already biased against the answer? Is this an attempt to prejudice a candidate?

By question I mean I may judge the candidate for it. Of course it's your choice if you prefer callbacks, but I'd view that as a potential compatibility issue with my team.

De-facto doesn't mean it's the only option, it means it's the dominant option, and that is true.

Promises don't have a built-in way to cancel chains (neither do plain callbacks), and I think that was the right decision. AbortController is becoming a common pattern, and that is working quite well in my opinion.

Promises are not purely stylistic. It gives you guarantees, such as that each Promise can only resolve or reject once. It can easily be used in utility functions such as Promise.all and Promise.race. There are libraries that have equivalents for callback-style APIs, but it's typically more tricky to use, and relies on specific conventions being followed.

NodeJS took a while to adopt it, but all Node APIs I use now have Promise versions. All/most Deno APIs use Promises. All new browser APIs use Promises, and for the existing callback-style ones such as IndexedDB, most developers use Promise wrapper libs to make it manageable.

Of course you're free to use callbacks of you want to. But I would definitely recommend Promises and async/await to any new developer, make it a requirement on any team I lead, and even question why someone uses callbacks in their code if I interview them.

How do you deal with shapes and permissions not being available yet?

How does this compare to Yjs/y-crdt?

SQLite's json1 works pretty well for arrays

SQLite comes up a lot on HN, but it's not PR - it's content that others find interesting, myself included. In most cases it's not even official SQLite content being posted, but rather other projects or blogs around it.

I can definitely see where ORDER BY in aggregates would be useful - I recently used it with Postgres, and didn't even know it's not available in SQLite. Often you can do the sorting in the app code, but it's nice to do directly in the query.

Other niche but useful features I've found with GROUP BY:

1. You can filter the rows used for the aggregate using a FILTER clause [1]: `group_concat(name order by name desc) filter where(salary > 100)`. While you could do the same filter in the main query, this form would still give you departments where all employees are filtered out.

2. When selecting non-aggregate values in a GROUP BY, an arbitrary row is typically picked. You can control which one is picked by using min() or max() on a different column [2]. Example: `select depertment, name as best_earner, max(salary) as best_salary from employees group by department`

[1]: https://www.sqlite.org/lang_aggfunc.html

[2]: https://www.sqlite.org/lang_select.html#bare_columns_in_an_a...

Azure KeyVault costs are about 10% that of the certificate (I think it's $6/month).

I'm probably not gonna get to a full post anytime soon, but I'll summarize here. This is from memory, so I may have some things wrong.

1. DigiCert CS certificate. You can validate your organization before paying anything, but it felt like we ended up in a low-priority queue because of that. After not hearing back for 2-3 weeks, I emailed support, then got validated in a day or two.

2. Azure KeyVault: "Premium" pricing model, since you need RSA 3072-bit or RSA 4096-bit HSM-backed keys. Generate a CSR here. There are a couple of annoying steps such as getting the access control setup right, but nothing too complicated.

3. Once you have a validated org and paid for the CS certificate, you can upload the CSR to DigiCert, and download the certificate.

4. "Merge" the certificate on Azure KeyVault.

5. Create an "application" on Azure which gives you API credentials. You need to copy a whole bunch of IDs:

# key vault:

azure-key-vault-url

azure-key-vault-certificate

# client application:

azure-key-vault-tenant-id

azure-key-vault-client-id

azure-key-vault-client-secret

You use the above with AzureSignTool to do the signing, e.g. from you CI system.

I recently went through this same issue at my company - only found out about the change in requirements when I couldn't renew my cert at the previous provider.

There is surprisingly little info available on how to do code signing for Windows now. I don't want to use a physical device - with fully remote teams it's not feasible. Eventually settled on Azure KeyVault with Digicert (I don't like Comodo aka Sectigo). There is really little info available on how to get it all to work together, and you have to spent around $600 before you can even try and see whether it can work.

Now that it's all configured, the setup works well. The new setup of doing the signing via Azure is more secure than storing the private keys on the CI system. But I never thought that signing an app for Windows would be more difficult than signing for macOS or iOS.

SQLite is by far my favorite database for any client-side application - mobile and desktop (and perhaps embedded). I still wouldn't use it server-side for anything serious.

While you can do it, especially with tech such as LiteFS, I much prefer using database systems with built-in support for replication, failover, and splitting data storage from the app server.