Skip to main content

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 owners 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

NameExample typeRequiredDescription
dataXOR<IssueCreateInput, IssueUncheckedCreateInput>YesAn 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.
selectIssueSelectNoA selection of fields to include in the returned object.
includeIssueIncludeNoSpecifies 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

NameExample typeRequiredDescription
dataIssueCreateManyInput | IssueCreateManyInput[]YesOne or more records to create. Records must contain values for all non-nullable fields and may include related fields to perform nested inserts transactionally.
skipDuplicatesbooleanNoDo 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

NameExample typeRequiredDescription
whereIssueWhereUniqueInputYesOne or more fields that uniquely identify a record.
selectIssueSelectNoA selection of fields to include on the returned object.
includeIssueIncludeNoSpecifies 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

NameExample typeRequiredDescription
whereIssueWhereInputNoFields on which to filter the records.
selectIssueSelectNoA selection of fields to include on the returned object.
includeIssueIncludeNoSpecifies relations to be included.
orderByIssueOrderByInput | IssueOrderByInput[]NoOrder the list of matching records by the specified properties.
skipnumberNoNumber of records to skip from the list of matching records.
distinctIssueDistinctFieldEnum[]NoFilter 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

NameExample typeRequiredDescription
whereIssueWhereInputNoFields on which to filter the records.
selectIssueSelectNoA selection of fields to include on the returned object.
includeIssueIncludeNoSpecifies relations to be included.
orderByIssueOrderByInput | IssueOrderByInput[]NoOrder the list of matching records by the specified properties.
skipnumberNoNumber of records to skip from the list of matching records.
distinctIssueDistinctFieldEnum[]NoFilter duplicates based on one or more fields.
takenumberNoNumber 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

NameExample typeRequiredDescription
dataXOR<IssueUpdateInput, IssueUncheckedUpdateInput>YesObject containing the fields to be updated.
whereIssueWhereUniqueInputYesOne or more fields that uniquely identify the record.
selectIssueSelectNoA selection of fields to include in the returned object.
includeIssueIncludeNoSpecifies 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

NameExample typeRequiredDescription
dataXOR<IssueUpdateManyMutationInput, IssueUncheckedUpdateManyInput>YesObject containing the fields to be updated.
whereIssueWhereInputNoFilters 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

NameExample typeRequiredDescription
createXOR<IssueCreateInput, IssueUncheckedCreateInput>YesAn 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.
updateXOR<IssueUpdateInput, IssueUncheckedUpdateInput>YesObject containing the fields to be updated.
whereIssueWhereUniqueInputYesOne or more fields that uniquely identify the record.
selectIssueSelectNoA selection of fields to include in the returned object.
includeIssueIncludeNoSpecifies 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

NameExample typeRequiredDescription
whereIssueWhereUniqueInputYesOne or more fields that uniquely identify the record.
selectIssueSelectNoA selection of fields to include in the returned object.
includeIssueIncludeNoSpecifies 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

NameExample typeRequiredDescription
whereIssueWhereInputNoFilters 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

NameExample typeRequiredDescription
whereIssueWhereUniqueInputYesOne or more fields that uniquely identify the record.
selectIssueSelectNoA selection of fields to include on the returned object.
includeIssueIncludeNoSpecifies relations to be included.

liveFirst

Live version of the findFirst query.

Options

NameExample typeRequiredDescription
whereIssueWhereInputNoFields on which to filter the records.
selectIssueSelectNoA selection of fields to include on the returned object.
includeIssueIncludeNoSpecifies relations to be included.
orderByIssueOrderByInput | IssueOrderByInput[]NoOrder the list of matching records by the specified properties.
skipnumberNoNumber of records to skip from the list of matching records.
distinctIssueDistinctFieldEnum[]NoFilter duplicates based on one or more fields.

liveMany

Live version of the findMany query.

Options

NameExample typeRequiredDescription
whereIssueWhereInputNoFields on which to filter the records.
selectIssueSelectNoA selection of fields to include on the returned object.
includeIssueIncludeNoSpecifies relations to be included.
orderByIssueOrderByInput | IssueOrderByInput[]NoOrder the list of matching records by the specified properties.
skipnumberNoNumber of records to skip from the list of matching records.
distinctIssueDistinctFieldEnum[]NoFilter duplicates based on one or more fields.
takenumberNoNumber 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.