> ## Documentation Index
> Fetch the complete documentation index at: https://docs.lancedb.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Basic Table Operations

> Create tables, search vectors, and append data in LanceDB.

export const RsBasicDropTable = "db.drop_table(\"camelot\", &[]).await.unwrap();\n";

export const RsBasicDropColumns = "table.drop_columns(&[\"power\"]).await.unwrap();\n";

export const RsBasicAddColumns = "table\n    .add_columns(\n        NewColumnTransform::SqlExpressions(vec![(\n            \"power\".to_string(),\n            \"cast(((stats.strength + stats.courage + stats.magic + stats.wisdom) / 4.0) as float)\"\n                .to_string(),\n        )]),\n        None,\n    )\n    .await\n    .unwrap();\n";

export const RsBasicDeleteRows = "table.delete(\"role = 'Traitor Knight'\").await.unwrap();\n";

export const RsBasicVectorSearchQ4 = "// Who are the strongest characters?\nlet r4 = table\n    .query()\n    .select(Select::Columns(vec![\n        \"name\".to_string(),\n        \"role\".to_string(),\n        \"description\".to_string(),\n        \"power\".to_string(),\n    ]))\n    .execute()\n    .await\n    .unwrap()\n    .try_collect::<Vec<_>>()\n    .await\n    .unwrap();\nprintln!(\"{r4:?}\");\n";

export const RsBasicVectorSearchQ3 = "// Who are the strongest characters?\nlet r3 = table\n    .query()\n    .only_if(\"stats.strength > 3\")\n    .select(Select::Columns(vec![\n        \"name\".to_string(),\n        \"role\".to_string(),\n        \"description\".to_string(),\n    ]))\n    .limit(5)\n    .execute()\n    .await\n    .unwrap()\n    .try_collect::<Vec<_>>()\n    .await\n    .unwrap();\nprintln!(\"{r3:?}\");\n";

export const RsBasicVectorSearchQ2 = "// Who are the characters similar to \"wizard\" with high magic stats?\nlet query_vector_2 = [0.03, 0.85, 0.61, 0.90];\nlet r2 = table\n    .query()\n    .nearest_to(&query_vector_2)\n    .unwrap()\n    .only_if(\"stats.magic > 3\")\n    .select(Select::Columns(vec![\n        \"name\".to_string(),\n        \"role\".to_string(),\n        \"description\".to_string(),\n    ]))\n    .limit(5)\n    .execute()\n    .await\n    .unwrap()\n    .try_collect::<Vec<_>>()\n    .await\n    .unwrap();\nprintln!(\"{r2:?}\");\n";

export const RsBasicVectorSearchQ1 = "// Who are the characters similar to  \"wizard\"?\nlet query_vector_1 = [0.03, 0.85, 0.61, 0.90];\nlet r1 = table\n    .query()\n    .nearest_to(&query_vector_1)\n    .unwrap()\n    .limit(5)\n    .select(Select::Columns(vec![\n        \"name\".to_string(),\n        \"role\".to_string(),\n        \"description\".to_string(),\n    ]))\n    .execute()\n    .await\n    .unwrap()\n    .try_collect::<Vec<_>>()\n    .await\n    .unwrap();\nprintln!(\"{r1:?}\");\n";

export const RsBasicAddData = "let magical_characters = vec![\n    Character {\n        id: 9,\n        name: \"Morgan le Fay\".to_string(),\n        role: \"Sorceress\".to_string(),\n        description: \"A powerful enchantress, Arthur's half-sister, and a complex figure who oscillates between aiding and opposing Camelot.\".to_string(),\n        vector: [0.10, 0.84, 0.25, 0.70],\n        stats: Stats {\n            strength: 2,\n            courage: 3,\n            magic: 5,\n            wisdom: 4,\n        },\n    },\n    Character {\n        id: 10,\n        name: \"The Lady of the Lake\".to_string(),\n        role: \"Mystical Guardian\".to_string(),\n        description: \"A mysterious supernatural figure associated with Avalon, known for giving Arthur the sword Excalibur.\".to_string(),\n        vector: [0.00, 0.90, 0.58, 0.88],\n        stats: Stats {\n            strength: 2,\n            courage: 3,\n            magic: 5,\n            wisdom: 5,\n        },\n    },\n];\ntable\n    .add(characters_to_reader(camelot_schema(), &magical_characters))\n    .execute()\n    .await\n    .unwrap();\n";

export const RsBasicCreateEmptyTable = "let schema = Arc::new(Schema::new(vec![\n    Field::new(\"id\", DataType::Int16, false),\n    Field::new(\"name\", DataType::Utf8, false),\n    Field::new(\"role\", DataType::Utf8, false),\n    Field::new(\"description\", DataType::Utf8, false),\n    Field::new(\n        \"vector\",\n        DataType::FixedSizeList(Arc::new(Field::new(\"item\", DataType::Float32, true)), 4),\n        false,\n    ),\n    Field::new(\n        \"stats\",\n        DataType::Struct(arrow_schema::Fields::from(vec![\n            Arc::new(Field::new(\"strength\", DataType::Int8, false)),\n            Arc::new(Field::new(\"courage\", DataType::Int8, false)),\n            Arc::new(Field::new(\"magic\", DataType::Int8, false)),\n            Arc::new(Field::new(\"wisdom\", DataType::Int8, false)),\n        ])),\n        false,\n    ),\n]));\ndb.create_empty_table(\"camelot_empty\", schema)\n    .mode(CreateTableMode::Overwrite)\n    .execute()\n    .await\n    .unwrap();\n";

export const RsBasicOpenTable = "table = db.open_table(\"camelot\").execute().await.unwrap();\n";

export const RsBasicCreateTable = "let mut table = db\n    .create_table(\"camelot\", characters_to_reader(schema.clone(), &data))\n    .mode(CreateTableMode::Overwrite)\n    .execute()\n    .await\n    .unwrap();\n";

export const RsDataLoad = "let data: Vec<Character> =\n    serde_json::from_str(&fs::read_to_string(camelot_json_path()).unwrap()).unwrap();\n";

export const RsBasicImports = "use arrow_array::types::Float32Type;\nuse arrow_array::{\n    FixedSizeListArray, Int8Array, Int16Array, RecordBatch, RecordBatchIterator, StringArray,\n    StructArray,\n};\nuse arrow_schema::{DataType, Field, FieldRef, Schema};\nuse futures_util::TryStreamExt;\nuse lancedb::database::CreateTableMode;\nuse lancedb::query::{ExecutableQuery, QueryBase, Select};\nuse lancedb::{connect, table::NewColumnTransform};\n";

export const TsBasicDropTable = "await db.dropTable(\"camelot\");\n";

export const TsBasicDropColumns = "await table.dropColumns([\"power\"]);\n";

export const TsBasicAddColumns = "await table.addColumns([\n  {\n    name: \"power\",\n    valueSql:\n      \"cast(((stats.strength + stats.courage + stats.magic + stats.wisdom) / 4.0) as float)\",\n  },\n]);\n";

export const TsBasicAddData = "const magicalCharacters = [\n  {\n    id: 9,\n    name: \"Morgan le Fay\",\n    role: \"Sorceress\",\n    description:\n      \"A powerful enchantress, Arthur's half-sister, and a complex figure who oscillates between aiding and opposing Camelot.\",\n    vector: [0.1, 0.84, 0.25, 0.7],\n    stats: { strength: 2, courage: 3, magic: 5, wisdom: 4 },\n  },\n  {\n    id: 10,\n    name: \"The Lady of the Lake\",\n    role: \"Mystical Guardian\",\n    description:\n      \"A mysterious supernatural figure associated with Avalon, known for giving Arthur the sword Excalibur.\",\n    vector: [0.0, 0.9, 0.58, 0.88],\n    stats: { strength: 2, courage: 3, magic: 5, wisdom: 5 },\n  },\n];\nawait table.add(magicalCharacters);\n";

export const TsBasicDeleteRows = "await table.delete('role = \"Traitor Knight\"');\n";

export const TsBasicVectorSearchQ4 = "// Who are the strongest characters?\nconst r4 = await table\n  .query()\n  .select([\"name\", \"role\", \"description\", \"power\"])\n  .toArray();\nconsole.log(r4);\n";

export const TsBasicVectorSearchQ3 = "// Who are the strongest characters?\nconst r3 = await table\n  .query()\n  .where(\"stats.strength > 3\")\n  .select([\"name\", \"role\", \"description\"])\n  .limit(5)\n  .toArray();\nconsole.log(r3);\n";

export const TsBasicVectorSearchQ2 = "// Who are the characters similar to \"wizard\" with high magic stats?\nconst queryVector2 = [0.03, 0.85, 0.61, 0.9];\nconst r2 = await table\n  .search(queryVector2)\n  .where(\"stats.magic > 3\")\n  .select([\"name\", \"role\", \"description\"])\n  .limit(5)\n  .toArray();\nconsole.log(r2);\n";

export const TsBasicVectorSearchQ1 = "// Who are the characters similar to  \"wizard\"?\nconst queryVector1 = [0.03, 0.85, 0.61, 0.9];\nconst r1 = await table\n  .search(queryVector1)\n  .limit(5)\n  .select([\"name\", \"role\", \"description\"])\n  .toArray();\nconsole.log(r1);\n";

export const TsBasicCreateEmptyTable = "const schema = new arrow.Schema([\n  new arrow.Field(\"id\", new arrow.Int16()),\n  new arrow.Field(\"name\", new arrow.Utf8()),\n  new arrow.Field(\"role\", new arrow.Utf8()),\n  new arrow.Field(\"description\", new arrow.Utf8()),\n  new arrow.Field(\n    \"vector\",\n    new arrow.FixedSizeList(\n      4,\n      new arrow.Field(\"item\", new arrow.Float32(), true),\n    ),\n  ),\n  new arrow.Field(\n    \"stats\",\n    new arrow.Struct([\n      new arrow.Field(\"strength\", new arrow.Int8()),\n      new arrow.Field(\"courage\", new arrow.Int8()),\n      new arrow.Field(\"magic\", new arrow.Int8()),\n      new arrow.Field(\"wisdom\", new arrow.Int8()),\n    ]),\n  ),\n]);\nawait db.createEmptyTable(\"camelot_empty\", schema, { mode: \"overwrite\" });\n";

export const TsBasicOpenTable = "table = await db.openTable(\"camelot\");\n";

export const TsBasicCreateTable = "let table = await db.createTable(\"camelot\", data, {\n   mode: \"overwrite\",\n});\n";

export const TsDataLoad = "const data = JSON.parse(fs.readFileSync(dataPath, \"utf-8\"));\n";

export const TsBasicImports = "import * as lancedb from \"@lancedb/lancedb\";\nimport * as arrow from \"apache-arrow\";\n";

export const PyBasicDropTable = "db.drop_table(\"camelot\")\n";

export const PyBasicDropColumns = "table.drop_columns([\"power\"])\n";

export const PyBasicAddColumns = "table.add_columns(\n    {\n        \"power\": \"cast(((stats.strength + stats.courage + stats.magic + stats.wisdom) / 4.0) as float)\"\n    }\n)\n";

export const PyBasicAddData = "magical_characters = [\n    {\n        \"id\": 9,\n        \"name\": \"Morgan le Fay\",\n        \"role\": \"Sorceress\",\n        \"description\": \"A powerful enchantress, Arthur's half-sister, and a complex figure who oscillates between aiding and opposing Camelot.\",\n        \"vector\": [0.10, 0.84, 0.25, 0.70],\n        \"stats\": { \"strength\": 2, \"courage\": 3, \"magic\": 5, \"wisdom\": 4 }\n    },\n    {\n        \"id\": 10,\n        \"name\": \"The Lady of the Lake\",\n        \"role\": \"Mystical Guardian\",\n        \"description\": \"A mysterious supernatural figure associated with Avalon, known for giving Arthur the sword Excalibur.\",\n        \"vector\": [0.00, 0.90, 0.58, 0.88],\n        \"stats\": { \"strength\": 2, \"courage\": 3, \"magic\": 5, \"wisdom\": 5 }\n    }\n]\ntable.add(magical_characters)\n";

export const PyBasicDeleteRows = "table.delete('role = \"Traitor Knight\"')\n";

export const PyBasicVectorSearchQ4 = "# Who are the strongest characters?\nr4 = (\n    table.search()\n    .select([\"name\", \"role\", \"description\", \"power\"])\n    .to_polars()\n)\nprint(r4)\n";

export const PyBasicVectorSearchQ3 = "# Who are the strongest characters?\nr3 = (\n    table.search()\n    .where(\"stats.strength > 3\")\n    .select([\"name\", \"role\", \"description\"])\n    .limit(5)\n    .to_polars()\n)\nprint(r3)\n";

export const PyBasicVectorSearchQ2 = "# Who are the characters with high magic stats?\nquery_vector_2 = [0.03, 0.85, 0.61, 0.90]\nr2 = (\n    table.search(query_vector_2)\n    .where(\"stats.magic > 3\")\n    .select([\"name\", \"role\", \"description\"])\n    .limit(5)\n    .to_polars()\n)\nprint(r2)\n";

export const PyBasicVectorSearchQ1 = "# Who are the characters similar to  \"wizard\"?\nquery_vector_1 = [0.03, 0.85, 0.61, 0.90]\nr1 = (\n    table.search(query_vector_1)\n    .limit(5)\n    .select([\"name\", \"role\", \"description\"])\n    .to_polars()\n)\nprint(r1)\n";

export const PyBasicVectorSearch = "query_vector = [0.03, 0.85, 0.61, 0.90]\nresult = table.search(query_vector).limit(5).to_polars()\nprint(result)\n";

export const PyBasicAsyncApi = "import lancedb\n\nasync_db = await lancedb.connect_async(uri)\nasync_table = await async_db.create_table(\n    \"camelot_async\",\n    data=data,\n    mode=\"overwrite\",\n)\n\nquery_vector = [0.03, 0.85, 0.61, 0.90]\nasync_results = await (\n    await async_table.search(query_vector)\n).limit(5).select([\"name\", \"role\", \"description\"]).to_polars()\nprint(async_results)\n";

export const PyBasicCreateTablePolars = "polars_df = pl.DataFrame(data)\ntable_pl = db.create_table(\"camelot_pl\", data=polars_df, mode=\"overwrite\")\n";

export const PyBasicCreateTablePandas = "pandas_df = pd.DataFrame(data)\ntable_pd = db.create_table(\"camelot_pd\", data=pandas_df, mode=\"overwrite\")\n";

export const PyBasicCreateEmptyTable = "schema = pa.schema(\n    [\n        pa.field(\"id\", pa.uint16()),\n        pa.field(\"name\", pa.string()),\n        pa.field(\"role\", pa.string()),\n        pa.field(\"description\", pa.string()),\n        pa.field(\"vector\", pa.list_(pa.float32(), 4)),\n        pa.field(\n            \"stats\",\n            pa.struct(\n                [\n                    pa.field(\"strength\", pa.int8()),\n                    pa.field(\"courage\", pa.int8()),\n                    pa.field(\"magic\", pa.int8()),\n                    pa.field(\"wisdom\", pa.int8()),\n                ]\n            ),\n        ),\n    ]\n)\ndb.create_table(\"camelot_pa\", schema=schema, mode=\"overwrite\")\n";

export const PyBasicOpenTable = "table = db.open_table(\"camelot\")\n";

export const PyBasicCreateTable = "table = db.create_table(\"camelot\", data=data, mode=\"overwrite\")\n";

export const PyDataLoad = "with open(data_path, \"r\") as f:\n    data = json.load(f)\n";

export const PyBasicImports = "import json\n\nimport lancedb\nimport pandas as pd\nimport polars as pl\nimport pyarrow as pa\n";

export const RsConnectEnterprise = "let uri = \"db://your-database-uri\";\nlet api_key = \"your-api-key\";\nlet region = \"us-east-1\";\n";

export const RsConnect = "async fn connect_example(uri: &str) {\n    let db = connect(uri).execute().await.unwrap();\n    let _ = db;\n}\n";

export const TsConnectEnterprise = "const uri = \"db://your-database-uri\";\nconst apiKey = \"your-api-key\";\nconst region = \"us-east-1\";\n";

export const TsConnect = "import * as lancedb from \"@lancedb/lancedb\";\n\nasync function connectExample(uri: string) {\n  const db = await lancedb.connect(uri);\n  return db;\n}\n";

export const PyConnectEnterprise = "uri = \"db://your-database-uri\"\napi_key = \"your-api-key\"\nregion = \"us-east-1\"\n";

export const PyConnect = "import lancedb\n\nuri = \"ex_lancedb\"\ndb = lancedb.connect(uri)\n";

Now that you've completed the [LanceDB quickstart](/quickstart), you're ready to
explore some more table operations you'll typically need when working with LanceDB.

* **Ingest data into tables** from JSON data (and in Python, Pandas or Polars DataFrames)
* **Create empty tables** by defining explicit Arrow schemas
* **Vector similarity search** with filtering and projections
* **Filtered queries** that can operate on nested structs
* **Query Lance tables in DuckDB** via the Lance extension for SQL analytics (including joins)

<Note title="Python async users">
  This page uses **synchronous** Python snippets. If your Python app uses `asyncio`,
  the same flow works with `connect_async(...)` and `await`-based table/query calls.
  Use the example below as a template, and see [Quickstart](/quickstart#python-sync-and-async-apis)
  for example snippets on both sync and async Python usage.
</Note>

## Dataset

We'll work with this small dataset based on characters from the legends of Camelot. Note that
the `vector` column holds 4-dimensional embeddings, and the `stats` column is a nested struct
with several integer fields, indicating each character's attributes.

```json camelot.json icon="brackets-curly" expandable=true theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
[
  {
    "id": 1,
    "name": "King Arthur",
    "role": "King of Camelot",
    "description": "The legendary ruler of Camelot, wielder of Excalibur, and leader of the Knights of the Round Table.",
    "vector": [0.72, -0.28, 0.60, 0.86],
    "stats": { "strength": 2, "courage": 5, "magic": 1, "wisdom": 4 }
  },
  {
    "id": 2,
    "name": "Merlin",
    "role": "Wizard and Advisor",
    "description": "A powerful wizard and prophet who mentors Arthur and shapes the destiny of Camelot through magic and foresight.",
    "vector": [0.05, 0.88, 0.62, 0.85],
    "stats": { "strength": 2, "courage": 4, "magic": 5, "wisdom": 5 }
  },
  {
    "id": 3,
    "name": "Queen Guinevere",
    "role": "Queen of Camelot",
    "description": "Arthur's queen, admired for her grace and diplomacy, whose romances and loyalties influence Camelot's fate.",
    "vector": [0.22, -0.22, 0.42, 0.82],
    "stats": { "strength": 1, "courage": 3, "magic": 1, "wisdom": 4 }
  },
  {
    "id": 4,
    "name": "Sir Lancelot",
    "role": "Knight of the Round Table",
    "description": "Arthur's most skilled knight, famed for unmatched combat prowess and his tragic love for Queen Guinevere.",
    "vector": [0.86, -0.35, 0.38, 0.55],
    "stats": { "strength": 5, "courage": 5, "magic": 1, "wisdom": 3 }
  },
  {
    "id": 5,
    "name": "Sir Gawain",
    "role": "Knight of the Round Table",
    "description": "A noble and honorable knight known for his courtesy and his encounter with the Green Knight.",
    "vector": [0.82, -0.32, 0.52, 0.60],
    "stats": { "strength": 4, "courage": 5, "magic": 1, "wisdom": 4 }
  },
  {
    "id": 6,
    "name": "Sir Galahad",
    "role": "Knight of the Round Table",
    "description": "The purest and most virtuous knight, chosen to achieve the Holy Grail due to his unwavering spiritual purity.",
    "vector": [0.80, -0.20, 0.70, 0.78],
    "stats": { "strength": 4, "courage": 5, "magic": 2, "wisdom": 5 }
  },
  {
    "id": 7,
    "name": "Sir Percival",
    "role": "Knight of the Round Table",
    "description": "A loyal and innocent knight whose bravery and sincerity make him one of the key seekers of the Holy Grail.",
    "vector": [0.78, -0.36, 0.48, 0.52],
    "stats": { "strength": 4, "courage": 4, "magic": 1, "wisdom": 3 }
  },
  {
    "id": 8,
    "name": "Mordred",
    "role": "Traitor Knight",
    "description": "Arthur's treacherous son or nephew who ultimately rebels against him, leading to Camelot's downfall.",
    "vector": [0.68, -0.30, -0.65, 0.20],
    "stats": { "strength": 4, "courage": 2, "magic": 1, "wisdom": 2 }
  }
]
```

<Note>
  The `vector` arrays here are synthetic and for demonstration purposes only. In your real-world
  applications, you'd generate these vectors from the raw text fields using a suitable embedding model.
</Note>

## Connect to a database

### Option 1: Direct table access

We start by connecting to a LanceDB database path. The example below uses a local path in LanceDB OSS.

<CodeGroup>
  <CodeBlock filename="Python" language="Python" icon="Python">
    {PyConnect}
  </CodeBlock>

  <CodeBlock filename="TypeScript" language="TypeScript" icon="square-js">
    {TsConnect}
  </CodeBlock>

  <CodeBlock filename="Rust" language="Rust" icon="rust">
    {RsConnect}
  </CodeBlock>
</CodeGroup>

You can also connect LanceDB OSS directly to object storage. For credentials, endpoints, and provider-specific options, see
[Configuring storage](/storage/configuration).

### Option 2: Remote tables

If you're using LanceDB [Enterprise](/enterprise), you can connect using a `db://` URI,
along with any necessary credentials. Simply replace the local path with a remote `uri`
that points to where your data is stored, and you're ready to go.

<CodeGroup>
  <CodeBlock filename="Python" language="Python" icon="python">
    {PyConnectEnterprise}
  </CodeBlock>

  <CodeBlock filename="TypeScript" language="TypeScript" icon="square-js">
    {TsConnectEnterprise}
  </CodeBlock>

  <CodeBlock filename="Rust" language="Rust" icon="rust">
    {RsConnectEnterprise}
  </CodeBlock>
</CodeGroup>

<Note>
  * When you connect to a remote URI (Enterprise), `open_table(...)` returns a *remote* table.
    Remote tables support core operations (ingest, search, update, delete), but some convenience
    methods for bulk data export are not available.
  * In the Python SDK, `table.to_arrow()` and `table.to_pandas()` are not implemented for remote tables.
    To retrieve data, use search queries instead: `table.search(query).limit(n).to_arrow()`.
</Note>

## Create a table and ingest data

### From JSON

LanceDB stores records in Lance tables. Each row is a record and each column
holds a field or related metadata. The simplest way to start is to obtain the source
data as a list of JSON records that includes a vector column and any metadata
fields you care about.

<CodeGroup>
  <CodeBlock filename="Python" language="Python" icon="Python">
    {PyBasicImports}
  </CodeBlock>

  <CodeBlock filename="TypeScript" language="TypeScript" icon="square-js">
    {TsBasicImports}
  </CodeBlock>

  <CodeBlock filename="Rust" language="Rust" icon="rust">
    {RsBasicImports}
  </CodeBlock>
</CodeGroup>

Load the data from the JSON file:

<CodeGroup>
  <CodeBlock filename="Python" language="Python" icon="Python">
    {PyDataLoad}
  </CodeBlock>

  <CodeBlock filename="TypeScript" language="TypeScript" icon="square-js">
    {TsDataLoad}
  </CodeBlock>

  <CodeBlock filename="Rust" language="Rust" icon="rust">
    {RsDataLoad}
  </CodeBlock>
</CodeGroup>

You can now create a LanceDB table from the loaded data. Use the `mode="overwrite"` to
replace any existing table with the same name and overwrite its data (useful during
initial testing).

<CodeGroup>
  <CodeBlock filename="Python" language="Python" icon="Python">
    {PyBasicCreateTable}
  </CodeBlock>

  <CodeBlock filename="TypeScript" language="TypeScript" icon="square-js">
    {TsBasicCreateTable}
  </CodeBlock>

  <CodeBlock filename="Rust" language="Rust" icon="rust">
    {RsBasicCreateTable}
  </CodeBlock>
</CodeGroup>

<Info>
  If you want to avoid overwriting an existing table, omit the overwrite mode.
</Info>

### From Pandas DataFrames

<Badge color="green">Python Only</Badge>

You can create LanceDB tables directly from [Pandas](https://pandas.pydata.org/) DataFrames. Simply
obtain the source data as a Pandas DataFrame, then create the table
and directly ingest to it.

<CodeGroup>
  <CodeBlock filename="Python" language="Python" icon="Python">
    {PyBasicCreateTablePandas}
  </CodeBlock>
</CodeGroup>

### From Polars DataFrames

<Badge color="green">Python Only</Badge>

You can also create LanceDB tables directly from [Polars](https://www.pola.rs/) DataFrames. Simply
obtain the source data as a Polars DataFrame, then create the table
and directly ingest to it.

<CodeGroup>
  <CodeBlock filename="Python" language="Python" icon="Python">
    {PyBasicCreateTablePolars}
  </CodeBlock>
</CodeGroup>

### From an Arrow schema

If you want to create an *empty* table without any data -- say you want to
define the schema first and then incrementally add data later -- you can
do so by defining an Arrow schema explicitly.

<CodeGroup>
  <CodeBlock filename="Python" language="Python" icon="Python">
    {PyBasicCreateEmptyTable}
  </CodeBlock>

  <CodeBlock filename="TypeScript" language="TypeScript" icon="square-js">
    {TsBasicCreateEmptyTable}
  </CodeBlock>

  <CodeBlock filename="Rust" language="Rust" icon="rust">
    {RsBasicCreateEmptyTable}
  </CodeBlock>
</CodeGroup>

Once the empty table is defined, LanceDB is ready to accept new data via
the `add` method, as shown in the next section.

<Card title="Display table schema" icon="book">
  LanceDB tables are type-aware, leveraging Apache Arrow under the hood.
  You can display a given table's schema using the `schema` property or
  method. For example, in Python, running `print(table.schema)` would show
  something like the following:

  ```txt expandable=true theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
  id: int64
  name: string
  role: string
  description: string
  vector: fixed_size_list<item: float>[4]
    child 0, item: float
  stats: struct<courage: int64, magic: int64, strength: int64, wisdom: int64>
    child 0, courage: int64
    child 1, magic: int64
    child 2, strength: int64
    child 3, wisdom: int64
  ```
</Card>

## Append data to a table

LanceDB tables are mutable, and you can append new records to existing tables.
If you're starting with a fresh session, connect to the database and open the
existing table named `camelot`.

<CodeGroup>
  <CodeBlock filename="Python" language="Python" icon="Python">
    {PyBasicOpenTable}
  </CodeBlock>

  <CodeBlock filename="TypeScript" language="TypeScript" icon="square-js">
    {TsBasicOpenTable}
  </CodeBlock>

  <CodeBlock filename="Rust" language="Rust" icon="rust">
    {RsBasicOpenTable}
  </CodeBlock>
</CodeGroup>

Prepare the new records to add. Here, we add two new magical characters
via the `add` method. For the Rust snippet, you can find the helper functions in the
[code](https://github.com/lancedb/docs/blob/main/tests/rs/basic_usage.rs).

<CodeGroup>
  <CodeBlock filename="Python" language="Python" icon="Python">
    {PyBasicAddData}
  </CodeBlock>

  <CodeBlock filename="TypeScript" language="TypeScript" icon="square-js">
    {TsBasicAddData}
  </CodeBlock>

  <CodeBlock filename="Rust" language="Rust" icon="rust">
    {RsBasicAddData}
  </CodeBlock>
</CodeGroup>

We now have two new records in the table. Let's begin to query our data!

## Vector search

It's straightforward to run vector similarity search in LanceDB. Let's answer
some questions about the data using vector search with projections (returning only
the desired columns).

> Q1: *Who are the characters similar to "wizard"?*

<CodeGroup>
  <CodeBlock filename="Python" language="Python" icon="Python">
    {PyBasicVectorSearchQ1}
  </CodeBlock>

  <CodeBlock filename="TypeScript" language="TypeScript" icon="square-js">
    {TsBasicVectorSearchQ1}
  </CodeBlock>

  <CodeBlock filename="Rust" language="Rust" icon="rust">
    {RsBasicVectorSearchQ1}
  </CodeBlock>
</CodeGroup>

| name                 | role                      | description                     |
| -------------------- | ------------------------- | ------------------------------- |
| Merlin               | Wizard and Advisor        | A powerful wizard and prophet   |
| The Lady of the Lake | Mystical Guardian         | A mysterious supernatural figu… |
| Morgan le Fay        | Sorceress                 | A powerful enchantress, Arthur… |
| Queen Guinevere      | Queen of Camelot          | Arthur's queen, admired for he… |
| Sir Galahad          | Knight of the Round Table | The purest and most virtuous k… |

We have Merlin, The Lady of the Lake, and Morgan le Fay in the top results, who
all have magical abilities.

Next, let's try to answer a different question that involves vector search while
filtering on a nested struct field. Filtering is done using the `where` method,
into which you can pass SQL-like expressions.

> Q2: *Who are the characters similar to "wizard" with high magic stats?*

<CodeGroup>
  <CodeBlock filename="Python" language="Python" icon="Python">
    {PyBasicVectorSearchQ2}
  </CodeBlock>

  <CodeBlock filename="TypeScript" language="TypeScript" icon="square-js">
    {TsBasicVectorSearchQ2}
  </CodeBlock>

  <CodeBlock filename="Rust" language="Rust" icon="rust">
    {RsBasicVectorSearchQ2}
  </CodeBlock>
</CodeGroup>

| name                 | role               | description                     |
| -------------------- | ------------------ | ------------------------------- |
| Merlin               | Wizard and Advisor | A powerful wizard and prophet   |
| The Lady of the Lake | Mystical Guardian  | A mysterious supernatural figu… |
| Morgan le Fay        | Sorceress          | A powerful enchantress, Arthur… |

Only three characters have magical abilities greater than 3. Merlin is
clearly the most magical of them all!

## Filtered search

You can also run traditional analytics-style search queries that do not
involve vectors. For example, let's find the strongest characters in
the dataset. In the query below, we leave the `search` method empty to indicate
that we don't want to use any vector for similarity search (in TypeScript/Rust,
use `query()` instead), and use the `where` method to filter on the `strength` field.

> Q3: *Who are the strongest characters?*

<CodeGroup>
  <CodeBlock filename="Python" language="Python">
    {PyBasicVectorSearchQ3}
  </CodeBlock>

  <CodeBlock filename="TypeScript" language="TypeScript" icon="square-js">
    {TsBasicVectorSearchQ3}
  </CodeBlock>

  <CodeBlock filename="Rust" language="Rust" icon="rust">
    {RsBasicVectorSearchQ3}
  </CodeBlock>
</CodeGroup>

| name         | role                      | description                     |
| ------------ | ------------------------- | ------------------------------- |
| Sir Galahad  | Knight of the Round Table | The purest and most virtuous k… |
| Sir Gawain   | Knight of the Round Table | A noble and honorable knight k… |
| Sir Percival | Knight of the Round Table | A loyal and innocent knight wh… |
| Sir Lancelot | Knight of the Round Table | Arthur's most skilled knight, … |
| Mordred      | Traitor Knight            | Arthur's treacherous son or ne… |

Clearly, the strongest characters are all Knights of the Round Table!

<Note>
  Need SQL analytics like filters, aggregations, or joins on Lance tables? Use the DuckDB
  Lance extension to query Lance tables directly with SQL. See the
  [DuckDB integration guide](/integrations/data/duckdb).
</Note>

## Add column

We can also add new columns to an existing LanceDB table using the `add_columns` method.
For this example, let's add a new float column named `power` that shows the average
of each character's strength, courage, magic, and wisdom stats.

<CodeGroup>
  <CodeBlock filename="Python" language="Python">
    {PyBasicAddColumns}
  </CodeBlock>

  <CodeBlock filename="TypeScript" language="TypeScript" icon="square-js">
    {TsBasicAddColumns}
  </CodeBlock>

  <CodeBlock filename="Rust" language="Rust" icon="rust">
    {RsBasicAddColumns}
  </CodeBlock>
</CodeGroup>

The example above sums up the individual stats and divides by 4 to compute the average.
The resulting average total stats is cast to an Arrow float type under the hood for the
Lance table.

We can display the results of this column in descending order of power.

> Q4: *Who are the most powerful characters?*

<CodeGroup>
  <CodeBlock filename="Python" language="Python">
    {PyBasicVectorSearchQ4}
  </CodeBlock>

  <CodeBlock filename="TypeScript" language="TypeScript" icon="square-js">
    {TsBasicVectorSearchQ4}
  </CodeBlock>

  <CodeBlock filename="Rust" language="Rust" icon="rust">
    {RsBasicVectorSearchQ4}
  </CodeBlock>
</CodeGroup>

Note that LanceDB's `where` only filters rows, but doesn't sort them by applying an `ORDER BY`
clause that you may be used to when working with SQL databases.

You can also sort the results after converting them to a Polars DataFrame.
In TypeScript/Rust, you can sort the
returned array in application code.

```python Python icon="python" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
# Sort Polars DataFrame by power in descending order
print(r1.sort("power", descending=True).limit(5))
```

| name                 | role                      | description                     | power |
| -------------------- | ------------------------- | ------------------------------- | ----- |
| Merlin               | Wizard and Advisor        | A powerful wizard and prophet … | 4.0   |
| Sir Galahad          | Knight of the Round Table | The purest and most virtuous k… | 4.0   |
| The Lady of the Lake | Mystical Guardian         | A mysterious supernatural figu… | 3.75  |
| Sir Lancelot         | Knight of the Round Table | Arthur's most skilled knight, … | 3.5   |
| Sir Gawain           | Knight of the Round Table | A noble and honorable knight k… | 3.5   |

Merlin and Sir Galahad are the most powerful characters when considering the average of
all their abilities! Sir Lancelot and the Lady of the Lake follow closely behind.

## Delete data

You can delete rows from a LanceDB table using the `delete` method with
a filtering expression.

Say we want to remove Mordred, the traitor knight, from our table.

<CodeGroup>
  <CodeBlock filename="Python" language="Python">
    {PyBasicDeleteRows}
  </CodeBlock>

  <CodeBlock filename="TypeScript" language="TypeScript" icon="square-js">
    {TsBasicDeleteRows}
  </CodeBlock>

  <CodeBlock filename="Rust" language="Rust" icon="rust">
    {RsBasicDeleteRows}
  </CodeBlock>
</CodeGroup>

This will delete the row(s) where the `role` value matches "Traitor Knight".
You can verify that the row has been deleted by running a search query again,
and confirming that Mordred no longer appears in the results.

## Drop column

If you want to remove or delete a column from an existing LanceDB table, you can use
the `drop_columns` method.

<CodeGroup>
  <CodeBlock filename="Python" language="Python">
    {PyBasicDropColumns}
  </CodeBlock>

  <CodeBlock filename="TypeScript" language="TypeScript" icon="square-js">
    {TsBasicDropColumns}
  </CodeBlock>

  <CodeBlock filename="Rust" language="Rust" icon="rust">
    {RsBasicDropColumns}
  </CodeBlock>
</CodeGroup>

This will remove the `power` column we added earlier from the table schema.

## Drop table

If you want to delete an entire table from the database, you can use the
`drop_table` method.

<CodeGroup>
  <CodeBlock filename="Python" language="Python">
    {PyBasicDropTable}
  </CodeBlock>

  <CodeBlock filename="TypeScript" language="TypeScript" icon="square-js">
    {TsBasicDropTable}
  </CodeBlock>

  <CodeBlock filename="Rust" language="Rust" icon="rust">
    {RsBasicDropTable}
  </CodeBlock>
</CodeGroup>

This will delete the `camelot` table from the connected LanceDB database.

<Info>
  See the full code for these examples (including helper functions) in the
  `basic_usage` file for the appropriate client language in the
  [docs repo](https://github.com/lancedb/docs/tree/main/tests).
</Info>

## What about vector indexes?

LanceDB supports vector indexes to speed up similarity search on large datasets.
For datasets up to a few hundred thousand vectors, LanceDB's highly efficient kNN
(brute-force) retrieval of nearest neighbors is often sufficient. As your dataset
grows larger, you can create vector indexes on your vector columns to accelerate
search. See the [indexing](/indexing/) documentation for details on how to create and use
vector indexes in LanceDB.

## What's next?

Now that you've learned the basics of creating tables, adding data, running
vector search, and modifying table schemas, you're ready to explore more
advanced features of LanceDB. Below are some suggested next pages.

<Columns cols={2}>
  <Card title="Ingesting data" icon="cookie" href="/tables/create/">
    Learn the different approaches to creating and ingesting data into LanceDB tables from various sources.
  </Card>

  <Card title="Update and modify tables" icon="clone" href="/tables/update/">
    Learn how to update and modify existing LanceDB tables and their data.
  </Card>

  <Card title="Schema & data evolution" icon="boxes-stacked" href="/tables/schema/">
    Understand how to evolve your table schemas and data over time with LanceDB.
  </Card>

  <Card title="Versioning and time travel" icon="clock" href="/tables/versioning/">
    Explore LanceDB's built-in table versioning and time travel capabilities.
  </Card>
</Columns>