@protoutil/protoc-gen-sql

protoc-gen-sql

A protoc plugin that generates SQL schema and CRUD query files from annotated protobuf definitions. Define your entities once in proto, generate schema for multiple database engines from a single source of truth.

Installation

npm install -g @protoutil/protoc-gen-sql

Add the options proto as a dependency in your buf.yaml:

deps:
  - buf.build/protoutil/sql

Quick start

1. Annotate your proto messages

syntax = "proto3";
package myapp.v1;
import "protoutil/sql/v1/options.proto";

message User {
  option (protoutil.sql.v1.message) = {
    generate: true
    indexes: ["email"]
  };

  string name  = 1 [(protoutil.sql.v1.field).omit = true]; // derived at app layer
  string email = 2 [(protoutil.sql.v1.field).unique = true];
  string bio   = 3 [(protoutil.sql.v1.field).nullable = true];
  bool   active = 4;
}

2. Configure buf.gen.yaml

version: v2
inputs:
  - directory: proto
plugins:
  - local: protoc-gen-sql
    out: generated/postgres
    opt:
      - engine=postgres
  - local: protoc-gen-sql
    out: generated/sqlite
    opt:
      - engine=sqlite

3. Generate

buf generate

This produces:

generated/
├── postgres/
│   ├── schema.sql
│   └── queries/generated/
│       └── myapp_v1_users.sql
└── sqlite/
    ├── schema.sql
    └── queries/generated/
        └── myapp_v1_users.sql

---

Plugin options

Passed via the opt: key in buf.gen.yaml. Each invocation targets one engine.

Option	Required	Values	Default	Description
engine	✅	postgres mysql sqlite sqlserver	—	Target database engine
repeated_strategy		json_column array_column	json_column	How repeated fields are stored. array_column requires Postgres; falls back to json_column on other engines
oneof_strategy		nullable_columns type_column json_column	nullable_columns	Default strategy for oneof groups. Can be overridden per-oneof via OneofOptions
if_not_exists		true false	true	Emit CREATE TABLE IF NOT EXISTS and CREATE INDEX IF NOT EXISTS. Set to false only if your migration tooling manages existence checks itself
emit_create_schema		true false	false	Emit CREATE SCHEMA IF NOT EXISTS at the top of schema.sql. Only meaningful for postgres and sqlserver

---

Proto options reference

MessageOptions (protoutil.sql.v1.message)

Controls table-level generation for a message. Only messages with generate = true produce any output — all others are silently ignored.

Field	Type	Default	Description
generate	bool	false	Opt this message into generation. Must be true to produce any output
table_name	string	FQN snake_case	Override the generated table name. Default: library.v1.Book → library_v1_book
indexes	repeated string	[]	Composite indexes as comma-separated column names. e.g. ["author,title"]
unique_constraints	repeated string	[]	Composite unique constraints as comma-separated column names
skip_queries	repeated QueryType	[]	Suppress specific generated queries. See QueryType enum
foreign_keys	repeated ForeignKey	[]	Explicit FK column declarations. See ForeignKey message
timestamps	TimestampBehavior	BOTH	Controls created_at / updated_at injection. See TimestampBehavior enum
extra_columns	repeated FieldOptions	[]	Additional columns with no proto field. See extra columns section
primary_key	PrimaryKeyType	PRIMARY_KEY_TYPE_SERIAL	Storage type for the id primary key column. See PrimaryKeyType enum

FieldOptions (protoutil.sql.v1.field)

Controls column-level generation for a proto field. Also used as the element type for extra_columns.

Field	Type	Default	Description
column_name	string	field name	Override the column name. Required in extra_columns
column_type	string	engine default	Override the SQL type for all engines. Acts as a fallback when type_overrides has no entry for the target engine. Required in extra_columns unless type_overrides covers every engine you generate for
type_overrides	map<string, string>	{}	Per-engine type overrides. Keys must be valid engine names: postgres mysql sqlite sqlserver. Takes precedence over column_type. Validation: [V15] rejects unrecognised keys. See type_overrides usage
nullable	bool	false	Emit NULL instead of NOT NULL
default	string	—	SQL DEFAULT expression, written verbatim. e.g. "now()", "''", "0"
index	bool	false	Add a single-column index. For composite indexes use MessageOptions.indexes
unique	bool	false	Add a UNIQUE constraint
omit	bool	false	Exclude this field entirely. Must not be true in extra_columns
enum_strategy	EnumStrategy	ENUM_STRATEGY_NAME	How to store a proto enum field. Only meaningful on enum-typed fields
check	string	—	Inline CHECK constraint, written verbatim. e.g. "price_cents > 0"
skip_queries	repeated QueryType	[]	Suppress this column from specific generated queries. Applies to both proto-derived fields and extra_columns. e.g. (protoutil.sql.v1.field).skip_queries = QUERY_TYPE_GET, (protoutil.sql.v1.field).skip_queries = QUERY_TYPE_LIST to exclude a sensitive field from reads, or (protoutil.sql.v1.field).skip_queries = QUERY_TYPE_CREATE, (protoutil.sql.v1.field).skip_queries = QUERY_TYPE_UPDATE to exclude a DB-managed column from writes. See skip_queries usage

OneofOptions (sql.oneof)

Applied to the oneof declaration itself, not individual fields within it.

Field	Type	Default	Description
strategy	OneofStrategy	plugin opt	Override the global oneof_strategy for this group
skip_queries	repeated QueryType	[]	Suppress this oneof’s columns from specific generated queries. Same values and semantics as FieldOptions.skip_queries. See skip_queries usage

ForeignKey

Declares an explicit FK column on the table. Foreign keys are never inferred.

Field	Type	Default	Description
column	string	—	Required. Column name on this table. e.g. "shelf_id"
references_table	string	—	Required. Table being referenced. e.g. "library_v1_shelf"
references_column	string	"id"	Column on the referenced table
on_delete	ForeignKeyAction	engine default	CASCADE RESTRICT SET_NULL NO_ACTION
on_update	ForeignKeyAction	engine default	CASCADE RESTRICT SET_NULL NO_ACTION
skip_index	bool	false	Set to true to suppress the automatic index on the FK column. Useful when the FK column is already covered by a composite index declared in MessageOptions.indexes — generating a redundant single-column index wastes space and slows writes
skip_queries	repeated QueryType	[]	Suppress this FK column from specific generated queries. e.g. skip_queries: [QUERY_TYPE_UPDATE] to make the relationship immutable after creation, or skip_queries: [QUERY_TYPE_GET, QUERY_TYPE_LIST] to hide an internal column from reads. See skip_queries usage

---

Enums

QueryType

Used in MessageOptions.skip_queries, FieldOptions.skip_queries, OneofOptions.skip_queries, and ForeignKey.skip_queries.

Value	Generated query
QUERY_TYPE_GET	SELECT col1, col2, ... WHERE id = $1 LIMIT 1
QUERY_TYPE_LIST	SELECT col1, col2, ... ORDER BY id
QUERY_TYPE_CREATE	INSERT INTO ...
QUERY_TYPE_UPDATE	UPDATE ... SET ... WHERE id = $1
QUERY_TYPE_DELETE	DELETE ... WHERE id = $1
QUERY_TYPE_ALL	Suppresses all query generation for this message; schema DDL is still produced

TimestampBehavior

Used in MessageOptions.timestamps.

Value	created_at	updated_at
TIMESTAMP_BEHAVIOR_UNSPECIFIED / TIMESTAMP_BEHAVIOR_BOTH	✅ injected	✅ injected
TIMESTAMP_BEHAVIOR_CREATED_ONLY	✅ injected	❌ omitted
TIMESTAMP_BEHAVIOR_UPDATED_ONLY	❌ omitted	✅ injected
TIMESTAMP_BEHAVIOR_NONE	❌ omitted	❌ omitted

OneofStrategy

Value	Behavior
ONEOF_STRATEGY_NULLABLE_COLS	One nullable column per variant + CHECK constraint ensuring at most one is non-null
ONEOF_STRATEGY_TYPE_COLUMN	Discriminator column ({name}_type TEXT) + single value column ({name}_value TEXT)
ONEOF_STRATEGY_JSON_COLUMN	Entire oneof serialized to a single JSONB / JSON / TEXT column

---

EnumStrategy

Value	Behavior
ENUM_STRATEGY_NAME (default)	Store the enum value name as TEXT
ENUM_STRATEGY_INT	Store the integer value as INTEGER
ENUM_STRATEGY_CHECK_CONSTRAINT	Store as TEXT with a CHECK constraint listing all valid value names. The column identifier in the CHECK expression is quoted using the engine’s native quoting style (backticks on MySQL, double-quotes elsewhere)
ENUM_STRATEGY_NATIVE_TYPE	Use a native database enum type. Postgres only (CREATE TYPE ... AS ENUM); falls back to ENUM_STRATEGY_NAME on other engines

PrimaryKeyType

Value	Behavior
PRIMARY_KEY_TYPE_SERIAL (default)	Engine serial integer: BIGSERIAL (postgres), BIGINT UNSIGNED AUTO_INCREMENT (mysql), INTEGER (sqlite), BIGINT IDENTITY(1,1) (sqlserver)
PRIMARY_KEY_TYPE_UUID	Database-generated UUID: UUID DEFAULT gen_random_uuid() (postgres), CHAR(36) DEFAULT (UUID()) (mysql), TEXT DEFAULT (lower(hex(randomblob(16)))) (sqlite), UNIQUEIDENTIFIER DEFAULT NEWID() (sqlserver). FK columns referencing a UUID table automatically use the matching UUID type

---

Extra columns

extra_columns injects columns that have no corresponding proto field — useful for columns managed entirely at the database layer.

message Order {
  option (protoutil.sql.v1.message) = {
    generate: true
    extra_columns: [
      {
        column_name: "deleted_at"
        column_type: "TIMESTAMPTZ"
        nullable: true
        index: true
      },
      {
        column_name: "row_version"
        column_type: "BIGINT"
        default: "1"
        skip_queries: [QUERY_TYPE_CREATE, QUERY_TYPE_UPDATE]  // managed by DB trigger
      }
    ]
  };
  string reference = 1;
}

Extra columns appear after proto-derived fields and before timestamp columns.

By default, extra columns are included in SELECT, INSERT, and UPDATE. Use skip_queries to exclude specific columns from specific query types — see skip_queries usage for examples.

---

type_overrides usage

type_overrides lets you specify a different SQL type for each engine without changing the proto field type. Because the same proto is typically run through the plugin once per engine (via separate buf.gen.yaml plugin invocations), you can cover all engines without needing a column_type fallback:

// Cover every engine explicitly — no column_type needed
bool active = 1 [
  (protoutil.sql.v1.field).type_overrides = { key: "postgres"  value: "BOOLEAN"   },
  (protoutil.sql.v1.field).type_overrides = { key: "mysql"     value: "TINYINT(1)" },
  (protoutil.sql.v1.field).type_overrides = { key: "sqlite"    value: "INTEGER"    },
  (protoutil.sql.v1.field).type_overrides = { key: "sqlserver" value: "BIT"        }
];

Or use column_type as a universal fallback and only override where the default differs:

// column_type is the fallback; sqlite gets a specific override
bool active = 1 [(protoutil.sql.v1.field) = {
  column_type:    "BOOLEAN"
  type_overrides: { key: "sqlite" value: "INTEGER" }
}];

The same applies to extra_columns. [V06] will fire for any engine that has neither a matching type_overrides key nor a column_type fallback, so if you only provide a sqlite override and generate for postgres without column_type, you will get a validation error on the postgres run.

---

skip_index usage

By default, every FK column gets an automatic single-column index. Set skip_index: true to suppress it — typically when the FK column is already the leading column (or an early column) in a composite index you have declared in MessageOptions.indexes, making the single-column index redundant:

message Book {
  option (protoutil.sql.v1.message) = {
    generate: true
    // Composite index already covers shelf_id — single-column index is redundant
    indexes: ["shelf_id,created_at"]
    foreign_keys: [{
      column:          "shelf_id"
      references_table: "library_v1_shelf"
      skip_index:      true
    }]
  };
  string title = 1;
}

---

skip_queries usage

skip_queries suppresses a column from specific generated query types. It is available on FieldOptions, OneofOptions, and ForeignKey. Because skip_queries is a repeated field, each value must be specified as a separate option annotation.

Exclude a sensitive field from reads (GET and LIST):

message User {
  option (protoutil.sql.v1.message).generate = true;
  string email         = 1;
  string password_hash = 2 [
    (protoutil.sql.v1.field).skip_queries = QUERY_TYPE_GET,
    (protoutil.sql.v1.field).skip_queries = QUERY_TYPE_LIST
  ];
}

password_hash is included in INSERT and UPDATE but never appears in SELECT column lists.

Exclude a DB-managed column from writes (CREATE and UPDATE):

message Document {
  option (protoutil.sql.v1.message).generate = true;
  string body       = 1;
  string search_vec = 2 [
    (protoutil.sql.v1.field).skip_queries = QUERY_TYPE_CREATE,
    (protoutil.sql.v1.field).skip_queries = QUERY_TYPE_UPDATE
  ];
}

search_vec appears in SELECT but is never written by generated queries — a trigger or generated column expression manages it.

Make an FK relationship immutable after creation:

message Book {
  option (protoutil.sql.v1.message) = {
    generate: true
    foreign_keys: [{
      column:           "shelf_id"
      references_table: "library_v1_shelf"
      skip_queries:     [QUERY_TYPE_UPDATE]
    }]
  };
  string title = 1;
}

shelf_id is written on INSERT but excluded from UPDATE SET.

Suppress an entire oneof from all queries:

message Book {
  option (protoutil.sql.v1.message).generate = true;
  string title = 1;
  oneof contact {
    option (protoutil.sql.v1.oneof).skip_queries = QUERY_TYPE_CREATE;
    option (protoutil.sql.v1.oneof).skip_queries = QUERY_TYPE_UPDATE;
    option (protoutil.sql.v1.oneof).skip_queries = QUERY_TYPE_GET;
    option (protoutil.sql.v1.oneof).skip_queries = QUERY_TYPE_LIST;
    string email = 2;
    string phone = 3;
  }
}

QUERY_TYPE_DELETE and QUERY_TYPE_ALL have no effect at the column level.

---

Type mapping

Default proto scalar → SQL type per engine. Override per-field with column_type or type_overrides.

Proto type	postgres	mysql	sqlite	sqlserver
string	TEXT	TEXT	TEXT	NVARCHAR(MAX)
bool	BOOLEAN	TINYINT(1)	INTEGER	BIT
int32	INTEGER	INT	INTEGER	INT
int64	BIGINT	BIGINT	INTEGER	BIGINT
uint32	BIGINT	BIGINT UNSIGNED	INTEGER	BIGINT
uint64	NUMERIC(20,0)	BIGINT UNSIGNED	INTEGER	DECIMAL(20,0)
float	REAL	FLOAT	REAL	REAL
double	DOUBLE PRECISION	DOUBLE	REAL	FLOAT
bytes	BYTEA	BLOB	BLOB	VARBINARY(MAX)
sint32	INTEGER	INT	INTEGER	INT
sint64	BIGINT	BIGINT	INTEGER	BIGINT
fixed32	BIGINT	BIGINT UNSIGNED	INTEGER	BIGINT
fixed64	NUMERIC(20,0)	BIGINT UNSIGNED	INTEGER	DECIMAL(20,0)
sfixed32	INTEGER	INT	INTEGER	INT
sfixed64	BIGINT	BIGINT	INTEGER	BIGINT

Well-known types

Proto type	postgres	mysql	sqlite	sqlserver
google.protobuf.Timestamp	TIMESTAMPTZ	DATETIME(6)	TEXT	DATETIMEOFFSET
google.protobuf.Duration	BIGINT	BIGINT	INTEGER	BIGINT
google.protobuf.Struct	JSONB	JSON	TEXT	NVARCHAR(MAX)
google.protobuf.Value	JSONB	JSON	TEXT	NVARCHAR(MAX)
google.protobuf.ListValue	JSONB	JSON	TEXT	NVARCHAR(MAX)
google.protobuf.StringValue	TEXT	TEXT	TEXT	NVARCHAR(MAX)
google.protobuf.BoolValue	BOOLEAN	TINYINT(1)	INTEGER	BIT
google.protobuf.Int32Value	INTEGER	INT	INTEGER	INT
google.protobuf.Int64Value	BIGINT	BIGINT	INTEGER	BIGINT
google.protobuf.FloatValue	REAL	FLOAT	REAL	REAL
google.protobuf.DoubleValue	DOUBLE PRECISION	DOUBLE	REAL	FLOAT
google.protobuf.BytesValue	BYTEA	BLOB	BLOB	VARBINARY(MAX)

Injected columns

These columns are always added by the plugin and cannot be used as proto field names, FK columns, or extra column names unless the relevant injection is disabled.

Column	Type (postgres)	Controlled by
id	engine-specific serial	Always present; cannot be disabled
created_at	TIMESTAMPTZ	TimestampBehavior
updated_at	TIMESTAMPTZ	TimestampBehavior

---

Generated queries

For each opted-in message the plugin generates a CRUD query file. The file header includes the target engine so the source of each file is unambiguous when you generate for multiple engines.

Column inclusion in INSERT and UPDATE

The generated INSERT includes, in order:

1. FK columns (all, unless excluded via skip_queries)
2. Proto-derived fields (non-omitted, non-oneof)
3. Oneof columns (all strategies, unless excluded via skip_queries on the oneof)
4. Extra columns (unless excluded via skip_queries)

The generated UPDATE SET includes the same columns, except:

• FK columns with skip_queries: [QUERY_TYPE_UPDATE] are excluded
• Extra columns with skip_queries: [QUERY_TYPE_UPDATE] are excluded
• Oneof columns with skip_queries: [QUERY_TYPE_UPDATE] are excluded

---

Validation rules

The plugin validates all annotations before generating any output and reports all errors together. Each error includes a rule code for easy reference.

Rule	Description
V01	foreign_key.column must not be empty
V02	foreign_key.references_table must not be empty
V03	foreign_key.column must not be a reserved column (id, created_at, updated_at) unless the relevant injection is disabled via timestamps
V04	foreign_key.column must not duplicate another column in the table
V05	extra_columns entry must have a non-empty column_name
V06	extra_columns entry must have a non-empty column_type, or a type_overrides entry whose key matches the engine currently being generated for. Because the same proto is typically run through the plugin once per engine, an entry that only covers sqlite will pass when generating for sqlite but fail when generating for postgres — unless column_type is also set as a universal fallback
V07	extra_columns entry must not have omit = true
V08	extra_columns entry column_name must not be a reserved column
V09	extra_columns entry column_name must not duplicate another column
V10	Column names referenced in MessageOptions.indexes must exist in the table
V11	Column names referenced in MessageOptions.unique_constraints must exist in the table
V12	A proto field’s column_name override must not be a reserved column
V13	A proto field’s resolved column name must not duplicate another column
V14	ENUM_STRATEGY_NATIVE_TYPE requires engine=postgres. Error on all other engines
V15	type_overrides keys must be valid engine names (postgres, mysql, sqlite, sqlserver). Unrecognised keys (e.g. typos like "postgress" or wrong casing like "Postgres") are rejected at validation time

---

Table naming convention

By default, table names are derived from the fully-qualified proto message name:

• Dots replaced with underscores
• Converted to snake_case

Examples:

Proto message	Default table name
library.v1.Book	library_v1_book
library.v1.Shelf	library_v1_shelf
myapp.v2.UserProfile	myapp_v2_user_profile

This ensures table names are versioned alongside the proto package and never silently collide when you introduce a v2 of a package. Override with table_name if needed.