Collections

Zodgres provides a simple and intuitive API for querying collections using Postgres.js ES6 Tagged Template Strings.

Creating Records

Create single or multiple records in a collection.

For complex SQL operations, use insert() with raw SQL.

Single Record

const users = db.collection('users', {
  id: z.number().optional(),
  name: z.string(),
  age: z.number().optional(),
});

// Open database connection and run migrations
await db.open();

const user = await users.create({
  name: 'John',
  age: 30,
});

Before using collections: Make sure to call await db.open() after defining all your collections. This establishes the database connection and runs migrations before you can perform any operations.

Multiple Records

const newUsers = await users.create([
  { name: 'Alice' },
  { name: 'Bob', age: 25 },
]);

Validation

Data is validated against your Zod schema:

// ❌ Validation error
await users.create({
  name: '',
  age: -5,
});

Retrieving Records

Retrieve records from a collection using SQL template literals.

All Records

const allUsers = await users.select();
const allUsers = await users.select`*`;

With Conditions

const user = await users.select`* WHERE name = ${'John'}`;
const adults = await users.select`* WHERE age >= ${18}`;

Specific Columns

const names = await users.select`name`;
const adultNames = await users.select`name WHERE age > ${25}`;

Ordering and Limiting

const orderedUsers = await users.select`* ORDER BY age DESC`;
const topUsers = await users.select`* ORDER BY id LIMIT ${5}`;

Aggregations

const countResult = await users.select<{ count: string }>`COUNT(*)`;
const avgAge = await users.select<{ avg: string }>`AVG(age) WHERE age IS NOT NULL`;

Single Record

Select a single record. Automatically adds LIMIT 1 and returns the first result or undefined.

const firstUser = await users.selectOne`*`;
const user = await users.selectOne`* WHERE name = ${'John'}`;

Updating Records

Update records and return the number of updated records. If you want to return the updated data, use the RETURNING clause.

Basic Updates

const updated = await users.update`name = ${'John'} WHERE id = ${1}`;
console.log(updated.count); // 1

const multi = await users.update`age = ${25} WHERE name = ${'Bob'}`;
console.log(updated.count); // 1

Multiple Fields

You may pass an object to the first argument to update multiple fields at once:

const updated = await users.update`
  ${{ name: 'Jane', age: 31 }}
  WHERE id = ${2}
`;

Or, you may fill in the fields manually:

const updated = await users.update`
  name = ${'Jane'}, age = ${31}
  WHERE id = ${2}
`;

Returning Data

You may use the RETURNING clause to return the updated data:

const updated = await users.update`
  ${{ name: 'Jane', age: 31 }}
  WHERE id = ${2}
  RETURNING *
`;

All Records

const allUpdated = await users.update`status = ${'active'}`;
console.log(allUpdated.count); // 100

Deleting Records

Delete records from a collection.

All Records

const deletedCount = await users.delete();

With Conditions

const deletedCount = await users.delete`WHERE age < ${18}`;

With RETURNING

const deletedUsers = await users.delete<{ id: number, name: string }>`
  WHERE name = ${'John'} RETURNING *
`;

Counting Records

Count records in a collection.

const totalUsers = await users.count();
const adultCount = await users.count`WHERE age >= ${18}`;
const activeAdults = await users.count`
  WHERE age >= ${18} AND status = ${'active'}
`;

Raw SQL Insert

Insert records using raw SQL and return the inserted data.

The insert() method uses raw SQL, unlike create() which accepts structured objects.

const inserted = await users.insert`(name, age) VALUES (${'John'}, ${30})`;

const inserted = await users.insert`(name) VALUES (${'Jane'})`;

const multiple = await users.insert`
  (name, age) VALUES
  (${'Alice'}, ${25}),
  (${'Bob'}, ${30})
`;

Parameter Safety

Template literals automatically handle parameter escaping - thanks to Postgres.js.

const searchTerm = "'; DROP TABLE users; --";

// ✅ Safe - parameters are properly escaped
const results = await users.select`* WHERE name = ${searchTerm}`;

Error Handling

Handle query errors gracefully:

try {
  const user = await users.create({
    name: 'John',
    age: 30,
  });
} catch (error) {
  if (error.code === '23505') {
    // Unique constraint violation
    console.error('Duplicate key violation');
  } else if (error instanceof z.ZodError) {
    // Validation error
    console.error('Invalid data:', error.errors);
  } else {
    console.error('Database error:', error.message);
  }
}

Type Safety

All query results are properly typed based on your schema:

const users = db.collection('users', {
  id: z.number().optional(),
  name: z.string(),
  age: z.number().optional(),
});

const allUsers = await users.select();
// Type: Array<{ id: number, name: string, age: number | undefined }>

allUsers.forEach(user => {
  console.log(user.name); // ✅ TypeScript knows this is a string
  console.log(user.age);  // ✅ TypeScript knows this can be undefined
  // console.log(user.email); // ❌ TypeScript error - not in schema
});

Performance Tips

Batch Operations

// ✅ Better - single query
const newUsers = await users.create([
  { name: 'User 1' },
  { name: 'User 2' },
]);

// ❌ Slower - multiple queries
for (const userData of userDataArray) {
  await users.create(userData);
}

Select Only Needed Columns

// ✅ Better - only needed columns
const userNames = await users.select`name WHERE age > ${18}`;

// ❌ Slower - unnecessary data
const activeUsers = await users.select`* WHERE age > ${18}`;
const names = activeUsers.map(u => u.name);

Collections

On this page