db using Sqlite about clace HOT 5 CLOSED

The plan for data persistence support in Clace is two fold. First (managed) is a built-in solution and second (external) uses plugins for accessing external databases. The external approach will mean plugins for any database, exposing the database native api (like SQL), but the tables/collections have to be managed externally by the user. Clace plugins just provide the API to Starlark to work with the external storage.

The first (managed) solution involves Clace managing the storage abstraction, automatically creating the tables and providing the migration required as schema evolves. The plan is to implement this solution first. The second external solution will come after the support for external plugin loading is added.

The goals for the managed persistence solution are:

1. Storage automatically managed by Clace, no manual table management required.
2. Portable across storage providers, without requiring application code changes. Initial target is sqlite and postgres, mongodb should be possible later.
3. Avoid implementing an ORM

The current design to achieve these goals is:

1. Implement a document store approach. The documents are persisted as sqlite tables, postgres tables or later mongodb collections. JSON(B) support in the db is used to implement the sqlite/postgres solution.
2. Provide schema support, such that the document store has a structured schema instead of being a data dump. The schema is defined by the app developer, it is persisted to the storage as json. Nested lists and dicts are supported. The database schema is not expected to be normalized. Joins are not supported.
3. Each app can have a linked storage location. One store can be shared across apps. Each store has one main app, which manages the schema migration. Other secondary apps can work with the store as per their defined permissions. An admin app will be provided later which allows administering the storage.
4. Schema migration support using object update functions. No database changes are required for schema migration, since a JSON document is used for storage
5. CRUD API's are exposed for use from Starlark code, loosely based on the mongodb python API pymongo.
6. Indexes can be defined in the schema to improve performance and set uniqueness constraints.

The end user interface should be simple. An app create call on an app which used the managed persistence plugin should automatically create the tables in sqlite (default), no additional work should be required.

The current status is the schema loading support was added few days back 693fd62. The next release 0.4 will have the full managed sqlite support in a few weeks.

The goal of Clace is to prove a complete solution for building hypermedia based web applications, backend and UI. The storage API will be exposed as a Starlark call for use in the backend Starlark application code. There is no plan to automatically create JSON REST API's for use from external applications. The starlark code is the only client. That code can implement a JSON api if desired (though Hypermedia clients expose HTML apis generally). In that way, Clace is different from Firebase and Pocketbase type backend as a service solutions, which prove storage and automatically expose JSON REST API's.

Would appreciate any inputs on the proposed solution.

from clace.

It sounds really good.

JSON storage aspects are not clear yet.

Will the DB have a Web GUI that is HTMX driven, so that users can interact with the DB in some way ?

from clace.

Apps can share a storage, app A can be configured to use same storage as app B. The type information will be automatically loaded. This feature will be used to allow admin apps to show the storage details for any app. I am not sure about the timeline for the admin app currently.

from clace.

@gedw99 Basic functionality for sqlite support is working. See https://github.com/claceio/apps/blob/main/utils/bookmarks for a sample app.

The workflow is

1. Create a schema file https://github.com/claceio/apps/blob/main/utils/bookmarks/schema.star, which specified the types and indexes required
2. The tables and indexes are automatically created in the account linked to the db.store plugin, sqlite file in $CL_HOME/clace_apps.db by default
3. The app code can use doc.bookmark to create a new document of bookmark type, table.bookmark to use the store apis to work with the table. See https://github.com/claceio/apps/blob/d83c59601f01d145ec0534bdf1c802e1f508071b/utils/bookmarks/app.star#L21

Transaction support is still in progress. https://github.com/claceio/clace/blob/main/internal/app/tests/store_test.go has more tests. The query syntax is like mongo, which has the advantage of not having to worry about sql injection. Some complex queries are in

Please let me know any feedback

from clace.

Added transaction functionality for store plugin, docs are at https://clace.io/docs/plugins/store/

from clace.