Add roomserver tests (1/?) (#2434)

* Add EventJSONTable tests

* Add eventJSON tests

* Add EventStateKeysTable tests

* Add EventTypesTable tests

* Add Events Table tests
Move variable declaration outside loops
Switch to testify/assert for tests

* Move variable declaration outside loop

* Remove random data

* Fix issue where the EventReferenceSHA256 is not set

* Add more tests

* Revert "Fix issue where the EventReferenceSHA256 is not set"

This reverts commit 8ae34c4e5f78584f0edb479f5a893556d2b95d19.

* Update GMSL

* Add tests for duplicate entries

* Test what happens if we select non-existing NIDs

* Add test for non-existing eventType

* Really update GMSL

15 files changed, 490 insertions, 79 deletions

diff --git a/roomserver/storage/postgres/event_json_table.go b/roomserver/storage/postgres/event_json_table.go
index b3220eff..5f069ca1 100644
--- a/roomserver/storage/postgres/event_json_table.go
+++ b/roomserver/storage/postgres/event_json_table.go
@@ -59,12 +59,12 @@ type eventJSONStatements struct {
 	bulkSelectEventJSONStmt *sql.Stmt
 }
 
-func createEventJSONTable(db *sql.DB) error {
+func CreateEventJSONTable(db *sql.DB) error {
 	_, err := db.Exec(eventJSONSchema)
 	return err
 }
 
-func prepareEventJSONTable(db *sql.DB) (tables.EventJSON, error) {
+func PrepareEventJSONTable(db *sql.DB) (tables.EventJSON, error) {
 	s := &eventJSONStatements{}
 
 	return s, sqlutil.StatementList{
@@ -97,9 +97,9 @@ func (s *eventJSONStatements) BulkSelectEventJSON(
 	// We might get fewer results than NIDs so we adjust the length of the slice before returning it.
 	results := make([]tables.EventJSONPair, len(eventNIDs))
 	i := 0
+	var eventNID int64
 	for ; rows.Next(); i++ {
 		result := &results[i]
-		var eventNID int64
 		if err := rows.Scan(&eventNID, &result.EventJSON); err != nil {
 			return nil, err
 		}
diff --git a/roomserver/storage/postgres/event_state_keys_table.go b/roomserver/storage/postgres/event_state_keys_table.go
index 762b3a1f..338e11b8 100644
--- a/roomserver/storage/postgres/event_state_keys_table.go
+++ b/roomserver/storage/postgres/event_state_keys_table.go
@@ -76,12 +76,12 @@ type eventStateKeyStatements struct {
 	bulkSelectEventStateKeyStmt    *sql.Stmt
 }
 
-func createEventStateKeysTable(db *sql.DB) error {
+func CreateEventStateKeysTable(db *sql.DB) error {
 	_, err := db.Exec(eventStateKeysSchema)
 	return err
 }
 
-func prepareEventStateKeysTable(db *sql.DB) (tables.EventStateKeys, error) {
+func PrepareEventStateKeysTable(db *sql.DB) (tables.EventStateKeys, error) {
 	s := &eventStateKeyStatements{}
 
 	return s, sqlutil.StatementList{
@@ -123,9 +123,9 @@ func (s *eventStateKeyStatements) BulkSelectEventStateKeyNID(
 	defer internal.CloseAndLogIfError(ctx, rows, "bulkSelectEventStateKeyNID: rows.close() failed")
 
 	result := make(map[string]types.EventStateKeyNID, len(eventStateKeys))
+	var stateKey string
+	var stateKeyNID int64
 	for rows.Next() {
-		var stateKey string
-		var stateKeyNID int64
 		if err := rows.Scan(&stateKey, &stateKeyNID); err != nil {
 			return nil, err
 		}
@@ -149,9 +149,9 @@ func (s *eventStateKeyStatements) BulkSelectEventStateKey(
 	defer internal.CloseAndLogIfError(ctx, rows, "bulkSelectEventStateKey: rows.close() failed")
 
 	result := make(map[types.EventStateKeyNID]string, len(eventStateKeyNIDs))
+	var stateKey string
+	var stateKeyNID int64
 	for rows.Next() {
-		var stateKey string
-		var stateKeyNID int64
 		if err := rows.Scan(&stateKey, &stateKeyNID); err != nil {
 			return nil, err
 		}
diff --git a/roomserver/storage/postgres/event_types_table.go b/roomserver/storage/postgres/event_types_table.go
index 1d5de582..15ab7fd8 100644
--- a/roomserver/storage/postgres/event_types_table.go
+++ b/roomserver/storage/postgres/event_types_table.go
@@ -99,12 +99,12 @@ type eventTypeStatements struct {
 	bulkSelectEventTypeNIDStmt *sql.Stmt
 }
 
-func createEventTypesTable(db *sql.DB) error {
+func CreateEventTypesTable(db *sql.DB) error {
 	_, err := db.Exec(eventTypesSchema)
 	return err
 }
 
-func prepareEventTypesTable(db *sql.DB) (tables.EventTypes, error) {
+func PrepareEventTypesTable(db *sql.DB) (tables.EventTypes, error) {
 	s := &eventTypeStatements{}
 
 	return s, sqlutil.StatementList{
@@ -143,9 +143,9 @@ func (s *eventTypeStatements) BulkSelectEventTypeNID(
 	defer internal.CloseAndLogIfError(ctx, rows, "bulkSelectEventTypeNID: rows.close() failed")
 
 	result := make(map[string]types.EventTypeNID, len(eventTypes))
+	var eventType string
+	var eventTypeNID int64
 	for rows.Next() {
-		var eventType string
-		var eventTypeNID int64
 		if err := rows.Scan(&eventType, &eventTypeNID); err != nil {
 			return nil, err
 		}
diff --git a/roomserver/storage/postgres/events_table.go b/roomserver/storage/postgres/events_table.go
index 8012174a..86d226ce 100644
--- a/roomserver/storage/postgres/events_table.go
+++ b/roomserver/storage/postgres/events_table.go
@@ -155,12 +155,12 @@ type eventStatements struct {
 	selectRoomNIDsForEventNIDsStmt         *sql.Stmt
 }
 
-func createEventsTable(db *sql.DB) error {
+func CreateEventsTable(db *sql.DB) error {
 	_, err := db.Exec(eventsSchema)
 	return err
 }
 
-func prepareEventsTable(db *sql.DB) (tables.Events, error) {
+func PrepareEventsTable(db *sql.DB) (tables.Events, error) {
 	s := &eventStatements{}
 
 	return s, sqlutil.StatementList{
@@ -380,15 +380,15 @@ func (s *eventStatements) BulkSelectStateAtEventAndReference(
 	defer internal.CloseAndLogIfError(ctx, rows, "bulkSelectStateAtEventAndReference: rows.close() failed")
 	results := make([]types.StateAtEventAndReference, len(eventNIDs))
 	i := 0
+	var (
+		eventTypeNID     int64
+		eventStateKeyNID int64
+		eventNID         int64
+		stateSnapshotNID int64
+		eventID          string
+		eventSHA256      []byte
+	)
 	for ; rows.Next(); i++ {
-		var (
-			eventTypeNID     int64
-			eventStateKeyNID int64
-			eventNID         int64
-			stateSnapshotNID int64
-			eventID          string
-			eventSHA256      []byte
-		)
 		if err = rows.Scan(
 			&eventTypeNID, &eventStateKeyNID, &eventNID, &stateSnapshotNID, &eventID, &eventSHA256,
 		); err != nil {
@@ -446,9 +446,9 @@ func (s *eventStatements) BulkSelectEventID(ctx context.Context, txn *sql.Tx, ev
 	defer internal.CloseAndLogIfError(ctx, rows, "bulkSelectEventID: rows.close() failed")
 	results := make(map[types.EventNID]string, len(eventNIDs))
 	i := 0
+	var eventNID int64
+	var eventID string
 	for ; rows.Next(); i++ {
-		var eventNID int64
-		var eventID string
 		if err = rows.Scan(&eventNID, &eventID); err != nil {
 			return nil, err
 		}
@@ -491,9 +491,9 @@ func (s *eventStatements) bulkSelectEventNID(ctx context.Context, txn *sql.Tx, e
 	}
 	defer internal.CloseAndLogIfError(ctx, rows, "bulkSelectEventNID: rows.close() failed")
 	results := make(map[string]types.EventNID, len(eventIDs))
+	var eventID string
+	var eventNID int64
 	for rows.Next() {
-		var eventID string
-		var eventNID int64
 		if err = rows.Scan(&eventID, &eventNID); err != nil {
 			return nil, err
 		}
@@ -522,9 +522,9 @@ func (s *eventStatements) SelectRoomNIDsForEventNIDs(
 	}
 	defer internal.CloseAndLogIfError(ctx, rows, "selectRoomNIDsForEventNIDsStmt: rows.close() failed")
 	result := make(map[types.EventNID]types.RoomNID)
+	var eventNID types.EventNID
+	var roomNID types.RoomNID
 	for rows.Next() {
-		var eventNID types.EventNID
-		var roomNID types.RoomNID
 		if err = rows.Scan(&eventNID, &roomNID); err != nil {
 			return nil, err
 		}
diff --git a/roomserver/storage/postgres/storage.go b/roomserver/storage/postgres/storage.go
index da8d2584..34e89149 100644
--- a/roomserver/storage/postgres/storage.go
+++ b/roomserver/storage/postgres/storage.go
@@ -68,16 +68,16 @@ func Open(base *base.BaseDendrite, dbProperties *config.DatabaseOptions, cache c
 }
 
 func (d *Database) create(db *sql.DB) error {
-	if err := createEventStateKeysTable(db); err != nil {
+	if err := CreateEventStateKeysTable(db); err != nil {
 		return err
 	}
-	if err := createEventTypesTable(db); err != nil {
+	if err := CreateEventTypesTable(db); err != nil {
 		return err
 	}
-	if err := createEventJSONTable(db); err != nil {
+	if err := CreateEventJSONTable(db); err != nil {
 		return err
 	}
-	if err := createEventsTable(db); err != nil {
+	if err := CreateEventsTable(db); err != nil {
 		return err
 	}
 	if err := createRoomsTable(db); err != nil {
@@ -112,19 +112,19 @@ func (d *Database) create(db *sql.DB) error {
 }
 
 func (d *Database) prepare(db *sql.DB, writer sqlutil.Writer, cache caching.RoomServerCaches) error {
-	eventStateKeys, err := prepareEventStateKeysTable(db)
+	eventStateKeys, err := PrepareEventStateKeysTable(db)
 	if err != nil {
 		return err
 	}
-	eventTypes, err := prepareEventTypesTable(db)
+	eventTypes, err := PrepareEventTypesTable(db)
 	if err != nil {
 		return err
 	}
-	eventJSON, err := prepareEventJSONTable(db)
+	eventJSON, err := PrepareEventJSONTable(db)
 	if err != nil {
 		return err
 	}
-	events, err := prepareEventsTable(db)
+	events, err := PrepareEventsTable(db)
 	if err != nil {
 		return err
 	}
diff --git a/roomserver/storage/sqlite3/event_json_table.go b/roomserver/storage/sqlite3/event_json_table.go
index f470ea32..dc26885b 100644
--- a/roomserver/storage/sqlite3/event_json_table.go
+++ b/roomserver/storage/sqlite3/event_json_table.go
@@ -52,12 +52,12 @@ type eventJSONStatements struct {
 	bulkSelectEventJSONStmt *sql.Stmt
 }
 
-func createEventJSONTable(db *sql.DB) error {
+func CreateEventJSONTable(db *sql.DB) error {
 	_, err := db.Exec(eventJSONSchema)
 	return err
 }
 
-func prepareEventJSONTable(db *sql.DB) (tables.EventJSON, error) {
+func PrepareEventJSONTable(db *sql.DB) (tables.EventJSON, error) {
 	s := &eventJSONStatements{
 		db: db,
 	}
@@ -101,9 +101,9 @@ func (s *eventJSONStatements) BulkSelectEventJSON(
 	// We might get fewer results than NIDs so we adjust the length of the slice before returning it.
 	results := make([]tables.EventJSONPair, len(eventNIDs))
 	i := 0
+	var eventNID int64
 	for ; rows.Next(); i++ {
 		result := &results[i]
-		var eventNID int64
 		if err := rows.Scan(&eventNID, &result.EventJSON); err != nil {
 			return nil, err
 		}
diff --git a/roomserver/storage/sqlite3/event_state_keys_table.go b/roomserver/storage/sqlite3/event_state_keys_table.go
index f97541f4..347524a8 100644
--- a/roomserver/storage/sqlite3/event_state_keys_table.go
+++ b/roomserver/storage/sqlite3/event_state_keys_table.go
@@ -71,12 +71,12 @@ type eventStateKeyStatements struct {
 	bulkSelectEventStateKeyStmt    *sql.Stmt
 }
 
-func createEventStateKeysTable(db *sql.DB) error {
+func CreateEventStateKeysTable(db *sql.DB) error {
 	_, err := db.Exec(eventStateKeysSchema)
 	return err
 }
 
-func prepareEventStateKeysTable(db *sql.DB) (tables.EventStateKeys, error) {
+func PrepareEventStateKeysTable(db *sql.DB) (tables.EventStateKeys, error) {
 	s := &eventStateKeyStatements{
 		db: db,
 	}
@@ -128,9 +128,9 @@ func (s *eventStateKeyStatements) BulkSelectEventStateKeyNID(
 	}
 	defer internal.CloseAndLogIfError(ctx, rows, "bulkSelectEventStateKeyNID: rows.close() failed")
 	result := make(map[string]types.EventStateKeyNID, len(eventStateKeys))
+	var stateKey string
+	var stateKeyNID int64
 	for rows.Next() {
-		var stateKey string
-		var stateKeyNID int64
 		if err := rows.Scan(&stateKey, &stateKeyNID); err != nil {
 			return nil, err
 		}
@@ -159,9 +159,9 @@ func (s *eventStateKeyStatements) BulkSelectEventStateKey(
 	}
 	defer internal.CloseAndLogIfError(ctx, rows, "bulkSelectEventStateKey: rows.close() failed")
 	result := make(map[types.EventStateKeyNID]string, len(eventStateKeyNIDs))
+	var stateKey string
+	var stateKeyNID int64
 	for rows.Next() {
-		var stateKey string
-		var stateKeyNID int64
 		if err := rows.Scan(&stateKey, &stateKeyNID); err != nil {
 			return nil, err
 		}
diff --git a/roomserver/storage/sqlite3/event_types_table.go b/roomserver/storage/sqlite3/event_types_table.go
index c49cc509..0581ec19 100644
--- a/roomserver/storage/sqlite3/event_types_table.go
+++ b/roomserver/storage/sqlite3/event_types_table.go
@@ -79,12 +79,12 @@ type eventTypeStatements struct {
 	bulkSelectEventTypeNIDStmt *sql.Stmt
 }
 
-func createEventTypesTable(db *sql.DB) error {
+func CreateEventTypesTable(db *sql.DB) error {
 	_, err := db.Exec(eventTypesSchema)
 	return err
 }
 
-func prepareEventTypesTable(db *sql.DB) (tables.EventTypes, error) {
+func PrepareEventTypesTable(db *sql.DB) (tables.EventTypes, error) {
 	s := &eventTypeStatements{
 		db: db,
 	}
@@ -139,9 +139,9 @@ func (s *eventTypeStatements) BulkSelectEventTypeNID(
 	defer internal.CloseAndLogIfError(ctx, rows, "bulkSelectEventTypeNID: rows.close() failed")
 
 	result := make(map[string]types.EventTypeNID, len(eventTypes))
+	var eventType string
+	var eventTypeNID int64
 	for rows.Next() {
-		var eventType string
-		var eventTypeNID int64
 		if err := rows.Scan(&eventType, &eventTypeNID); err != nil {
 			return nil, err
 		}
diff --git a/roomserver/storage/sqlite3/events_table.go b/roomserver/storage/sqlite3/events_table.go
index 45b49e5c..feb06150 100644
--- a/roomserver/storage/sqlite3/events_table.go
+++ b/roomserver/storage/sqlite3/events_table.go
@@ -68,7 +68,8 @@ const bulkSelectStateEventByIDSQL = "" +
 const bulkSelectStateEventByNIDSQL = "" +
 	"SELECT event_type_nid, event_state_key_nid, event_nid FROM roomserver_events" +
 	" WHERE event_nid IN ($1)"
-	// Rest of query is built by BulkSelectStateEventByNID
+
+// Rest of query is built by BulkSelectStateEventByNID
 
 const bulkSelectStateAtEventByIDSQL = "" +
 	"SELECT event_type_nid, event_state_key_nid, event_nid, state_snapshot_nid, is_rejected FROM roomserver_events" +
@@ -126,12 +127,12 @@ type eventStatements struct {
 	//selectRoomNIDsForEventNIDsStmt       *sql.Stmt
 }
 
-func createEventsTable(db *sql.DB) error {
+func CreateEventsTable(db *sql.DB) error {
 	_, err := db.Exec(eventsSchema)
 	return err
 }
 
-func prepareEventsTable(db *sql.DB) (tables.Events, error) {
+func PrepareEventsTable(db *sql.DB) (tables.Events, error) {
 	s := &eventStatements{
 		db: db,
 	}
@@ -404,15 +405,15 @@ func (s *eventStatements) BulkSelectStateAtEventAndReference(
 	defer internal.CloseAndLogIfError(ctx, rows, "bulkSelectStateAtEventAndReference: rows.close() failed")
 	results := make([]types.StateAtEventAndReference, len(eventNIDs))
 	i := 0
+	var (
+		eventTypeNID     int64
+		eventStateKeyNID int64
+		eventNID         int64
+		stateSnapshotNID int64
+		eventID          string
+		eventSHA256      []byte
+	)
 	for ; rows.Next(); i++ {
-		var (
-			eventTypeNID     int64
-			eventStateKeyNID int64
-			eventNID         int64
-			stateSnapshotNID int64
-			eventID          string
-			eventSHA256      []byte
-		)
 		if err = rows.Scan(
 			&eventTypeNID, &eventStateKeyNID, &eventNID, &stateSnapshotNID, &eventID, &eventSHA256,
 		); err != nil {
@@ -491,9 +492,9 @@ func (s *eventStatements) BulkSelectEventID(ctx context.Context, txn *sql.Tx, ev
 	defer internal.CloseAndLogIfError(ctx, rows, "bulkSelectEventID: rows.close() failed")
 	results := make(map[types.EventNID]string, len(eventNIDs))
 	i := 0
+	var eventNID int64
+	var eventID string
 	for ; rows.Next(); i++ {
-		var eventNID int64
-		var eventID string
 		if err = rows.Scan(&eventNID, &eventID); err != nil {
 			return nil, err
 		}
@@ -545,9 +546,9 @@ func (s *eventStatements) bulkSelectEventNID(ctx context.Context, txn *sql.Tx, e
 	}
 	defer internal.CloseAndLogIfError(ctx, rows, "bulkSelectEventNID: rows.close() failed")
 	results := make(map[string]types.EventNID, len(eventIDs))
+	var eventID string
+	var eventNID int64
 	for rows.Next() {
-		var eventID string
-		var eventNID int64
 		if err = rows.Scan(&eventID, &eventNID); err != nil {
 			return nil, err
 		}
@@ -595,9 +596,9 @@ func (s *eventStatements) SelectRoomNIDsForEventNIDs(
 	}
 	defer internal.CloseAndLogIfError(ctx, rows, "selectRoomNIDsForEventNIDsStmt: rows.close() failed")
 	result := make(map[types.EventNID]types.RoomNID)
+	var eventNID types.EventNID
+	var roomNID types.RoomNID
 	for rows.Next() {
-		var eventNID types.EventNID
-		var roomNID types.RoomNID
 		if err = rows.Scan(&eventNID, &roomNID); err != nil {
 			return nil, err
 		}
diff --git a/roomserver/storage/sqlite3/storage.go b/roomserver/storage/sqlite3/storage.go
index e6cf1a53..9522d305 100644
--- a/roomserver/storage/sqlite3/storage.go
+++ b/roomserver/storage/sqlite3/storage.go
@@ -77,16 +77,16 @@ func Open(base *base.BaseDendrite, dbProperties *config.DatabaseOptions, cache c
 }
 
 func (d *Database) create(db *sql.DB) error {
-	if err := createEventStateKeysTable(db); err != nil {
+	if err := CreateEventStateKeysTable(db); err != nil {
 		return err
 	}
-	if err := createEventTypesTable(db); err != nil {
+	if err := CreateEventTypesTable(db); err != nil {
 		return err
 	}
-	if err := createEventJSONTable(db); err != nil {
+	if err := CreateEventJSONTable(db); err != nil {
 		return err
 	}
-	if err := createEventsTable(db); err != nil {
+	if err := CreateEventsTable(db); err != nil {
 		return err
 	}
 	if err := createRoomsTable(db); err != nil {
@@ -121,19 +121,19 @@ func (d *Database) create(db *sql.DB) error {
 }
 
 func (d *Database) prepare(db *sql.DB, writer sqlutil.Writer, cache caching.RoomServerCaches) error {
-	eventStateKeys, err := prepareEventStateKeysTable(db)
+	eventStateKeys, err := PrepareEventStateKeysTable(db)
 	if err != nil {
 		return err
 	}
-	eventTypes, err := prepareEventTypesTable(db)
+	eventTypes, err := PrepareEventTypesTable(db)
 	if err != nil {
 		return err
 	}
-	eventJSON, err := prepareEventJSONTable(db)
+	eventJSON, err := PrepareEventJSONTable(db)
 	if err != nil {
 		return err
 	}
-	events, err := prepareEventsTable(db)
+	events, err := PrepareEventsTable(db)
 	if err != nil {
 		return err
 	}
diff --git a/roomserver/storage/tables/event_json_table_test.go b/roomserver/storage/tables/event_json_table_test.go
new file mode 100644
index 00000000..b490d0fe
--- /dev/null
+++ b/roomserver/storage/tables/event_json_table_test.go
@@ -0,0 +1,95 @@
+package tables_test
+
+import (
+	"context"
+	"fmt"
+	"testing"
+
+	"github.com/matrix-org/dendrite/internal/sqlutil"
+	"github.com/matrix-org/dendrite/roomserver/storage/postgres"
+	"github.com/matrix-org/dendrite/roomserver/storage/sqlite3"
+	"github.com/matrix-org/dendrite/roomserver/storage/tables"
+	"github.com/matrix-org/dendrite/roomserver/types"
+	"github.com/matrix-org/dendrite/setup/config"
+	"github.com/matrix-org/dendrite/test"
+	"github.com/stretchr/testify/assert"
+)
+
+func mustCreateEventJSONTable(t *testing.T, dbType test.DBType) (tables.EventJSON, func()) {
+	t.Helper()
+	connStr, close := test.PrepareDBConnectionString(t, dbType)
+	db, err := sqlutil.Open(&config.DatabaseOptions{
+		ConnectionString: config.DataSource(connStr),
+	}, sqlutil.NewExclusiveWriter())
+	assert.NoError(t, err)
+	var tab tables.EventJSON
+	switch dbType {
+	case test.DBTypePostgres:
+		err = postgres.CreateEventJSONTable(db)
+		assert.NoError(t, err)
+		tab, err = postgres.PrepareEventJSONTable(db)
+	case test.DBTypeSQLite:
+		err = sqlite3.CreateEventJSONTable(db)
+		assert.NoError(t, err)
+		tab, err = sqlite3.PrepareEventJSONTable(db)
+	}
+	assert.NoError(t, err)
+
+	return tab, close
+}
+
+func Test_EventJSONTable(t *testing.T) {
+	test.WithAllDatabases(t, func(t *testing.T, dbType test.DBType) {
+		tab, close := mustCreateEventJSONTable(t, dbType)
+		defer close()
+
+		// create some dummy data
+		for i := 0; i < 10; i++ {
+			err := tab.InsertEventJSON(
+				context.Background(), nil, types.EventNID(i),
+				[]byte(fmt.Sprintf(`{"value":%d"}`, i)),
+			)
+			assert.NoError(t, err)
+		}
+
+		tests := []struct {
+			name      string
+			args      []types.EventNID
+			wantCount int
+		}{
+			{
+				name:      "select subset of existing NIDs",
+				args:      []types.EventNID{1, 2, 3, 4, 5},
+				wantCount: 5,
+			},
+			{
+				name:      "select subset of existing/non-existing NIDs",
+				args:      []types.EventNID{1, 2, 12, 50},
+				wantCount: 2,
+			},
+			{
+				name:      "select single existing NID",
+				args:      []types.EventNID{1},
+				wantCount: 1,
+			},
+			{
+				name:      "select single non-existing NID",
+				args:      []types.EventNID{13},
+				wantCount: 0,
+			},
+		}
+
+		for _, tc := range tests {
+			t.Run(tc.name, func(t *testing.T) {
+				// select a subset of the data
+				values, err := tab.BulkSelectEventJSON(context.Background(), nil, tc.args)
+				assert.NoError(t, err)
+				assert.Equal(t, tc.wantCount, len(values))
+				for i, v := range values {
+					assert.Equal(t, v.EventNID, types.EventNID(i+1))
+					assert.Equal(t, []byte(fmt.Sprintf(`{"value":%d"}`, i+1)), v.EventJSON)
+				}
+			})
+		}
+	})
+}
diff --git a/roomserver/storage/tables/event_state_keys_table_test.go b/roomserver/storage/tables/event_state_keys_table_test.go
new file mode 100644
index 00000000..a856fe55
--- /dev/null
+++ b/roomserver/storage/tables/event_state_keys_table_test.go
@@ -0,0 +1,79 @@
+package tables_test
+
+import (
+	"context"
+	"fmt"
+	"testing"
+
+	"github.com/matrix-org/dendrite/internal/sqlutil"
+	"github.com/matrix-org/dendrite/roomserver/storage/postgres"
+	"github.com/matrix-org/dendrite/roomserver/storage/sqlite3"
+	"github.com/matrix-org/dendrite/roomserver/storage/tables"
+	"github.com/matrix-org/dendrite/roomserver/types"
+	"github.com/matrix-org/dendrite/setup/config"
+	"github.com/matrix-org/dendrite/test"
+	"github.com/stretchr/testify/assert"
+)
+
+func mustCreateEventStateKeysTable(t *testing.T, dbType test.DBType) (tables.EventStateKeys, func()) {
+	t.Helper()
+	connStr, close := test.PrepareDBConnectionString(t, dbType)
+	db, err := sqlutil.Open(&config.DatabaseOptions{
+		ConnectionString: config.DataSource(connStr),
+	}, sqlutil.NewExclusiveWriter())
+	assert.NoError(t, err)
+	var tab tables.EventStateKeys
+	switch dbType {
+	case test.DBTypePostgres:
+		err = postgres.CreateEventStateKeysTable(db)
+		assert.NoError(t, err)
+		tab, err = postgres.PrepareEventStateKeysTable(db)
+	case test.DBTypeSQLite:
+		err = sqlite3.CreateEventStateKeysTable(db)
+		assert.NoError(t, err)
+		tab, err = sqlite3.PrepareEventStateKeysTable(db)
+	}
+	assert.NoError(t, err)
+
+	return tab, close
+}
+
+func Test_EventStateKeysTable(t *testing.T) {
+	test.WithAllDatabases(t, func(t *testing.T, dbType test.DBType) {
+		tab, close := mustCreateEventStateKeysTable(t, dbType)
+		defer close()
+		ctx := context.Background()
+		var stateKeyNID, gotEventStateKey types.EventStateKeyNID
+		var err error
+		// create some dummy data
+		for i := 0; i < 10; i++ {
+			stateKey := fmt.Sprintf("@user%d:localhost", i)
+			stateKeyNID, err = tab.InsertEventStateKeyNID(ctx, nil, stateKey)
+			assert.NoError(t, err)
+			gotEventStateKey, err = tab.SelectEventStateKeyNID(ctx, nil, stateKey)
+			assert.NoError(t, err)
+			assert.Equal(t, stateKeyNID, gotEventStateKey)
+		}
+		// This should fail, since @user0:localhost already exists
+		stateKey := fmt.Sprintf("@user%d:localhost", 0)
+		_, err = tab.InsertEventStateKeyNID(ctx, nil, stateKey)
+		assert.Error(t, err)
+
+		stateKeyNIDsMap, err := tab.BulkSelectEventStateKeyNID(ctx, nil, []string{"@user0:localhost", "@user1:localhost"})
+		assert.NoError(t, err)
+		wantStateKeyNIDs := make([]types.EventStateKeyNID, 0, len(stateKeyNIDsMap))
+		for _, nid := range stateKeyNIDsMap {
+			wantStateKeyNIDs = append(wantStateKeyNIDs, nid)
+		}
+		stateKeyNIDs, err := tab.BulkSelectEventStateKey(ctx, nil, wantStateKeyNIDs)
+		assert.NoError(t, err)
+		// verify that BulkSelectEventStateKeyNID and BulkSelectEventStateKey return the same values
+		for userID, nid := range stateKeyNIDsMap {
+			if v, ok := stateKeyNIDs[nid]; ok {
+				assert.Equal(t, v, userID)
+			} else {
+				t.Fatalf("unable to find %d in result set", nid)
+			}
+		}
+	})
+}
diff --git a/roomserver/storage/tables/event_types_table_test.go b/roomserver/storage/tables/event_types_table_test.go
new file mode 100644
index 00000000..92c57a91
--- /dev/null
+++ b/roomserver/storage/tables/event_types_table_test.go
@@ -0,0 +1,79 @@
+package tables_test
+
+import (
+	"context"
+	"fmt"
+	"testing"
+
+	"github.com/matrix-org/dendrite/internal/sqlutil"
+	"github.com/matrix-org/dendrite/roomserver/storage/postgres"
+	"github.com/matrix-org/dendrite/roomserver/storage/sqlite3"
+	"github.com/matrix-org/dendrite/roomserver/storage/tables"
+	"github.com/matrix-org/dendrite/roomserver/types"
+	"github.com/matrix-org/dendrite/setup/config"
+	"github.com/matrix-org/dendrite/test"
+	"github.com/stretchr/testify/assert"
+)
+
+func mustCreateEventTypesTable(t *testing.T, dbType test.DBType) (tables.EventTypes, func()) {
+	t.Helper()
+	connStr, close := test.PrepareDBConnectionString(t, dbType)
+	db, err := sqlutil.Open(&config.DatabaseOptions{
+		ConnectionString: config.DataSource(connStr),
+	}, sqlutil.NewExclusiveWriter())
+	assert.NoError(t, err)
+	var tab tables.EventTypes
+	switch dbType {
+	case test.DBTypePostgres:
+		err = postgres.CreateEventTypesTable(db)
+		assert.NoError(t, err)
+		tab, err = postgres.PrepareEventTypesTable(db)
+	case test.DBTypeSQLite:
+		err = sqlite3.CreateEventTypesTable(db)
+		assert.NoError(t, err)
+		tab, err = sqlite3.PrepareEventTypesTable(db)
+	}
+	assert.NoError(t, err)
+
+	return tab, close
+}
+
+func Test_EventTypesTable(t *testing.T) {
+	test.WithAllDatabases(t, func(t *testing.T, dbType test.DBType) {
+		tab, close := mustCreateEventTypesTable(t, dbType)
+		defer close()
+		ctx := context.Background()
+		var eventTypeNID, gotEventTypeNID types.EventTypeNID
+		var err error
+		// create some dummy data
+		eventTypeMap := make(map[string]types.EventTypeNID)
+		for i := 0; i < 10; i++ {
+			eventType := fmt.Sprintf("dummyEventType%d", i)
+			eventTypeNID, err = tab.InsertEventTypeNID(ctx, nil, eventType)
+			assert.NoError(t, err)
+			eventTypeMap[eventType] = eventTypeNID
+			gotEventTypeNID, err = tab.SelectEventTypeNID(ctx, nil, eventType)
+			assert.NoError(t, err)
+			assert.Equal(t, eventTypeNID, gotEventTypeNID)
+		}
+		// This should fail, since the dummyEventType0 already exists
+		eventType := fmt.Sprintf("dummyEventType%d", 0)
+		_, err = tab.InsertEventTypeNID(ctx, nil, eventType)
+		assert.Error(t, err)
+
+		// This should return an error, as this eventType does not exist
+		_, err = tab.SelectEventTypeNID(ctx, nil, "dummyEventType13")
+		assert.Error(t, err)
+
+		eventTypeNIDs, err := tab.BulkSelectEventTypeNID(ctx, nil, []string{"dummyEventType0", "dummyEventType3"})
+		assert.NoError(t, err)
+		// verify that BulkSelectEventTypeNID and InsertEventTypeNID return the same values
+		for eventType, nid := range eventTypeNIDs {
+			if v, ok := eventTypeMap[eventType]; ok {
+				assert.Equal(t, v, nid)
+			} else {
+				t.Fatalf("unable to find %d in result set", nid)
+			}
+		}
+	})
+}
diff --git a/roomserver/storage/tables/events_table_test.go b/roomserver/storage/tables/events_table_test.go
new file mode 100644
index 00000000..d5d699c4
--- /dev/null
+++ b/roomserver/storage/tables/events_table_test.go
@@ -0,0 +1,157 @@
+package tables_test
+
+import (
+	"context"
+	"testing"
+
+	"github.com/matrix-org/dendrite/internal/sqlutil"
+	"github.com/matrix-org/dendrite/roomserver/storage/postgres"
+	"github.com/matrix-org/dendrite/roomserver/storage/sqlite3"
+	"github.com/matrix-org/dendrite/roomserver/storage/tables"
+	"github.com/matrix-org/dendrite/roomserver/types"
+	"github.com/matrix-org/dendrite/setup/config"
+	"github.com/matrix-org/dendrite/test"
+	"github.com/matrix-org/gomatrixserverlib"
+	"github.com/stretchr/testify/assert"
+)
+
+func mustCreateEventsTable(t *testing.T, dbType test.DBType) (tables.Events, func()) {
+	t.Helper()
+	connStr, close := test.PrepareDBConnectionString(t, dbType)
+	db, err := sqlutil.Open(&config.DatabaseOptions{
+		ConnectionString: config.DataSource(connStr),
+	}, sqlutil.NewExclusiveWriter())
+	assert.NoError(t, err)
+	var tab tables.Events
+	switch dbType {
+	case test.DBTypePostgres:
+		err = postgres.CreateEventsTable(db)
+		assert.NoError(t, err)
+		tab, err = postgres.PrepareEventsTable(db)
+	case test.DBTypeSQLite:
+		err = sqlite3.CreateEventsTable(db)
+		assert.NoError(t, err)
+		tab, err = sqlite3.PrepareEventsTable(db)
+	}
+	assert.NoError(t, err)
+
+	return tab, close
+}
+
+func Test_EventsTable(t *testing.T) {
+	alice := test.NewUser()
+	room := test.NewRoom(t, alice)
+	ctx := context.Background()
+	test.WithAllDatabases(t, func(t *testing.T, dbType test.DBType) {
+		tab, close := mustCreateEventsTable(t, dbType)
+		defer close()
+		// create some dummy data
+		eventIDs := make([]string, 0, len(room.Events()))
+		wantStateAtEvent := make([]types.StateAtEvent, 0, len(room.Events()))
+		wantEventReferences := make([]gomatrixserverlib.EventReference, 0, len(room.Events()))
+		wantStateAtEventAndRefs := make([]types.StateAtEventAndReference, 0, len(room.Events()))
+		for _, ev := range room.Events() {
+			eventNID, snapNID, err := tab.InsertEvent(ctx, nil, 1, 1, 1, ev.EventID(), ev.EventReference().EventSHA256, nil, ev.Depth(), false)
+			assert.NoError(t, err)
+			gotEventNID, gotSnapNID, err := tab.SelectEvent(ctx, nil, ev.EventID())
+			assert.NoError(t, err)
+			assert.Equal(t, eventNID, gotEventNID)
+			assert.Equal(t, snapNID, gotSnapNID)
+			eventID, err := tab.SelectEventID(ctx, nil, eventNID)
+			assert.NoError(t, err)
+			assert.Equal(t, eventID, ev.EventID())
+
+			// The events shouldn't be sent to output yet
+			sentToOutput, err := tab.SelectEventSentToOutput(ctx, nil, gotEventNID)
+			assert.NoError(t, err)
+			assert.False(t, sentToOutput)
+
+			err = tab.UpdateEventSentToOutput(ctx, nil, gotEventNID)
+			assert.NoError(t, err)
+
+			// Now they should be sent to output
+			sentToOutput, err = tab.SelectEventSentToOutput(ctx, nil, gotEventNID)
+			assert.NoError(t, err)
+			assert.True(t, sentToOutput)
+
+			eventIDs = append(eventIDs, ev.EventID())
+			wantEventReferences = append(wantEventReferences, ev.EventReference())
+
+			// Set the stateSnapshot to 2 for some events to verify they are returned later
+			stateSnapshot := 0
+			if eventNID < 3 {
+				stateSnapshot = 2
+				err = tab.UpdateEventState(ctx, nil, eventNID, 2)
+				assert.NoError(t, err)
+			}
+			stateAtEvent := types.StateAtEvent{
+				Overwrite:              false,
+				BeforeStateSnapshotNID: types.StateSnapshotNID(stateSnapshot),
+				IsRejected:             false,
+				StateEntry: types.StateEntry{
+					EventNID: eventNID,
+					StateKeyTuple: types.StateKeyTuple{
+						EventTypeNID:     1,
+						EventStateKeyNID: 1,
+					},
+				},
+			}
+			wantStateAtEvent = append(wantStateAtEvent, stateAtEvent)
+			wantStateAtEventAndRefs = append(wantStateAtEventAndRefs, types.StateAtEventAndReference{
+				StateAtEvent:   stateAtEvent,
+				EventReference: ev.EventReference(),
+			})
+		}
+
+		stateEvents, err := tab.BulkSelectStateEventByID(ctx, nil, eventIDs)
+		assert.NoError(t, err)
+		assert.Equal(t, len(stateEvents), len(eventIDs))
+		nids := make([]types.EventNID, 0, len(stateEvents))
+		for _, ev := range stateEvents {
+			nids = append(nids, ev.EventNID)
+		}
+		stateEvents2, err := tab.BulkSelectStateEventByNID(ctx, nil, nids, nil)
+		assert.NoError(t, err)
+		// somehow SQLite doesn't return the values ordered as requested by the query
+		assert.ElementsMatch(t, stateEvents, stateEvents2)
+
+		roomNIDs, err := tab.SelectRoomNIDsForEventNIDs(ctx, nil, nids)
+		assert.NoError(t, err)
+		// We only inserted one room, so the RoomNID should be the same for all evendNIDs
+		for _, roomNID := range roomNIDs {
+			assert.Equal(t, types.RoomNID(1), roomNID)
+		}
+
+		stateAtEvent, err := tab.BulkSelectStateAtEventByID(ctx, nil, eventIDs)
+		assert.NoError(t, err)
+		assert.Equal(t, len(eventIDs), len(stateAtEvent))
+
+		assert.ElementsMatch(t, wantStateAtEvent, stateAtEvent)
+
+		evendNIDMap, err := tab.BulkSelectEventID(ctx, nil, nids)
+		assert.NoError(t, err)
+		t.Logf("%+v", evendNIDMap)
+		assert.Equal(t, len(evendNIDMap), len(nids))
+
+		nidMap, err := tab.BulkSelectEventNID(ctx, nil, eventIDs)
+		assert.NoError(t, err)
+		// check that we got all expected eventNIDs
+		for _, eventID := range eventIDs {
+			_, ok := nidMap[eventID]
+			assert.True(t, ok)
+		}
+
+		references, err := tab.BulkSelectEventReference(ctx, nil, nids)
+		assert.NoError(t, err)
+		assert.Equal(t, wantEventReferences, references)
+
+		stateAndRefs, err := tab.BulkSelectStateAtEventAndReference(ctx, nil, nids)
+		assert.NoError(t, err)
+		assert.Equal(t, wantStateAtEventAndRefs, stateAndRefs)
+
+		// check we get the expected event depth
+		maxDepth, err := tab.SelectMaxEventDepth(ctx, nil, nids)
+		assert.NoError(t, err)
+		assert.Equal(t, int64(len(room.Events())+1), maxDepth)
+	})
+}
diff --git a/roomserver/storage/tables/interface.go b/roomserver/storage/tables/interface.go
index 97e4afcf..95609787 100644
--- a/roomserver/storage/tables/interface.go
+++ b/roomserver/storage/tables/interface.go
@@ -10,9 +10,8 @@ import (
 )
 
 type EventJSONPair struct {
-	EventNID    types.EventNID
-	RoomVersion gomatrixserverlib.RoomVersion
-	EventJSON   []byte
+	EventNID  types.EventNID
+	EventJSON []byte
 }
 
 type EventJSON interface {
@@ -36,7 +35,8 @@ type EventStateKeys interface {
 
 type Events interface {
 	InsertEvent(
-		ctx context.Context, txn *sql.Tx, i types.RoomNID, j types.EventTypeNID, k types.EventStateKeyNID, eventID string,
+		ctx context.Context, txn *sql.Tx, roomNID types.RoomNID, eventTypeNID types.EventTypeNID,
+		eventStateKeyNID types.EventStateKeyNID, eventID string,
 		referenceSHA256 []byte, authEventNIDs []types.EventNID, depth int64, isRejected bool,
 	) (types.EventNID, types.StateSnapshotNID, error)
 	SelectEvent(ctx context.Context, txn *sql.Tx, eventID string) (types.EventNID, types.StateSnapshotNID, error)