Typescript client
The API -> Generator script page explained how to generate an Electric client for your application. In this section, we demonstrate the usage of a generated Electric client for an issue tracking application where users participate in projects, projects have issues, and users can comment on issues (in which case they are said to be the author of that comment). The data model for this application can be found on the API -> Typescript client page.
Instantiation
To instantiate an Electric client we need to electrify our database, passing along the generated database schema.
import { schema } from './generated/client'
import { insecureAuthToken } from 'electric-sql/auth'
import { electrify, ElectricDatabase } from 'electric-sql/wa-sqlite'
const config = {
auth: {
token: await insecureAuthToken({user_id: 'dummy'})
}
}
const conn = await ElectricDatabase.init('electric.db', '')
const electric = await electrify(conn, schema, config)
The electrify call returns a promise that will resolve to an ElectricClient
for our database.
The client exposes the following interface:
interface ElectricClient<DB> {
db: ClientTables<DB> & RawQueries
}
export type ClientTables<DB> = {
users: UsersTable
projects: ProjectsTable
memberships: MembershipsTable
issues: IssuesTable
comments: CommentsTable
}
interface RawQueries {
raw(sql: Statement): Promise<Row[]>
liveRaw(sql: Statement): LiveResultContext<any>
}
type Statement = {
sql: string
args?: BindParams
}
The Electric client above defines a property for every table in our data model: electric.db.users
, electric.db.projects
, etc.
The API of these tables is explained below when we discuss the supported queries.
In addition, one can execute raw SQL queries using the electric.db.raw
and electric.db.liveRaw
escape patches.
Raw queries should be used with caution as they are unchecked and may cause the sync service to stop if they are ill-formed.
Therefore, only use raw queries for features that are not supported by our regular API.
Authentication
Connectivity
Shapes
Shapes define the portion of the database that syncs to the user's device. Initially, users are not subscribed to any shape. Tables can be synced by requesting new shape subscriptions.
sync
To request a new shape subscription, we use the sync
method on database tables.
We can sync a single table:
const { synced } = await electric.db.comments.sync()
// shape request was acknowledged by the server
// waiting for the data to be delivered...
await synced
// now the shape data has been delivered
Or we can sync several tables using one shape:
const { synced } = await electric.db.projects.sync({
include: {
owner: true
}
})
await synced
// data for both tables got delivered
The code snippet above subscribes to a shape containing the projects
table as well as the users
table
since we explicitly included the owner
s of projects.
We can achieve the same using two separate shape subscriptions:
const { synced: sync1 } = await electric.db.projects.sync()
const { synced: sync2 } = await electric.db.users.sync()
await sync1
// data for projects table has been delivered
await sync2
// data for users table has been delivered
This approach differs from the previous code snippet because the data for the projects
and users
tables
is delivered independently, whereas, in the previous example they are deliver together as one database transaction.
When a table is not yet synced, it exists on the device's local database but is empty. If you try to read from an unsynced table you will get empty results and a warning will be logged:
Reading from unsynced table memberships
discover
- Subsetting
- Retention
- Derived shapes
- Sync boundaries
Queries
As explained before, every Electric client contains a db
property representing the electrified database.
The db
object defines a property for every database table; e.g. electric.db.issues
corresponds to the issues
table in the database.
Each table supports a variety of queries to create, read, update, and delete data. The interface definition of these queries can be found on our GitHub repository. Below, we demonstrate the different queries using the issue tracking application example.
create
create
creates a single database record and returns the created record (or a selection of the created record's fields if the select
argument is used).
Accepts an object containing the following arguments:
Options
Name | Example type | Required | Description |
---|---|---|---|
data | XOR<IssueCreateInput, IssueUncheckedCreateInput> | Yes | An object representing the record to be inserted. This object must contain a value for all non-nullable fields and may contain relation fields in order to perform nested transactional insertions. |
select | IssueSelect | No | A selection of fields to include in the returned object. |
include | IssueInclude | No | Specifies relations to be included. |
Examples
We can use create
to create a new issue:
await electric.db.issues.create({
data: {
id: "0c67311d-196e-4504-b64d-27fa59679a65",
title: "Create my first Electric app",
project_id: "9c0f2a8f-0d8b-405d-8278-b1b2f200e7d2"
}
})
Similarly, we can create an issue and limit the fields that are returned:
const { id, title } = await electric.db.issues.create({
data: {
id: "0c67311d-196e-4504-b64d-27fa59679a65",
title: "Create my first Electric app",
project_id: "9c0f2a8f-0d8b-405d-8278-b1b2f200e7d2"
},
select: {
id: true,
title: true
}
})
Or, we can include related fields on the returned object:
await electric.db.issues.create({
data: {
id: "0c67311d-196e-4504-b64d-27fa59679a65",
title: "Create my first Electric app",
},
include: {
project: true
}
})
We can also create an issue and the project it belongs to:
await electric.db.issues.create({
data: {
id: "0c67311d-196e-4504-b64d-27fa59679a65",
title: "Create my first Electric app",
project: {
create: {
id: "9c0f2a8f-0d8b-405d-8278-b1b2f200e7d2",
name: "My project",
owner_id: "Alice"
}
}
}
})
createMany
createMany
creates one or more database records within one transaction.
Returns a count of how many records were created.
Options
Name | Example type | Required | Description |
---|---|---|---|
data | IssueCreateManyInput | IssueCreateManyInput[] | Yes | One or more records to create. Records must contain values for all non-nullable fields and may include related fields to perform nested inserts transactionally. |
skipDuplicates | boolean | No | Do not create records for which a unique field or ID field already exists. Thereby, ignoring records that are conflicting. |
Examples
Create a single issue:
const { count } = await electric.db.issues.createMany({
data: {
id: "0c67311d-196e-4504-b64d-27fa59679a65",
title: "Create my first Electric app",
}
})
Create multiple issues:
const { count } = await electric.db.issues.createMany({
data: [
{
id: "0c67311d-196e-4504-b64d-27fa59679a65",
title: "Create my first Electric app",
},
{
id: "5252f22d-4223-4b18-be1c-b149f21e6f5c",
title: "Improve the app",
},
]
})
Ignore conflicting records:
const id = "0c67311d-196e-4504-b64d-27fa59679a65"
const { count } = await electric.db.issues.createMany({
data: [
{
id,
title: "Create my first Electric app",
},
{
id,
title: "Improve the app",
},
],
skipDuplicates: true
})
console.log(count) // prints 1 because the 2nd record has the same ID
// as the first one so it is ignored
findUnique
findUnique
retrieves a single and uniquely identified database record.
Returns the record if it is found and null
otherwise.
Options
Name | Example type | Required | Description |
---|---|---|---|
where | IssueWhereUniqueInput | Yes | One or more fields that uniquely identify a record. |
select | IssueSelect | No | A selection of fields to include on the returned object. |
include | IssueInclude | No | Specifies relations to be included. |
Examples
Using the where
argument we can fetch a record by its unique identifier or by a combination of fields
that uniquely identify the record:
const result = await electric.db.issues.findUnique({
where: {
id: 5
}
})
If we are only interested in the author's name we can use select
:
const { author } = await electric.db.issues.findUnique({
where: {
id: 5
},
select: {
author: true
}
})
And if we want to also get the issue's comments and their authors we can use include
:
const issueWithCommentsAndTheirAuthor = await electric.db.issues.findUnique({
where: {
id: 5
},
include: {
comments: {
include: {
author: true
}
}
}
})
findFirst
findFirst
returns the first data record from the list of data records that match the query.
Returns a record if one was found and null
otherwise.
findFirst
is equivalent to findMany
with argument take: 1
.
Options
Name | Example type | Required | Description |
---|---|---|---|
where | IssueWhereInput | No | Fields on which to filter the records. |
select | IssueSelect | No | A selection of fields to include on the returned object. |
include | IssueInclude | No | Specifies relations to be included. |
orderBy | IssueOrderByInput | IssueOrderByInput[] | No | Order the list of matching records by the specified properties. |
skip | number | No | Number of records to skip from the list of matching records. |
distinct | IssueDistinctFieldEnum[] | No | Filter duplicates based on one or more fields. |
Examples
Get an issue of some project:
await electric.db.issues.findFirst({
where: {
project: {
id: '17b1daef-07b3-4689-9e73-5af05474f17d'
}
}
})
To get the first issue of some project we can use orderBy
to order on ascending time of insertion:
await electric.db.issues.findFirst({
where: {
project: {
id: '17b1daef-07b3-4689-9e73-5af05474f17d'
}
},
orderBy: {
inserted_at: 'asc'
}
})
Similarly, we can get the latest issue of some project using orderBy
to order on descending time of insertion:
await electric.db.issues.findFirst({
where: {
project: {
id: '17b1daef-07b3-4689-9e73-5af05474f17d'
}
},
orderBy: {
inserted_at: 'desc'
}
})
And if we want to get the 2nd latest issue of some project we can combine orderBy
with skip: 1
:
await electric.db.issues.findFirst({
where: {
project: {
id: '17b1daef-07b3-4689-9e73-5af05474f17d'
}
},
orderBy: {
inserted_at: 'desc'
},
skip: 1
})
findMany
findMany
returns a list of all data records that match the query.
It supports the same arguments as findFirst
with the addition of take
.
Options
Name | Example type | Required | Description |
---|---|---|---|
where | IssueWhereInput | No | Fields on which to filter the records. |
select | IssueSelect | No | A selection of fields to include on the returned object. |
include | IssueInclude | No | Specifies relations to be included. |
orderBy | IssueOrderByInput | IssueOrderByInput[] | No | Order the list of matching records by the specified properties. |
skip | number | No | Number of records to skip from the list of matching records. |
distinct | IssueDistinctFieldEnum[] | No | Filter duplicates based on one or more fields. |
take | number | No | Number of matching data records to return. |
Examples
We can fetch all issues:
await electric.db.issues.findMany() // equivalent to passing an empty object {}
We can also use take
to limit the results to the first 5 issues:
await electric.db.issues.findMany({
take: 5
})
update
update
updates a uniquely identified database record and returns the updated record.
Options
Name | Example type | Required | Description |
---|---|---|---|
data | XOR<IssueUpdateInput, IssueUncheckedUpdateInput> | Yes | Object containing the fields to be updated. |
where | IssueWhereUniqueInput | Yes | One or more fields that uniquely identify the record. |
select | IssueSelect | No | A selection of fields to include in the returned object. |
include | IssueInclude | No | Specifies relations to be included in the returned object. |
Examples
We can use update
to update the title of a specific issue:
await electric.db.issues.update({
data: {
title: 'Updated issue title'
},
where: {
id: '17b1daef-07b3-4689-9e73-5af05474f17'
}
})
We can also update an issue and one of its comments by using a nested update
:
await electric.db.issues.update({
data: {
title: 'Updated issue title',
comments: {
update: {
data: {
text: 'Updated comment text'
},
where: {
author_id: '422bfea3-2a1f-45ae-b6f9-3efeec4a5864'
}
}
}
},
where: {
id: '6a87a816-4b8c-48e5-9ccf-eaf558032d82'
}
})
We can also update an issue and all or several of its comments by using a nested updateMany
:
await electric.db.issues.update({
data: {
title: 'Updated issue title',
comments: {
updateMany: {
data: {
text: 'Updated comment text'
}
}
}
},
where: {
id: '6a87a816-4b8c-48e5-9ccf-eaf558032d82'
}
})
We can even nest several queries by providing an array of nested update
and/or updateMany
queries.
The following query updates an issue and two of its comments:
await electric.db.issues.update({
data: {
title: 'Updated issue title',
comments: {
update: [
{
data: {
text: 'Updated comment text'
},
where: {
id: 'f4ec1d54-664d-40e8-bc64-2736fb3c14b3'
}
},
{
data: {
text: "Updated another comment's text"
},
where: {
id: '15d1cf30-80c3-4fb8-bcbf-a681171d134f'
}
}
]
}
},
where: {
id: '6a87a816-4b8c-48e5-9ccf-eaf558032d82'
}
})
Note that updates can be arbitrarily nested, i.e., there is no limit to the number of nested updates.
updateMany
updateMany
updates several database records and returns a count indicating how many records were updated.
Options
Name | Example type | Required | Description |
---|---|---|---|
data | XOR<IssueUpdateManyMutationInput, IssueUncheckedUpdateManyInput> | Yes | Object containing the fields to be updated. |
where | IssueWhereInput | No | Filters the database records based on the provided field values. |
Examples
updateMany
can be used to update all database records:
await electric.db.issues.updateMany({
data: {
description: 'Default description for all issues'
}
})
or it can be used to update certain database records, e.g, all issues of a project:
await electric.db.issues.updateMany({
data: {
description: 'Default description for all issues of this project'
},
where: {
project_id: '6c0b6320-830e-42f8-937d-da389e9591e3'
}
})
upsert
upsert
updates a uniquely identified database record if it exists and creates it otherwise.
upsert
returns the updated/created record.
Options
Name | Example type | Required | Description |
---|---|---|---|
create | XOR<IssueCreateInput, IssueUncheckedCreateInput> | Yes | An object representing the record to be inserted. This object must contain a value for all non-nullable fields and may contain relation fields in order to perform nested transactional insertions. |
update | XOR<IssueUpdateInput, IssueUncheckedUpdateInput> | Yes | Object containing the fields to be updated. |
where | IssueWhereUniqueInput | Yes | One or more fields that uniquely identify the record. |
select | IssueSelect | No | A selection of fields to include in the returned object. |
include | IssueInclude | No | Specifies relations to be included in the returned object. |
Examples
We can use upsert
to update or create a new issue:
const issue = await electric.db.issues.upsert({
create: {
id: '0c67311d-196e-4504-b64d-27fa59679a65',
title: 'Create my first Electric app',
project_id: '9c0f2a8f-0d8b-405d-8278-b1b2f200e7d2'
},
update: {
title: 'Create my first Electric app'
},
where: {
id: '0c67311d-196e-4504-b64d-27fa59679a65'
}
})
delete
delete
deletes a uniquely identified database record and returns the deleted record.
If the record to be deleted is not found, delete
throws an InvalidArgumentError
.
Options
Name | Example type | Required | Description |
---|---|---|---|
where | IssueWhereUniqueInput | Yes | One or more fields that uniquely identify the record. |
select | IssueSelect | No | A selection of fields to include in the returned object. |
include | IssueInclude | No | Specifies relations to be included in the returned object. |
Examples
We can delete a specific issue:
const issue = await electric.db.issues.delete({
where: {
id: '0c67311d-196e-4504-b64d-27fa59679a65'
}
})
deleteMany
deleteMany
deletes all database records that match the query and returns a count indicating how many records were deleted.
Options
Name | Example type | Required | Description |
---|---|---|---|
where | IssueWhereInput | No | Filters the database records based on the provided field values. |
Examples
We can use deleteMany
to delete all issues:
const { count } = await electric.db.issues.deleteMany()
or only delete the issues belonging to a certain project:
const { count } = await electric.db.issues.deleteMany({
where: {
project_id: '9c0f2a8f-0d8b-405d-8278-b1b2f200e7d2'
}
})
Live Queries
The queries discussed above are examples of one-off queries.
However, often times applications need to react to live changes of the data.
To this end, the Electric client supports live versions for all find queries.
Live queries are integrated with React by means of the useLiveQuery
hook:
import { useLiveQuery } from 'electric-sql/react'
const { results } = useLiveQuery(db.issues.liveMany())
The live query above fetches all issues.
The results
variable will automatically be updated
when new issues are created and when existing issues are updated or deleted.
The useLiveQuery
hook can be used in combination with any live query.
The supported live queries are discussed below.
liveUnique
Live version of the findUnique
query.
Options
Name | Example type | Required | Description |
---|---|---|---|
where | IssueWhereUniqueInput | Yes | One or more fields that uniquely identify the record. |
select | IssueSelect | No | A selection of fields to include on the returned object. |
include | IssueInclude | No | Specifies relations to be included. |
liveFirst
Live version of the findFirst
query.
Options
Name | Example type | Required | Description |
---|---|---|---|
where | IssueWhereInput | No | Fields on which to filter the records. |
select | IssueSelect | No | A selection of fields to include on the returned object. |
include | IssueInclude | No | Specifies relations to be included. |
orderBy | IssueOrderByInput | IssueOrderByInput[] | No | Order the list of matching records by the specified properties. |
skip | number | No | Number of records to skip from the list of matching records. |
distinct | IssueDistinctFieldEnum[] | No | Filter duplicates based on one or more fields. |
liveMany
Live version of the findMany
query.
Options
Name | Example type | Required | Description |
---|---|---|---|
where | IssueWhereInput | No | Fields on which to filter the records. |
select | IssueSelect | No | A selection of fields to include on the returned object. |
include | IssueInclude | No | Specifies relations to be included. |
orderBy | IssueOrderByInput | IssueOrderByInput[] | No | Order the list of matching records by the specified properties. |
skip | number | No | Number of records to skip from the list of matching records. |
distinct | IssueDistinctFieldEnum[] | No | Filter duplicates based on one or more fields. |
take | number | No | Number of matching data records to return. |
Advanced examples
We now provide a number of advanced examples regarding the orderBy
and distinct
options
supported by several of the queries documented above.
Grouping and ordering with orderBy
Sometimes we need to order query results based on some field.
To this end, we can chose to sort any field in ascending ('asc'
) or descending ('desc'
) order:
await electric.db.issues.findMany({
orderBy: {
title: 'asc'
}
})
The above query fetches all issues and sorts them in ascending lexicographical order of their title.
We can also sort on several fields. For instance, we can group issues by project and sort the issues of each project on their title:
await electric.db.issues.findMany({
orderBy: [
{
project_id: 'asc'
},
{
title: 'asc'
}
]
})
Selecting distinct records with distinct
We can select distinct records based on one or more fields using distinct
.
For example, we can fetch distinct issue titles and include their comments:
await electric.db.projects.findMany({
distinct: ['title'],
include: {
comments: true
}
})
The above query will return only 1 record per distinct issue title so if two issues have the same title, it will return only one of them. We can also fetch distinct issues based on both their title and description:
await electric.db.projects.findMany({
distinct: ['title', 'description'],
include: {
comments: true
}
})
Now, if there are two issues with the same title but different descriptions, the query will return both issues.
Operators
Electric clients support a variety of operators that can be applied to strings, numbers, and datetimes.
gt
/ gte
Greater than operator:
{
where: {
age: {
gt: 17
}
}
}
Greater than or equal operator:
{
where: {
name: {
gte: 18
}
}
}
lt
/ lte
Lesser than operator:
{
where: {
age: {
lt: 66
}
}
}
Lesser than or equal operator:
{
where: {
age: {
lte: 65
}
}
}
Equality operator
Equality is expressed via direct assignment to the column name:
{
where: {
username: 'Alice'
}
}
not
Inequality can be expressed using not
:
{
where: {
username: {
not: 'Alice'
}
}
}
in
/ notIn
We can use in
to check that the value is part of a list of values:
{
where: {
username: {
in: ['Alice', 'Bob']
}
}
}
We can use notIn
to check that the value is not part of a list of values:
{
where: {
username: {
notIn: ['Alice', 'Bob']
}
}
}
startsWith
startsWith
checks that the string starts with a given prefix:
where: {
title: {
startsWith: 'The'
}
}
endsWith
endsWith
checks that the string ends with a given suffix:
where: {
title: {
endsWith: 'documentation'
}
}
contains
contains
checks that the string contains a given string:
where: {
title: {
contains: 'ElectricSQL'
}
}
AND
/ OR
/ NOT
Combining operators such as AND
, OR
, and NOT
are supported.
Operators can be combined as multiple parts of the same clause, e.g.:
{
where: {
age: {
gte: 18,
lte: 65
}
}
}
The same can be achieved by using AND
explicitly:
{
where: {
AND: [
{
name: {
gte: 18,
}
},
{
name: {
lte: 65,
}
},
]
}
}
Operators can also be combined using OR
:
{
where: {
OR: [
{
age: {
lt: 18,
}
},
{
name: {
gt: 65,
}
},
]
}
}
Operators can be negated using NOT
:
{
where: {
NOT: [
{
age: {
lt: 18,
}
},
{
name: {
gt: 65,
}
},
]
}
}
NOT
applies to every condition in the list,
so the above example filters out users under the age of 18 as well as users above the age of 65.