What is SynthQL?

SynthQL is a full-stack HTTP client for your PostgreSQL database. It allows you declaratively describe your client components' data dependencies.

With SynthQL, you can focus on building great products instead of spending time thinking about how to efficiently fetch data into your components.

SynthQL reads your PostgreSQL database schema and generates types, so you get type safety end-to-end.

// Compose your query using the type-safe query builder
const q = from('movies')
  .columns('id', 'title')
  .filter({ id: 1 })
  .take(2);

// Executing the query (using the React query client)
const { data: movies } = useSynthql(q);

console.log(movies);
// Will print:
[
 { id: 1, title: 'The Empire Strikes Back' },
];

Why SynthQL?

End-to-end type safety

Generate types from your schema with a single command. These types will then power the type-safety provided by the query builder. You should run this command on your CI to ensure that the types are always up to date.

// Running the command:
npx @synthql/cli generate --url $DATABASE_URL

// generates a database types file like:

export interface DB {
    actor: {
        columns: {
            actor_id: {
                type: number;
                selectable: true;
                includable: true;
                whereable: true;
                nullable: false;
                isPrimaryKey: true;
            };
            name: {
                type: string;
                selectable: true;
                includable: true;
                whereable: true;
                nullable: false;
                isPrimaryKey: false;
            };
        };
    }
}

// which in turn powers a query builder, `from()`,
// which you can use to create SynthQL queries like:

const q = from('actor')
    .columns('actor_id', 'name')
    .filter({ actor_id: 1 })
    .first();
            

Composable query language

Build complex queries by composing smaller queries. The SynthQL query language is designed for easy composition and reusability.

const findPetsByOwner = (owner) =>
    from('pets')
        .filter({ owner })
        .all();

const findPersonById = (id) => {
    const pets = findPetsByOwner(id);

    return from('people')
        .filter({ id })
        .include({ pets })
        .firstOrThrow();
};

Deferred queries

As queries grow in size, latency increases. Deferred queries allow you to split large object graphs, optimizing page load times. In the following example, we use a deferred query to load the store and its products separately. This allows the store to load quickly, while the products load in the background. This is particularly useful when the products aren't immediately visible on the page.

const products = from('products')
    .column('id', 'name', 'price')
    .filter({
        product_id: { in: col('store.product_ids') }
    })
    .defer() // <<== Marks the subquery as 'deferred'
    .all();

const query = from('store')
    .column('id', 'name')
    .filter({ id })
    .include({
        products
    })
    .all();

/* This returns two JSON lines */

// First line of JSON:
[
    {
        "id": "1",
        "name": "Fancy store",
        "products": {
            "status": "pending"
        }
    }
]

// Once the products have loaded:
[
    {
        "id": "1",
        "name": "Fancy store",
        "products": {
            "status": "done",
            "data": [
                {
                    "id": "1",
                    "name": "Shoe",
                    "price": 199
                }
            ]
        }
    }
]
        

Security

SynthQL offers several security features to help secure your application, including built-in authentication, query whitelisting, and more.

const findPetsByOwner = (ownerId) => {
    return from('pets')
        .column('name', 'id')
        .filter({ ownerId })
        .permissions('users:read', 'pets:read')
        .all();
};

const findPersonByIds = (ids) => {
    return from('people')
        .column('first_name','last_name')
        .filter({ id: { in: ids }})
        .permissions('person:read')
        .include({
            films: findPetsByOwner(col('people.id'))
        })
        .all();
};

Custom query providers

Not all data comes from the database. Use custom providers to join your database tables with data from third-party APIs, ensuring predictable performance.

const findFilmsWithRatings = () => {
    const ratings = from('rotten_tomatoes_ratings')
        .filter({
            year: col('film.year')
        })
        .all();

    return from('films')
        .filter({ year: 1965 })
        .include({ ratings })
        .all();
};