What LLMs do for schema drafts

Data model generation is the most immediate fit for large language models in a PostgreSQL context. Given a single sentence of description, an LLM returns a draft schema — tables, columns, indexes — that can be used as a starting point rather than something to write by hand from zero.

The tests here ran against a local deployment of Meta's llama3, a general-purpose model. Local execution is the point: no subscription and no external service, which matters in secure environments. The runtime is ollama, an open source tool that downloads, deploys and runs the model as a server behind a simple interface. On an Apple M1, macOS brings in the GPU automatically; NVIDIA hardware on Linux takes more setup but generally returns faster responses, provided the chip isn't outdated. Python is the convenient way to talk to a model, though the command line is enough for a first look.

From prompt to DDL

Starting ollama and asking a question straight from the shell is trivial:

Python

1

2

[hs@MacBook-Air ~]$ ollama run llama3

>>> Create a data model for PostgreSQL including indexes to store addresses.

The reply arrives as a markdown document. Read top to bottom, it opens with a polite greeting and then a workable SQL model, already reasonably indexed:

PgSQL

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

Here is an example of a data model for storing addresses in PostgreSQL:

```

CREATE TABLE addresses (

id SERIAL PRIMARY KEY,

street TEXT NOT NULL,

city TEXT NOT NULL,

state TEXT NOT NULL,

zip_code INTEGER NOT NULL,

country TEXT NOT NULL

);

CREATE INDEX idx_addresses_street ON addresses USING BTREE (street);

CREATE INDEX idx_addresses_city ON addresses USING BTREE (city);

CREATE INDEX idx_addresses_state ON addresses USING BTREE (state);

CREATE INDEX idx_addresses_zip_code ON addresses USING BTREE (zip_code);

```

The model also explains the choices it made, which is where the material becomes usable as documentation:

PgSQL

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

1. **Table name**: I chose `addresses` as the table name,

       which is self-explanatory.

2. **Columns**:

* `id`: A serial primary key column to uniquely identify

           each address record. This will automatically increment

           for each new row inserted into the table.

* `street`, `city`, `state`, and `country`: These are

           the main attributes of an address, which we want

           to store.

* `zip_code`: This is a numerical attribute that can

           be used to quickly filter or search for addresses

           within a specific geographic region.

3. **Indexes**: I created four indexes to improve query performance:

+ `idx_addresses_street`: An index on the `street`

           column to enable fast searching and filtering

           by street name.

+ `idx_addresses_city`: An index on the `city`

            column to support queries that filter or sort

            addresses by city.

+ `idx_addresses_state`: An index on the `state`

            column to facilitate searches or aggregations based on state.

+ `idx_addresses_zip_code`: An index on the `zip_code`

            column to speed up queries that involve searching

            or filtering by zip code.

Those indexes target the obvious access paths: address lookups within a street range, retrieval by city or state, and filtering on zip code. The model notes that BTREE was chosen because it covers most cases, and points at Hash and GiST or more elaborate strategies for very large datasets and advanced requirements. It closes by advising you to adjust the design to your own constraints.

That explanation is genuinely useful. It costs far less effort than producing equivalent text manually, and at minimum it can be edited and extended. The generated code usually runs in PostgreSQL as-is, with one recurring exception: when foreign keys are involved, the model may emit tables in the wrong order. Splitting the request — create the tables first, then ask for keys and indexes — avoids the problem.

Driving the model from Python

A small Python program shows how little is needed. It sends a sentence to the model and then pulls the SQL out of the response, since the reply mixes prose and code:

Python

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

#!/usr/bin/python3

import re

import ollama

def get_model_result(prompt, model):

res = ollama.chat(model, messages=[{

     'role': 'user',

     'content': prompt,

     "format": "json",

     "raw": "False"

}])

return res['message']['content']

def extract_code_from_markdown(markdown_text):

code_blocks = []

for block in re.findall(r'```(.*?)```', markdown_text, re.DOTALL):

     if block.startswith("sql"):

         code_blocks.append(block[3:])

     else:

         code_blocks.append(block)

result = ''.join(code_blocks)

return result

model_name = "llama3"

user_input = """

  Make me an SQL model for PostgreSQL including indexes

  and constraints which can store:

  customers, products, prices and product categories

"""

j = get_model_result(user_input, model_name)

res = extract_code_from_markdown(j)

print(res)

get_model_result takes the user input and hands it to the chosen model. extract_code_from_markdown dissects the answer and returns the SQL portion, so the result can be deployed and tested directly. The rest of the output could be extracted too, but that is a refinement rather than a necessity. The script ends by defining a question, running the model and printing what comes back, which can look like this:

PgSQL

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

CREATE TABLE customers (

id SERIAL PRIMARY KEY,

name VARCHAR(255) NOT NULL,

email VARCHAR(255) UNIQUE NOT NULL,

phone VARCHAR(20),

address VARCHAR(255)

);

CREATE TABLE product_categories (

id SERIAL PRIMARY KEY,

name VARCHAR(255) NOT NULL

);

CREATE TABLE products (

id SERIAL PRIMARY KEY,

name VARCHAR(255) NOT NULL,

description TEXT,

category_id INTEGER NOT NULL

REFERENCES product_categories(id),

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

);

CREATE TABLE prices (

id SERIAL PRIMARY KEY,

product_id INTEGER NOT NULL REFERENCES products(id),

price DECIMAL(10, 2) NOT NULL,

effective_date DATE NOT NULL DEFAULT CURRENT_DATE,

expires_date DATE,

PRIMARY KEY (product_id, effective_date)

);

CREATE INDEX idx_customers_email ON customers (email);

CREATE INDEX idx_products_category_id

ON products (category_id);

CREATE INDEX idx_prices_product_id_date

ON prices (product_id, effective_date);

The result is useful inspiration for extending a data model further. One sentence of input cannot compete with a human who has had no customer interaction, no subject knowledge and no background — under those constraints the model's output is respectable. The approach is being folded into tooling such as CYPEX.

For a more traditional treatment of trends and similar problems, see the write-up on string encoding in SQL for handling time series.