02-custom/

02-custom

The DSL cannot express every query. This example adds hand-written SQL and proto next to the generated files, and both halves compile into one typed API.

• Hand-written custom.sql and custom.proto live beside the generated files and survive regeneration
• A hand-written service that reuses a generated proto message
• Virtual fields: Contracts(entlite.PROTO()) gives a field with no column, filled in by the server
• Choosing the key type per entity: field.Int64("id") on a high volume table
• A foreign key follows the type of the entity it points at
• Query level Contracts(): a query that stays in the database layer and gets no rpc
• Two entities in one schema
• A multi-word entity name: SensorReading becomes the table sensor_reading, which sqlc reads back as the Go type SensorReading

Entities

sqlite/ent/schema has two:

• Sensor — a device in the field. Also declares latest_value as Contracts(entlite.PROTO()). That is a virtual field: no column, no place in any generated SQL, but it is in the proto message. The server fills it in.
• SensorReading — a measurement from a sensor. Declares its own key as field.Int64("id") instead of taking the default int32. Readings are high volume and int32 stops at 2.1B rows. sensor_id stays field.Int because it points at Sensor's int32 key.

SensorReading's Update() is Contracts(entlite.SQLC()). A reading is a recorded fact, so clients never edit it. The database query exists, the rpc does not.

Hand-written files

entlite only writes fixed filenames, so anything else in these folders stays.

• sqlite/ent/contract/sqlc/custom.sql — a LEFT JOIN with a subquery using sqlc.embed, an aggregate, and a bulk delete.
• sqlite/ent/contract/proto/custom.proto — a SensorAnalyticsService in the same package. It imports schema.proto and reuses the generated Sensor message.

Server

sqlite/server/server.go serves all three services from the same generated types. ListWithLatestReading is where the two halves meet: the custom LEFT JOIN returns an embedded sensor row, the generated converter turns it into the same pb.Sensor the CRUD service returns, and latest_value is filled from the joined reading.

int64 key, end to end

The key type travels the whole stack. Proto gets int64 id. SQLite columns are already 64-bit, so the wrapper drops the narrowing convert it emits for int32 keys — compare GetSensorReadingById with GetSensorById in sqlite/ent/gen/db/queries.sql.go.

On the wire an int64 is JSON encoded as a string, so a reading is {"id":"2", ...} and a sensor is {"id":2, ...}. In TypeScript it is a bigint, which is why reading IDs in the frontend use bigIntInput() and not numberInput().

Run

cd sqlite
make run     # serves on :8080

On sqlite filter.Range("recorded_at") emits recorded_at >= @min_recorded_at AND recorded_at <= @max_recorded_at, not BETWEEN: sqlc drops the bounds from the params struct unless the range is the first filter, and the query then binds parameters it never received. custom.sql writes its ranges the same way.