Connect a connection to a class for quick actions

Is your feature request related to a problem? Please describe. The problem this proposal attempts to resolve is standardizing a way for table turned class models can easily run database operations without having to always rely on building out queries with the query builder chain pattern.

Describe the solution you'd like When a user uses the introspectively generated models created by the db:models package call, those models should inherit from a BaseTable class that obscures and hides some easy to use operations for interacting with your database. Such functions should include operations such as:

INSERT
UPDATE
DELETE
UPSERT*

Upon the completion of the operations INSERT, UPDATE, and UPSERT the server should return with the latest values of the model and re-set all the properties of the model based on the server response. This will help when new models are inserted and the column may have a custom DEFAULT operation that is performed on the database level such as generating a uuid() value to populate it.

By taking the database response and setting those columns to the newly set, or server updated values, that ensures our model contains the latest and most accurate values leaving the database as the continued source of truth and value definition.

Describe alternatives you've considered

Proposal 1 – Pass connection as a parameter to helper functions

Create a new instance of the model class and call one of the pre-defined helper functions from BaseTable. In this version I propose that we continue to follow as close to SQL nomenclature as possible and allow you to call .insert(db) where the user can feel safe knowing that it will be executing an INSERT operation onto their database.

const connection: OuterbaseConnection = new OuterbaseConnection('API_KEY')
const db = Outerbase(connection)

let newPerson: Person = new Person({
    id: '123',
    first_name: 'Johnny',
    last_name: 'Appleseed',
})

// Should it be this:
newPerson.insert(db)

// ... some time passes ...

newPerson.update(db)

Proposal 2 – Attach a connection to the entity and then call operations directly

This option allows users to attach a connection object to an entity once and potentially call multiple calls to it without having to continue passing the connection object to it.

const connection: OuterbaseConnection = new OuterbaseConnection('API_KEY')
const db = Outerbase(connection)

let newPerson: Person = new Person({
    // id: '123', // <-- Not provided by default, assigned by database
    first_name: 'Johnny',
    last_name: 'Appleseed',
})

newPerson.attachConnection(db) // <-- If you fetch the models via the library, automatically attach the db
await newPerson.insert()

console.log(newPerson.id) // --> 123

// ... some time passes ...

await newPerson.update()

Proposal 3 – Call the operation from the connection instead of from the model

Another alternative option could be that when we want to perform an operation from a model it could originate from the connection directly like such:

const connection: OuterbaseConnection = new OuterbaseConnection('API_KEY')
const db = Outerbase(connection)

let newPerson: Person = new Person({
    id: '123',
    first_name: 'Johnny',
    last_name: 'Appleseed',
})

newPerson = await db.insert(newPerson)

// ... some time passes ...

newPerson = await db.update(newPerson)

The pro for this approach is that it reads with very clear intent – we are using the database to insert newPerson into it.

Additional context N/A

outerbase / sdk