292 lines
12 KiB
TypeScript
292 lines
12 KiB
TypeScript
|
import { SQL, randomUUIDv7 } from "bun";
|
||
|
|
import { beforeEach, expect, test } from "bun:test";
|
||
|
|
import { describeWithContainer } from "harness";
|
||
|
|
|
||
|
|
describeWithContainer(
|
||
|
|
"mysql",
|
||
|
|
{
|
||
|
|
image: "mysql_plain",
|
||
|
|
env: {},
|
||
|
|
concurrent: true,
|
||
|
|
args: [],
|
||
|
|
},
|
||
|
|
container => {
|
||
|
|
// Use a getter to avoid reading port/host at define time
|
||
|
|
const getOptions = () => ({
|
||
|
|
url: `mysql://root@${container.host}:${container.port}/bun_sql_test`,
|
||
|
|
max: 1,
|
||
|
|
bigint: true,
|
||
|
|
});
|
||
|
|
|
||
|
|
beforeEach(async () => {
|
||
|
|
await container.ready;
|
||
|
|
});
|
||
|
|
|
||
|
|
test("insert helper", async () => {
|
||
|
|
await using sql = new SQL({ ...getOptions(), max: 1 });
|
||
|
|
const random_name = "test_" + randomUUIDv7("hex").replaceAll("-", "");
|
||
|
|
await sql`CREATE TEMPORARY TABLE ${sql(random_name)} (id int, name text, age int)`;
|
||
|
|
await sql`INSERT INTO ${sql(random_name)} ${sql({ id: 1, name: "John", age: 30 })}`;
|
||
|
|
const result = await sql`SELECT * FROM ${sql(random_name)}`;
|
||
|
|
expect(result[0].id).toBe(1);
|
||
|
|
expect(result[0].name).toBe("John");
|
||
|
|
expect(result[0].age).toBe(30);
|
||
|
|
});
|
||
|
|
|
||
|
|
test("insert into with select helper with IN", async () => {
|
||
|
|
await using sql = new SQL({ ...getOptions(), max: 1 });
|
||
|
|
const random_name = "test_" + randomUUIDv7("hex").replaceAll("-", "");
|
||
|
|
await sql`CREATE TEMPORARY TABLE ${sql(random_name)} (id int, name text, age int)`;
|
||
|
|
{
|
||
|
|
await sql`INSERT INTO ${sql(random_name)} ${sql({ id: 1, name: "John", age: 30 })}`;
|
||
|
|
const result = await sql`SELECT * FROM ${sql(random_name)}`;
|
||
|
|
expect(result[0].id).toBe(1);
|
||
|
|
expect(result[0].name).toBe("John");
|
||
|
|
expect(result[0].age).toBe(30);
|
||
|
|
}
|
||
|
|
await sql`CREATE TEMPORARY TABLE ${sql(random_name + "2")} (id int, name text, age int)`;
|
||
|
|
{
|
||
|
|
await sql`INSERT INTO ${sql(random_name + "2")} (id, name, age) SELECT id, name, age FROM ${sql(random_name)} WHERE id IN ${sql([1, 2])}`;
|
||
|
|
const result = await sql`SELECT * FROM ${sql(random_name + "2")}`;
|
||
|
|
expect(result[0].id).toBe(1);
|
||
|
|
expect(result[0].name).toBe("John");
|
||
|
|
expect(result[0].age).toBe(30);
|
||
|
|
}
|
||
|
|
});
|
||
|
|
|
||
|
|
test("select helper with IN using fragment", async () => {
|
||
|
|
await using sql = new SQL({ ...getOptions(), max: 1 });
|
||
|
|
const random_name = "test_" + randomUUIDv7("hex").replaceAll("-", "");
|
||
|
|
await sql`CREATE TEMPORARY TABLE ${sql(random_name)} (id int, name text, age int)`;
|
||
|
|
await sql`INSERT INTO ${sql(random_name)} ${sql({ id: 1, name: "John", age: 30 })}`;
|
||
|
|
const fragment = sql`id IN ${sql([1, 2])}`;
|
||
|
|
const result = await sql`SELECT * FROM ${sql(random_name)} WHERE ${fragment}`;
|
||
|
|
expect(result[0].id).toBe(1);
|
||
|
|
expect(result[0].name).toBe("John");
|
||
|
|
expect(result[0].age).toBe(30);
|
||
|
|
});
|
||
|
|
|
||
|
|
test("update helper", async () => {
|
||
|
|
await using sql = new SQL({ ...getOptions(), max: 1 });
|
||
|
|
const random_name = "test_" + randomUUIDv7("hex").replaceAll("-", "");
|
||
|
|
await sql`CREATE TEMPORARY TABLE ${sql(random_name)} (id int, name text, age int)`;
|
||
|
|
await sql`INSERT INTO ${sql(random_name)} ${sql({ id: 1, name: "John", age: 30 })}`;
|
||
|
|
await sql`UPDATE ${sql(random_name)} SET ${sql({ name: "Mary", age: 18 })} WHERE id = 1`;
|
||
|
|
const result = await sql`SELECT * FROM ${sql(random_name)}`;
|
||
|
|
expect(result[0].id).toBe(1);
|
||
|
|
expect(result[0].name).toBe("Mary");
|
||
|
|
expect(result[0].age).toBe(18);
|
||
|
|
});
|
||
|
|
|
||
|
|
test("update helper with IN", async () => {
|
||
|
|
await using sql = new SQL({ ...getOptions(), max: 1 });
|
||
|
|
const random_name = "test_" + randomUUIDv7("hex").replaceAll("-", "");
|
||
|
|
await sql`CREATE TEMPORARY TABLE ${sql(random_name)} (id int, name text, age int)`;
|
||
|
|
const users = [
|
||
|
|
{ id: 1, name: "John", age: 30 },
|
||
|
|
{ id: 2, name: "Jane", age: 25 },
|
||
|
|
];
|
||
|
|
await sql`INSERT INTO ${sql(random_name)} ${sql(users)}`;
|
||
|
|
|
||
|
|
await sql`UPDATE ${sql(random_name)} SET ${sql({ name: "Mary", age: 18 })} WHERE id IN ${sql([1, 2])}`;
|
||
|
|
const result = await sql`SELECT * FROM ${sql(random_name)}`;
|
||
|
|
expect(result[0].id).toBe(1);
|
||
|
|
expect(result[0].name).toBe("Mary");
|
||
|
|
expect(result[0].age).toBe(18);
|
||
|
|
expect(result[1].id).toBe(2);
|
||
|
|
expect(result[1].name).toBe("Mary");
|
||
|
|
expect(result[1].age).toBe(18);
|
||
|
|
});
|
||
|
|
|
||
|
|
test("update helper with AND IN", async () => {
|
||
|
|
await using sql = new SQL({ ...getOptions(), max: 1 });
|
||
|
|
const random_name = "test_" + randomUUIDv7("hex").replaceAll("-", "");
|
||
|
|
await sql`CREATE TEMPORARY TABLE ${sql(random_name)} (id int, name text, age int)`;
|
||
|
|
const users = [
|
||
|
|
{ id: 1, name: "John", age: 30 },
|
||
|
|
{ id: 2, name: "Jane", age: 25 },
|
||
|
|
];
|
||
|
|
await sql`INSERT INTO ${sql(random_name)} ${sql(users)}`;
|
||
|
|
|
||
|
|
await sql`UPDATE ${sql(random_name)} SET ${sql({ name: "Mary", age: 18 })} WHERE 1=1 AND id IN ${sql([1, 2])}`;
|
||
|
|
const result = await sql`SELECT * FROM ${sql(random_name)}`;
|
||
|
|
expect(result[0].id).toBe(1);
|
||
|
|
expect(result[0].name).toBe("Mary");
|
||
|
|
expect(result[0].age).toBe(18);
|
||
|
|
expect(result[1].id).toBe(2);
|
||
|
|
expect(result[1].name).toBe("Mary");
|
||
|
|
expect(result[1].age).toBe(18);
|
||
|
|
});
|
||
|
|
|
||
|
|
test("update helper with undefined values", async () => {
|
||
|
|
await using sql = new SQL({ ...getOptions(), max: 1 });
|
||
|
|
const random_name = "test_" + randomUUIDv7("hex").replaceAll("-", "");
|
||
|
|
await sql`CREATE TEMPORARY TABLE ${sql(random_name)} (id int, name text, age int)`;
|
||
|
|
const users = [
|
||
|
|
{ id: 1, name: "John", age: 30 },
|
||
|
|
{ id: 2, name: "Jane", age: 25 },
|
||
|
|
];
|
||
|
|
await sql`INSERT INTO ${sql(random_name)} ${sql(users)}`;
|
||
|
|
|
||
|
|
await sql`UPDATE ${sql(random_name)} SET ${sql({ name: "Mary", age: undefined })} WHERE id IN ${sql([1, 2])}`;
|
||
|
|
const result = await sql`SELECT * FROM ${sql(random_name)}`;
|
||
|
|
expect(result[0].id).toBe(1);
|
||
|
|
expect(result[0].name).toBe("Mary");
|
||
|
|
expect(result[0].age).toBe(30);
|
||
|
|
expect(result[1].id).toBe(2);
|
||
|
|
expect(result[1].name).toBe("Mary");
|
||
|
|
expect(result[1].age).toBe(25);
|
||
|
|
});
|
||
|
|
test("update helper that starts with undefined values", async () => {
|
||
|
|
await using sql = new SQL({ ...getOptions(), max: 1 });
|
||
|
|
const random_name = "test_" + randomUUIDv7("hex").replaceAll("-", "");
|
||
|
|
await sql`CREATE TEMPORARY TABLE ${sql(random_name)} (id int, name text, age int)`;
|
||
|
|
const users = [
|
||
|
|
{ id: 1, name: "John", age: 30 },
|
||
|
|
{ id: 2, name: "Jane", age: 25 },
|
||
|
|
];
|
||
|
|
await sql`INSERT INTO ${sql(random_name)} ${sql(users)}`;
|
||
|
|
|
||
|
|
await sql`UPDATE ${sql(random_name)} SET ${sql({ name: undefined, age: 19 })} WHERE id IN ${sql([1, 2])}`;
|
||
|
|
const result = await sql`SELECT * FROM ${sql(random_name)}`;
|
||
|
|
expect(result[0].id).toBe(1);
|
||
|
|
expect(result[0].name).toBe("John");
|
||
|
|
expect(result[0].age).toBe(19);
|
||
|
|
expect(result[1].id).toBe(2);
|
||
|
|
expect(result[1].name).toBe("Jane");
|
||
|
|
expect(result[1].age).toBe(19);
|
||
|
|
});
|
||
|
|
|
||
|
|
test("update helper with undefined values and no columns", async () => {
|
||
|
|
await using sql = new SQL({ ...getOptions(), max: 1 });
|
||
|
|
const random_name = "test_" + randomUUIDv7("hex").replaceAll("-", "");
|
||
|
|
await sql`CREATE TEMPORARY TABLE ${sql(random_name)} (id int, name text, age int)`;
|
||
|
|
const users = [
|
||
|
|
{ id: 1, name: "John", age: 30 },
|
||
|
|
{ id: 2, name: "Jane", age: 25 },
|
||
|
|
];
|
||
|
|
await sql`INSERT INTO ${sql(random_name)} ${sql(users)}`;
|
||
|
|
|
||
|
|
try {
|
||
|
|
await sql`UPDATE ${sql(random_name)} SET ${sql({ name: undefined, age: undefined })} WHERE id IN ${sql([1, 2])}`;
|
||
|
|
expect.unreachable();
|
||
|
|
} catch (e) {
|
||
|
|
expect(e).toBeInstanceOf(SyntaxError);
|
||
|
|
expect(e.message).toBe("Update needs to have at least one column");
|
||
|
|
}
|
||
|
|
});
|
||
|
|
|
||
|
|
test("upsert helper", async () => {
|
||
|
|
await using sql = new SQL({ ...getOptions(), max: 1 });
|
||
|
|
const random_name = "test_" + randomUUIDv7("hex").replaceAll("-", "");
|
||
|
|
await sql`
|
||
|
|
CREATE TABLE IF NOT EXISTS ${sql(random_name)} (
|
||
|
|
id int PRIMARY KEY,
|
||
|
|
foo text NOT NULL,
|
||
|
|
email VARCHAR(255) NOT NULL UNIQUE
|
||
|
|
)
|
||
|
|
`;
|
||
|
|
|
||
|
|
const data = { id: 1, foo: "hello", email: "[email protected]" };
|
||
|
|
await sql`
|
||
|
|
INSERT INTO ${sql(random_name)} ${sql(data)}
|
||
|
|
ON DUPLICATE KEY UPDATE ${sql(data)}
|
||
|
|
`;
|
||
|
|
let id = 0;
|
||
|
|
{
|
||
|
|
const result = await sql`SELECT * FROM ${sql(random_name)}`;
|
||
|
|
expect(result[0].id).toBeDefined();
|
||
|
|
expect(result[0].foo).toBe("hello");
|
||
|
|
expect(result[0].email).toBe("[email protected]");
|
||
|
|
id = result.lastInsertRowid;
|
||
|
|
}
|
||
|
|
|
||
|
|
{
|
||
|
|
const data = { foo: "hello2", email: "[email protected]" };
|
||
|
|
await sql`
|
||
|
|
INSERT INTO ${sql(random_name)} ${sql({ id, ...data })}
|
||
|
|
ON DUPLICATE KEY UPDATE ${sql(data)}
|
||
|
|
`;
|
||
|
|
const result = await sql`SELECT * FROM ${sql(random_name)}`;
|
||
|
|
expect(result[0].id).toBeDefined();
|
||
|
|
expect(result[0].foo).toBe("hello2");
|
||
|
|
expect(result[0].email).toBe("[email protected]");
|
||
|
|
}
|
||
|
|
|
||
|
|
{
|
||
|
|
const data = { foo: "hello3", email: "[email protected]" };
|
||
|
|
await sql`
|
||
|
|
INSERT INTO ${sql(random_name)} ${sql({ id, ...data })}
|
||
|
|
ON DUPLICATE KEY UPDATE ${sql(data)}
|
||
|
|
`;
|
||
|
|
const result = await sql`SELECT * FROM ${sql(random_name)}`;
|
||
|
|
expect(result[0].id).toBeDefined();
|
||
|
|
expect(result[0].foo).toBe("hello3");
|
||
|
|
expect(result[0].email).toBe("[email protected]");
|
||
|
|
}
|
||
|
|
});
|
||
|
|
test("update helper with IN and column name", async () => {
|
||
|
|
await using sql = new SQL({ ...getOptions(), max: 1 });
|
||
|
|
const random_name = "test_" + randomUUIDv7("hex").replaceAll("-", "");
|
||
|
|
await sql`CREATE TEMPORARY TABLE ${sql(random_name)} (id int, name text, age int)`;
|
||
|
|
const users = [
|
||
|
|
{ id: 1, name: "John", age: 30 },
|
||
|
|
{ id: 2, name: "Jane", age: 25 },
|
||
|
|
];
|
||
|
|
await sql`INSERT INTO ${sql(random_name)} ${sql(users)}`;
|
||
|
|
|
||
|
|
await sql`UPDATE ${sql(random_name)} SET ${sql({ name: "Mary", age: 18 })} WHERE id IN ${sql(users, "id")}`;
|
||
|
|
const result = await sql`SELECT * FROM ${sql(random_name)}`;
|
||
|
|
expect(result[0].id).toBe(1);
|
||
|
|
expect(result[0].name).toBe("Mary");
|
||
|
|
expect(result[0].age).toBe(18);
|
||
|
|
expect(result[1].id).toBe(2);
|
||
|
|
expect(result[1].name).toBe("Mary");
|
||
|
|
expect(result[1].age).toBe(18);
|
||
|
|
});
|
||
|
|
|
||
|
|
test("update multiple values no helper", async () => {
|
||
|
|
await using sql = new SQL({ ...getOptions(), max: 1 });
|
||
|
|
const random_name = "test_" + randomUUIDv7("hex").replaceAll("-", "");
|
||
|
|
await sql`CREATE TEMPORARY TABLE ${sql(random_name)} (id int, name text, age int)`;
|
||
|
|
await sql`INSERT INTO ${sql(random_name)} ${sql({ id: 1, name: "John", age: 30 })}`;
|
||
|
|
await sql`UPDATE ${sql(random_name)} SET ${sql("name")} = ${"Mary"}, ${sql("age")} = ${18} WHERE id = 1`;
|
||
|
|
const result = await sql`SELECT * FROM ${sql(random_name)} WHERE id = 1`;
|
||
|
|
expect(result[0].id).toBe(1);
|
||
|
|
expect(result[0].name).toBe("Mary");
|
||
|
|
expect(result[0].age).toBe(18);
|
||
|
|
});
|
||
|
|
|
||
|
|
test("SELECT with IN and NOT IN", async () => {
|
||
|
|
await using sql = new SQL({ ...getOptions(), max: 1 });
|
||
|
|
const random_name = "test_" + randomUUIDv7("hex").replaceAll("-", "");
|
||
|
|
await sql`CREATE TEMPORARY TABLE ${sql(random_name)} (id int, name text, age int)`;
|
||
|
|
const users = [
|
||
|
|
{ id: 1, name: "John", age: 30 },
|
||
|
|
{ id: 2, name: "Jane", age: 25 },
|
||
|
|
];
|
||
|
|
await sql`INSERT INTO ${sql(random_name)} ${sql(users)}`;
|
||
|
|
|
||
|
|
const result =
|
||
|
|
await sql`SELECT * FROM ${sql(random_name)} WHERE id IN ${sql(users, "id")} and id NOT IN ${sql([3, 4, 5])}`;
|
||
|
|
expect(result[0].id).toBe(1);
|
||
|
|
expect(result[0].name).toBe("John");
|
||
|
|
expect(result[0].age).toBe(30);
|
||
|
|
expect(result[1].id).toBe(2);
|
||
|
|
expect(result[1].name).toBe("Jane");
|
||
|
|
expect(result[1].age).toBe(25);
|
||
|
|
});
|
||
|
|
|
||
|
|
test("syntax error", async () => {
|
||
|
|
await using sql = new SQL({ ...getOptions(), max: 1 });
|
||
|
|
const random_name = "test_" + randomUUIDv7("hex").replaceAll("-", "");
|
||
|
|
const users = [
|
||
|
|
{ id: 1, name: "John", age: 30 },
|
||
|
|
{ id: 2, name: "Jane", age: 25 },
|
||
|
|
];
|
||
|
|
|
||
|
|
expect(() => sql`DELETE FROM ${sql(random_name)} ${sql(users, "id")}`.execute()).toThrow(SyntaxError);
|
||
|
|
});
|
||
|
|
},
|
||
|
|
);
|