Unable to create indexes on astro db #10451
Comments
|
cc @bholmesdev not sure if this is on the Studio or the DB side? |
|
It's on the studio |
|
@arch-fan Did this happen the first time you pushed or had you previously pushed and made some schema changes and then pushed again? |
|
It was the first time I pushed to studio |
|
Can you try to push again and let me know if you still get this error? Also, if you do, what version of |
|
Still getting the same error: Sure, these are the versions: |
|
Here you have the whole definition of the tables: import { defineDb, defineTable, column, NOW } from "astro:db";
const Categories = defineTable({
columns: {
id: column.number({ primaryKey: true }),
name: column.text({ unique: true }),
slug: column.text({ unique: true }),
},
indexes: {
slug_idx: {
on: "slug",
unique: true,
},
id_idx: {
on: "id",
unique: true,
},
},
});
const Authors = defineTable({
columns: {
id: column.number({ primaryKey: true }),
name: column.text(),
},
indexes: {
id_idx: {
on: "id",
unique: true,
},
},
});
const Posts = defineTable({
columns: {
id: column.number({ primaryKey: true }),
title: column.text({ unique: true }),
excerpt: column.text(),
slug: column.text({ unique: true }),
content: column.text(),
date: column.date({ default: NOW }),
categoryId: column.number({ references: () => Categories.columns.id }),
authorId: column.number({ references: () => Authors.columns.id }),
},
indexes: {
slug_idx: {
on: "slug",
unique: true,
},
title_excerpt_idx: {
on: ["title", "excerpt"],
},
},
});
// https://astro.build/db/config
export default defineDb({
tables: { Categories, Authors, Posts },
}); |
|
Can you try with |
|
Same |
|
Ok, thank you, this is helpful. To clarify, you have tried the example you provided and get the problem there? |
|
Yep, is the same example. It also happens if I change the indexes names: import { defineDb, defineTable, column, NOW } from "astro:db";
const Categories = defineTable({
columns: {
id: column.number({ primaryKey: true }),
name: column.text({ unique: true }),
slug: column.text({ unique: true }),
},
indexes: {
slug2_idx: {
on: "slug",
unique: true,
},
id2_idx: {
on: "id",
unique: true,
},
},
});
const Authors = defineTable({
columns: {
id: column.number({ primaryKey: true }),
name: column.text(),
},
indexes: {
id2_idx: {
on: "id",
unique: true,
},
},
});
const Posts = defineTable({
columns: {
id: column.number({ primaryKey: true }),
title: column.text({ unique: true }),
excerpt: column.text(),
slug: column.text({ unique: true }),
content: column.text(),
date: column.date({ default: NOW }),
categoryId: column.number({ references: () => Categories.columns.id }),
authorId: column.number({ references: () => Authors.columns.id }),
},
indexes: {
slug2_idx: {
on: "slug",
unique: true,
},
title_excerpt2_idx: {
on: ["title", "excerpt"],
},
},
});
// https://astro.build/db/config
export default defineDb({
tables: { Categories, Authors, Posts },
});Error: |
|
@arch-fan That's not the same as the example, the example on has a Categories table. I just want to confirm, you can see this with the example and only the example? No extra code at all? |
|
I get an error after adding a second table. |
|
@arch-fan I think I know what's going on here. Indices names in SQLite are global to the database. So it looks like you have 2 indices with the same name, which is why it's producing this error. If you instead name them something like We should probably detect this and provide a better error message for you. |
|
That seemed to be the problem, thank you! By the way, Is this behavior expected? I had to delete them manually to be able to push them. |
|
I don't think you should get that error, so let me treat that as a separate bug and I'll investigate. For the indexing problem, I think we're going to fix with an API change, likely moving indexes out of the |
|
Also, when I delete tables from Astro Studio's console (didn't try with astro db cli) the tables don't update on the studio, but the db gets modified |
Astro Info
If this issue only occurs in one browser, which browser is a problem?
No response
Describe the Bug
Astro db push fails at creating the indexes. It doesn't matter the way I name the index, it throws the same error:
What's the expected result?
Indexes being created
Link to Minimal Reproducible Example
https://stackblitz.com/edit/github-deerzq?file=db%2Fconfig.ts
Participation
The text was updated successfully, but these errors were encountered: