Can't generate Go code when using CREATE EVENT .. in MySQL

[senicko]

Version

1.26.0

What happened?

Hello! I am working on a side project and I am using MySQL events. The CREATE EVENT statement is a part of my migration file.

-- event that clears stale sessions every hour
CREATE EVENT clear_stale_sessions ON SCHEDULE EVERY 1 HOUR DO DELETE
                                                              FROM sessions
                                                              WHERE expires_at < CURRENT_TIMESTAMP;

When I run sqlc generate I get the following error.

# package
sql/schema/002_sessions.sql:17:13: syntax error near "EVENT clear_stale_sessions ON SCHEDULE EVERY 1 HOUR DO DELETE"

(Code works fine of course when run agains MySQL database.)

I don't think sqlc needs to know about this event to generate my queries. It could be just skipped. I can't find any docs on how to do that, or if it's even possible. If someone could nudge me into where this could be implemented I am willing to try adding feature like this myself. I think it could look like

CREATE TABLE sessions
(
	id            VARCHAR(64) NOT NULL UNIQUE,
	user_id       BIGINT      NOT NULL UNIQUE,
	expires_at    TIMESTAMP   NOT NULL,
	last_activity TIMESTAMP   NOT NULL,

	created_at    TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
	updated_at    TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

	CONSTRAINT FK_sessions_user_id FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
);

--  :sqlcdisable

-- event that clears stale sessions every hour
CREATE EVENT clear_stale_sessions ON SCHEDULE EVERY 1 HOUR DO DELETE
                                                              FROM sessions
                                                              WHERE expires_at < CURRENT_TIMESTAMP;
                                                              
-- :sqlcenable

Idk, maybe there already is some solution for such cases. I appreciate any help!

Relevant log output

# package
sql/schema/002_sessions.sql:17:13: syntax error near "EVENT clear_stale_sessions ON SCHEDULE EVERY 1 HOUR DO DELETE"

Database schema

-- +goose Up 

CREATE TABLE sessions
(
	id            VARCHAR(64) NOT NULL UNIQUE,
	user_id       BIGINT      NOT NULL UNIQUE,
	expires_at    TIMESTAMP   NOT NULL,
	last_activity TIMESTAMP   NOT NULL,

	created_at    TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
	updated_at    TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

	CONSTRAINT FK_sessions_user_id FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
);

-- event that clears stale sessions every hour
CREATE EVENT clear_stale_sessions ON SCHEDULE EVERY 1 HOUR DO DELETE
                                                              FROM sessions
                                                              WHERE expires_at < CURRENT_TIMESTAMP;

-- +goose Down

DROP TABLE sessions;
DROP EVENT clear_stale_sessions;

SQL queries

No response

Configuration

version: "2"
sql:
  - engine: "mysql"
    queries: "./sql/queries"
    schema: "./sql/schema"
    gen:
      go:
        package: "db"
        out: "gen/db"

Playground URL

No response

What operating system are you using?

macOS

What database engines are you using?

MySQL

What type of code are you generating?

Go

[orisano]

The TiDB parser does not support the CREATE EVENT statement, which poses an issue (sqlc's MySQL engine relies on the TiDB parser). There is no workaround other than removing or commenting out the statement before processing with sqlc. I believe this is related to #3130.